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.

Answers


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.


Need Your Help

Using PHP variables inside SQL statements?

php mysql

For some reason I can't pass a var inside a mysql statement. I have a function that can be used for multiple tables. So instead of repeating the code I want to change the table that is selected fro...

How can I click on this child node / descendant using C# Selenium?

c# html selenium css-selectors

TL;DR: The developer had bound the jQuery click listener to the parent div, not the child span that I had been trying to click.