Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Procedure from Cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Procedure from Cell value
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 procedu ProcessMyMacro Range("A1").Value, "You called me!" Open the Macros dialog and run 'MyMacro1'. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Procedure from Cell value
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") On 06/25/2010 18:57, Raj wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Procedure from Cell value
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") On 06/25/2010 18:57, Raj wrote: 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 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling procedure when leaving cell | Excel Worksheet Functions | |||
Calling sub procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming | |||
Calling a procedure in a procedure | Excel Programming |