Bitwise comparison question

[Ian Gilmore]

I have some data which presents active days like this NNYYYYN or any
combination of them. I am told I can store the value of all combinations
in a single integer. I have read that this can be acheived by using
bitwise comparison. Unfortunately, I have never used this so don't
really understand it without seeing a simple explanation. I know you
guys are the best so would appreciate an explanation and ideally some
sample code.

Many Thanks

 

[Helmut Weber]

Hi Ian,

active days like this NNYYYYN

I understand that YYYY may represent a year from 0000 to 9999.
But what would be N?

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Guest]

I think he means that for each day the value is yes or no. If represented by
0/1 the bitwise comparison makes sense.

 

[RB Smissaert]

Have a look here:

http://www.dicks-blog.com/archives/2004/07/23/

RBS

 

[Peter T]

Function bHasBit(ByVal num As Long, ByVal bit As Long) As Boolean
bHasBit = ((num And 2 ^ bit) = 2 ^ bit)
End Function

Sub test()
Dim i As Long
Dim strIn As String
Dim strOut As String
Dim sBin As String, sVal
Dim WeekBits As Long ' store somewhere else, eg Global

strIn = "NNYYYYN"
For i = 1 To Len(strIn)
WeekBits = WeekBits Or 2 ^ -((Mid(strIn, i, 1) = "Y") * i)

sBin = sBin & -((Mid(strIn, i, 1) = "Y"))
sVal = sVal & 2 ^ -((Mid(strIn, i, 1) = "Y") * i) & " "
Next

For i = 1 To 7
strOut = strOut & IIf(bHasBit(WeekBits, i), "Y", "N")
Next

MsgBox strIn & vbCr & sBin & vbCr & sVal & vbCr & strOut
End Sub

Hopefully looking at sBin and sVal above will go some way to giving an
explanation.
Note that bits are numbered from zero. So this demo takes advantage of not
bothering about filling the first bit, which it does with any false's, 2^0 =
1. The use of 'Or' instead or '+' prevents the false-1 added more than once.

See 'And' & 'Or' in help and more about bitwise comparison.

FWIW, you could store a month's worth of N/Y, 31days. BUT, the example would
need to modified to offset by -1, ie zero to 30. Also, you couldn't add any
false 2^0 as in this lazy demo (guess purists wouldn't like the use of the
minus to convert True(-1) to +1).

Regards,
Peter T

 

[Ian Gilmore]

Function bHasBit(ByVal num As Long, ByVal bit As Long) As Boolean
bHasBit = ((num And 2 ^ bit) = 2 ^ bit)
End Function

Sub test()
Dim i As Long
Dim strIn As String
Dim strOut As String
Dim sBin As String, sVal
Dim WeekBits As Long ' store somewhere else, eg Global

strIn = "NNYYYYN"
For i = 1 To Len(strIn)
WeekBits = WeekBits Or 2 ^ -((Mid(strIn, i, 1) = "Y") * i)

sBin = sBin & -((Mid(strIn, i, 1) = "Y"))
sVal = sVal & 2 ^ -((Mid(strIn, i, 1) = "Y") * i) & " "
Next

For i = 1 To 7
strOut = strOut & IIf(bHasBit(WeekBits, i), "Y", "N")
Next

MsgBox strIn & vbCr & sBin & vbCr & sVal & vbCr & strOut
End Sub

Hopefully looking at sBin and sVal above will go some way to giving an
explanation.
Note that bits are numbered from zero. So this demo takes advantage of not
bothering about filling the first bit, which it does with any false's, 2^0 =
1. The use of 'Or' instead or '+' prevents the false-1 added more than once.

See 'And' & 'Or' in help and more about bitwise comparison.

FWIW, you could store a month's worth of N/Y, 31days. BUT, the example would
need to modified to offset by -1, ie zero to 30. Also, you couldn't add any
false 2^0 as in this lazy demo (guess purists wouldn't like the use of the
minus to convert True(-1) to +1).

Regards,
Peter T

Thany you very much Peter. A really good explanation.

 

[Dana DeLouis]

... I am told I can store the value of all combinations
Just another idea...

Sub Demo()
Dim s As String
Dim n As Long

s = "NNYYYYN"
s = Replace(s, "N", "0")
s = Replace(s, "Y", "1")
n = WorksheetFunction.Bin2Dec(s)
End Sub

 

[Peter T]

Hi Dana,

I like the use of Replace, looping a string of 0/1's would be faster than
N/Y's and use of 'If' etc.

A few things to consider with Bin2Dex though:

- Requires Analysis toolpack and probably slower in VBA than using a simple
loop

- Converts a Binary string with the least significant bit last, in contrast
to least significant bit first.

This might be fine providing any other functions that use the added bit
number work with same order. So it means in effect re-numbering the days
from 1-7 to 6-0. Also, in any related functions 2^0=1 should be reserved for
the day numbered 0, and not for gratuitously adding any False as in my
previous demo.

FWIW, a bit numbered 'n' in a number named 'num' can in effect be set to
True like this
num = num Or 2^n

and set to False
num = num And Not 2^n

or toggled
num = num Xor 2^n

Regards,
Peter T


That would be fine providing other cod