For all of the following discussion, let's say the 5 numbers to toggle
between in the listed order are these... 5, 123, 73, 10, 9 and the toggle
variable is named V (where V is assumed to be initialized to one of the
variables in the list).

1. If..Then blocking is not really that bad to implement...

If V = 5 Then
V = 123
ElseIf V = 123 Then
V = 73
ElseIf V = 73 Then
V = 10
ElseIf V = 10 Then
V = 9
ElseIf V = 9 Then
V = 5
End If

2. Select..Case blocking can be made more compact...

Select Case V
Case 5: V = 123
Case 123: V = 73
Case 73: V = 10
Case 10: V = 9
Case 9: V = 5
End Select

3. Bernd's array method is also a feasible way to go...

' These statements go in the module's (General)(Declarations) section
Option Base 0
Dim Arr As Variant
Static Index As Long

' These statements go in your toggle subroutine
Arr = Array(5, 123, 73, 10, 9)
Index = (Index + 1) Mod UBound(Arr)
V = Arr(Index)

4. A one-liner method involves setting up a string with all the numbers
expanded to the same number of digits as the longest number in the
toggle sequence (using leading zeroes) with any non-digit character
between them (if that number has a decimal point in it, consider it
as a digit). So, for our example, this is how to set it up so the
toggle subroutine is a one-liner...

' Declare V as a Long or Double depending on what the actual values
' are and put it in the module's (General)(Declarations) section
Dim V As Long

' Initialize a toggle sequence constant named TS for this example
' in the code module's (General)(Declarations) section; note that
' the first number is repeated at the end.
Const TS As String = "005,123,073,010,009,005"

' This one-liner statement goes in your toggle subroutine
V = Mid(TS, InStr(TS, Format(V, "000")) + 4, 3)

The value being added (4 in this case) is one greater than the
number of digits in the largest toggle sequence value (123 has
3 digits, so we add 4... the extra 1 is so we will skip over the
non-digit delimiter (a comma in my example), the 3 (number of
characters returned from the Mid function call) is equal to the
number of digits in the largest toggle sequence value.

--
Rick (MVP - Excel)



"Robert Crandal" wrote in message
...
Hi Rick. I usually do my programming with C++ or Intel
assembly language, which I know have bit flipping operators.
I was just curious if VBA had something similar.

However, your solution is very interesting because it can
be generalized. However, if I wanted to toggle between
5 different values, it would involve a "little extra work"
of the if-then statements right?? In cases like that, would
it be better to use a "select-case" statement??

BTW, I always appreciate your expert advice. thank you!


"Rick Rothstein" wrote in message
...
Others have shown you other ways to toggle between 0 and 1 (although I
kind of think the subtraction method I proposed is the easiest to
understand and implement). And this method lends itself to generalizing
as well. To toggle a variable V between any two numbers N1 and N2, you
would just use this line of code...

V = N1 + N2 - V

Think about it... if V equals N1, then the above line returns N2 and if V
equals N2, then the above line returns N1... simple, right? This is
exactly what I used for my initial response to you where N1 equaled 0 and
N2 equaled 1. Now, if one of your numbers is not zero, then there might
be a little extra work involved to implement this (same problem if you
use If..Then statements as well)... all numeric variables start off
defaulted to zero, so you can use the above line immediately when one of
the numbers is zero; but when both numbers are not zero, then you have to
get your variable V initialized to one of the numbers before you can
start toggling them.

--
Rick (MVP - Excel)