CODEWITHSUNDEEP
excelvba
RFikes
RFikes

Reputation: 81

Macros Not Showing Up in Macro Table

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.

Upvotes: 8

Views: 67381

Answers (6)

Ashutosh Vyas
Ashutosh Vyas

Reputation: 419

My problem was that I had defined Macro directly through Visual Basic Editor in Excel and had defined it under "Class Modules" folder instead of "Modules"

Once I moved it into Modules folder, it was visible.

Hope this helps someone.

Upvotes: 0

user14214931
user14214931

Reputation: 1

The macro is not listed when it has parameters, but knowing the name you should be able to simply write the name and theirs parameters when Excel prompts in the Assign Macro window, using single and double quotes.

The following example will guide you how to do it:

'mymacro "param1"'
'mymacro TRUE, FALSE'
'mymacro "param1","param2"'

Upvotes: 0

Borgunit
Borgunit

Reputation: 1

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.

Upvotes: 0

RIck_R
RIck_R

Reputation: 121

Obviously if the macro requires parameters 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.

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.

' 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

Upvotes: 2

Vegard
Vegard

Reputation: 4917

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.

Upvotes: 27

Jayant Kumar jain
Jayant Kumar jain

Reputation: 27

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

Upvotes: -2

Related Questions