vba runtime error 6 overflow

[hailstorm]

Hello there,

The vba code is working just fine if i enter a number ( id code of a product ) smaller than 10 digits.
Now if i type a number ( id code of a product ) bigger than 10 digits, example 7908147464200, i get a vba error 6 overflow.
Is it because of the CLng? And if it is, how can i fix it? Thanks.

Private Sub Reg1_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.reg1.Value) = 0 Then
MsgBox "This is an incorrect ID"
Me.reg1.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.reg2 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 2, 0)
.reg3 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 3, 0)
.reg4 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 4, 0)
.reg5 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 5, 0)
.reg6 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 6, 0)
.reg7 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 7, 0)
End With
End Sub

 

[joeu2004]

Yes. The largest type Long value is 2147483647. Use CDbl instead of CLng. But keep in mind that Me.reg1 should not exceed 15 digits.

PS.... If Me.reg1 and the first column of Range("Lookup") are product ID codes, they should be type text, IMHO, even if they might look like numbers. That would permit ID codes that exceed 15 chararcters ("digits" [sic]), as well as ID codes that are alphanumeric.

 

[hailstorm]

Yes. The largest type Long value is 2147483647. Use CDbl instead of CLng. But keep in mind that Me.reg1 should not exceed 15 digits.

PS.... If Me.reg1 and the first column of Range("Lookup") are product ID codes, they should be type text, IMHO, even if they might look like numbers. That would permit ID codes that exceed 15 chararcters ("digits" [sic]), as well as ID codes that are alphanumeric.

CDbl worked, THank you.

 

[joeu2004]

You're welcome. FYI....

Use CDbl [....] But keep in mind that Me.reg1 should not exceed 15 digits.

Actually, the largest type Double is 9007199254740992, which is 16 digits. And CDbl("1000000000000001") does return the correct binary value, unlike Excel.

However, beware that VBA formats that as 1000000000000000 because it is limited to formatting only the first 15 significant digits (rounded), like Excel.

My assumption is that the first column of Range("Lookup") is numeric. So in general, it does not help to use CDec(Me.reg1) -- an afterthought of mine.