使用 VBA 的名称管理器 - 宏与函数调用给出不同的响应

Name Manager using VBA - Macro vs. Function Call Gives Different Response

提问by Bryan

I have an XLA I'm use to make calculations and I'd like to create variables in the Name Manager to use in those calculations. I want to check to see if those named ranged already exist and if not let the user assign values to them. I have a Sub() that I'm using to set the Name Manager -example below- :

我有一个用于计算的 XLA，我想在名称管理器中创建变量以用于这些计算。我想检查一下那些命名的 ranged 是否已经存在，如果不存在，则让用户为它们分配值。我有一个 Sub() 用于设置名称管理器 - 示例如下：

Public Sub SetNames()
On Error Resume Next
   IsRangeName = CheckName("test")

If IsRangeName = Empty Then
   Application.ThisWorkbook.Names.Add Name:="test", RefersTo:=0
End If

End Sub

If I go into the "Macro" menu and run the SetNames routine it works and sets test = 0 in the Name Manager.

如果我进入“宏”菜单并运行 SetNames 例程，它会起作用并在名称管理器中设置 test = 0。

However, what I want to do is run this through a Function and allow the function to use the variables in the Name Manager if they exist, if they don't exist then those values get set to an initial value in the Name Manager through the subroutine.

但是，我想要做的是通过一个函数运行它，并允许函数使用名称管理器中的变量（如果它们存在），如果它们不存在，那么这些值将通过名称管理器中的初始值设置为子程序。

When I try to run the following code the values are never set in the Name Manager:

当我尝试运行以下代码时，从未在名称管理器中设置值：

Sub Function1()
   Call SetNames()

   -Do Other Things-

End Function

All of the names are declared as global variables.

所有名称都声明为全局变量。

The intent is to have a user install the add-in and on the first function call using the add-in the Name Manager gets set, either to initialize the names or to allow the user to set the initial value. I don't want the user to go through the Macro ribbon option and execute the subroutine to initialize the Name Manager names.

目的是让用户安装加载项，并在使用加载项的第一次函数调用中设置名称管理器，以初始化名称或允许用户设置初始值。我不希望用户通过宏功能区选项并执行子例程来初始化名称管理器名称。

Any help on this would be appreciated.

对此的任何帮助将不胜感激。

采纳答案by Tim Williams

This seemsto work in my quick testing, but you should be sure it performs in whatever your final use case is. It's a hack around the restrictions on a UDF being able to update the workbook, so it's outside of "normal" usage.

这在我的快速测试中似乎有效，但您应该确保它在您的最终用例中执行。这是对 UDF 能够更新工作簿的限制的一种破解，因此它超出了“正常”使用范围。

Sub SetNameIfMissing(swb As String)

    Dim r As Name, wb As Workbook
    Set wb = Workbooks(swb)
    On Error Resume Next
    Set r = wb.Names("test")
    On Error GoTo 0

    If r Is Nothing Then
        Debug.Print "adding name..."
        wb.Names.Add "test", 99
    Else
        Debug.Print "already added"
    End If

End Sub


Function SetIt(v)

    Dim wb
    wb = Application.Caller.Parent.Parent.Name

    'using Evaluate gets around the UDF restriction
    Application.Caller.Parent.Evaluate "SetNameIfMissing(""" & wb & """)"

    SetIt = "OK" 'or whatever return value is useful...

End Function

回答by Ditto

Not sure what "CheckName" is in your script - you didn't provide it .. however, I got it to work via:

不确定您的脚本中的“CheckName”是什么 - 您没有提供它......但是，我通过以下方式使其工作：

1) comment out On Error Resume Next - this allows you to see CheckNames failing.

1) 注释掉 On Error Resume Next - 这使您可以看到 CheckNames 失败。

2) Replaced CheckNames with a loop to loop throw the defined names, looking for ours.

2) 用循环替换 CheckNames 以循环抛出定义的名称，寻找我们的。

3) change your "function" definition from "sub" to "function".

3）将您的“功能”定义从“子”更改为“功能”。

test it, runs fine. Sets the "test" name if it doesn't exist. Change it manually to another value, run again, doesn't touch it.

测试一下，运行正常。如果“测试”名称不存在，则设置它。手动将其更改为另一个值，再次运行，不碰它。

  Public Sub SetNames()
  'On Error Resume Next
    For i = 1 To Application.ThisWorkbook.Names.Count
      If Application.ThisWorkbook.Names(i).Name = "test" Then
         IsRangeName = True
         Exit For
      End If
    Next i

  If Not IsRangeName Then
     Application.ThisWorkbook.Names.Add Name:="test", RefersTo:=1
  End If

  End Sub

  Function Function1()
     Call SetNames

     '-Do Other Things-

  End Function