Overload On Long Variable

**DMT Dave** · Yesterday, 11:35 AM

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.

Code:

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** · Yesterday, 12:53 PM

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** · Yesterday, 01:20 PM

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** · Yesterday, 01:24 PM

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

Code:

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

Code:

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** · Yesterday, 01:46 PM

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** · Yesterday, 02:06 PM

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

Code:

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** · Today, 02:02 AM

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

Code:

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

**DMT Dave** · Today, 02:31 AM

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

**Welshgasman** · Today, 03:13 AM

Dave,
It is just an object, and you specify the properties by starting them with a full stop .
The form is an object, the control is an object.

**CJ_London** · Today, 04:24 AM

if you want to save more typing, you don't need Is for =

Case Is = 2

you can just use

Case 2

you use Is when you want something like

Case Is <=2

as with text - you wouldn't use Like unless you are using a wild card

A Like "abc"

is the same as

A = "abc"

**DMT Dave** · Today, 06:03 AM

Ah ok CJ, thank you, yes can see what you mean, this is now what i have based on suggestions, so not only my overload issue, now cleaned up

The combo has 4 options
case 0 is expand top line of lists
case 1 is next row of lists
etc....
case 4 is reset all lists height and top

added top on bottom row

Code:

Dim lngOrg As Long, lngLarge As Long, lngOrgH As Long, lngNewH As Long
lngOrg = 2210
lngLarge = 4962
lngOrgH = 11291
lngNewH = 8558


Dim strPath As String, strFile As String


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


Select Case intMainIndex
Case 2 'VIEW ROUTES
    Select Case intIndex
    Case 0 ' LISTS 1,5,9,13
        With Me
        .lstRoute1.Height = lngLarge
        .lstRoute5.Height = lngLarge
        .lstRoute9.Height = lngLarge
        .lstRoute13.Height = lngLarge
        .lstRoute2.Height = lngOrg
        .lstRoute6.Height = lngOrg
        .lstRoute10.Height = lngOrg
        .lstRoute14.Height = lngOrg
        .lstRoute3.Height = lngOrg
        .lstRoute7.Height = lngOrg
        .lstRoute11.Height = lngOrg
        .lstRoute15.Height = lngOrg
        .lstRoute4.Height = lngOrg
        .lstRoute8.Height = lngOrg
        .lstRoute12.Height = lngOrg
        .lstRoute16.Height = lngOrg
        End With
   Case 1
        With Me
        .lstRoute1.Height = lngOrg
        .lstRoute5.Height = lngOrg
        .lstRoute9.Height = lngOrg
        .lstRoute13.Height = lngOrg
        .lstRoute2.Height = lngLarge
        .lstRoute6.Height = lngLarge
        .lstRoute10.Height = lngLarge
        .lstRoute14.Height = lngLarge
        .lstRoute3.Height = lngOrg
        .lstRoute7.Height = lngOrg
        .lstRoute11.Height = lngOrg
        .lstRoute15.Height = lngOrg
        .lstRoute4.Height = lngOrg
        .lstRoute8.Height = lngOrg
        .lstRoute12.Height = lngOrg
        .lstRoute16.Height = lngOrg
        End With
   Case 2
        With Me
        .lstRoute1.Height = lngOrg
        .lstRoute5.Height = lngOrg
        .lstRoute9.Height = lngOrg
        .lstRoute13.Height = lngOrg
        .lstRoute2.Height = lngOrg
        .lstRoute6.Height = lngOrg
        .lstRoute10.Height = lngOrg
        .lstRoute14.Height = lngOrg
        .lstRoute3.Height = lngLarge
        .lstRoute7.Height = lngLarge
        .lstRoute11.Height = lngLarge
        .lstRoute15.Height = lngLarge
        .lstRoute4.Height = lngOrg
        .lstRoute8.Height = lngOrg
        .lstRoute12.Height = lngOrg
        .lstRoute16.Height = lngOrg
        End With
   Case 3
        With Me
        .lstRoute1.Height = lngOrg
        .lstRoute5.Height = lngOrg
        .lstRoute9.Height = lngOrg
        .lstRoute13.Height = lngOrg
        .lstRoute2.Height = lngOrg
        .lstRoute6.Height = lngOrg
        .lstRoute10.Height = lngOrg
        .lstRoute14.Height = lngOrg
        .lstRoute3.Height = lngOrg
        .lstRoute7.Height = lngOrg
        .lstRoute11.Height = lngOrg
        .lstRoute15.Height = lngOrg
        .lstRoute4.Height = lngLarge
        .lstRoute8.Height = lngLarge
        .lstRoute12.Height = lngLarge
        .lstRoute16.Height = lngLarge
        End With
    Case 4
        With Me
        .lstRoute1.Height = lngOrg
        .lstRoute5.Height = lngOrg
        .lstRoute9.Height = lngOrg
        .lstRoute13.Height = lngOrg
        .lstRoute2.Height = lngOrg
        .lstRoute6.Height = lngOrg
        .lstRoute10.Height = lngOrg
        .lstRoute14.Height = lngOrg
        .lstRoute3.Height = lngOrg
        .lstRoute7.Height = lngOrg
        .lstRoute11.Height = lngOrg
        .lstRoute15.Height = lngOrg
        .lstRoute4.Top = lngOrgH
        .lstRoute4.Height = lngOrg
        .lstRoute8.Top = lngOrgH
        .lstRoute8.Height = lngOrg
        .lstRoute8.Top = lngOrgH
        .lstRoute12.Height = lngOrg
        .lstRoute12.Top = lngOrgH
        .lstRoute16.Height = lngOrg
        .lstRoute16.Top = lngOrgH
        End With
    End Select
