Holding a variable in a dormant macro

J

jindon

MrExcel MVP

Joined

Aug 21, 2004

Messages

16,995

• Feb 6, 2009

• #2

Use a Global variable like

Code:

Public a As Long
 
Sub FirstMacro()
MsgBox a
a = 1
SecondMacro
End Sub
 
Sub SecondMacro()
MsgBox a
End Sub

 

Upvote 0

S

ScottD

Board Regular

Joined

Sep 8, 2008

Messages

67

• Feb 6, 2009

• #3

how about placing the number 0 somewhere in the excel sheet and designating say

a=activesheet.range(A6).value

then at the end of the macro have

.range(a6).value=1

 

Last edited: Feb 6, 2009

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Feb 6, 2009

• #4

Thanks Jindon, that makes sense.

In theory then that would mean if i used

Code:

Sub test()
If a > 0 Then do something
more code
a = 1
End Sub

That in the first run a would have a default of 0 because it hasn't been defined.
But after it's defined as a=1 it will remain that way?

 

Upvote 0

J

jindon

MrExcel MVP

Joined

Aug 21, 2004

Messages

16,995

• Feb 6, 2009

• #5

jasonb75 said:

Thanks Jindon, that makes sense.

In theory then that would mean if i used

Code:

Sub test()
If a > 0 Then do something
more code
a = 1
End Sub

That in the first run a would have a default of 0 because it hasn't been defined.
But after it's defined as a=1 it will remain that way?

Click to expand...

Interpretation of above code is, no execution of test subroutine only for the 1st time when a is delcared as a global variable.

 

Last edited: Feb 6, 2009

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Feb 6, 2009

• #6

Good answer though Scott, and something I hadn't thought of.

Only problem would be that when the workbook is saved so will the value of 1, which needs to be back to 0 when the workbook is opened again.

ScottD said:

how about placing the number 0 somewhere in the excel sheet and designating say

a=activesheet.range(A6).value

then at the end of the macro have

.range(a6).value=1

Click to expand...

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Feb 6, 2009

• #7

Thanks Jindon, that's exactly what I wanted to hear

Thanks very much for your help.

jindon said:

Interpretation of above code is, no execution of test subroutine only for the 1st time when a is delcared as a global bariable.

Click to expand...

 

Upvote 0

J

jindon

MrExcel MVP

Joined

Aug 21, 2004

Messages

16,995

• Feb 6, 2009

• #8

In that case, I would use Boolean type variable. (True/False)
False is a default value

Code:

Public PubFlg As Boolean
 
Sub test()
If PubFlg Then do something
more code
PubFlg = True
End Sub

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Feb 6, 2009

• #9

That looks like a better option Jindon,

I'm assuming that default would be Pubflag = False

In the code would "If PubFlg Then" Mean "If Pubflg = True" or "If Pubflg = False"?

Sorry for so many questions but not used this before.

Thanks again.

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Feb 6, 2009

• #10

You could use a static variable

Code:

Sub myRoutine()
    Static hasRun As Boolean

    If hasRun Then
        Rem do something
    Else
        hasRun = True
        Rem something else (first pass only)
    End If

End Sub

 

Last edited: Feb 6, 2009

Upvote 0