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?

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?

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?

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?

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?

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?

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?

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?

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

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?

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

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?

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

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.

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

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.