CODEWITHSUNDEEP
vbaexcel
HonnSolo
HonnSolo

Reputation: 165

Using a variable as the name of a macro

This might be a fairly easy "no you can't" answer. But if any of you know of a way to make this work, I would really appreciate it.

I have a macro called "DES" and another called "FA". When I change a cell to either "DES" or "FA" I want it to run either the "DES" or the "FA" macro. I do not what to use a series of if/else logics to figure out the macro to run. I would rather have it so the Go_to_macro() function would feed in the variable and run the function. The reason for this is that I will eventually have dozens of macros that I would like to run this way.

Example code below: "Call command" is where there is a problem.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("command_des").Value <> Sheets("Back End").Range("command_be").Value Then
    Dim command, number
    command = Range("command_des").Value
    ticker = Range("number_des").Value
    Call Go_to_macro(command, number)
    Else
End If

End Sub

//////////////////////////////////////////////////////////////////////////

Public Function Go_to_macro(command, number)

Range("command_be").Value = command
Range("command_" & command).Value = Range("command_be").Value
If Range("number_" & command).Value <> Range("number_be").Value Then
    Call command
    Else
End If

End Function

//////////////////////////////////////////////////////////////////////////

Sub DES()

Cells(1,1).Value = "Hello World!"

End Sub

/////////////////////////////////////////////////////////////////////////

Sub FA()

Cells(1,1).Value = "Goodbye World..."

End Sub

/////////////////////////////////////////////////////////////////////////

Ideas on a work around?

In the end I should get "Hello World" in cell A1 if I write "DES" into the namedrange or "Goodbye World..." in cell A1 if I write "FA" into the same namedrange. Just so you are clear on the outputs.

Upvotes: 0

Views: 45

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

You can use

Application.Run command

(FYI, the Call keyword is obsolete and should not be used.
Instead of

Call Go_to_macro(command, number)

use

Go_to_macro command, number

)

Upvotes: 2

Related Questions