Can a function or sub be called using a variable name?

omahadivision · #1 12-02-2013, 10:09 PM

Hello,

I have a variable that can be about twenty values. For each different value, I would want to call a different sub or function. I don't want to have to have a ton of If or case statements. Is it possible to do something like

dim VariableName as string

(other code figures out what VariableName is)

then the function

Call VariableName

?

I have tried brackets, putting .value after variable name, quotes, and other tactics. I always am told that the sub or function is not defined. The sub or function VariableName would always exist, but I couldn't call it that way.

macropod · #2 12-02-2013, 11:07 PM

Here's a simple demo of a Function taking an argument. The UDF below can use a formula like ‘=Ordinal (A1)’ or or ‘=Ordinal(37)’ for any cell on any worksheet to which the event-driven code is attached.

Code:

Function Ordinal(ByVal Num As Long) As Long
Ordinal = Num
End Function
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
  If UCase(Cell.Formula) Like "=ORDINAL(?*)" Then
    Cell.NumberFormat = "#""" & Mid$("thstndrdthththththth", 1 - 2 * _
      ((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"
  End If
Next
End Sub

omahadivision · #3 12-03-2013, 11:08 AM

Thanks! I think I understand it except for the "Then" statement. Is that specific to a certain macro?

macropod · #4 12-03-2013, 02:16 PM

The 'Then' is part of an ordinary 'If x Then y End If' construct - all three elements are present in the code. In this case, the 'y' expression is so long it spans two lines.

BobBridges · #5 12-03-2013, 11:00 PM

Paul, I'm confused. This is an interesting solution to a question we've heard discussed before, but it isn't the question omaha asked. Did you two have a private conversation between his post and your answer in which he explained what he was after? Or did you accidentally post this answer to the wrong thread?

I think I've seen an answer to his question, but I can't lay my memory on it just now.

macropod · #6 12-03-2013, 11:25 PM

It was my interpretation of what omahadivision was seeking (as per the thread title) - a way to call a function via/with a variable. I may be wrong, but so far my response does seem to have met omahadivision's needs.

BobBridges · #7 12-04-2013, 12:24 AM

Yes, he does, and that's what makes me think I must have misunderstood his question. But to me his question seems to be "How can I (in effect) use the value of some variable to control which of several subroutines I call?", while you seem to have answered the question "how can I format a cell to cause the value 21 to display as '21st', 22 as '22nd', 23 as '23rd' and so on?". Do I need to look at your solution more closely, or at his question?

macropod · #8 12-04-2013, 12:55 AM

The code I posted is of no consequence in terms of whether or how one can pass parameters to a Function or Sub; what is important is that both pieces of code demonstrate the principle.

BobBridges · #9 12-04-2013, 08:47 AM

Oh, I see. But his question (it seems to me) isn't about passing values to subroutines; it's about calling a variable subroutine. Sort of like this:

Code:

Select Case MyVar
  Case 1 To 7: SubName = "MySub_" & MyVar
  Case "Binge": SubName = "MaxTime"
  Case "Micro": SubName = "Micro"
  Case Else: SubName = ""
  End Select
If SubName <> "" Then Call SubName

That won't work, but what he's asking (I think) is how to do that sort of thing. If MyVar happens to be 5, then he wants his program to Call MySub_5; if it's "Binge" he wants to Call MaxTime; and so on.

macropod · #10 12-04-2013, 06:28 PM

Quote:

Originally Posted by BobBridges

... calling a variable subroutine. Sort of like this:

Code:

Select Case MyVar
  Case 1 To 7: SubName = "MySub_" & MyVar
  Case "Binge": SubName = "MaxTime"
  Case "Micro": SubName = "Micro"
  Case Else: SubName = ""
  End Select
If SubName <> "" Then Call SubName

That won't work

True, but it would work for:

Code:

Select Case MyVar
  Case 1 To 7: Call MySub(MyVar)
  Case "Binge": Call MaxTime
  Case "Micro": Call Micro
  Case Else
End Select

BobBridges · #11 12-05-2013, 07:58 AM

Sure, but in his original post he said he has about twenty different values and he's hoping not to have to make a Select statement with twenty different cases. It's possible to rig that in VBScript, using an Execute statement, but VBA doesn't have that statement. Is there some way to use a variable subroutine name? I've a vague memory that there used to be a way to do that in Basic, but I'm not sure it's in VBA.

BobBridges · #12 12-05-2013, 08:00 AM

By the way, omaha, why don't you want to spell it out using a Select Case statement? Maybe there's a useful workaround, if we knew the problem. For example, is it because you're going to have to make this "variable call" several times in your program? If so, I'm pretty sure we can restrict it to just one call, in which case you still have to do the Select but just one time, not many.

macropod · #13 12-05-2013, 08:38 AM

Bob: I'm really not interested in debating whether my interpretation of the question or yours is correct. What I'm interested in is that the OP seems to have an answer that meets his needs.

BobBridges · #14 12-05-2013, 09:50 AM

No, no, Paul, you're missing my question. Maybe omaha is satisfied, as you say, but what I ask in my last post is this: Is there a way in VBA to put the name of a subroutine into a variable and then call it that way, so to speak "indirectly"?

macropod · #15 12-05-2013, 03:24 PM

Quote:

Originally Posted by BobBridges

Is there a way in VBA to put the name of a subroutine into a variable and then call it that way, so to speak "indirectly"?

Sure there is! Try:

Code:

Dim MacroName As String, i As Long
 
Sub Demo()
i = CLng(InputBox("Which macro to run?"))
MacroName = "ThisWorkbook.Macro" & i
Application.Run MacroName
End Sub
 
Private Sub Macro1()
MsgBox "Success! You ran macro: " & i
End Sub
 
Private Sub Macro2()
MsgBox "Success! You ran: " & MacroName
End Sub
 
Private Sub Macro3()
MsgBox "Ho hum ... you ran macro 3"
End Sub

Simply input 1, 2 or 3 into the inputbox.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
object variable or with block variable not set	MJP143	Excel	1	02-11-2013 05:07 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
excel where is procedure being called from	davids67	Excel Programming	1	11-18-2011 06:48 AM
What is this symbol called as?	Franky	Word	3	11-12-2011 08:16 AM
What is this right-click sub-menu called?	wornways	Word	3	08-13-2010 09:17 PM