Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
I am trying to create a branching macro that tests for certain conditions and
then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
You can test for the next cell being empty by using:
If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
Two answers:
Sub main() Call branchout End Sub Sub branchout() MsgBox ("Hello World") End Sub for how to branch to another macro and then return Sub whatsbelow() If IsEmpty(Selection.Offset(1, 0)) Then MsgBox ("What's below is empty") End If End Sub for how to test the cell below the Selected cell. -- Gary''s Student - gsnu200744 "Branching macros" wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
Su
Go down one cell from a relative one, test for blank and execute a macro: ActiveCell.Offset(1, 0).select If ActiveCell.Value = "" then Macro2 End If or If ActiveCell.Offset(1,0).Value = "" then Macro2 End If To test if the value is numeric: If IsNumeric(ActiveCell.Value) Then Blah, blah, blah End if Keep in mind just a few things: Offset(row, col). The first number represents the number of rows and the second number represents the number of columns. For rows, a positive number means a move down. A negative number means a move up. For columns, a positive number means a move to the right and a negative number means a move to the left. Also, macros are simply public sub procedures in a module. You can call them as you would any other sub procedure within the scope you're in. A public sub procedure in a module has public scope, so you can call it from anywhere. HTH! "Branching macros" wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
If I may: I would not use the keyword "Call." It's not necessary and adds a
layer of processing. Call the proc directly. So, instad of: Call MyProc Just do this: MyProc Think about it. Call is an intrinsic subprocedure that takes as an argument the procedure you're calling so that it can call it for you! Just call it yourself! Save a layer of processing. Call exists mostly for legacy backward compatibility but it's time for it's demise now. We don't need it anymore. "JW" wrote: You can test for the next cell being empty by using: If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
Just curious. What makes you think that using the key word Call adds a layer
of processing? I have never heard that. I know that using Run adds a lot of overhead and processing but I had never heard anything about Call. Do you have any related articles or references. -- HTH... Jim Thomlinson "ppsa" wrote: If I may: I would not use the keyword "Call." It's not necessary and adds a layer of processing. Call the proc directly. So, instad of: Call MyProc Just do this: MyProc Think about it. Call is an intrinsic subprocedure that takes as an argument the procedure you're calling so that it can call it for you! Just call it yourself! Save a layer of processing. Call exists mostly for legacy backward compatibility but it's time for it's demise now. We don't need it anymore. "JW" wrote: You can test for the next cell being empty by using: If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
Look at the statement carefully:
Call [SPACE] ProcName That's the format for any sub procedure call with an argument! Same format: ProcName [SPACE] ArgumentList Both these statements work: MyProc Call MyProc Both are calls to subprocedures. The difference is that the first one does not have arguments, while the second one does! The second one is a call to an intrinsic (built in to VBA as opposed to created by a developer) sub procedure called "Call" with an argument (MyProc). The second one adds a layer because it calls another procedure (Call) that calls yours, AND it has an argument pass thrown in to boot (another layer)! It's plain as day. The first statement accomplishes what the second one does but with one difference: It's more efficient. If you'll only believe an authority (though not all articles are written by authorities), I can tell you that I have a PhD in computer science and used to teach at Harvard, which is true. I could write an article about this and maybe then you'd feel better about it, but I hope you won't put me to that trouble! :) All you have to do is look at the statement and the truth will stare back at you with wide eyes! "Jim Thomlinson" wrote: Just curious. What makes you think that using the key word Call adds a layer of processing? I have never heard that. I know that using Run adds a lot of overhead and processing but I had never heard anything about Call. Do you have any related articles or references. -- HTH... Jim Thomlinson "ppsa" wrote: If I may: I would not use the keyword "Call." It's not necessary and adds a layer of processing. Call the proc directly. So, instad of: Call MyProc Just do this: MyProc Think about it. Call is an intrinsic subprocedure that takes as an argument the procedure you're calling so that it can call it for you! Just call it yourself! Save a layer of processing. Call exists mostly for legacy backward compatibility but it's time for it's demise now. We don't need it anymore. "JW" wrote: You can test for the next cell being empty by using: If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
I was not doubting you I was just curious. When I tested it I found no
appreciable difference so I was wondering if there was any kind of info on it. I like knowing what the compiler is up to and what will generate more efficient code... Sub TimeTest() Dim lng As Long Dim dblStartTime As Double Dim dblEndTime As Double Const lngLoops As Long = 100000000 dblStartTime = Timer For lng = 1 To lngLoops Call DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime dblStartTime = Timer For lng = 1 To lngLoops DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime End Sub Sub DoStuff() Dim x As Long x = 1 End Sub -- HTH... Jim Thomlinson "ppsa" wrote: Look at the statement carefully: Call [SPACE] ProcName That's the format for any sub procedure call with an argument! Same format: ProcName [SPACE] ArgumentList Both these statements work: MyProc Call MyProc Both are calls to subprocedures. The difference is that the first one does not have arguments, while the second one does! The second one is a call to an intrinsic (built in to VBA as opposed to created by a developer) sub procedure called "Call" with an argument (MyProc). The second one adds a layer because it calls another procedure (Call) that calls yours, AND it has an argument pass thrown in to boot (another layer)! It's plain as day. The first statement accomplishes what the second one does but with one difference: It's more efficient. If you'll only believe an authority (though not all articles are written by authorities), I can tell you that I have a PhD in computer science and used to teach at Harvard, which is true. I could write an article about this and maybe then you'd feel better about it, but I hope you won't put me to that trouble! :) All you have to do is look at the statement and the truth will stare back at you with wide eyes! "Jim Thomlinson" wrote: Just curious. What makes you think that using the key word Call adds a layer of processing? I have never heard that. I know that using Run adds a lot of overhead and processing but I had never heard anything about Call. Do you have any related articles or references. -- HTH... Jim Thomlinson "ppsa" wrote: If I may: I would not use the keyword "Call." It's not necessary and adds a layer of processing. Call the proc directly. So, instad of: Call MyProc Just do this: MyProc Think about it. Call is an intrinsic subprocedure that takes as an argument the procedure you're calling so that it can call it for you! Just call it yourself! Save a layer of processing. Call exists mostly for legacy backward compatibility but it's time for it's demise now. We don't need it anymore. "JW" wrote: You can test for the next cell being empty by using: If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
I ran my own code using "timeGetTime" over 100,000 loops. "Call" averaged 48 milliseconds. Without "Call" averaged 48 milliseconds. (identical result) Each version was run 20 times. Also, Call is self documenting. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jim Thomlinson" wrote in message I was not doubting you I was just curious. When I tested it I found no appreciable difference so I was wondering if there was any kind of info on it. I like knowing what the compiler is up to and what will generate more efficient code... Sub TimeTest() Dim lng As Long Dim dblStartTime As Double Dim dblEndTime As Double Const lngLoops As Long = 100000000 dblStartTime = Timer For lng = 1 To lngLoops Call DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime dblStartTime = Timer For lng = 1 To lngLoops DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime End Sub Sub DoStuff() Dim x As Long x = 1 End Sub -- HTH... Jim Thomlinson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
It's compiled away!
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ppsa" wrote in message ... Look at the statement carefully: Call [SPACE] ProcName That's the format for any sub procedure call with an argument! Same format: ProcName [SPACE] ArgumentList Both these statements work: MyProc Call MyProc Both are calls to subprocedures. The difference is that the first one does not have arguments, while the second one does! The second one is a call to an intrinsic (built in to VBA as opposed to created by a developer) sub procedure called "Call" with an argument (MyProc). The second one adds a layer because it calls another procedure (Call) that calls yours, AND it has an argument pass thrown in to boot (another layer)! It's plain as day. The first statement accomplishes what the second one does but with one difference: It's more efficient. If you'll only believe an authority (though not all articles are written by authorities), I can tell you that I have a PhD in computer science and used to teach at Harvard, which is true. I could write an article about this and maybe then you'd feel better about it, but I hope you won't put me to that trouble! :) All you have to do is look at the statement and the truth will stare back at you with wide eyes! "Jim Thomlinson" wrote: Just curious. What makes you think that using the key word Call adds a layer of processing? I have never heard that. I know that using Run adds a lot of overhead and processing but I had never heard anything about Call. Do you have any related articles or references. -- HTH... Jim Thomlinson "ppsa" wrote: If I may: I would not use the keyword "Call." It's not necessary and adds a layer of processing. Call the proc directly. So, instad of: Call MyProc Just do this: MyProc Think about it. Call is an intrinsic subprocedure that takes as an argument the procedure you're calling so that it can call it for you! Just call it yourself! Save a layer of processing. Call exists mostly for legacy backward compatibility but it's time for it's demise now. We don't need it anymore. "JW" wrote: You can test for the next cell being empty by using: If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
And it has the benefit that parameters are ALWAYS passed in parentheses if
Call is used, so a function returning a value and a procedure look similar, adding to the documentary value IMO. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Cone" wrote in message ... I ran my own code using "timeGetTime" over 100,000 loops. "Call" averaged 48 milliseconds. Without "Call" averaged 48 milliseconds. (identical result) Each version was run 20 times. Also, Call is self documenting. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jim Thomlinson" wrote in message I was not doubting you I was just curious. When I tested it I found no appreciable difference so I was wondering if there was any kind of info on it. I like knowing what the compiler is up to and what will generate more efficient code... Sub TimeTest() Dim lng As Long Dim dblStartTime As Double Dim dblEndTime As Double Const lngLoops As Long = 100000000 dblStartTime = Timer For lng = 1 To lngLoops Call DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime dblStartTime = Timer For lng = 1 To lngLoops DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime End Sub Sub DoStuff() Dim x As Long x = 1 End Sub -- HTH... Jim Thomlinson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
Just for the record, I ran Jim's code and the part using "Call" was faster.
23.519 seconds with call 23.737 seconds without -- JNW "Jim Thomlinson" wrote: I was not doubting you I was just curious. When I tested it I found no appreciable difference so I was wondering if there was any kind of info on it. I like knowing what the compiler is up to and what will generate more efficient code... Sub TimeTest() Dim lng As Long Dim dblStartTime As Double Dim dblEndTime As Double Const lngLoops As Long = 100000000 dblStartTime = Timer For lng = 1 To lngLoops Call DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime dblStartTime = Timer For lng = 1 To lngLoops DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime End Sub Sub DoStuff() Dim x As Long x = 1 End Sub -- HTH... Jim Thomlinson "ppsa" wrote: Look at the statement carefully: Call [SPACE] ProcName That's the format for any sub procedure call with an argument! Same format: ProcName [SPACE] ArgumentList Both these statements work: MyProc Call MyProc Both are calls to subprocedures. The difference is that the first one does not have arguments, while the second one does! The second one is a call to an intrinsic (built in to VBA as opposed to created by a developer) sub procedure called "Call" with an argument (MyProc). The second one adds a layer because it calls another procedure (Call) that calls yours, AND it has an argument pass thrown in to boot (another layer)! It's plain as day. The first statement accomplishes what the second one does but with one difference: It's more efficient. If you'll only believe an authority (though not all articles are written by authorities), I can tell you that I have a PhD in computer science and used to teach at Harvard, which is true. I could write an article about this and maybe then you'd feel better about it, but I hope you won't put me to that trouble! :) All you have to do is look at the statement and the truth will stare back at you with wide eyes! "Jim Thomlinson" wrote: Just curious. What makes you think that using the key word Call adds a layer of processing? I have never heard that. I know that using Run adds a lot of overhead and processing but I had never heard anything about Call. Do you have any related articles or references. -- HTH... Jim Thomlinson "ppsa" wrote: If I may: I would not use the keyword "Call." It's not necessary and adds a layer of processing. Call the proc directly. So, instad of: Call MyProc Just do this: MyProc Think about it. Call is an intrinsic subprocedure that takes as an argument the procedure you're calling so that it can call it for you! Just call it yourself! Save a layer of processing. Call exists mostly for legacy backward compatibility but it's time for it's demise now. We don't need it anymore. "JW" wrote: You can test for the next cell being empty by using: If IsEmpty(ActiveCell.Offset(1,0)) Then MsgBox "Yup, it's empty" You can call other macros when needed by using: Call MacroName If the macro you are calling has arguements, pass them as well: Call MacroName(arguement1) Branching macros wrote: I am trying to create a branching macro that tests for certain conditions and then proceeds to one or more macros that then sub branch to other macros. Is there any way for a macro to take a relative address, go down one cell, and test to see if the cell is blank, or contains text or values. And is it possible to have a macro branch to a new one, do that macro and then return to the same relative space and continue at that point in the macro function? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
I always use the Call keyword to document my code. That makes it obvious what
that line of code is doing. With no appreciable difference between the two methods I will stick with Call. Thank Jim... -- HTH... Jim Thomlinson "Jim Cone" wrote: I ran my own code using "timeGetTime" over 100,000 loops. "Call" averaged 48 milliseconds. Without "Call" averaged 48 milliseconds. (identical result) Each version was run 20 times. Also, Call is self documenting. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jim Thomlinson" wrote in message I was not doubting you I was just curious. When I tested it I found no appreciable difference so I was wondering if there was any kind of info on it. I like knowing what the compiler is up to and what will generate more efficient code... Sub TimeTest() Dim lng As Long Dim dblStartTime As Double Dim dblEndTime As Double Const lngLoops As Long = 100000000 dblStartTime = Timer For lng = 1 To lngLoops Call DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime dblStartTime = Timer For lng = 1 To lngLoops DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime End Sub Sub DoStuff() Dim x As Long x = 1 End Sub -- HTH... Jim Thomlinson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
Hmmm... in my world, testing trumps appeals to authority every time.
Testing 100 million calls each using (1) Call and (2) just the procedure name, produced no significant difference on my machine. Specifically, the version *not* using Call was *slower* on average by less than 25 nanoseconds per call, but the difference wasn't statistically significant. In article , ppsa wrote: If you'll only believe an authority (though not all articles are written by authorities), I can tell you that I have a PhD in computer science and used to teach at Harvard, which is true. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
You make a good point. The difference really is miniscule and MAY only be
felt in a complex environment: many arguments, complex arguments such as arrays and pointers, etc., resource-intensive processes running concurrently, and so on. Even then, Id concede that youd be hard pressed to find a difference in performance. But thats not really what its about. It goes against best practices. Philosophically: Why call a procedure to call a procedure? Whether the hit is felt in performance or not, it most definitely is another layer of processing, and, as such, it is wasteful. Even if only philosophically. I've heard the argument for documentation, but here are some points about that: There are better ways to document things than with a call that makes a call to a call. You could use €˜^ for sub procedure calls and, lets say, €˜^^ for function calls: MyProc €˜^ MyFunc() €˜^^ Either of which should stand out better than the word Call. Besides, what's really the point of documenting a call? If there is a point, then why not come up with any combination of symbols to represent other types of statements? Why document just calls, after all? Why not also document assignments, branches, etc.? We usually document processes; we don't usually point out: "This is a call, this is a function, this is a branch." When we document code, it is to point out what happens when, what the logic is behind what we're doing, and so on. Besides, really, with practice you learn to spot the sub procedure calls as easily as with a call to Call, or as easily as you spot function calls. Or assignments, branches, etc. In almost all cases, the Call statement makes no unique contribution. It offers no added value and is philosophically bizarre. It is thus silly (no offense meant to anyone). There is one case in which the Call statement makes sense. And thats when you want to discard the return value of a function. Call MyFunc() will run the function as if it were a sub procedure and the return value will be ignored. This is the only instance where Call actually has something unique and justifiable to contribute. I realize all this may seem very anal to a lot of people, but I do believe that the philosophy you employ in your coding will end up reflecting in a lot of places: Elegance (simplicity + functionality), efficiency, clarity, consistency, etc., all of which ultimately add up to make better programs. Peter :) "Jim Cone" wrote: I ran my own code using "timeGetTime" over 100,000 loops. "Call" averaged 48 milliseconds. Without "Call" averaged 48 milliseconds. (identical result) Each version was run 20 times. Also, Call is self documenting. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jim Thomlinson" wrote in message I was not doubting you I was just curious. When I tested it I found no appreciable difference so I was wondering if there was any kind of info on it. I like knowing what the compiler is up to and what will generate more efficient code... Sub TimeTest() Dim lng As Long Dim dblStartTime As Double Dim dblEndTime As Double Const lngLoops As Long = 100000000 dblStartTime = Timer For lng = 1 To lngLoops Call DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime dblStartTime = Timer For lng = 1 To lngLoops DoStuff Next lng dblEndTime = Timer MsgBox "Duration " & dblEndTime - dblStartTime End Sub Sub DoStuff() Dim x As Long x = 1 End Sub -- HTH... Jim Thomlinson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
branching macro
In article ,
ppsa wrote: Philosophically: Why call a procedure to call a procedure? I guess this is the crux of my question to you: What evidence do you have that the Call *statement* is implemented as a procedure? I find none in XL/VBA Help, nor does the empirical timing evidence provide any. I don't have access to anything that can check the internal control stack. It seems to me that it's just as likely to be implemented as an (optional) compiler directive, like "Let" is. IF that's the case, using it becomes just a matter of style, with no implications for performance. Like whitespace, or line continuations, or long variable names. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Branching Logic after SENDMAIL | Excel Worksheet Functions | |||
Branching Drop Down Menu | Excel Discussion (Misc queries) | |||
Multiple conditional branching in excel | Excel Programming | |||
Branching in a macro | Excel Programming | |||
Branching Macro | Excel Programming |