End Select

**jojowhite** · Today, 06:52 AM

you can further "shorten" it:

Code:

Dim lngOrg As Long, lngLarge As Long, lngOrgH As Long, lngNewH As Long
lngOrg = 2210
lngLarge = 4962
lngOrgH = 11291
lngNewH = 8558




Dim strPath As String, strFile As String




With Me
    intMainIndex = .tbcMainMenu.value ' Tab Page Index
    intIndex = .cboOption7.ListIndex 'Combo Index
    
    
    Select Case intMainIndex
    Case 2 'VIEW ROUTES
        Select Case intIndex
        Case 0 ' LISTS 1,5,9,13
            .lstRoute1.Height = lngLarge
            .lstRoute5.Height = lngLarge
            .lstRoute9.Height = lngLarge
            .lstRoute13.Height = lngLarge
            .lstRoute2.Height = lngOrg
            .lstRoute6.Height = lngOrg
            .lstRoute10.Height = lngOrg
            .lstRoute14.Height = lngOrg
            .lstRoute3.Height = lngOrg
            .lstRoute7.Height = lngOrg
            .lstRoute11.Height = lngOrg
            .lstRoute15.Height = lngOrg
            .lstRoute4.Height = lngOrg
            .lstRoute8.Height = lngOrg
            .lstRoute12.Height = lngOrg
            .lstRoute16.Height = lngOrg
       Case 1
            .lstRoute1.Height = lngOrg
            .lstRoute5.Height = lngOrg
            .lstRoute9.Height = lngOrg
            .lstRoute13.Height = lngOrg
            .lstRoute2.Height = lngLarge
            .lstRoute6.Height = lngLarge
            .lstRoute10.Height = lngLarge
            .lstRoute14.Height = lngLarge
            .lstRoute3.Height = lngOrg
            .lstRoute7.Height = lngOrg
            .lstRoute11.Height = lngOrg
            .lstRoute15.Height = lngOrg
            .lstRoute4.Height = lngOrg
            .lstRoute8.Height = lngOrg
            .lstRoute12.Height = lngOrg
            .lstRoute16.Height = lngOrg
       Case 2
            .lstRoute1.Height = lngOrg
            .lstRoute5.Height = lngOrg
            .lstRoute9.Height = lngOrg
            .lstRoute13.Height = lngOrg
            .lstRoute2.Height = lngOrg
            .lstRoute6.Height = lngOrg
            .lstRoute10.Height = lngOrg
            .lstRoute14.Height = lngOrg
            .lstRoute3.Height = lngLarge
            .lstRoute7.Height = lngLarge
            .lstRoute11.Height = lngLarge
            .lstRoute15.Height = lngLarge
            .lstRoute4.Height = lngOrg
            .lstRoute8.Height = lngOrg
            .lstRoute12.Height = lngOrg
            .lstRoute16.Height = lngOrg
       Case 3
            .lstRoute1.Height = lngOrg
            .lstRoute5.Height = lngOrg
            .lstRoute9.Height = lngOrg
            .lstRoute13.Height = lngOrg
            .lstRoute2.Height = lngOrg
            .lstRoute6.Height = lngOrg
            .lstRoute10.Height = lngOrg
            .lstRoute14.Height = lngOrg
            .lstRoute3.Height = lngOrg
            .lstRoute7.Height = lngOrg
            .lstRoute11.Height = lngOrg
            .lstRoute15.Height = lngOrg
            .lstRoute4.Height = lngLarge
            .lstRoute8.Height = lngLarge
            .lstRoute12.Height = lngLarge
            .lstRoute16.Height = lngLarge
        Case 4
            .lstRoute1.Height = lngOrg
            .lstRoute5.Height = lngOrg
            .lstRoute9.Height = lngOrg
            .lstRoute13.Height = lngOrg
            .lstRoute2.Height = lngOrg
            .lstRoute6.Height = lngOrg
            .lstRoute10.Height = lngOrg
            .lstRoute14.Height = lngOrg
            .lstRoute3.Height = lngOrg
            .lstRoute7.Height = lngOrg
            .lstRoute11.Height = lngOrg
            .lstRoute15.Height = lngOrg
            .lstRoute4.Top = lngOrgH
            .lstRoute4.Height = lngOrg
            .lstRoute8.Top = lngOrgH
            .lstRoute8.Height = lngOrg
            .lstRoute8.Top = lngOrgH
            .lstRoute12.Height = lngOrg
            .lstRoute12.Top = lngOrgH
            .lstRoute16.Height = lngOrg
            .lstRoute16.Top = lngOrgH
        End Select
    End Select
End With

**DMT Dave** · Today, 08:09 AM

Very neat and tidy jojowhite, as per post #8, this is why you are all GOAT on this

Yes, having 1 x With block to accommodate the full procedure

Kindest

**Welshgasman** · Today, 08:24 AM

I will have to be more explicit in future.

**DMT Dave** · Today, 08:33 AM

GOAT includes you WGM

Overload On Long Variable

Thread Tools

Overload On Long Variable

Similar Threads

Modules and Object Overload

Object variable or With block variable not set

Long Variable syntax

Object variable or With block variable not set problem

Refering to variable form names inside a variable

Posting Permissions