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

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"

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


"Helmut Weber" wrote:

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

Have a look here:

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

RBS

"Ian Gilmore" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

Peter T wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>
Thany you very much Peter. A really good explanation.

>>> ... I am told I can store the value of all combinations
>>> in a single integer.

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

--
HTH :>)
Dana DeLouis
Windows XP & Office 2007


"Ian Gilmore" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Peter T wrote:
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>
>>
> Thany you very much Peter. A really good explanation.

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
"Dana DeLouis" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> >>> ... I am told I can store the value of all combinations
> >>> in a single integer.
>
> 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
>
> --
> HTH :>)
> Dana DeLouis
> Windows XP & Office 2007
>
>
> "Ian Gilmore" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Peter T wrote:
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >>> 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
> >>
> >>
> > Thany you very much Peter. A really good explanation.
>
>