Clever Geek Handbook

Excel VBA returns strange results with large calculations

I created a function in excel that basically looks for a dynamic range in a For statement for a row and returns the value of the cell that the column is in. This is mainly a budgeting function, but that is beside the point.

Here's the problem, everything works with small results, but when the results get too large (say around 32000 ... that's a number for some reason), the function starts returning 0 .

Has anyone had such problems?

Here is the code in question:

Function Material(item As String, Optional sheetType As String) As Integer

Dim wSheet As Worksheetr
Dim count As Integer
Dim offSet As Integer
Dim ref As Integer
Dim bottomRight As Integer
Dim upperLeft As Integer
Dim rng As Range
Dim cVal As Integer

For Each wSheet In Worksheets
    If wSheet.Name = "Drywall Pricing" Then
        dwIndex = wSheet.Index - 1
    End If
Next wSheet

If IsMissing(sheetType) Then
    sheetType = " "
Else
    sheetType = UCase(sheetType)
End If
For i = 1 To dwIndex
    wSheetName = Sheets(i).Name
    If InStr(UCase(wSheetName), sheetType) > 0 Then
        count = 9
        offSet = 44
        ref = 27
        For wall = 0 To count - 1
            On Error Resume Next
            Err.Clear
            upperLeft = (ref + 12) + (offSet * wall)
            bottomRight = (ref + 30) + (offSet * wall)
            Set rng = Sheets(i).Range("A" & upperLeft & ":B" & bottomRight)
            cVal = Application.WorksheetFunction.VLookup(item, rng, 2, False)
            If Err.Number > 0 Then
                cVal = 0
            End If
            units = units + cVal
        Next wall
    Else
        units = units + 0
    End If
Next i

If units > 0 Then
    Material = units
Else
    Material = 0
End If

End Function

I set up the spreadsheet to manually count a certain number of items (eg "= A13 + B5 + B26", etc.) and compare it with the result of running the given function, and when the results were low they are equal to each other, so I know that the function itself is working correctly. Is it a memory problem?

Any help would be greatly appreciated.

Thanks in advance!

+2


source to share


4 answers


An integer in VBA is 16 bits, which means a maximum value of 32,767 (and a minimum of -32,768).



Use Long instead of Integer to store the results, which gives you over 2 billion before it reaches the limit.

+12


source


Can you post an excerpt from the content of a searchable Excel document?

two small comments:

on the lines

If wSheet.Name = "Drywall Pricing" Then
    dwIndex = wSheet.Index - 1
End If


you may want Exit For

, since you found your sheet and you don't want to keep searching.

Is there any reason why you want to find the MINUS 1 sheet found?

and another comment is that units = units + 0

the Else

-clause does nothing.

considers

0


source


In VBA and "Integer" is not a 16 bit number? Ie, -32.768 to +32.767? If so, then this code right here is your culprit:

Dim cVal As Integer
...
        cVal = Application.WorksheetFunction.VLookup(item, rng, 2, False)
        If Err.Number > 0 Then
            cVal = 0
        End If

Also, I would suggest using "Option Explicit".

0


source


the maximum value for an integer is (2 ^ 15), which is 32,768. Your error trap is calling cVal = 0. Try changing the datatype from integer to long. Max Long is (2 ^ 31 -1) which is 2,147,483,647, which should deal with your values ​​easily.

0


source






More articles:


All Articles