-
May 30th, 2022, 03:50 AM
#1
Thread Starter
Junior Member
Excel visual basic form, overflow issue
I'm working on a custom form that contains 32 checkboxes, I'm using a double type variable to store the checked flags via bit shifting.
The problem is that the max value of this variable can be 4294967295, and when the last checkbox is checked, I get an error that says "overflow".
What type of variables should I use to store the value 4294967295?
This is the code I'm using:
Code:
Private Sub CancelBttn_Click()
Unload Me
End Sub
Private Sub OKBttn_Click()
Cells.Item(ActiveCell.Row, ActiveCell.Column) = GetFlags()
Unload Me
End Sub
Private Sub UserForm_Activate()
'propogate the flag names into the check boxes
Dim i As Double
Dim flagCaption As String
For i = 0 To 31
flagCaption = Sheets("FlagNames").Cells.Item(i + 2, 2)
If (flagCaption = "") Then
flagCaption = "Flag #" + Str$(i) + " (Unused)"
End If
Call SetFlagCaption(i, flagCaption)
Next i
SetFlags (Cells.Item(ActiveCell.Row, ActiveCell.Column))
End Sub
Public Sub SetFlagCaption(ByVal position As Double, ByVal flagCaption As String)
'Iterate over all controls
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "CheckBox" Then
If oCtrl.Name = "FlagCheckBoxCtrl" & position Then
oCtrl.Caption = flagCaption
Exit For
End If
End If
Next
End Sub
Public Sub SetFlags(ByVal flags As Double)
For index = 0 To 31
If shr(flags, index) And 1 Then
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "CheckBox" Then
If StrComp(oCtrl.Name, "FlagCheckBoxCtrl" & index) = 0 Then
oCtrl.Value = 1
Exit For
End If
End If
Next
End If
Next
End Sub
Public Function GetFlags() As Double
Dim selectedFlags As Double
selectedFlags = 0
For index = 0 To 31
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "CheckBox" Then
If StrComp(oCtrl.Name, "FlagCheckBoxCtrl" & index) = 0 Then
If oCtrl.Value = True Then
selectedFlags = selectedFlags Or shl(1, index)
End If
Exit For
End If
End If
Next
Next
GetFlags = selectedFlags
End Function
Private Function shr(ByVal Value As Double, ByVal Shift As Byte) As Double
shr = Value
If Shift > 0 Then
shr = Int(shr / (2 ^ Shift))
End If
End Function
Private Function shl(ByVal Value As Double, ByVal Shift As Byte) As Double
shl = Value
If Shift > 0 Then
shl = Value * (2 ^ Shift)
End If
End Function
-
May 30th, 2022, 04:01 AM
#2
Re: Excel visual basic form, overflow issue
If you have 32 Flags, why are you using a 64-Bit floating-point Type?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 30th, 2022, 04:37 AM
#3
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
Originally Posted by Zvoni
If you have 32 Flags, why are you using a 64-Bit floating-point Type?
Just trying to avoid the overflow error, not sure why is caused.
-
May 30th, 2022, 04:47 AM
#4
Re: Excel visual basic form, overflow issue
And why are you shifting bits instead of setting/getting a bit in a Long?
When using Shift have a look at these implementations, they don't overflow:
http://www.xbeat.net/vbspeed/c_ShiftLeft.htm
http://www.xbeat.net/vbspeed/c_ShiftRight.htm
-
May 30th, 2022, 04:50 AM
#5
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
Originally Posted by Arnoutdv
Hmm, not sure how to do it, could you show me how to implement it?
I tried these functions, but I always get an overflow error when the last flag is checked
I do not have too much experience in binary operations.
Last edited by jms2505; May 30th, 2022 at 04:54 AM.
-
May 30th, 2022, 04:56 AM
#6
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
The problem seems to be in the SetFlags method in line:
Code:
If shr(flags, index) And 1 Then
-
May 30th, 2022, 06:18 AM
#7
Re: Excel visual basic form, overflow issue
Check this post:
https://www.vbforums.com/showthread....=1#post5559398
Code:
Option Explicit
Private m_Bits(31) As Long
Public Sub InitBits()
Dim i As Long, lBits As Long
lBits = 1
For i = 0 To 30
m_Bits(i) = lValue
If i < 30 Then lBits = lBits * 2
Next i
m_Bits(31) = -2147483648#
End Sub
Public Sub SetBit(lValue As Long, ByVal lIndex As Long)
' Sets indicated bit to one, or ON.
Dim indx As Long, ofst As Long
indx = lIndex \ 32
ofst = lIndex And 31&
lValue(indx) = lValue(indx) Or m_Bits(ofst) 'turn the bit ON
End Sub
Public Function GetBit(lValue As Long, ByVal lIndex As Long) As Long
' Returns zero if the indicated bit is off, NON-ZERO if on.
Dim indx As Long, ofst As Long
indx = lIndex \ 32
ofst = lIndex And 31& 'posn MOD 32
GetBit = lValue And m_Bits(ofst)
End Function
Public Function IsBitSet(lValue As Long, ByVal lIndex As Long) As Boolean
' Returns zero if the indicated bit is off, NON-ZERO if on.
Dim indx As Long, ofst As Long
indx = lIndex \ 32
ofst = lIndex And 31& 'posn MOD 32
IsBitSet = (lValue And m_Bits(ofst)) <> 0
End Function
Public Sub ClearBit(lValue As Long, ByVal posn As Long)
' Sets indicated bit to zero, or OFF.
Dim indx As Long, ofst As Long
indx = posn \ 32
ofst = posn And 31 ' BITS0thru4 ' posn MOD 32
lValue = lValue And (Not m_Bits(ofst)) 'turn the bit OFF
End Sub
Public Function CountBits(lValue As Long) As Long
Dim lValue As Long
If lValue Then
If lValue = -1 Then
CountBits = CountBits + 32
Else
If lValue And &H1 Then CountBits = CountBits + 1
If lValue And &H2 Then CountBits = CountBits + 1
If lValue And &H4 Then CountBits = CountBits + 1
If lValue And &H8 Then CountBits = CountBits + 1
If lValue And &H10& Then CountBits = CountBits + 1
If lValue And &H20& Then CountBits = CountBits + 1
If lValue And &H40& Then CountBits = CountBits + 1
If lValue And &H80& Then CountBits = CountBits + 1
If lValue And &H100& Then CountBits = CountBits + 1
If lValue And &H200& Then CountBits = CountBits + 1
If lValue And &H400& Then CountBits = CountBits + 1
If lValue And &H800& Then CountBits = CountBits + 1
If lValue And &H1000& Then CountBits = CountBits + 1
If lValue And &H2000& Then CountBits = CountBits + 1
If lValue And &H4000& Then CountBits = CountBits + 1
If lValue And &H8000& Then CountBits = CountBits + 1
If lValue And &H10000 Then CountBits = CountBits + 1
If lValue And &H20000 Then CountBits = CountBits + 1
If lValue And &H40000 Then CountBits = CountBits + 1
If lValue And &H80000 Then CountBits = CountBits + 1
If lValue And &H100000 Then CountBits = CountBits + 1
If lValue And &H200000 Then CountBits = CountBits + 1
If lValue And &H400000 Then CountBits = CountBits + 1
If lValue And &H800000 Then CountBits = CountBits + 1
If lValue And &H1000000 Then CountBits = CountBits + 1
If lValue And &H2000000 Then CountBits = CountBits + 1
If lValue And &H4000000 Then CountBits = CountBits + 1
If lValue And &H8000000 Then CountBits = CountBits + 1
If lValue And &H10000000 Then CountBits = CountBits + 1
If lValue And &H20000000 Then CountBits = CountBits + 1
If lValue And &H40000000 Then CountBits = CountBits + 1
If lValue And &H80000000 Then CountBits = CountBits + 1
End If
End If
End Function
-
May 30th, 2022, 06:25 AM
#8
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
Originally Posted by Arnoutdv
Check this post:
https://www.vbforums.com/showthread....=1#post5559398
Code:
Option Explicit
Private m_Bits(31) As Long
Public Sub InitBits()
Dim i As Long, lBits As Long
lBits = 1
For i = 0 To 30
m_Bits(i) = lValue
If i < 30 Then lBits = lBits * 2
Next i
m_Bits(31) = -2147483648#
End Sub
Public Sub SetBit(lValue As Long, ByVal lIndex As Long)
' Sets indicated bit to one, or ON.
Dim indx As Long, ofst As Long
indx = lIndex \ 32
ofst = lIndex And 31&
lValue(indx) = lValue(indx) Or m_Bits(ofst) 'turn the bit ON
End Sub
Public Function GetBit(lValue As Long, ByVal lIndex As Long) As Long
' Returns zero if the indicated bit is off, NON-ZERO if on.
Dim indx As Long, ofst As Long
indx = lIndex \ 32
ofst = lIndex And 31& 'posn MOD 32
GetBit = lValue And m_Bits(ofst)
End Function
Public Function IsBitSet(lValue As Long, ByVal lIndex As Long) As Boolean
' Returns zero if the indicated bit is off, NON-ZERO if on.
Dim indx As Long, ofst As Long
indx = lIndex \ 32
ofst = lIndex And 31& 'posn MOD 32
IsBitSet = (lValue And m_Bits(ofst)) <> 0
End Function
Public Sub ClearBit(lValue As Long, ByVal posn As Long)
' Sets indicated bit to zero, or OFF.
Dim indx As Long, ofst As Long
indx = posn \ 32
ofst = posn And 31 ' BITS0thru4 ' posn MOD 32
lValue = lValue And (Not m_Bits(ofst)) 'turn the bit OFF
End Sub
Public Function CountBits(lValue As Long) As Long
Dim lValue As Long
If lValue Then
If lValue = -1 Then
CountBits = CountBits + 32
Else
If lValue And &H1 Then CountBits = CountBits + 1
If lValue And &H2 Then CountBits = CountBits + 1
If lValue And &H4 Then CountBits = CountBits + 1
If lValue And &H8 Then CountBits = CountBits + 1
If lValue And &H10& Then CountBits = CountBits + 1
If lValue And &H20& Then CountBits = CountBits + 1
If lValue And &H40& Then CountBits = CountBits + 1
If lValue And &H80& Then CountBits = CountBits + 1
If lValue And &H100& Then CountBits = CountBits + 1
If lValue And &H200& Then CountBits = CountBits + 1
If lValue And &H400& Then CountBits = CountBits + 1
If lValue And &H800& Then CountBits = CountBits + 1
If lValue And &H1000& Then CountBits = CountBits + 1
If lValue And &H2000& Then CountBits = CountBits + 1
If lValue And &H4000& Then CountBits = CountBits + 1
If lValue And &H8000& Then CountBits = CountBits + 1
If lValue And &H10000 Then CountBits = CountBits + 1
If lValue And &H20000 Then CountBits = CountBits + 1
If lValue And &H40000 Then CountBits = CountBits + 1
If lValue And &H80000 Then CountBits = CountBits + 1
If lValue And &H100000 Then CountBits = CountBits + 1
If lValue And &H200000 Then CountBits = CountBits + 1
If lValue And &H400000 Then CountBits = CountBits + 1
If lValue And &H800000 Then CountBits = CountBits + 1
If lValue And &H1000000 Then CountBits = CountBits + 1
If lValue And &H2000000 Then CountBits = CountBits + 1
If lValue And &H4000000 Then CountBits = CountBits + 1
If lValue And &H8000000 Then CountBits = CountBits + 1
If lValue And &H10000000 Then CountBits = CountBits + 1
If lValue And &H20000000 Then CountBits = CountBits + 1
If lValue And &H40000000 Then CountBits = CountBits + 1
If lValue And &H80000000 Then CountBits = CountBits + 1
End If
End If
End Function
I think that it could be easier, the problem is that I should UInteger instead of Int, but VBA doesn't support unsigned variables.
To parse from signed to unsiged I used this:
Code:
Cells.Item(ActiveCell.Row, ActiveCell.Column) = Hex2Dbl(Hex(GetFlags()))
Code:
Function Hex2Dbl(h As String) As Double
Hex2Dbl = CDbl("&h0" & h) ' Overflow Error if more than 2 ^ 64
If Hex2Dbl < 0 Then Hex2Dbl = Hex2Dbl + 4294967296# ' 16 ^ 8 = 4294967296
End Function
How could I parse from Unsiged to Signed?
Now the only problem has been reduced to parse the unsigned string to a signed integer.
Here is the full code:
Code:
Private byte_result() As Byte
Private Type double_t
dbl As Double
End Type
Private Type bytes_t
byts(1 To 8) As Byte
End Type
Private Sub CancelBttn_Click()
Unload Me
End Sub
Private Sub OKBttn_Click()
Cells.Item(ActiveCell.Row, ActiveCell.Column) = Hex2Dbl(Hex(GetFlags()))
Unload Me
End Sub
Private Sub UserForm_Activate()
'propogate the flag names into the check boxes
Dim i As Double
Dim flagCaption As String
For i = 0 To 31
flagCaption = Sheets("FlagNames").Cells.Item(i + 2, 2)
If (flagCaption = "") Then
flagCaption = "Flag #" + Str$(i) + " (Unused)"
End If
Call SetFlagCaption(i, flagCaption)
Next i
SetFlags (Cells.Item(ActiveCell.Row, ActiveCell.Column))
End Sub
Public Sub SetFlagCaption(ByVal position As Double, ByVal flagCaption As String)
'Iterate over all controls
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "CheckBox" Then
If oCtrl.Name = "FlagCheckBoxCtrl" & position Then
oCtrl.Caption = flagCaption
Exit For
End If
End If
Next
End Sub
Public Sub SetFlags(ByVal flags As Variant)
If IsNumeric(flags) Then
For index = 0 To 31
If ShiftRight08(flags, index) And 1 Then
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "CheckBox" Then
If StrComp(oCtrl.Name, "FlagCheckBoxCtrl" & index) = 0 Then
oCtrl.Value = 1
Exit For
End If
End If
Next
End If
Next
End If
End Sub
Public Function GetFlags() As Variant
Dim selectedFlags As Variant
selectedFlags = 0
For index = 0 To 31
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "CheckBox" Then
If StrComp(oCtrl.Name, "FlagCheckBoxCtrl" & index) = 0 Then
If oCtrl.Value = True Then
selectedFlags = selectedFlags + ShiftLeft06(CDec(1), index)
End If
Exit For
End If
End If
Next
Next
GetFlags = selectedFlags
End Function
Public Function ShiftRight08(ByVal Value As Variant, ByVal ShiftCount As Byte) As Variant
' by Jost Schwider, jost@schwider.de, 20011010
Select Case ShiftCount
Case 0&: ShiftRight08 = Value
Case 1&: ShiftRight08 = (Value And &HFFFFFFFE) \ &H2&
Case 2&: ShiftRight08 = (Value And &HFFFFFFFC) \ &H4&
Case 3&: ShiftRight08 = (Value And &HFFFFFFF8) \ &H8&
Case 4&: ShiftRight08 = (Value And &HFFFFFFF0) \ &H10&
Case 5&: ShiftRight08 = (Value And &HFFFFFFE0) \ &H20&
Case 6&: ShiftRight08 = (Value And &HFFFFFFC0) \ &H40&
Case 7&: ShiftRight08 = (Value And &HFFFFFF80) \ &H80&
Case 8&: ShiftRight08 = (Value And &HFFFFFF00) \ &H100&
Case 9&: ShiftRight08 = (Value And &HFFFFFE00) \ &H200&
Case 10&: ShiftRight08 = (Value And &HFFFFFC00) \ &H400&
Case 11&: ShiftRight08 = (Value And &HFFFFF800) \ &H800&
Case 12&: ShiftRight08 = (Value And &HFFFFF000) \ &H1000&
Case 13&: ShiftRight08 = (Value And &HFFFFE000) \ &H2000&
Case 14&: ShiftRight08 = (Value And &HFFFFC000) \ &H4000&
Case 15&: ShiftRight08 = (Value And &HFFFF8000) \ &H8000&
Case 16&: ShiftRight08 = (Value And &HFFFF0000) \ &H10000
Case 17&: ShiftRight08 = (Value And &HFFFE0000) \ &H20000
Case 18&: ShiftRight08 = (Value And &HFFFC0000) \ &H40000
Case 19&: ShiftRight08 = (Value And &HFFF80000) \ &H80000
Case 20&: ShiftRight08 = (Value And &HFFF00000) \ &H100000
Case 21&: ShiftRight08 = (Value And &HFFE00000) \ &H200000
Case 22&: ShiftRight08 = (Value And &HFFC00000) \ &H400000
Case 23&: ShiftRight08 = (Value And &HFF800000) \ &H800000
Case 24&: ShiftRight08 = (Value And &HFF000000) \ &H1000000
Case 25&: ShiftRight08 = (Value And &HFE000000) \ &H2000000
Case 26&: ShiftRight08 = (Value And &HFC000000) \ &H4000000
Case 27&: ShiftRight08 = (Value And &HF8000000) \ &H8000000
Case 28&: ShiftRight08 = (Value And &HF0000000) \ &H10000000
Case 29&: ShiftRight08 = (Value And &HE0000000) \ &H20000000
Case 30&: ShiftRight08 = (Value And &HC0000000) \ &H40000000
Case 31&: ShiftRight08 = CBool(Value And &H80000000)
End Select
End Function
Public Function ShiftLeft06(ByVal Value As Long, ByVal ShiftCount As Long) As Long
' by Jost Schwider, jost@schwider.de, 20011001
Select Case ShiftCount
Case 0&
ShiftLeft06 = Value
Case 1&
If Value And &H40000000 Then
ShiftLeft06 = (Value And &H3FFFFFFF) * &H2& Or &H80000000
Else
ShiftLeft06 = (Value And &H3FFFFFFF) * &H2&
End If
Case 2&
If Value And &H20000000 Then
ShiftLeft06 = (Value And &H1FFFFFFF) * &H4& Or &H80000000
Else
ShiftLeft06 = (Value And &H1FFFFFFF) * &H4&
End If
Case 3&
If Value And &H10000000 Then
ShiftLeft06 = (Value And &HFFFFFFF) * &H8& Or &H80000000
Else
ShiftLeft06 = (Value And &HFFFFFFF) * &H8&
End If
Case 4&
If Value And &H8000000 Then
ShiftLeft06 = (Value And &H7FFFFFF) * &H10& Or &H80000000
Else
ShiftLeft06 = (Value And &H7FFFFFF) * &H10&
End If
Case 5&
If Value And &H4000000 Then
ShiftLeft06 = (Value And &H3FFFFFF) * &H20& Or &H80000000
Else
ShiftLeft06 = (Value And &H3FFFFFF) * &H20&
End If
Case 6&
If Value And &H2000000 Then
ShiftLeft06 = (Value And &H1FFFFFF) * &H40& Or &H80000000
Else
ShiftLeft06 = (Value And &H1FFFFFF) * &H40&
End If
Case 7&
If Value And &H1000000 Then
ShiftLeft06 = (Value And &HFFFFFF) * &H80& Or &H80000000
Else
ShiftLeft06 = (Value And &HFFFFFF) * &H80&
End If
Case 8&
If Value And &H800000 Then
ShiftLeft06 = (Value And &H7FFFFF) * &H100& Or &H80000000
Else
ShiftLeft06 = (Value And &H7FFFFF) * &H100&
End If
Case 9&
If Value And &H400000 Then
ShiftLeft06 = (Value And &H3FFFFF) * &H200& Or &H80000000
Else
ShiftLeft06 = (Value And &H3FFFFF) * &H200&
End If
Case 10&
If Value And &H200000 Then
ShiftLeft06 = (Value And &H1FFFFF) * &H400& Or &H80000000
Else
ShiftLeft06 = (Value And &H1FFFFF) * &H400&
End If
Case 11&
If Value And &H100000 Then
ShiftLeft06 = (Value And &HFFFFF) * &H800& Or &H80000000
Else
ShiftLeft06 = (Value And &HFFFFF) * &H800&
End If
Case 12&
If Value And &H80000 Then
ShiftLeft06 = (Value And &H7FFFF) * &H1000& Or &H80000000
Else
ShiftLeft06 = (Value And &H7FFFF) * &H1000&
End If
Case 13&
If Value And &H40000 Then
ShiftLeft06 = (Value And &H3FFFF) * &H2000& Or &H80000000
Else
ShiftLeft06 = (Value And &H3FFFF) * &H2000&
End If
Case 14&
If Value And &H20000 Then
ShiftLeft06 = (Value And &H1FFFF) * &H4000& Or &H80000000
Else
ShiftLeft06 = (Value And &H1FFFF) * &H4000&
End If
Case 15&
If Value And &H10000 Then
ShiftLeft06 = (Value And &HFFFF&) * &H8000& Or &H80000000
Else
ShiftLeft06 = (Value And &HFFFF&) * &H8000&
End If
Case 16&
If Value And &H8000& Then
ShiftLeft06 = (Value And &H7FFF&) * &H10000 Or &H80000000
Else
ShiftLeft06 = (Value And &H7FFF&) * &H10000
End If
Case 17&
If Value And &H4000& Then
ShiftLeft06 = (Value And &H3FFF&) * &H20000 Or &H80000000
Else
ShiftLeft06 = (Value And &H3FFF&) * &H20000
End If
Case 18&
If Value And &H2000& Then
ShiftLeft06 = (Value And &H1FFF&) * &H40000 Or &H80000000
Else
ShiftLeft06 = (Value And &H1FFF&) * &H40000
End If
Case 19&
If Value And &H1000& Then
ShiftLeft06 = (Value And &HFFF&) * &H80000 Or &H80000000
Else
ShiftLeft06 = (Value And &HFFF&) * &H80000
End If
Case 20&
If Value And &H800& Then
ShiftLeft06 = (Value And &H7FF&) * &H100000 Or &H80000000
Else
ShiftLeft06 = (Value And &H7FF&) * &H100000
End If
Case 21&
If Value And &H400& Then
ShiftLeft06 = (Value And &H3FF&) * &H200000 Or &H80000000
Else
ShiftLeft06 = (Value And &H3FF&) * &H200000
End If
Case 22&
If Value And &H200& Then
ShiftLeft06 = (Value And &H1FF&) * &H400000 Or &H80000000
Else
ShiftLeft06 = (Value And &H1FF&) * &H400000
End If
Case 23&
If Value And &H100& Then
ShiftLeft06 = (Value And &HFF&) * &H800000 Or &H80000000
Else
ShiftLeft06 = (Value And &HFF&) * &H800000
End If
Case 24&
If Value And &H80& Then
ShiftLeft06 = (Value And &H7F&) * &H1000000 Or &H80000000
Else
ShiftLeft06 = (Value And &H7F&) * &H1000000
End If
Case 25&
If Value And &H40& Then
ShiftLeft06 = (Value And &H3F&) * &H2000000 Or &H80000000
Else
ShiftLeft06 = (Value And &H3F&) * &H2000000
End If
Case 26&
If Value And &H20& Then
ShiftLeft06 = (Value And &H1F&) * &H4000000 Or &H80000000
Else
ShiftLeft06 = (Value And &H1F&) * &H4000000
End If
Case 27&
If Value And &H10& Then
ShiftLeft06 = (Value And &HF&) * &H8000000 Or &H80000000
Else
ShiftLeft06 = (Value And &HF&) * &H8000000
End If
Case 28&
If Value And &H8& Then
ShiftLeft06 = (Value And &H7&) * &H10000000 Or &H80000000
Else
ShiftLeft06 = (Value And &H7&) * &H10000000
End If
Case 29&
If Value And &H4& Then
ShiftLeft06 = (Value And &H3&) * &H20000000 Or &H80000000
Else
ShiftLeft06 = (Value And &H3&) * &H20000000
End If
Case 30&
If Value And &H2& Then
ShiftLeft06 = (Value And &H1&) * &H40000000 Or &H80000000
Else
ShiftLeft06 = (Value And &H1&) * &H40000000
End If
Case 31&
If Value And &H1& Then
ShiftLeft06 = &H80000000
Else
ShiftLeft06 = &H0&
End If
End Select
End Function
Function Hex2Dbl(h As String) As Double
Hex2Dbl = CDbl("&h0" & h) ' Overflow Error if more than 2 ^ 64
If Hex2Dbl < 0 Then Hex2Dbl = Hex2Dbl + 4294967296# ' 16 ^ 8 = 4294967296
End Function
-
May 30th, 2022, 06:38 AM
#9
Re: Excel visual basic form, overflow issue
Why would you parse for signed/unsigned?
You're interested in the Bits, not the Value.
Arnout provided you everything you might need (GetBit, SetBit, IsBitSet etc.)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 30th, 2022, 06:40 AM
#10
Re: Excel visual basic form, overflow issue
Sorry but I don't understand the need for all the bit shifts.
If you have 32 yes/no on/off values then you can just store them in a single Long variable.
Each of the values on it's own position.
Code:
Dim lFlag As Long
InitBits
' Set the flag for the 10th value
SetBit lFlag, 10
' Check if the 10th value has been set
If IsBitSet(lFlag, 10) Then ...
' Clear the 10th value
ClearBit lFlag, 10
-
May 30th, 2022, 06:40 AM
#11
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
Originally Posted by Zvoni
Why would you parse for signed/unsigned?
You're interested in the Bits, not the Value
Yeah, I know that probably has no sense. But this spreadsheet I'm working on, then is read by an external tool to convert it to a binary file (for a videogame) and the requisite is that the numbers in this column must be unsigned.
-
May 30th, 2022, 06:42 AM
#12
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
The spreadsheet is this:
Attachment 184961
All digits in the Object Usage column must be unsigned, so when you click on OK, the checked flags should be converted into a UInteger type.
-
May 30th, 2022, 06:43 AM
#13
Re: Excel visual basic form, overflow issue
Originally Posted by jms2505
Yeah, I know that probably has no sense. But this spreadsheet I'm working on, then is read by an external tool to convert it to a binary file (for a videogame) and the requisite is that the numbers in this column must be unsigned.
You gotta be kidding me.
You mean to tell us, that there is an external program, which expects an unsigned 32-Bit Integer Value, representing Set Bits?
The only thing coming to mind, would be to use LongLong instead of Long, if your Excel is 64-Bit
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 30th, 2022, 06:46 AM
#14
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
Originally Posted by Zvoni
You gotta be kidding me.
You mean to tell us, that there is an external program, which expects an unsigned 32-Bit Integer Value, representing Set Bits?
The only thing coming to mind, would be to use LongLong instead of Long, if your Excel is 64-Bit
Yeah, exactly, this spreadsheet is used to set the inventory of a video game, and then the external tool reads this spreadsheet and converts this spreadsheet into a binary file. The thing is that after setting the flags in the form, the result must be an unsigned 32-Bit Integer Value.
-
May 30th, 2022, 06:48 AM
#15
Re: Excel visual basic form, overflow issue
Well, then....
The only thing coming to mind, would be to use LongLong instead of Long, if your Excel is 64-Bit
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 30th, 2022, 06:50 AM
#16
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
No, my excel is 32 bit, but this should be possible, the original developers did it in 2003 using the Excel 2000. The problem is that hey used an OCX control that we don't have.
But if in 2003 has been possible, now it should be.
-
May 30th, 2022, 06:58 AM
#17
Re: Excel visual basic form, overflow issue
Originally Posted by jms2505
No, my excel is 32 bit, but this should be possible, the original developers did it in 2003 using the Excel 2000. The problem is that hey used an OCX control that we don't have.
But if in 2003 has been possible, now it should be.
Then my last attempt would be the Currency-Datatype
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
May 30th, 2022, 10:28 AM
#18
Re: Excel visual basic form, overflow issue
Moved to Office Development, where there might be answers more focused on Excel.
My usual boring signature: Nothing
-
May 30th, 2022, 10:32 AM
#19
Thread Starter
Junior Member
Re: Excel visual basic form, overflow issue
Originally Posted by Zvoni
Then my last attempt would be the Currency-Datatype
I have tried, but the cell format that applies is not valid.
Originally Posted by Shaggy Hiker
Moved to Office Development, where there might be answers more focused on Excel.
Thank you!
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|