本文介绍了宏不显示在宏表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在excel 2016中编写VBA宏.我编写的具有参数的宏不会显示在宏表中,而只会显示没有参数的宏.帮助.

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.

推荐答案

带参数的宏在宏框中不可见,因为在那里没有任何意义.如果它们需要运行参数,则无法从宏框中运行它们,因为无法为所讨论的宏提供参数.

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.

  • 宏是一个函数.函数通常返回信息, 他们需要将信息传递给他们.由于运行 宏列表中的宏不允许上述任何一项操作 发生这种情况,Excel认为没有必要列出它.用户自定义 在Excel中非常有用的函数不会显示在 宏对话框,因为它们毕竟是函数.

  • 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.

宏是 具有参数的子例程. Excel假定由于参数是 是必需的,并且您无法通过选择 宏列表中的子例程,则无需列出它.

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.

来源.

根据您的需要,可能的解决方法是使用如下所示的helper-sub:

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

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

您可以使用 InputBox 或类似的方法您需要传递的参数是非静态的.当然,根据您需要将哪种数据类型作为参数传递,此方法可能会受到限制和/或需要一些额外的限制.

You can use InputBox or 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.

这篇关于宏不显示在宏表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-16 20:57