January 6th, 2009
I would like to know how to call, from excel vba, an arbitrary
procedure located in a module associated with a spreadsheet. Ideally
it would work like this:
1) user enters a procedure name in a cell as plain text (eg, user
enters "func_a" into cell A1)
2) some time later inside vba script, the value of this cell is read
(some_variant = cells(1,1).value)
3) the script then executes a procedure named identical to the user's
text (somehow call the function with the name identical to the string
value of some_variant)
For the sake of simplicity, I will assume that the code for func_a is
located in Module1 of the same workbook as the spreadsheet with
[A1]="func_a". The point of this is that I do not want to limit the
procedure names, so I cannot hard-code function calls based on the
text. Code such as "if string="func_a" then call func_a" will not
work, the user needs to be able to enter arbitrary procedure names. I
have not been able to figure out how to do this with the vba "Call"
statement, but maybe you can!The CallByName function will do what you're asking
Sub test()
MsgBox ("hi")
End Sub
Sub test2()
CallByName Me, Worksheets("sheet1").Range("a1").Value, VbMethod
End SubSub routine1()
MsgBox "This is a message from within routine one"
End Sub
Sub doCall(r As Range)
Application.Run r.Value
End Sub
Sub doIt()
Set ws = Worksheets("sheet1")
ws.Range("a1").Value = "routine1"
doCall ws.Range("A1")
Set ws = Nothing
End SubI suggest asking this question here...
http://www.mrexcel.com/board2/viewforum.php?f=2&sid=f1d3659905763fc732fce0bad09e4732
You will probably get a response within a half hour.#If you have any other info about this subject , Please add it free.# |
|
Posted in weletomylife.com | edit
