Why am I getting an overflow error

[dpaton05]

I am getting an overflow error message and I don't know why. Here is the code:

Sub AddReference()

Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Dim f As Range, client As Variant, Ref As String, ref2 As Integer

    Set wb1 = ThisWorkbook
    Set sh1 = wb1.Sheets("npss_quote_sheet")
    Set wb2 = Workbooks("client_list.xlsm")
    Set sh3 = wb2.Sheets("Reference")
    
    Ref = sh3.Cells(Rows.Count, 1).End(xlUp).Value
[U]    ref2 = Ref + 1[/U]
    
    sh3.Cells(1, 1).End(xlDown).Offset(1, 0).Value = ref2
    sh1.Range("H5").Value = ref2

End Sub

I have underlined the line that is highlighted. Can someone help me please?

 

[Michael M]

what value is Ref when you hover over it and why have you declared it as String

Ref = sh3.Cells(Rows.Count, 1).End(xlUp).Value

 

[dpaton05]

Ref is 49000, which is the previous invoice number.

 

[Toadstool]

What is the maximum value you can hold in the Integer ref2?

 

[Trebor76]

You have declared your variable "ref2" as an integer so it can only values in the range -32,768 to 32,767. If the value is outside this you will get the the "overflow error" error message.

Change the variable to long (I generally use this variable type for reference data like row numbers invoice numbers) or double (I generally use this variable type for amounts) and try again. Also (as Michael has said) change the "Ref" variable as well.

Robert

 

[dpaton05]

Thanks for that

 

[Kenneth Hobson]

Maybe change Ref's type to Integer? Code can convert it but I doubt you want that? Basically, one needs to know the type of data. If it is not known, do not set a type and it will be Variant.

'ref2 = Ref + 1
ref2 = CInt(Ref) + 1

or better probably

'Dim f As Range, client As Variant, Ref As String, ref2 As Integer
'Dim f As Range, client As Variant, Ref As [COLOR="#FF0000"]Integer[/COLOR], ref2 As Integer
Dim f As Range, client As Variant, Ref As [COLOR="#FF0000"]Long[/COLOR], ref2 As [COLOR="#FF0000"]Long[/COLOR]

As others noted, I usually use Long or Double if decimals are possible. I normally just use Integers for loop counters. Integers are ok for column numbers but use Long for row numbers.

 

[Artik]

Kenneth, probably from version 2003, type Byte and Integer are implicitly converted to type Long and only after conversion are performed. In addition, the Long type is the fastest in operation. That is why for several years I gave up declaring Integer type. Profit is double. Because Long is the fastest and there is no implicit type conversion.

Artik

 

[joeu2004]

probably from version 2003, type Byte and Integer are implicitly converted to type Long and only after conversion are performed

That is incorrect, as demonstrated by the following code (I use Excel 2010):

Sub testit()
Dim b As Byte, i As Integer, x As Double
On Error Resume Next
b = 128
x = b + b
If Err Then MsgBox "byte overflow #1 "
Err.Clear
x = b + 128
If Err Then MsgBox "byte overflow #2 "  ' will not happen
Err.Clear
i = 16384
x = i + i
If Err Then MsgBox "integer overflow #1 "
Err.Clear
x = b + 32640   ' = 32768
If Err Then MsgBox "integer overflow #2 "
Err.Clear
End Sub

Your misunderstandings might stem from the fact that in the (Intel-compatible) CPU, type Byte, Integer and Long values are loaded into 32-bit registers, which is the size of type Long.

But arithmetic instructions are coded to perform type Byte, Integer or Long operations.

Contributing to the misunderstanding, the default size of a non-floating-point constant depends on how it appears.

(All constants with a decimal point are type Double. And we can append "!" and "#" to force a constant to be type Single or Double.)

And VBA implicitly converts the values of a pair of operands to the largest type.

Constants -32768 through 32767 are implicitly type Integer. That is why we do not see "byte overflow #2 ".

(But we can write -32768& and 32767& to specify a type Long constant.)

In addition, the Long type is the fastest in operation

That is incorrect. In modern computers (especially Intel-compatible CPUs), there is no arithmetic performance difference. Every operation is performed in one instruction cycle (RISC architecture).

OTOH, if a value spans two cache lines when it is stored in memory, arguably there is a greater probability of a cache miss and a concomitant prefetch performance penalty ("stall") for the larger data types.

 

[Artik]

OK, I don't have "low-level knowledge" because I deal with completely different things on a daily basis.
In my defense I can give you a Microsoft website that mentions this issue. https://docs.microsoft.com/en-us/pr...-xp/aa164754(v=office.10)?redirectedfrom=MSDN
And test results. Sorry, in Polish. http://www.excelforum.pl/viewtopic.php?p=213878

So something's up

Artik