Procedure name as variable

[Redbeard]

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.

[Jim Rech]

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" <[email protected]> wrote in
message news:[email protected]...
|
| 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
|

[Andy Pope]

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

[[email protected]]

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

[Andy Pope]

Works for me in both Xl2000 and xl2003.

Where abouts does it actually error out?

[email protected] 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

[Dave Peterson]

Did you put the name of an existing procedure in A1 of that activesheet?



[email protected] wrote:
>
> Don't think you can, at least not in office XP - it gives an
> application or object defined error message

--

Dave Peterson

[AidanH]

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

[AidanH]

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]

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

[Redbeard]

Thanks a load, Andy. It works like a charm.

[Redbeard]

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]

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

[Redbeard]

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]

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

[Redbeard]

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]

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