Where name created with “Application.Names.Add” go?

For learning and work purposes I needed to create mesage box that pop-ups first only first time opening workbook. For that task I found elegant solution in one of the old treads (old thread).

Working principle of sugested code is more or less clear to me exept part where named created with Application.Names.Add go? I checked under Forlulas -> name manager and did not find created name, I gues it went somwhere else?

Used Code:

Sub RunOnce()
Dim Flag As Boolean: Flag = False
For Each Item In Application.Names
If Item.Name = "FunctionHasRun" Then Flag = True
Next
If Flag = False Then
Call Application.Names.Add("FunctionHasRun", True, False)
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
Call RunOnceFunction
End If
End Sub


False

3 Answers
3

You cannot see it in the Name Manager because your third argument, Visible:=False, is set False.

Visible - True specifies that the name is defined as visible. False specifies that the name is defined as hidden. A hidden name does not appear in the Define Name, Paste Name, or Goto dialog box. The default value is True .

See Names.Add Method for more information.

Formulas -> Names only shows you the ThisWorkbook.Names and Worksheet.Names (depending on the scope of your Name).

Formulas -> Names

ThisWorkbook.Names

Worksheet.Names

Name

If you insert a breakpoint and look at View > Locals Window you can find the Application.Names in Me > Application > Names.

View > Locals Window

Application.Names

Me > Application > Names

My preferred method of adding an "opened" flag is to add to the CustomDocumentProperties. I've never seen anyone use Application.Names for this.

CustomDocumentProperties

Application.Names

If what you want is for a messagebox to pop up when the workbook is opened then the following code will do that for you, simply place it under ThisWorkbook.

ThisWorkbook

Also I'm pretty sure the syntax in your code is incorrect, if you wanted to add a Named Range you would do it as follows: Sheet1.Range("A1:Z10").Name = "MyNamedRange" there would be no need for the Call before it, as it isn't calling another subroutine/function:

Sheet1.Range("A1:Z10").Name = "MyNamedRange"

Private Sub Workbook_Open()
MsgBox "This will pop up when the Workbook is opened!", vbInformation, "My MessageBox Title"
End Sub


UPDATE:

If you only want it to run the very first time the workbook is opened and then never again, the following will achieve that for you:

Sub RunOnce()
Dim Flag As Boolean: Flag = False
For Each Item In Application.Names
If Item.Name = "FunctionHasRun" Then Flag = True
Next
If Flag = False Then
Sheet1.Range("$A$1:$A$1").Name = "FunctionHasRun"
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
Call RunOnceFunction
End If
End Sub


RunOnceFunction

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.