PDA

View Full Version : [SOLVED] Can VBA Statements Be Stacked?



Cyberdude
05-26-2005, 01:25 PM
I run a sequence of 5 macros. At the end of each is a statement that Calls the next. As a consequence, the first of the 5 to execute cannot terminate until the 5th one finishes execution and passes control back to the 4th, which passes control back to the 3rd, and so on.
Back in my PL/1 days we used a stack mechanism which allowed the programmer to stack commands at the end of a program. The program would terminate, then the system would go to the stack for the next statement to execute. If I had such a stack mechanism, I could stack the Call to the next macro. The current macro would exit, then the system would take the Call from the stack and execute it.
My question is: does VBA have such a stack mechanism available?

Ken Puls
05-26-2005, 03:16 PM
Sorry, Sid...

Are you saying that you have this:


Sub Macro1()
'Some code here
Call Macro2
End Sub

Sub Macro2()
'Some code here
Call Macro3
End Sub

And so on...?

I usually do this:


Sub Wrapper()
Call Macro1
Call Macro2
Call Macro3
End Sub

I have been known to set up a global variable like bError, and change the state inside one of those macros if there is an error. You can then kick out easily by testing bError and exiting the sub.

HTH,

Steiner
05-27-2005, 12:30 AM
Sometime I use an approach very much like Ken, but with 1 difference, I use functions that return a boolean. Since I can't throw errors up (1 point I love about Java), I give back true if all is ok, false otherwise. It then looks something like that


Option Explicit

Sub Main()
If Not Macro1 Then Exit Sub
If Not Macro2 Then Exit Sub
If Not Macro3 Then Exit Sub
End Sub
Function Macro1() As Boolean
Debug.Print "Ok"
Macro1 = True
End Function
Function Macro2() As Boolean
Debug.Print "Error"
Macro2 = False
End Function
Function Macro3() As Boolean
Debug.Print "ok"
Macro3 = True
End Function



Daniel

Andy Pope
05-27-2005, 12:45 AM
Hi Daniel,


Since I can't throw errors up
You can raise your own errors and then trap them.


Sub Main()

On Error GoTo ErrTrap
Macro1
Macro2
Macro3
ErrTrap:
MsgBox "Error " & Err.Number & " in " & Err.Source & vbLf & Err.Description
Exit Sub
End Sub

Function Macro1() As Boolean
Debug.Print "Ok"
Macro1 = True
End Function

Function Macro2() As Boolean
Err.Raise vbObjectError + 1, "Macro2", "I just raised an error"
Debug.Print "Error"
Macro2 = False
End Function

Function Macro3() As Boolean
Debug.Print "ok"
Macro3 = True
End Function

But this is not to say that using functions is not a good way of handling the flow through your code as it is and one I use.

Cyberdude
05-27-2005, 02:05 PM
Actually each macro asks me if I want to run the next one, so I remain in control in case of error occurrences. That's not exactly what I was asking. If I answer "yes", then the next macro runs, but the one I said "yes" to can't complete until the last one is finished. I'm not sure how to diagram that on this editor:

Sub Macro1
If Msgbox("Execute the next one?") = vbYes _
Then Call Macro2 'This stmt must complete before the End Sub can execute
End Sub
Note that this isn't causing a problem. Actually I was using it as an example of how I could use a stack mechanism if I had one. If I could, I would respond to the Msgbox question by stacking the Call statement, then proceed to execute the End Sub statement. Macro1 would terminate, then Macro2 would execute. At least something along those lines. So I wasn't actually looking for a workaround, but rather I was asking if VBA has a stacking mechanism. It can be used for other things during the execution of a macro too.
Thanx for all the replies.

Bob Phillips
05-27-2005, 02:28 PM
I am really getting confused by this thread.

I am not understanding the benefit of (what I think) you are saying.

Surely, even if you could push Macro2 onto the stack at the end of Macro1, as soon as #1 finishes, Macro2 will then execute, and you will have to wait anyway?

BlueCactus
05-27-2005, 02:47 PM
I am really getting confused by this thread.

I am not understanding the benefit of (what I think) you are saying.
So it's not just me then? Perhaps he has reason to believe that his macros will fill the stack if they call each other?

MWE
05-27-2005, 03:08 PM
I read the original request a little differently. The stack mechanism as explained by Cyperdude is "called" by the "parent" with some sort of arguement list describing what is to be done; and then the parent exits. The "system" then processes whatever is "in" the stack.

BlueCactus
05-27-2005, 03:24 PM
I read the original request a little differently. The stack mechanism as explained by Cyperdude is "called" by the "parent" with some sort of arguement list describing what is to be done; and then the parent exits. The "system" then processes whatever is "in" the stack.
I don't think VBA offers that much control over the stack. I don't think you can even do something simple like change its size.

So what would be the benefit of exiting the parent process first? Actually seems a little dangerous - if the child processes are event dependent, they could sit in the background all day waiting for a trigger to screw up your worksheet.

Bob Phillips
05-27-2005, 03:50 PM
I read the original request a little differently. The stack mechanism as explained by Cyperdude is "called" by the "parent" with some sort of arguement list describing what is to be done; and then the parent exits. The "system" then processes whatever is "in" the stack.

Wouldn't OnTime do that?

Cyberdude
05-27-2005, 08:36 PM
Guys, I'm really sorry I brought this up. All I wanted was a yes or no answer. It has become apparent that the answer is no. Don't spend a lot of time about it.
The traditional stack mechanism has two main commands: PUSH and POP. As I recall, it didn't much matter what you "pushed" onto the stack. It could be executable statements or just data. The programmer had to make sense out of it depending on the context in which he is using it. The stack could be operated FIFO or LIFO.
As far as I'm concerned, this is "SOLVED".