vba 宏没有出现在宏表中

Macros Not Showing Up in Macro Table

提问by RFikes

I am writing VBA macros in excel 2016. Macros I write that have arguments do not show up in the Macro Table, only the ones that have no arguments. Help.

我正在 excel 2016 中编写 VBA 宏。我编写的有参数的宏不会显示在宏表中，只有那些没有参数的宏。帮助。

回答by Vegard

Macros that take arguments are not visible in the macro box because there is no point in having them there. If they need arguments to run, they cannot be run from the macro box because there is no way to supply an argument to the macro in question.

带参数的宏在宏框中不可见，因为将它们放在那里没有意义。如果它们需要运行参数，则无法从宏框中运行它们，因为无法为相关宏提供参数。

Normally, a macro shows up in the macro list when you display the Macros dialog box (press Alt+F8), unless one of three conditions is met:

• The macro is a function. Functions typically return information, and they require information to be passed to them. Since running a macro from the macro list doesn't allow either of these things to happen, Excel figures there is no need to list it. User-defined functions, which are quite useful in Excel, are not displayed in the Macros dialog box because they are, after all, functions.

• The macro is a subroutine with parameters.Excel assumes that since parameters are necessary, and you cannot provide parameters by choosing the subroutine from the macro list, there is no need to list it.

• The subroutine has been declared Private. This means that the subroutine is only useful to code within the module in which it is declared.

通常，当您显示“宏”对话框（按 Alt+F8）时，宏列表中会显示一个宏，除非满足以下三个条件之一：

• 宏是一个函数。函数通常返回信息，并且需要将信息传递给它们。由于从宏列表运行宏不允许这些事情发生，Excel 认为没有必要列出它。在 Excel 中非常有用的用户定义函数没有显示在宏对话框中，因为它们毕竟是函数。

• 宏是一个带参数的子程序。Excel 假定由于参数是必需的，并且您无法通过从宏列表中选择子程序来提供参数，因此无需列出它。

• 子程序已被声明为私有。这意味着子例程仅对声明它的模块内的代码有用。

Source.

来源。

Depending on your need, a possible workaround is to use a helper-sub like this:

根据您的需要，可能的解决方法是使用这样的 helper-sub：

Sub InvisibleMacro(strArg As String)
    MsgBox("The passed argument was " & strArg)
    ' This macro won't be visible in the macro dialog because it can only be called with an argument
End Sub

Sub VisibleMacro()
    Call InvisibleMacro("Abc")
    ' This macro will be visible in the macro dialog because it requires no arguments and is not private. 
    ' It will call the "invisible" macro with a preset argument.
End Sub

You can use InputBoxor the likes if you need the passed argument to be non-static. Of course, depending on what datatype you need to pass as an argument, this approach may be limited and/or require some extra hoops.

InputBox如果您需要传递的参数是非静态的，您可以使用或喜欢。当然，根据您需要作为参数传递的数据类型，这种方法可能受到限制和/或需要一些额外的箍。

回答by RIck_R

Obviously if the macro requiresparameters they can't be passed by clicking an icon.

显然，如果宏需要参数，则无法通过单击图标来传递它们。

But I have a Sub that only has one Optional parameter and that doesn't show up in the list either when trying to attach it to an icon in the customized ribbon.

但是我有一个只有一个 Optional 参数的 Sub 并且在尝试将它附加到自定义功能区中的图标时也没有显示在列表中。

Comment out the full Sub declaration and substitute a line without parameters, attach the macro to an icon, then put back the real line. The icon will still work.

注释掉完整的 Sub 声明并替换一个不带参数的行，将宏附加到一个图标上，然后放回真正的行。该图标仍然有效。

' Temporarily use the parameterless line to set things up, then put
' back the real line.  I assume this would crash if the Sub has required
' parameters.

Sub MySub()
' Sub MySub(Optional ByVal MyParm As String)
   If MyParm = "" Then MyParm = "No parameter"
   MsgBox (MyParm)
End Sub

回答by Borgunit

I know this is an older post but I had the same issue. My fix was that I had to open the macros in the VB editor and then they showed up in the macro list. from there I could add them to the ribbon and change the icons. Hope this helps.

我知道这是一个较旧的帖子，但我遇到了同样的问题。我的解决方法是我必须在 VB 编辑器中打开宏，然后它们才会出现在宏列表中。从那里我可以将它们添加到功能区并更改图标。希望这可以帮助。

回答by Jayant Kumar jain

You probably have made macro as "Function()". To make it visible in macros list, you have to declare it as "Sub()".

您可能已经将宏定义为“Function()”。要使其在宏列表中可见，您必须将其声明为“Sub()”。