vba mid function weird error

Mutsop
Mutsop used Ask the Experts™
on
Hi,

So I'm trying to get the last 9 numbers from an integer by using the mid function.
In this example It's to calculate a French TVA number:

Private Sub TextBoxBtw_Change()
    CommandButtonBtw.Enabled = False
    
    Dim TrimmedResult As String
    Dim IsResultNumeric As Boolean
    
    TrimmedResult = Replace(TextBoxBtw.Text, " ", "")
    TrimmedResult = Replace(UCase(TrimmedResult), "FR", "")
    IsResultNumeric = IsNumeric(TrimmedResult)
    If Len(TrimmedResult) = 11 And IsResultNumeric = True Then
        Dim controlNumber As Integer
        Dim sirenNumber As Integer
        
        controlNumber = Mid(TrimmedResult, 1, 2)
        sirenNumber = Mid(TrimmedResult, 3)
        If controlNumber = (12 + 3 * (sirenNumber Mod 97)) Mod 97 Then
            CommandButtonBtw.Enabled = True
        End If
    End If
End Sub

Open in new window


But for some reason it gives me a "loop error?" on sirenNumber = Mid(TrimmedResult, 3)

The weird part is when I type 6 instead of 3 it works and give me the last 5 numbers.
BUT when I type for example Mid(TrimmedResult, 3, 5) which would give me also 5 numbers it doesnt work... only 4 in length work when I indicate the length.

Why oh why does vba hate me?
Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
The number is too large for an integer. Change this line:

Dim sirenNumber As Integer

to this:

Dim sirenNumber As Long

Author

Commented:
Offcourse around 65000 max for an integer.... Forgot about that.
Sigh :D

Thanks alot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial