Calling Procedure from Cell value

[Raj]

Hi,

I want to store Procedure names in cells, loop through the cells and
call the Procedures. I tried storing the cell value in a string
variable eg procstring and using "Call procstring", but it gives an
error. Is there a way to do this?

Thanks in advance for the help.

Regards,
Raj

 

[GS]

Raj used his keyboard to write :

Hi,

I want to store Procedure names in cells, loop through the cells and
call the Procedures. I tried storing the cell value in a string
variable eg procstring and using "Call procstring", but it gives an
error. Is there a way to do this?

Thanks in advance for the help.

Regards,
Raj

You need to look up (F1) the CallByName() function if you want to
execute procedures by procedure name.

In this case, you'll need a class module (ie: cEntryPoints) that lists
the procedures as public methods of the class. These don't have to have
the actual code you want to run, but they should call the procedure you
want to run.

Example:
Enter "Macro1" in Range("A1") of the active worksheet

In a class module named cEntryPoints:
Public gsParams As String

Sub Macro1()
'Do stuff or redirect to another procedure
MsgBox gsParams
End Sub


In a standard module named mEntryPoints:
Sub ProcessMyMacro(ProcName As String, Optional Params As String)
Dim oEntryPoints As New cEntryPoints
oEntryPoints.gsParams = Params
CallByName oEntryPoints, ProcName, vbMethod
Set oEntryPoints = Nothing
End Sub

In any standard module, userform, or worksheet/ThisWorkbook
'OnAction for a control:
Sub MyMacro1()
ProcessMyMacro Range("A1").Value, "You called me!"
End Sub

'Inside an event procedure:
ProcessMyMacro Range("A1").Value, "You called me!"

Open the Macros dialog and run 'MyMacro1'.

HTH

 

[Dave Peterson]

Another way that may work for you:

Application.run "'" & activeworkbook.name & "'!" & activesheet.range("A1").value

I saved this from a previous response. It may help you if you're passing parms
or calling a function:

Dim pWkbk as workbook
set pwkbk = workbooks("Personal.xls")
application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2"

or if you're returning a value from a function:

dim res as string 'or variant or long or ...
res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2")

 

[GS]

Dave Peterson presented the following explanation :

Another way that may work for you:

Application.run "'" & activeworkbook.name & "'!" &
activesheet.range("A1").value

I saved this from a previous response. It may help you if you're passing
parms or calling a function:

Dim pWkbk as workbook
set pwkbk = workbooks("Personal.xls")
application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2"

or if you're returning a value from a function:

dim res as string 'or variant or long or ...
res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2")

Hi Dave,
This is a very good suggestion as it works with any workbook that
contains macros you want to call from another workbook!

My suggestion (as posted) only works in the workbook using the class
module OR if calling into a COMAddin/DLL. The latter is how I use the
example code because the class is located in a VB6 COMAddin/DLL, and
this is used as the mechanism to implement using custom toolbar menus
(created by a table in an XLA) to run code in the COMAddin/DLL. While
the CallByName() function provides a means of passing params in an args
array, I find it faster and more convenient to put params in a
delimited string that each proc in the COMAddin/DLL parses according to
its needs.