1. Christoph Jüngling
  2. VB and VBA Code Library

Source

VB and VBA Code Library / VBReplacementFunctions.bas

Attribute VB_Name = "VBReplacementFunctions"
''
' Functions to replace internal VB functions because of erroneous behaviour
'
' @remarks  Name and signature of the functions in this moduls MUST NOT be changed!
' @author   Christoph Juengling <christoph@juengling-edv.de>
' @link   https://bitbucket.org/juengling/vb-and-vba-code-library
'
Option Explicit

''
' Checks if the argument is a numeric expression
'
' @param        Expression   Expression to be checked
' @return       TRUE = Expression is numeric, otherwise FALSE
' @overrides    VBA.IsNumeric()
' @remarks      Corrects the behaviour, that "12..34" or "1,2.3" is stated as numeric, but in fact it is not
'
Public Function IsNumeric(Expression As Variant) As Boolean

Dim sDecSign As String

'------------------------------

' Set default return value
IsNumeric = False

' A NULL value is not numeric at all
If IsNull(Expression) Then Exit Function

' The negative check of the default function is ok
If Not VBA.IsNumeric(Expression) Then Exit Function

' *** Start of corrective code ***

' If more than one of the usually decimal signs (comma and dot) can be found, the expression can't be numeric
If StrCnt(CStr(Expression), ".") + StrCnt(CStr(Expression), ",") > 1 Then Exit Function

sDecSign = Mid(FormatNumber(val("1"), 1), 2, 1)
If StrCnt(CStr(Expression), sDecSign) > 1 Then Exit Function

IsNumeric = True

End Function