Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Hello all, I hope someone can answer what may be a dumb question. Is it possible to execute a procedure from a variable? For example, if cell A1 has the value of "Macro_1" and I have a sub named Macro_1 is there some way to read cell A1 and convert that value into a sub name I can execute? I hope that made sense. lol Thanks for any tips you can give. -- Redbeard ------------------------------------------------------------------------ Redbeard's Profile: http://www.excelforum.com/member.php...o&userid=24612 View this thread: http://www.excelforum.com/showthread...hreadid=536813 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
I've wished for that but I don't think it's possible. You could do
something like this of course: Select Case Range("A1").Value Case "Sub1": Sub1 Case "Sub2": Sub2 Case "Sub3": Sub3 End Select -- Jim "Redbeard" wrote in message ... | | Hello all, I hope someone can answer what may be a dumb question. Is it | possible to execute a procedure from a variable? For example, if cell | A1 has the value of "Macro_1" and I have a sub named Macro_1 is there | some way to read cell A1 and convert that value into a sub name I can | execute? I hope that made sense. lol | | Thanks for any tips you can give. | | | -- | Redbeard | ------------------------------------------------------------------------ | Redbeard's Profile: http://www.excelforum.com/member.php...o&userid=24612 | View this thread: http://www.excelforum.com/showthread...hreadid=536813 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Hi,
Can you not use Application.Run. Run the routine Test where cell A1 contains Macro1. ' Place in standard code module. Sub Macro1() MsgBox "Macro1" End Sub Sub Macro2() MsgBox "Macro2" End Sub Sub Macro3() MsgBox "Macro3" End Sub Sub Test() Application.Run Range("A1").Value End Sub Cheers Andy Redbeard wrote: Hello all, I hope someone can answer what may be a dumb question. Is it possible to execute a procedure from a variable? For example, if cell A1 has the value of "Macro_1" and I have a sub named Macro_1 is there some way to read cell A1 and convert that value into a sub name I can execute? I hope that made sense. lol Thanks for any tips you can give. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Don't think you can, at least not in office XP - it gives an
application or object defined error message |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Works for me in both Xl2000 and xl2003.
Where abouts does it actually error out? wrote: Don't think you can, at least not in office XP - it gives an application or object defined error message -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Did you put the name of an existing procedure in A1 of that activesheet?
wrote: Don't think you can, at least not in office XP - it gives an application or object defined error message -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
in the Application.Run Range("A1").Value bit of code - mind you, with
our odd office settings, anything is possible (office network boys had a field day "standardising" pc's!!!) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
I have a macro called A, and cell A1 has A in it, so yes, BUT I've
figured out the solution - IF the macro is stored in a module it works fine, but if it is on the workbook/worksheet then it won't. Which is therefore a consideration that needs to be bourne in mind when creating this type of macro! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
I did included a code comment about putting it all in a standard module.
If the code is in a worksheet object, for example sheet1, then this should work. Cell contains Sheet1.Macro1 Cheers Andy AidanH wrote: I have a macro called A, and cell A1 has A in it, so yes, BUT I've figured out the solution - IF the macro is stored in a module it works fine, but if it is on the workbook/worksheet then it won't. Which is therefore a consideration that needs to be bourne in mind when creating this type of macro! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Thanks a load, Andy. It works like a charm. -- Redbeard ------------------------------------------------------------------------ Redbeard's Profile: http://www.excelforum.com/member.php...o&userid=24612 View this thread: http://www.excelforum.com/showthread...hreadid=536813 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
This system seems to work fine once, but it won't loop. Here is an example of what I'm trying to do: For A = 1 To 30 Macro(A) = ActiveCell.Value ActiveCell.Offset(1, 0).Select Next A For A = 1 To 30 Application.Run Macro(A) Next A When I run this it goes fine the first time but then ends. Does it have to wait for one to finish before it will run the next? If so, is there a way to tell it to wait? I can insert a pause but that seem clumsy. Again, thanks for your help. -- Redbeard ------------------------------------------------------------------------ Redbeard's Profile: http://www.excelforum.com/member.php...o&userid=24612 View this thread: http://www.excelforum.com/showthread...hreadid=536813 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Is the activecell the correct one when it is run the second time?
Redbeard wrote: This system seems to work fine once, but it won't loop. Here is an example of what I'm trying to do: For A = 1 To 30 Macro(A) = ActiveCell.Value ActiveCell.Offset(1, 0).Select Next A For A = 1 To 30 Application.Run Macro(A) Next A When I run this it goes fine the first time but then ends. Does it have to wait for one to finish before it will run the next? If so, is there a way to tell it to wait? I can insert a pause but that seem clumsy. Again, thanks for your help. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Yeah, the cells are in a vertical row and all have the correct values. If I run it this way: For A = 1 To 30 Macro(A) = ActiveCell.Value ActiveCell.Offset(1, 0).Select Next A For A = 1 To 30 Msgbox A Next A all the values are displayed perfectly. I'm baffled -- Redbear ----------------------------------------------------------------------- Redbeard's Profile: http://www.excelforum.com/member.php...fo&userid=2461 View this thread: http://www.excelforum.com/showthread.php?threadid=53681 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Could it be related to what the macros actual do?
Do you have an End statement in the first macro? Cheers Andy Redbeard wrote: Yeah, the cells are in a vertical row and all have the correct values. If I run it this way: For A = 1 To 30 Macro(A) = ActiveCell.Value ActiveCell.Offset(1, 0).Select Next A For A = 1 To 30 Msgbox A Next A all the values are displayed perfectly. I'm baffled. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
I decided to try it several more times to ensure that the result was consistant. It wasn't. Occasionally it would make it through the loop twice. This makes me think even more that it might be an issue of it not running the next until the previous is finished. -- Redbeard ------------------------------------------------------------------------ Redbeard's Profile: http://www.excelforum.com/member.php...o&userid=24612 View this thread: http://www.excelforum.com/showthread...hreadid=536813 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure name as variable
Yes the actions will be sequential. Macro 2 will not start until macro 1
is completed. Redbeard wrote: I decided to try it several more times to ensure that the result was consistant. It wasn't. Occasionally it would make it through the loop twice. This makes me think even more that it might be an issue of it not running the next until the previous is finished. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Dim the contents of a variable in running procedure? | Excel Discussion (Misc queries) | |||
Variable procedure call | Excel Programming | |||
Call procedure using variable | Excel Programming | |||
Public/Procedure Variable | Excel Programming | |||
can I call a procedure using a variable | Excel Programming |