Why am I getting an overflow error

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am getting an overflow error message and I don't know why. Here is the code:

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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
what value is Ref when you hover over it and why have you declared it as String
Code:
Ref = sh3.Cells(Rows.Count, 1).End(xlUp).Value
 
Upvote 0
Ref is 49000, which is the previous invoice number.
 
Upvote 0
What is the maximum value you can hold in the Integer ref2?
 
Upvote 0
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
 
Last edited:
Upvote 0
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.

Code:
'ref2 = Ref + 1
ref2 = CInt(Ref) + 1
or better probably
Code:
'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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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):
Rich (BB code):
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top