'**********************************************************************************************************
' Name:                 onlyDigits
' Author:               mielk | 2011-07-15
'
' Comment:              Function removes all the non-digits characters from the specified text.
'
' Parameters:
'   text                The text to be processed.
'                       In fact, values of other types than String also can be passed to this function,
'                       provided that they can be converted to String. Otherwise an IllegalTypeException
'                       will be thrown.
'   leaveSpecialChars   Optional parameter of Boolean type.
'                       It determines if special characters typical for numbers (like minus, dot or coma)
'                       should be returned by this functions.
'                       If this parameter is set to True - the returned String contains both digits and
'                       characters typical for numbers.
'                       If this parameter is set to False - function returns only digits. In this case
'                       characters typical for numbers are treated as any other character.
'                       Default value of this parameter is True.
'
' Returns:
'   String              The source text from which all the non digits characters have been removed
'                       (alternately characters typical for numbers can be left as if they were digits, it
'                       depends on value of leaveSpecialChars parameter).
'
'
' Exceptions:
'   IllegalTypeException            Thrown if the parameter passed to this function cannot be converted to
'                                   a String type.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2011-07-15        mielk           Method created.
' 2013-05-17        mielk           Added logic to accept only one minus and one fraction separator and to
'                                   check if there are located on the proper position (i.e. minus at the
'                                   beginning of the number).
' 2013-06-04        mielk           Decimal separator is not worked out by the separate function anymore,
'                                   since the function take system regional settings into consideration
'                                   instead of the MS Office settings.
'**********************************************************************************************************
Public Function onlyDigits(ByVal text As Variant, _
              Optional ByVal leaveSpecialChars As Boolean = True) As String
    Const METHOD_NAME As String = "onlyDigits"
    '------------------------------------------------------------------------------------------------------
    Const MINUS As String = "-"
    Const COMA As String = ","
    Const DOT As String = "."
    '------------------------------------------------------------------------------------------------------
    Dim sText As String
    Dim iChar As Integer
    Dim sChar As String
    Dim iAsciiCode As Integer
    Dim bHasComa As Boolean
    '------------------------------------------------------------------------------------------------------


    'Function tries to convert the given parameter to the String type. If it is not possible (i.e. this
    'parameter is an array or an object), code execution moves to the IllegalTypeException label.
    On Error GoTo IllegalTypeException
    sText = VBA.CStr(text)


    'Analyses every single character of the source string.
    For iChar = 1 To VBA.Len(sText)
    
        'Assign a single character of the source text to [sChar] variable.
        sChar = VBA.Mid$(sText, iChar, 1)
        
        'Check if the currently analyzed character is a digit. ------------------------------------------|
        If isDigit(sChar) Then                                                                          '|
                                                                                                        '|
            onlyDigits = onlyDigits & sChar                                                             '|
                                                                                                        '|
        Else                                                                                            '|
                                                                                                        '|
            'If parameter [leaveSpecialChars] is set to True, it has to be checked whether ----------|  '|
            'this character is minus, dot or coma.                                                  '|  '|
            If leaveSpecialChars Then                                                               '|  '|
                                                                                                    '|  '|
                '--------------------------------------------------------------------------------|  '|  '|
                If sChar = MINUS Then                                                           '|  '|  '|
                                                                                                '|  '|  '|
                    'Minus can be added only at the beginning of the number so it -----------|  '|  '|  '|
                    'has to be checked if the result string is empty yet...                 '|  '|  '|  '|
                    If VBA.Len(onlyDigits) = 0 Then                                         '|  '|  '|  '|
                                                                                            '|  '|  '|  '|
                        'Moreover, minus has to stand directly before the first, --------|  '|  '|  '|  '|
                        'digit so we have to check if the next character after          '|  '|  '|  '|  '|
                        'minus is a digit.                                              '|  '|  '|  '|  '|
                        If iChar < VBA.Len(sText) Then                                  '|  '|  '|  '|  '|
                            If isDigit(VBA.Mid$(sText, iChar + 1, 1)) Then              '|  '|  '|  '|  '|
                                onlyDigits = MINUS                                      '|  '|  '|  '|  '|
                            End If                                                      '|  '|  '|  '|  '|
                        End If                                                          '|  '|  '|  '|  '|
                        '----------------------------------------------------------------|  '|  '|  '|  '|
                                                                                            '|  '|  '|  '|
                    End If                                                                  '|  '|  '|  '|
                    '------------------------------------------------------------------------|  '|  '|  '|
                                                                                                '|  '|  '|
                'There can be only one fraction separator in a number, so it has to be          '|  '|  '|
                'changed if there is no one already.                                            '|  '|  '|
                ElseIf Not bHasComa And (sChar = COMA Or sChar = DOT) Then                      '|  '|  '|
                                                                                                '|  '|  '|
                    'Fraction separator (dot or coma) cannot stand at the beginning of ------|  '|  '|  '|
                    ' a number. It has to be preceded by at least one digit. Therefore,     '|  '|  '|  '|
                    'it has to be checked if there is already any digit in the result       '|  '|  '|  '|
                    'string.                                                                '|  '|  '|  '|
                    If VBA.Len(onlyDigits) Then                                             '|  '|  '|  '|
                        If VBA.right$(onlyDigits, 1) <> MINUS Then                          '|  '|  '|  '|
                                                                                            '|  '|  '|  '|
                            onlyDigits = onlyDigits & Application.decimalSeparator          '|  '|  '|  '|
                            bHasComa = True                                                 '|  '|  '|  '|
                                                                                            '|  '|  '|  '|
                        End If                                                              '|  '|  '|  '|
                    End If                                                                  '|  '|  '|  '|
                    '------------------------------------------------------------------------|  '|  '|  '|
                                                                                                '|  '|  '|
                                                                                                '|  '|  '|
                End If                                                                          '|  '|  '|
                '--------------------------------------------------------------------------------|  '|  '|
                                                                                                    '|  '|
            End If                                                                                  '|  '|
            '-------- [If leaveSpecialChars Then] ---------------------------------------------------|  '|
                                                                                                        '|
        End If                                                                                          '|
        '------------ [If isDigit(sChar) Then] ----------------------------------------------------------|
        
    Next iChar



'==========================================================================================================
ExitPoint:
    Exit Function


'----------------------------------------------------------------------------------------------------------
IllegalTypeException:
    '(...)
    'Put your own error handling here for a case if the given parameter cannot be converted to String.
    
    GoTo ExitPoint

End Function




'**********************************************************************************************************
' Name:                 isDigit
' Author:               mielk | 2012-02-19
'
' Comment:              Function checks if the given character is a digit.
'
' Parameters:
'   char                The character to be checked.
'                       The given text should contain only one character. If longer string is passed to
'                       this function, only first character is analyzed and the other ones are ignored.
'
' Returns:
'   Boolean             True - if the given character is a digit.
'                       False - if the given character is not a digit.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-02-19    mielk       Method created.
'**********************************************************************************************************
Public Function isDigit(ByVal char As String) As Boolean
    Const METHOD_NAME As String = "isDigit"
    '------------------------------------------------------------------------------------------------------
    Dim iAsciiCode As Integer
    '------------------------------------------------------------------------------------------------------


    'Uses [Asc] function to get an ASCII code of a character.
    iAsciiCode = VBA.Asc(char)


    'The ASCII codes for the digits range from 48 to 57. If an ASCII code of the character is
    'within this range, the character is a digit and will be appended to the result string.
    If iAsciiCode >= 48 And iAsciiCode <= 57 Then
        isDigit = True
    End If


End Function