Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

vba mid function weird error

Posted on 2011-09-06
2
Medium Priority
?
400 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:Mutsop
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 36487466
The number is too large for an integer. Change this line:

Dim sirenNumber As Integer

to this:

Dim sirenNumber As Long

0
 
LVL 3

Author Closing Comment

by:Mutsop
ID: 36487490
Offcourse around 65000 max for an integer.... Forgot about that.
Sigh :D

Thanks alot
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question