Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# vba mid function weird error

Posted on 2011-09-06
Medium Priority
396 Views
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
``````

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
Question by:Mutsop
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 85

Accepted Solution

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

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

Thanks alot
0

## Featured Post

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month9 days, 11 hours left to enroll