Excel VBA returning weird results with large calculations

Question

I've created a function in excel which basically searches a dynamic range in a For statement for a string and returns the value of the cell one column over. It's basically a budgeting function, but that's beside the point.

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

Has anybody had problems like this?

Here's 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've set up a spreadsheet to manually count a certain number of items ( i.e. "=A13+B5+B26" etc), and compared it to the result of running the function in question, and when the results are low, they are equal to each other, so I know that the function itself is working correctly. Is this a memory issue, then?

Any help would be greatly appreciated.

Thanks in advance!

Answer 1

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

Instead of Integer, use Long to store your results, which gives you more than 2 billion before it hits the limit.

Answer 2

may you post an excerpt of the content of the to be searched excel document?

two little comments:

on the lines

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

you might want to Exit For as you've found your sheet and you don't want to continue searching.

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

and the other comment is that units = units + 0 in the Else-clause does nothing at all.

regards

Answer 3

In VBA and "Integer" isn't an Integer 16 bits? I.e., -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'd suggest using "Option Explicit".

Answer 4

maximum value for an integer is (2^15) which is 32,768. Your Error catching is forcing the cVal = 0. Try changing the datatype from integer to long. Max Long is (2^31 -1) which is 2,147,483,647, which should easily handle your values.