User Sub with Optional parameters - not visible in Macro window

Question

I have a macro that goes through column(s) and removed numbers from all cells in the range. I would like to add an optional parameter, so I can call the sub while telling it which columns to run on. Here's what I have:

Sub GEN_USE_Remove_Numbers_from_Columns(Optional myColumns as String)

The idea being I can call it from another sub, like this GEN_USE_...Columns("A B C")

But, I can't run that from the VB Editor, nor can I see that macro in the Macro Window (when clicking View --> Macros). Why not? Why do I have to call it with a parameter (even GEN_USE_...Columns("")) I can't just call GEN_USE_...Columns() anymore.

I've seen that you can add = Nothing to the end, to set a default value if none is given. I've tried that () but it didn't do anything.

I guess my question is A) How come I can't see my macros that have Optional parameters, in the macro window? and B) Why can't I call the macro with parameters directly from the VB Editor? I have to actually create a sub, then I can call the macro within that sub. No more just highlighting some text and hitting "Play".

I know the two issues are probably related, so any insight would be appreciated!

(PS: I know we're supposed to post code, but I don't think that's very relevant. Of course, if you'd like to see it, let me know and I'll update).

Answer 1

Use Optional myColumns as Variant to show it in the Run Macro ([alt]+[F8]) dialog. Alternately, leave it hidden; you can type the name and click Run. The variant type is also the only one that responds properly to the IsMissing function.

Sub GEN_USE_Remove_Numbers_from_Columns(Optional myColumns As Variant)
    If IsMissing(myColumns) Then
        myColumns = Intersect(Selection.Parent.UsedRange, Selection).Address '.address 'cause you were using a string
    End If
    Debug.Print Range(myColumns).Address(external:=True)
End Sub

        

You can call the sub with parameters from the VBE's Immediate window ([ctrl]+G).