IsNumeric gives unexpected results in excel/vba for different regional settings

The problem is using the Val function which does

Returns the numbers contained in a string as a numeric value of appropriate type.

So it does not what you expet, because it does not convert a string into numbers but extract numbers contained in a string until the first non-numeric character that is not a whitespace.

That means

Val("    1615 198th Street N.E.")

will return 1615198 as double.

So what Val interprets as a decimal is always the . in all localizations. That means it will always cosider Val("1.1") as a number but in Val("1,1") the comma is the first non-numberic, non-whitespace character so it stops there and returns 1 only.

What you were looking for is the CDbl which actually converts a string into a number using the decimal seperator of your system.

Option Explicit

Function ConvertToNumber(ByVal MyNumber As String) As Double
    Dim RegionalNumber As String
    RegionalNumber = MyNumber
    
    ' if you want the user to be able to enter dots as well as commas
    ' make sure all dots and commas are converted to the decimal seperator of your system
    ' otherwise go without the conversion
    RegionalNumber = Replace$(RegionalNumber, ".", Application.DecimalSeparator)
    RegionalNumber = Replace$(RegionalNumber, ",", Application.DecimalSeparator)
    
    If IsNumeric(RegionalNumber) Then
        ConvertToNumber = CDbl(RegionalNumber)
    Else
        MsgBox "Invalid format!"
    End If
End Function

If you test this with

Sub test() 
    Debug.Print ConvertToNumber("1.1")
    Debug.Print ConvertToNumber("1,1")
End Sub

on a system where comma is the decimal seperator it should both times return 1,1 as a number.

Explanation why your tests returned the results they returned

Example 1, Swedish regional settings (uses ',' as decimal separator):

• IsNumeric("1.1")=> True
  Because . is considered as date seperator so it is a valid number
• Val("1.1")=> 1.1
  Because val always considers dot as decimal separator
• IsNumeric("1,1")=> True
  Because , is considered as decimal seperator
• Val("1,1")=> 1 Because val always considers comma as non-numeric character

Example 2, Estonian regional settings (also uses ',' as decimal separator):

• IsNumeric("1.1")=> False
  Because dot in estonian is not considered date seperator this is not a valid number
• Val("1.1")=> 1.1
  Because val always considers dot as decimal separator
• IsNumeric("1,1")=> True
  Because , is considered as decimal seperator
• Val("1,1")=> 1 Because val always considers comma as non-numeric character

Note that IsNumeric accepts thousand seperators, date seperators and decimal seperators as well as currency symbols.

Unfortunately, I am not familiar with getting around those particular regional settings to work with the built-in function IsNumeric(). But fortunately, I can help you create your own customized function with the help of Regular Expressions.

You can utilize the below function. Once you've added it to a standard code module, just use IsNumber("1.1") instead of IsNumeric("1.1").

Function IsNumber(ByVal TestString As Variant) As Boolean

    With CreateObject("VBScript.RegExp")
    
        .Pattern = "^(?:\d+[.,]?\d*|\d*[.,]\d+)$"
        IsNumber = .test(TestString)
        
    End With
        
End Function

Now of course, if someone else comes in with a more direct approach, I'd recommend using that method. However, this should work for you otherwise.

Let's break down the pattern ^(?:\d+[.,]?\d*|\d*[.,]\d+)$ real quick. This pattern has an OR operator, which is the | symbol, so we will review them separately.

• ^ asserts that this is the start of your string.
• (?:...) is a non-capturing group. This is just so we don't have to add start of string ^ and end of string $ anchors to each of the OR | statements.
• \d+ matches any numerical character \d, one or more times +, then immediately is followed by
• [.,]? is a character group that will match any character in that group, zero or one times ? (making it optional), then immediately followed by
• \d*, which will match any digit \d, zero or more times * (essentially making it optional, but can have unlimited characters)
• and finally, we have the end of string anchor $

The second part of the OR statement is essentially the same, but I made the first \d optional to allow for things such as .1 (notice no number in front of the .) to still match as numeric.

With everything said, this would now allow you to update your code to:

Function ConvertToNumber(MyNumber as String) as Double 
    If IsNumber(MyNumber) then
        'Your code
    Else
        'Your code
    End If
End Function

Although if it were my code, I'd check for numeric value prior to sending to your conversion function:

If IsNumber(MyNumber) Then
    Debug.Print ConvertToNumber(MyNumber)
Else
    'Handle it here
End If

Although it doesn't really make much of a difference, this makes more since to me personally.