Results 1 to 19 of 19

Thread: Excel visual basic form, overflow issue

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Question 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

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by Zvoni View Post
    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.

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by Arnoutdv View Post
    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
    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.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    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

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by Arnoutdv View Post
    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

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    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

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by Zvoni View Post
    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.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    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.

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by jms2505 View Post
    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

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by Zvoni View Post
    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.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    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.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by jms2505 View Post
    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

  18. #18
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    23

    Re: Excel visual basic form, overflow issue

    Quote Originally Posted by Zvoni View Post
    Then my last attempt would be the Currency-Datatype
    I have tried, but the cell format that applies is not valid.

    Quote Originally Posted by Shaggy Hiker View Post
    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
  •  



Click Here to Expand Forum to Full Width