How to call a sub from a sheet with arguments?

[JenniferMurphy]

I have a Sub that does a lot of analysis on one named range and returns the results to another named range. I call it using a button control. The problem is if I want to have it work on more than 1 range, I have to keep renaming the ranges. I would like to be able to have a different button control for each range, on the same sheet or a different one in the same book. Each button would call the same Sub, but with a different range.

I tried adding parameters to the Sub statement (Sub MySub(Name1 as range)), but then it no longer appears in the list of macros to assign to the button.

Is there a way to do this?

Thanks

 

[GWteB]

A UDF is simply not the super glue that can do everything ...

Description of limitations of custom functions in Excel - Microsoft Support

Lists the functionality limitations of user-defined functions in Excel. These functions are called by a formula in a worksheet cell and cannot change the environment of Excel. You must use the Visual Basic subroutine to change the environment.

support.microsoft.com

 

[Alex Blakenburg]

As GWTeB has already confirmed yes that is the Paul Kelly I was referring to.
I tried googling a reference to how he structured his Click_Events but I am afraid I can't find it.
He has lots of stuff on YouTube but he only makes his longer lesson type webinar replays available for 48 hours and that is where you would be able to more of his approach.
(other than buying his handbook or his subscription offering)
Here is one of his shorter ones (9 mins). Which includes using the watch window

 

[JenniferMurphy]

A UDF is simply not the super glue that can do everything ...

Description of limitations of custom functions in Excel - Microsoft Support

Lists the functionality limitations of user-defined functions in Excel. These functions are called by a formula in a worksheet cell and cannot change the environment of Excel. You must use the Visual Basic subroutine to change the environment.

support.microsoft.com

That's an understatement. But UDFs are a h*ll of a lot more super than macros.

VBA is by far the worst programming language I have ever used and I've used more than a dozen going all the way back to Fortran, COBOL, and BAL. It's even a step down from VB, which is a high (low?) bar. I probably spend about as much time looking for workarounds to the many limitations and inconsistencies in Excel, VBA, Access, PowerPoint, Outlook, and even Word, as I do in productive work. The inability to pass parameters to macros is stupid, stupid, stupid.

That page you referenced from m$ft reminds me of statements from politicians justifying their self-serving behaviors or religious leaders explaining the unexplainable.

 

[GWteB]

I'm afraid you're comparing apples to oranges. We are not talking about programming in its own right. We are talking about an application (Excel) that supports automation through a (derived) programming language. Despite certain limitations, I find VBA quite adequate.

If you really want to get the most out of it: teach yourself Intel assembly language, write your own DLLs and have the time of your life. Everything is possible and it all goes very fast.

 

[JenniferMurphy]

As GWTeB has already confirmed yes that is the Paul Kelly I was referring to.
I tried googling a reference to how he structured his Click_Events but I am afraid I can't find it.
He has lots of stuff on YouTube but he only makes his longer lesson type webinar replays available for 48 hours and that is where you would be able to more of his approach.
(other than buying his handbook or his subscription offering)
Here is one of his shorter ones (9 mins). Which includes using the watch window

That is an excellent video. I'll have to look up moce from him. Thanks.

 

[Alex Blakenburg]

Actually just circling back to this

does a lot of analysis on one named range and returns the results to another named range

Are the named ranges meaningful enough to be usable in a drop down box ?
And ideally is there a naming convention so that the source and destination names are translatable eg same root just a different suffix or prefix ?

If the above is the case you could have a drop down box allowing you to select the range name or range name root.
eg Drop down select = CalculateUK. Translated to range names CalculateUKSource & CalculateUKResult.

Possibly triggered from a Worksheet Change Event OR a button

My (validation list) drop down box was in I3.

Sub ScenarioRangeSelect()

    Dim rngSrc As Range
    Dim rngResult As Range
    
    Set rngSrc = Range(Range("I3").Value & "Source")
    Set rngResult = Range(Range("I3").Value & "Result")
    ' For testing only
    Debug.Print "Drop Down Value: " & Range("I3").Value
    Debug.Print "Source: " & rngSrc.Address
    Debug.Print "Results: " & rngResult.Address

    ' Call Action sub routine
    Call MakeBold(rngSrc, rngResult)

End Sub


' Action sub routine
Public Sub MakeBold(rngSrc As Range, rngResult As Range)

    rngSrc.Font.Color = vbBlue
    rngResult.Font.Color = vbBlue

End Sub

 

[JenniferMurphy]

Actually just circling back to this


Are the named ranges meaningful enough to be usable in a drop down box ?
And ideally is there a naming convention so that the source and destination names are translatable eg same root just a different suffix or prefix ?

If the above is the case you could have a drop down box allowing you to select the range name or range name root.
eg Drop down select = CalculateUK. Translated to range names CalculateUKSource & CalculateUKResult.

Possibly triggered from a Worksheet Change Event OR a button

That's an interesting idea. I haven't done a lot of work with controls, so I'll have to play with it a bit, but thanks for the suggestion. ?

 

[Alex Blakenburg]

If your are happy to use an Excel Data Validation list you don't even need to worry about controls.

 

[JenniferMurphy]

If your are happy to use an Excel Data Validation list you don't even need to worry about controls.

I don't know what that is. Do you have a link to a good tutorial?

 

[Rick Rothstein]

Would it be possible to select the range (or if the columns are fixed, simply any cell on the row) before clicking the button? If so, you could use one macro that uses Selection (if you are selecting the entire range) or the range constructed from the ActiveCell (if the columns are fixed). Alternately, if your columns are fixed, you could designate a cell to type the row number into and create the range inside the macro using that.