"bisecting" loop with boolean flags

[Guest]

Help pls! I need a procedure which can help me to select a selection A and B
respectively, so that selection A is chosen every other time the procedure
runs (and the same for selection B). I intended to make a procedure that sets
the flag to TRUE when the flag is FALSE and vice versa. In the code presented
below, the boolean value remains FALSE and never changes to TRUE.

What I am doing wrong??

Public Sub Insert_Flag()
Dim Worksheet As Worksheet
Dim Flg As Boolean

Set Worksheet = ActiveSheet

Select Case Flg

Case Flg = True (Selection A)
Flg = False
If Flg = False Then Cells(1, 3) = False

Case Flg = False (Selection B)
Flg = True
If Flg = True Then Cells(1, 3) = True

End Select

End Sub

 

[Niek Otten]

Use

Static Flg as Boolean

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Help pls! I need a procedure which can help me to select a selection A and B
| respectively, so that selection A is chosen every other time the procedure
| runs (and the same for selection B). I intended to make a procedure that sets
| the flag to TRUE when the flag is FALSE and vice versa. In the code presented
| below, the boolean value remains FALSE and never changes to TRUE.
|
| What I am doing wrong??
|
| Public Sub Insert_Flag()
| Dim Worksheet As Worksheet
| Dim Flg As Boolean
|
| Set Worksheet = ActiveSheet
|
| Select Case Flg
|
| Case Flg = True (Selection A)
| Flg = False
| If Flg = False Then Cells(1, 3) = False
|
| Case Flg = False (Selection B)
| Flg = True
| If Flg = True Then Cells(1, 3) = True
|
| End Select
|
| End Sub

 

[John Coleman]

You have declared Flg to be a local (private) variable to the
procedure. You need a public variable. In a general code module,
outside of any sub or function, have the line

Public Flg as Boolean

and delete the line

Dim Flg as Boolean

from your code. This will toggle back and forth, It will start false
when you open Excel or whenever you reset the project. If you want
persistence across sessions you need to do something like hide the flag
in an out-of-the-way cell or something fancier like a registry setting
or entry in the names collection.

HTH

-John Coleman

 

[Guest]

Dear John & Niek,

thanks a bunch for your input, I had no idea I needed to declare a static
variable. I did as you suggested, John, but unfortunately, the procedure
still doesn't toggle anywhere away from FALSE. Could there be any other
points I should take into consideration? I am running the procedure from
general code module (not a worksheet one).

Thanks in advance
Henrik

 

[John Coleman]

By all means go the static approach if it works for you. It is probably
a cleaner approach unless other procedures need access to the same
flag. Still - the Public variable approach should work. I just tried
the following code to check my sanity (which is often an open
question):

Public Flg As Boolean

Public Sub Insert_Flag()

Select Case Flg
Case True
Flg = False
If Flg = False Then Cells(1, 3) = False
Case False
Flg = True
If Flg = True Then Cells(1, 3) = True

End Select
End Sub

It toggles back and forth for me with no problem.

Are you sure that you declared the public variable *outside* the
procedure? If you did, are you sure that you eliminated the local
variable declaration *inside* the procedure as well? Any local
declaration would hide any global declaration within the scope of the
procedure.

-John Coleman

 

[Guest]

John,

Given the state of my own wet matter, I don't question your sanity. Your
version works fine. My problem here was that I had defined Case Flg = True,
which didn't work, whereas you defined it Case True which works perfectly. As
you may have guessed I'm a newbie.

Many thanks, you saved me quite a number of my valuable few remaining hairs.

Henrik

 

[bz]

Help pls! I need a procedure which can help me to select a selection A
and B respectively, so that selection A is chosen every other time the
procedure runs (and the same for selection B). I intended to make a
procedure that sets the flag to TRUE when the flag is FALSE and vice
versa. In the code presented below, the boolean value remains FALSE and
never changes to TRUE.

What I am doing wrong??

Public Sub Insert_Flag()
Dim Worksheet As Worksheet
Dim Flg As Boolean

Set Worksheet = ActiveSheet

Select Case Flg

Case Flg = True (Selection A)
Flg = False
If Flg = False Then Cells(1, 3) = False

Case Flg = False (Selection B)
Flg = True
If Flg = True Then Cells(1, 3) = True

End Select

End Sub

I think your flag needs to be public so that it doesn't get re-initialized
between executions of your subroutine. I doubt that private variables
'exist' when the subroutine isn't active.

I think also that there is no guarantee that Flg will hold any specific
value the first time you run your routine (or it might be null).

You could use the contents of a cell in your workbook as your flag, if you
don't want a global variable. You could start with 1 or -1 and multiply it
by -1 every time your routine executes.




--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

(e-mail address removed) remove ch100-5 to avoid spam trap

 

[Peter T]

One more way -

Public gbFlag As Boolean

Public Sub Insert_Flag2()
Cells(1, 3) = gbFlag
gbFlag = Not gbFlag
End Sub

Regards,
Peter T

 

[Guest]

Peter T and bz,

thanks for your input. Both are super elegant solutions. Thanks also for
helping me expand my knowledge of vb and programming logic in general.

Henrik