Overload On Long Variable

[DMT Dave]

Hi Guys, i have tried a couple of things to fix this one

I have 16 lists on screen, each list represents a route number,

I have tried setting variables as Integer knowing the variables are whole numbers, then converting to Long using Clng(), i was receiving runtime error 6 overload which is normally an incorrect variable setting and after multiplying the TWIPS there is a result of 1404246 in the immediate window, still receive an overload (Now Set to Long) to accommodate large numbers that may calculate to include decimals.

Is there a better way of doing this ?



So my intention is because there 16 list boxes, i want the option to expand the top row of lists to display without having to scroll on each list


Once I know the best method, i can expand/collapse a row of lists at a time.

Const TWIPS_PER_CM As Long = 283
Dim intMainIndex As Integer, intIndex As Integer

Dim lngOrgSize As Long, lngLarge As Long


Dim strPath As String, strFile As String


intMainIndex = Me.tbcMainMenu.Value ' Tab Page Index
intIndex = Me.cboOption7.ListIndex 'Combo Index
lngLarge = 4962
lngOrgSize = 2210


Select Case intMainIndex
Case Is = 2 'VIEW ROUTES
    Select Case intIndex
    Case Is = 0 ' LISTS 1,5,9,13
    
        With Me.lstRoute1        
Debug.Print lngLarge * TWIPS_PER_CM
        
        Stop



            .Height = (lngLarge * TWIPS_PER_CM)
        End With
        With Me.lstRoute5
            .Height = (lngLarge * TWIPS_PER_CM)
        End With
        With Me.lstRoute9
            .Height = (lngLarge * TWIPS_PER_CM)
        End With
        With Me.lstRoute13
            .Height = (lngLarge * TWIPS_PER_CM)
        End With
    
    Case Is = 4 'REST TO ORIGIONAL SIZE
        With Me.lstRoute1
            .Height = (lngOrgSize * TWIPS_PER_CM)
        End With
        With Me.lstRoute5
            .Height = (lngOrgSize * TWIPS_PER_CM)
        End With
        With Me.lstRoute9
            .Height = (lngOrgSize * TWIPS_PER_CM)
        End With
        With Me.lstRoute13
            .Height = (lngOrgSize * TWIPS_PER_CM)
        End With
        
    End Select
End Select

[Micron]

Why have a nested Select block when it only has one case? Your issue isn't obvious (at least to me). 4692*283 does not exceed the size of a long variable, so I don't see a problem there. Knowing which line raises the error might help. This may be something that you need to upload a db in order to solve it.

[davegri]

Try setting lngLarge and lngOrgSize to CM instead of twips.
Why is code choosing third tab page, but modifying the first page?

Edit: Everyone is seeing this wrong. Overflow is not due to the calculation, it's because the code is trying to set the listbox height to a bit over 81 FEET.

[Welshgasman]

4692*283 = 1,327,836
That still gives me error 6?

Sub TestLong()
Dim lngResult As Long
Dim i1 As Integer, i2 As Integer
i1 = 4692
i2 = 283
lngResult = i1 * i2 'Errors here
Debug.Print lngResult


End Sub

yet Long is supposed to be able to contain -2,147,483,648 to 2,147,483,648

If I use lngResult = 2147483640, it accepts it no problem?

This works

Sub TestLong()
Dim lngResult As Long
Dim i1 As Long, i2 As Long
i1 = 4692
i2 = 283
lngResult = i1 * i2
Debug.Print lngResult


End Sub

[Welshgasman]

I asked on another site about this and the answer I received was

Multiply an integer by an integer results in an integer value

[DMT Dave]

Thanks guys, i have this working by avoiding Const to Twips and using Long variables

Dim lngOrg As Long, lngLarge As Long
lngOrg = 2210
lngLarge = 4962


Dim strPath As String, strFile As String


intMainIndex = Me.tbcMainMenu.Value ' Tab Page Index
intIndex = Me.cboOption7.ListIndex 'Combo Index


Select Case intMainIndex
Case Is = 2 'VIEW ROUTES
    Select Case intIndex
    Case Is = 0 ' LISTS 1,5,9,13
    
        With Me.lstRoute1
            .Height = lngLarge
        End With
        With Me.lstRoute5
            .Height = lngLarge
        End With
        With Me.lstRoute9
            .Height = lngLarge
        End With
        With Me.lstRoute13
            .Height = lngLarge
        End With
    Case Is = 1
        'Lists 2,6,10,14
    Case Is = 2
        'Lists 3,7,11,15
    Case Is = 3
        'Lists 4,7,12,16
        
    Case Is = 4 'REST TO ORGIGIONAL SIZE
        With Me.lstRoute1
            .Height = lngOrg
        End With
        With Me.lstRoute5
            .Height = lngOrg
        End With
        With Me.lstRoute9
            .Height = lngOrg
        End With
        With Me.lstRoute13
            .Height = lngOrg
        End With
        
    End Select
End Select

[Welshgasman]

FWIW I was consequently advised that only one of the values needs to be Long.
However someone mentioned variants, and one of the integers as a variant works as well.

@DMT Dave,
I would save some typing and just use With Me, then

 .lstRoute1.Height = lngOrg
 .lstRoute5.Height = lngOrg
 .lstRoute9.Height = lngOrg
 .lstRoute13.Height = lngOrg

[DMT Dave]

Thanks for the tip WGM, So With Me, didn't realize you could declare an array with nothing after ("ME")

This is why you guys are the GOAT in vba

Just adapted and yes, it does shorten the procedure