Menu
Another way to define a procedure with optional parameters is to use overloading. If you have one optional parameter, you can define two overloaded versions of the procedure, one accepting the parameter and one without it.
Make sure that your code is in standard codemodules and not private workbook or worksheet object modules, that Personal. xlsb is open in the same instance of Excel, and you are choosing Personal. xlsb in the macro run dialog.
Click the File tab, and then click Options at the very bottom of the left bar. On the left-side pane, select Trust Center, and then click Trust Center Settings… . In the Trust Center dialog box, click Macro Settings on the left, select Enable all macros and click OK.
A statement in a Sub or Function procedure can pass values to called procedures by using named arguments. You can list named arguments in any order.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With