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?

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?

Sure:

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?

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?
> > > >
> > > >

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"
<James_Thomlinson@owfg-Re-Move-This-.com>
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

It's compiled away!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ppsa" <(E-Mail Removed)> wrote in message
news:6FED3A1F-F814-49D2-8D41-(E-Mail Removed)...
> 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?
>> > >
>> > >