excel - Macros Not Showing Up in Macro Table

Question

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.

Answer 1

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.

Source.

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

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.