Custom Sub with additional parameters - does not appear in the macro window

Question

I have a macro that goes through columns (columns) and removes numbers from all cells in a range. I would like to add an optional parameter, so I can call sub, telling it which columns to run for. Here is what I have:

Sub GEN_USE_Remove_Numbers_from_Columns(Optional myColumns as String)

The idea is that I can name it from another sub, like this GEN_USE_...Columns("ABC")

But I can not start it from the VB editor, and I can not see this macro in the macro window (when I click "View" → "Macros"). Why not? Why should I call it with a parameter (even GEN_USE_...Columns("") ), I can no longer just call GEN_USE_...Columns() .

I saw that you can add = Nothing to the end to set the default value if none are specified. I tried this () but did nothing.

I think my question is: A) Why can not I see my macros, which have additional parameters, in the macro window? and B) Why can't I call a macro with parameters directly from the VB editor? I have to create a sub, then I can call the macro in that sub. No longer just select the text and click "Play."

I know that the two problems are probably related, so any understanding will be appreciated!

(PS: I know that we should send the code, but I don’t think it is very important. Of course, if you want to see it, let me know and I will update it).

Answer 1

Use Optional myColumns as Variant to display it in the Run Macro dialog box ([alt] + [F8]). Alternatively, leave it hidden; you can enter a name and click "Run." The variant type is also the only one that responds correctly 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 sub with parameters from the VBE Immediate window ([ctrl] + G).

Answer 2

Sub with any parameters, optional or not, cannot be started directly and can only be called from another Sub or Function

The best option is to write a Sub wrapper that appears in the macro window

 Sub USER_Remove_Numbers_from_Columns() GEN_USE_Remove_Numbers_from_Columns End Sub