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?

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?

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?

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?

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?

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?

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?

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.

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?

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?