Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
Have a look he
http://www.dicks-blog.com/archives/2004/07/23/ RBS "Ian Gilmore" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
... 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" wrote in message ... 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise comparison question
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" wrote in message ... ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bitwise functions | Excel Worksheet Functions | |||
bitwise operations as in xor | Excel Discussion (Misc queries) | |||
Complicated comparison and highlighting question | Excel Worksheet Functions | |||
Where are Bitwise Operators | Excel Worksheet Functions | |||
Bitwise And | Excel Programming |