Excel VBA module subroutine not being picked up with parameter
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.
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.