Calling Subs with Parameters from a Worksheet in Excel

From Controls

It is possible! assigning macros to buttons is one thing but without being able to pass parameters to the methods you're calling one might even resort to a number of hard-coded trigger methods to call more generic parameterised ones but that would be ugly and really go against our DRY sensibilities if all we were changing was parameters.

So a few notes on this approach; ideally you want to have declared your methods in a module rather than the 'This Worksheet' document, otherwise you'll have to reference the filename of the current workbook and that's just another thing that can fall out of sync, the other thing to bear in mind is that you'll be using VBA syntax rather than formula syntax. So to business - simply wrap your method call in apostrophes, outside of that everything else is as you'd expect, the below can be copied and pasted into the 'assign a macro' screen and if you've declared the ShowHideRows method in a module this will show or hide rows 8 to 32 on a worksheet called After, but that's for another article.

'ShowHideRows "After", "8:32"'

From Cells

From cells is pretty easy in fairness, you simply call the function as you would any other function native to excel as long as the worksheet that you're in is able to see it. If the sub is declared on another worksheet privately then you won't be able to use it - the best place for something like that would be a new module so that you can access the methods defined there globally.