8

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

Ben Rhys-Lewis
  • 3,118
  • 8
  • 34
  • 45
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 3
    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 teh only one that responds properly to `IsMissing`. –  Jan 25 '16 at 19:45
  • 3
    You can call the sub with parameters from the VBE's Immediate window ([ctrl]+G). –  Jan 25 '16 at 19:49

2 Answers2

10

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

        optional_ismissing

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

  • Aha! That's a pretty neat "trick". I like this because I use `F8` a ton to walkthrough and this allows me to use the macro like a "normal" one, without really much more complication. Tangent, but I noticed that if I run the sub without giving any `myColumns` data, using `If IsEmpty(myColumns)` will return `FALSE`...despite `myColumns` having `Nothing`. (Then I noticed it's actually `IsMissing`, so switched it. Just a counterintuative quirk I think with `IsEmpty`). – BruceWayne Jan 25 '16 at 20:51
  • 1
    Yes, testing the existence of an optional parameter against `Is Nothing` or with the [IsEmpty function](https://msdn.microsoft.com/en-us/library/office/gg264227.aspx) does not produce correct results. A variant type parameter with the [IsMissing function](https://msdn.microsoft.com/en-us/library/aa445048%28v=vs.60%29.aspx) is the best test. Alternately, hardcode impossible values (constants) as defaults and test if the parameter equals those constants. –  Jan 25 '16 at 20:58
7

A Sub with ANY parameters, optional or not, cannot be run directly and can only be called from another Sub or Function

Best option is to write a wrapper Sub that will appear in the Macros window

Sub USER_Remove_Numbers_from_Columns()
    GEN_USE_Remove_Numbers_from_Columns
End Sub
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Thanks for this one! I'll keep this in mind for macros in the future. For this specific one, Jeeped's answer works a little better, but thanks a lot for your answer and comment!! – BruceWayne Jan 25 '16 at 20:52