Excel用户定义的函数不能在Excel 2007中使用ArgumentDescriptions。我试图在我的Add中绕过它

I have created a couple of user-defined functions in Excel and created a simple add-in so that I can share them with colleagues.

我在Excel中创建了一些用户定义的函数,并创建了一个简单的加载项,以便我可以与同事共享它们。

I use Application.MacroOptions in order to give some useful indications for how to use the functions. Especially, I use ArgumentDescriptions to give descriptions for the arguments.

我使用Application.MacroOptions来为如何使用这些函数提供一些有用的指示。特别是,我使用ArgumentDescriptions来给出参数的描述。

Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc _
      HelpFile:= Helpfile

I have just found out that the ArgumentDescriptions parameter is not supported in Excel 2007 and this is causing an error for this version. Excluding this parameter solves the problem.

我刚刚发现Excel 2007中不支持ArgumentDescriptions参数,这导致此版本出错。排除此参数可以解决问题。

I don't want to have to distribute two versions of the add-in. I tried to use this code to include the parameter if the version more recent than Excel 2007, but it still gets tripped up on the unrecognized parameter name if used in Excel 2007:

我不想分发两个版本的加载项。我尝试使用此代码来包含参数,如果版本比Excel 2007更新,但如果在Excel 2007中使用它仍会在无法识别的参数名称上绊倒:

If Left(Application.Version, 2) > 12 Then
     Application.MacroOptions Macro:=FuncName, ArgumentDescriptions:=ArgDesc
End If

Is there a way to solve this problem so I can do this all with just one add-in?

有没有办法解决这个问题,所以我只需一个加载项即可完成所有这些操作?

Thanks

2 个解决方案

#1

An easy way to get around the Named argument not found error in the your function is to move the setting of the macro options into 2 separate functions:

在函数中找到未找到Named参数错误的简单方法是将宏选项的设置移动到2个单独的函数中:

 If Left(Application.Version, 2) > 12 Then
    setUpNewMacroOptions
 Else
    setUpOldMacroOptions
 End If

And then the 2 functions:

然后是2个功能:

 Public Sub setUpOldMacroOptions()
    Application.MacroOptions Macro:="myMacro", Description:="desc", Category:="cat", HelpFile:="file"
 End Sub

 Public Sub setUpNewMacroOptions()
    Application.MacroOptions Macro:="myMacro", Description:="desc", Category:="cat", ArgumentDescriptions:="blah", HelpFile:="file"
 End Sub

As the setUpNewMacroOptions function is never called in Excel 2007, the VBA compiler doesn't try to validate the function so no error.

由于从未在Excel 2007中调用setUpNewMacroOptions函数,因此VBA编译器不会尝试验证函数,因此不会出现错误。

#2

This procedure works in Excel 2010 with argument descriptiors and in 2007 without them:

此过程适用于带有参数描述符的Excel 2010,并且在2007年没有它们:

    Private Sub AddDesc(Macro As String, Description As String, _
Category As String, ArgumentDescriptions As Variant)
        #If VBA7 Then 'in Excel 2010 and over
            Application.MacroOptions Macro:=Macro, Description:=Description, _
                Category:=Category, ArgumentDescriptions:=ArgumentDescriptions
        #Else 'in Excel 2007 and under
            Application.MacroOptions Macro:=Macro, Description:=Description, _
                Category:=Category
        #End If
    End Sub

Best regards, Peter Krassoi

最诚挚的问候,Peter Krassoi

#1