Since function onlyDigits uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
'**********************************************************************************************************
' 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