Excel VBA module subroutine not being picked up with parameter

Question

In a new excel spreadsheet, I have inserted a new module and entered a public sub. When trying to test, the sub does not appear in the macro list unless I remove the parameter.

This behaviour is bizarre and I cannot find any reference to it as an issue, everything I have read declares that subroutines (or functions, tried that too) can have parameters.

Public Sub RetrieveSIR()  <-- Can be found
Public Sub RetrieveSIR( SIRNumber as Integer)  <-- Cannot be found

It is driving me around the bend trying to work this out. If anyone can help, it would be much appreciated.

Silenxor · Accepted Answer · 2014-01-31 03:10:39Z

11

Subs with parameters won't show up in the macro list for the same reason you can't simply run a sub with parameters from the vba editor screen. They can only be called via code so the parameter required can be input.

Edit: If for whatever reason you really need your macro to be in that macro list, you should make that parameter a variable in your macro and use an inputbox to specify it. That way when the user clicks the macro, they'll be prompted for the input and then the macro can run accordingly.

With regards to functions, you can have a function with parameters and use it as a formula in excel but as far as I'm aware they won't show up in the macro list either.

edited Jan 31, 2014 at 3:10

answered Jan 31, 2014 at 3:04

Silenxor

2442 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roddy Over a year ago

Thanks so much, is great to have a definitive answer. Much appreciated.

Collectives™ on Stack Overflow

Excel VBA module subroutine not being picked up with parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related