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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
|
Option VBASupport 1
Option Explicit
Dim passCount As Integer
Dim failCount As Integer
Dim result As String
Function doUnitTest() As String
result = verify_format()
If failCount <> 0 And passCount > 0 Then
doUnitTest = result
Else
doUnitTest = "OK"
End If
End Function
Function verify_format() as String
passCount = 0
failCount = 0
result = "Test Results" & Chr$(10) & "============" & Chr$(10)
'Predefined_Datetime_Format_Sample
Predefined_Number_Format_Sample
'Custom_Datetime_Format_Sample
Custom_Number_Format_Sample
Custom_Text_Format_Sample
result = result & Chr$(10) & "Tests passed: " & passCount & Chr$(10) & "Tests failed: " & failCount & Chr$(10)
verify_format = result
End Sub
Sub Predefined_Datetime_Format_Sample()
Dim testName As String
Dim myDate, MyTime, TestStr As String
myDate = "01/06/98"
MyTime = "17:08:06"
testName = "Test Predefined_Datetime_Format_Sample function"
On Error GoTo errorHandler
' The date/time format have a little different between ms office and OOo due to different locale and system...
TestStr = Format(myDate, "General Date") ' 1/6/98
TestLog_ASSERT IsDate(TestStr), "General Date: " & TestStr & " (Test only applies to en_US locale)"
'TestLog_ASSERT TestStr = "1/6/98", "General Date: " & TestStr
TestStr = Format(myDate, "Long Date") ' Tuesday, January 06, 1998
TestLog_ASSERT TestStr = "Tuesday, January 06, 1998", "Long Date: " & TestStr & " (Test only applies to en_US locale)"
'TestLog_ASSERT IsDate(TestStr), "Long Date: " & TestStr
TestStr = Format(myDate, "Medium Date") ' 06-Jan-98
'TestLog_ASSERT TestStr = "06-Jan-98", "Medium Date: " & TestStr
TestLog_ASSERT IsDate(TestStr), "Medium Date: " & TestStr & " (Test only applies to en_US locale)"
TestStr = Format(myDate, "Short Date") ' 1/6/98
'TestLog_ASSERT TestStr = "1/6/98", "Short Date: " & TestStr
TestLog_ASSERT IsDate(TestStr), "Short Date: " & TestStr & " (Test only applies to en_US locale)"
TestStr = Format(MyTime, "Long Time") ' 5:08:06 PM
'TestLog_ASSERT TestStr = "5:08:06 PM", "Long Time: " & TestStr
TestLog_ASSERT IsDate(TestStr), "Long Time: " & TestStr & " (Test only applies to en_US locale)"
TestStr = Format(MyTime, "Medium Time") ' 05:08 PM
'TestLog_ASSERT TestStr = "05:08 PM", "Medium Time: " & TestStr
TestLog_ASSERT IsDate(TestStr), "Medium Time: " & TestStr & " (Test only applies to en_US locale)"
TestStr = Format(MyTime, "Short Time") ' 17:08
'TestLog_ASSERT TestStr = "17:08", "Short Time: " & TestStr
TestLog_ASSERT IsDate(TestStr), "Short Time: " & TestStr & " (Test only applies to en_US locale)"
Exit Sub
errorHandler:
TestLog_ASSERT (false), testName & ": hit error handler"
End Sub
Sub Predefined_Number_Format_Sample()
Dim myNumber, TestStr As String
Dim testName As String
testName = "Test Predefined_Number_Format_Sample function"
myNumber = 562486.2356
On Error GoTo errorHandler
TestStr = Format(myNumber, "General Number") '562486.2356
TestLog_ASSERT TestStr = "562486.2356", "General Number: " & TestStr
'MsgBox TestStr
TestStr = Format(0.2, "Fixed") '0.20
TestLog_ASSERT TestStr = "0.20", "Fixed: " & TestStr
'MsgBox TestStr
TestStr = Format(myNumber, "Standard") '562,486.24
TestLog_ASSERT TestStr = "562,486.24", "Standard: " & TestStr
'MsgBox TestStr
TestStr = Format(0.7521, "Percent") '75.21%
TestLog_ASSERT TestStr = "75.21%", "Percent: " & TestStr
'MsgBox TestStr
TestStr = Format(myNumber, "Scientific") '5.62E+05
TestLog_ASSERT TestStr = "5.62E+05", "Scientific: " & TestStr
'MsgBox TestStr
TestStr = Format(-3456.789, "Scientific") '-3.46E+03
TestLog_ASSERT TestStr = "-3.46E+03", "Scientific: " & TestStr
'MsgBox TestStr
TestStr = Format(0, "Yes/No") 'No
TestLog_ASSERT TestStr = "No", "Yes/No: " & TestStr
'MsgBox TestStr
TestStr = Format(23, "Yes/No") 'Yes
TestLog_ASSERT TestStr = "Yes", "Yes/No: " & TestStr
'MsgBox TestStr
TestStr = Format(0, "True/False") 'False
TestLog_ASSERT TestStr = "False", "True/False: " & TestStr
'MsgBox TestStr
TestStr = Format(23, "True/False") 'True
TestLog_ASSERT TestStr = "True", "True/False: " & TestStr
'MsgBox TestStr
TestStr = Format(0, "On/Off") 'Off
TestLog_ASSERT TestStr = "Off", "On/Off: " & TestStr
'MsgBox TestStr
TestStr = Format(23, "On/Off") 'On
TestLog_ASSERT TestStr = "On", "On/Off: " & TestStr
'MsgBox TestStr
Exit Sub
errorHandler:
TestLog_ASSERT (false), testName & ": hit error handler"
End Sub
Sub Custom_Datetime_Format_Sample()
Dim myDate, MyTime, TestStr As String
Dim testName As String
myDate = "01/06/98"
MyTime = "05:08:06"
testName = "Test Custom_Datetime_Format_Sample function"
On Error GoTo errorHandler
TestStr = Format("01/06/98 17:08:06", "c") ' 1/6/98 5:08:06 PM
TestLog_ASSERT TestStr = "1/6/98 5:08:06 PM", "c: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "dddddd") ' (Long Date), Tuesday, January 06, 1998
TestLog_ASSERT TestStr = "Tuesday, January 06, 1998", "dddddd: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "mm-dd-yyyy") ' 01-06-19s98
TestLog_ASSERT TestStr = "01-06-1998", "mm-dd-yyyy: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "d") ' 6
TestLog_ASSERT TestStr = "6", "d: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "dd") ' 06
TestLog_ASSERT TestStr = "06", "dd: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "ddd") ' Tue
TestLog_ASSERT TestStr = "Tue", "ddd: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "dddd") ' Tuesday
TestLog_ASSERT TestStr = "Tuesday", "dddd: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(MyTime, "h") ' 5
TestLog_ASSERT TestStr = "5", "h: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(MyTime, "hh") ' 05
TestLog_ASSERT TestStr = "05", "hh: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(MyTime, "n") ' 8
TestLog_ASSERT TestStr = "8", "n: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(MyTime, "nn") ' 08
TestLog_ASSERT TestStr = "08", "nn: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "m") ' 1
TestLog_ASSERT TestStr = "1", "m: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "mm") ' 01
TestLog_ASSERT TestStr = "01", "mm: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "mmm") ' Jan
TestLog_ASSERT TestStr = "Jan", "mmm: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "mmmm") ' January
TestLog_ASSERT TestStr = "January", "mmmm: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(MyTime, "s") ' 6
TestLog_ASSERT TestStr = "6", "s: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(MyTime, "ss") ' 06
TestLog_ASSERT TestStr = "06", "ss: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
MyTime = "17:08:06"
TestStr = Format(MyTime, "hh:mm:ss AM/PM") ' 05:08:06 PM
TestLog_ASSERT TestStr = "05:08:06 PM", "hh:mm:ss AM/PM: " & TestStr & " (Test only applies to en_US locale)"
TestStr = Format(MyTime, "hh:mm:ss") ' 17:08:06
TestLog_ASSERT TestStr = "17:08:06", "hh:mm:ss: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "ww") ' 2
TestLog_ASSERT TestStr = "2", "ww: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "w") ' 3
TestLog_ASSERT TestStr = "3", "w: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "y") ' 6
TestLog_ASSERT TestStr = "6", "y: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "yy") ' 98
TestLog_ASSERT TestStr = "98", "yy: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
TestStr = Format(myDate, "yyyy") ' 1998
TestLog_ASSERT TestStr = "1998", "yyyy: " & TestStr & " (Test only applies to en_US locale)"
'MsgBox TestStr
Exit Sub
errorHandler:
TestLog_ASSERT (false), testName & ": hit error handler"
End Sub
Sub Custom_Number_Format_Sample()
Dim TestStr As String
Dim testName As String
testName = "Test Custom_Number_Format_Sample function"
On Error GoTo errorHandler
TestStr = Format(23.675, "00.0000") ' 23.6750
TestLog_ASSERT TestStr = "23.6750", "00.0000: " & TestStr
'MsgBox TestStr
TestStr = Format(23.675, "00.00") ' 23.68
TestLog_ASSERT TestStr = "23.68", "00.00: " & TestStr
'MsgBox TestStr
TestStr = Format(2658, "00000") ' 02658
TestLog_ASSERT TestStr = "02658", "00000: " & TestStr
'MsgBox TestStr
TestStr = Format(2658, "00.00") ' 2658.00
TestLog_ASSERT TestStr = "2658.00", "00.00: " & TestStr
'MsgBox TestStr
TestStr = Format(23.675, "##.####") ' 23.675
TestLog_ASSERT TestStr = "23.675", "##.####: " & TestStr
'MsgBox TestStr
TestStr = Format(23.675, "##.##") ' 23.68
TestLog_ASSERT TestStr = "23.68", "##.##: " & TestStr
'MsgBox TestStr
TestStr = Format(12345.25, "#,###.##") '12,345.25
TestLog_ASSERT TestStr = "12,345.25", "#,###.##: " & TestStr
'MsgBox TestStr
TestStr = Format(0.25, "##.00%") '25.00%
TestLog_ASSERT TestStr = "25.00%", "##.00%: " & TestStr
'MsgBox TestStr
TestStr = Format(1000000, "#,###") '1,000,000
TestLog_ASSERT TestStr = "1,000,000", "#,###: " & TestStr
'MsgBox TestStr
TestStr = Format(1.09837555, "######E-###") '109838E-5
TestLog_ASSERT TestStr = "109838E-5", "######E-###: " & TestStr
'MsgBox TestStr
TestStr = Format(2345.25, "$#,###.##") '$2.345.25
TestLog_ASSERT TestStr = "$2,345.25", "$#,###.##: " & TestStr
'MsgBox TestStr
TestStr = Format(0.25, "##.###\%") '.25%
TestLog_ASSERT TestStr = ".25%", "##.###\%: " & TestStr
'MsgBox TestStr
Exit Sub
errorHandler:
TestLog_ASSERT (false), testName & ": hit error handler"
End Sub
Sub Custom_Text_Format_Sample()
Dim myText, TestStr As String
myText = "VBA"
Dim testName As String
testName = "Test Custom_Text_Format_Sample function"
On Error GoTo errorHandler
TestStr = Format(myText, "<") 'vba
TestLog_ASSERT TestStr = "vba", "<: " & TestStr
'MsgBox TestStr
TestStr = Format("vba", ">") 'VBA
TestLog_ASSERT TestStr = "VBA", ">: " & TestStr
'MsgBox TestStr
Exit Sub
errorHandler:
TestLog_ASSERT (false), testName & "hit error handler"
End Sub
Sub testFormat()
Dim testName As String
Dim TestDateTime As Date
Dim TestStr As String
testName = "Test Format function"
On Error GoTo errorHandler
TestDateTime = "1/27/2001 5:04:23 PM"
' Returns the value of TestDateTime in user-defined date/time formats.
' Returns "17:4:23".
TestStr = Format(TestDateTime, "h:m:s")
TestLog_ASSERT TestStr = "17:4:23", "the format of h:m:s: " & TestStr
' Returns "05:04:23 PM".
TestStr = Format(TestDateTime, "ttttt")
TestLog_ASSERT TestStr = "5:04:23 PM", "the format of ttttt: " & TestStr
' Returns "Saturday, Jan 27 2001".
TestStr = Format(TestDateTime, "dddd, MMM d yyyy")
TestLog_ASSERT TestStr = "Saturday, Jan 27 2001", "the format of dddd, MMM d yyyy: " & TestStr
' Returns "17:04:23".
TestStr = Format(TestDateTime, "HH:mm:ss")
TestLog_ASSERT TestStr = "17:04:23", "the format of HH:mm:ss: " & TestStr
' Returns "23".
TestStr = Format(23)
TestLog_ASSERT TestStr = "23", "no format:" & TestStr
' User-defined numeric formats.
' Returns "5,459.40".
TestStr = Format(5459.4, "##,##0.00")
TestLog_ASSERT TestStr = "5,459.40", "the format of ##,##0.00: " & TestStr
' Returns "334.90".
TestStr = Format(334.9, "###0.00")
TestLog_ASSERT TestStr = "334.90", "the format of ###0.00: " & TestStr
' Returns "500.00%".
TestStr = Format(5, "0.00%")
TestLog_ASSERT TestStr = "500.00%", "the format of 0.00%: " & TestStr
Exit Sub
errorHandler:
TestLog_ASSERT (false), testName & ": hit error handler"
End Sub
Sub TestLog_ASSERT(assertion As Boolean, Optional testId As String, Optional testComment As String)
If assertion = True Then
passCount = passCount + 1
Else
Dim testMsg As String
If Not IsMissing(testId) Then
testMsg = testMsg + " : " + testId
End If
If Not IsMissing(testComment) And Not (testComment = "") Then
testMsg = testMsg + " (" + testComment + ")"
End If
result = result & Chr$(10) & " Failed: " & testMsg
failCount = failCount + 1
End If
End Sub
|
