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

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
|

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

Procedure name as variable
 

Don't think you can, at least not in office XP - it gives an
application or object defined error message

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

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

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!!!)

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!

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

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

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

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

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

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

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

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