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
'**********************************************************************************************************
' Name: addLeadingZeros
' Author: mielk | 2012-02-12
'
' Comment: Function pads the given numeric value with leading zeros to a specified length.
'
' Parameters:
' value Value to be padded with leading zeros.
' Fractional part and minus sign are ignored.
' Parameter [value] should be numeric, but giving a value of any other type will
' not cause an error to occur.
' strLength Optional parameter that determines the minimal length of the result string.
' The default value of this parameter is 2.
'
' Returns:
' String Text consisting of the given input value padded with so many leading zeros to reach
' a specified length (determined by value of parameter [strLength]).
'
' If the input value is longer than minimal length determined by parameter
' [strLength], the function returns this input value without modifying it.
'
' If the given value is not numeric and can't be converted to numeric, string
' representation of this value is returned.
' If it is not possible to get a string representation of the given value, an empty
' string is returned.
'
'
' Exceptions:
' IllegalTypeException Thrown if the parameter passed to this function cannot be
' converted to a String type.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-02-12 mielk Method created.
'**********************************************************************************************************
Public Function addLeadingZeros(ByVal value As Variant, Optional ByVal strLength As Byte = 2) As String
Const METHOD_NAME As String = "addLeadingZeros"
'------------------------------------------------------------------------------------------------------
Dim iMissingChars As Integer
Dim lNumber As Long
'------------------------------------------------------------------------------------------------------
'At first, it is checked if the input value is numeric. If not, a string representation of ----------|
'the original value is returned or an empty string for the values having no string representation. '|
If VBA.IsNumeric(value) Then '|
'|
'The fractional part is removed (not round up) from '|
'the original value as well as a minus sign. '|
lNumber = VBA.Int(VBA.Abs(value)) '|
'|
'It calculates how long the source text is and how many zeros '|
'should be added to reach a specified string length. '|
iMissingChars = strLength - VBA.Len(VBA.CStr(lNumber)) '|
'|
'If a number of missing characters is less than 0, it means the original value is longer ----| '|
'than the minimum required length for the result string and no leading zeros are needed. '| '|
'Otherwise the necessary number of leading zeros is appended to the result string. '| '|
If iMissingChars > 0 Then '| '|
'| '|
'It uses [String] function to create a substring consisting of n occurences '| '|
'of specified character (0 in this case). '| '|
addLeadingZeros = VBA.String(iMissingChars, "0") & lNumber '| '|
'| '|
Else '| '|
'| '|
addLeadingZeros = lNumber '| '|
'| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Else '|
'|
'If the argument passed to the function is not numeric, its String representation is returned. '|
'If it is impossible to retrieve String representation of the argument (i.e. it is an array '|
'or an object), IllegalTypeException is thrown. '|
On Error GoTo IllegalTypeException '|
addLeadingZeros = VBA.CStr(value) '|
'|
End If '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
IllegalTypeException:
'(...)
'Put your own error handling here for a case if the given parameter cannot be converted to String type.
GoTo ExitPoint
End Function