basic/qa/vba_tests/format.vb

   1 Option VBASupport 1
   2 Option Explicit
   3
   4 Dim passCount As Integer
   5 Dim failCount As Integer
   6 Dim result As String
   7
   8 Function doUnitTest() As String
   9 result = verify_format()
  10 If failCount <> 0 And passCount > 0 Then
  11     doUnitTest = result
  12 Else
  13     doUnitTest = "OK"
  14 End If
  15 End Function
  16
  17 Function verify_format() as String
  18     passCount = 0
  19     failCount = 0
  20
  21     result = "Test Results" & Chr$(10) & "============" & Chr$(10)
  22
  23     'Predefined_Datetime_Format_Sample
  24     Predefined_Number_Format_Sample
  25     'Custom_Datetime_Format_Sample
  26     Custom_Number_Format_Sample
  27     Custom_Text_Format_Sample
  28     result = result & Chr$(10) & "Tests passed: " & passCount & Chr$(10) & "Tests failed: " & failCount & Chr$(10)
  29     verify_format = result
  30
  31 End Sub
  32
  33
  34 Sub Predefined_Datetime_Format_Sample()
  35     Dim testName As String
  36     Dim myDate, MyTime, TestStr As String
  37     myDate = "01/06/98"
  38     MyTime = "17:08:06"
  39     testName = "Test Predefined_Datetime_Format_Sample function"
  40
  41     On Error GoTo errorHandler
  42
  43     ' The date/time format have a little different between ms office and OOo due to different locale and system...
  44     TestStr = Format(myDate, "General Date") ' 1/6/98
  45
  46     TestLog_ASSERT IsDate(TestStr), "General Date: " & TestStr & " (Test only applies to en_US locale)"
  47     'TestLog_ASSERT TestStr = "1/6/98", "General Date: " & TestStr
  48
  49     TestStr = Format(myDate, "Long Date") ' Tuesday, January 06, 1998
  50     TestLog_ASSERT TestStr = "Tuesday, January 06, 1998", "Long Date: " & TestStr & " (Test only applies to en_US locale)"
  51     'TestLog_ASSERT IsDate(TestStr), "Long Date: " & TestStr
  52
  53     TestStr = Format(myDate, "Medium Date") ' 06-Jan-98
  54     'TestLog_ASSERT TestStr = "06-Jan-98", "Medium Date: " & TestStr
  55     TestLog_ASSERT IsDate(TestStr), "Medium Date: " & TestStr & " (Test only applies to en_US locale)"
  56
  57
  58     TestStr = Format(myDate, "Short Date") ' 1/6/98
  59     'TestLog_ASSERT TestStr = "1/6/98", "Short Date: " & TestStr
  60     TestLog_ASSERT IsDate(TestStr), "Short Date: " & TestStr & " (Test only applies to en_US locale)"
  61
  62     TestStr = Format(MyTime, "Long Time") ' 5:08:06 PM
  63     'TestLog_ASSERT TestStr = "5:08:06 PM", "Long Time: " & TestStr
  64     TestLog_ASSERT IsDate(TestStr), "Long Time: " & TestStr & " (Test only applies to en_US locale)"
  65
  66
  67     TestStr = Format(MyTime, "Medium Time") ' 05:08 PM
  68     'TestLog_ASSERT TestStr = "05:08 PM", "Medium Time: " & TestStr
  69     TestLog_ASSERT IsDate(TestStr), "Medium Time: " & TestStr & " (Test only applies to en_US locale)"
  70
  71
  72     TestStr = Format(MyTime, "Short Time") ' 17:08
  73     'TestLog_ASSERT TestStr = "17:08", "Short Time: " & TestStr
  74     TestLog_ASSERT IsDate(TestStr), "Short Time: " & TestStr & " (Test only applies to en_US locale)"
  75     Exit Sub
  76 errorHandler:
  77         TestLog_ASSERT (false), testName & ": hit error handler"
  78 End Sub
  79
  80 Sub Predefined_Number_Format_Sample()
  81     Dim myNumber, TestStr As String
  82     Dim testName As String
  83     testName = "Test Predefined_Number_Format_Sample function"
  84     myNumber = 562486.2356
  85
  86      On Error GoTo errorHandler
  87
  88     TestStr = Format(myNumber, "General Number") '562486.2356
  89     TestLog_ASSERT TestStr = "562486.2356", "General Number: " & TestStr
  90     'MsgBox TestStr
  91
  92     TestStr = Format(0.2, "Fixed") '0.20
  93     TestLog_ASSERT TestStr = "0.20", "Fixed: " & TestStr
  94     'MsgBox TestStr
  95
  96     TestStr = Format(myNumber, "Standard") '562,486.24
  97     TestLog_ASSERT TestStr = "562,486.24", "Standard: " & TestStr
  98     'MsgBox TestStr
  99
 100     TestStr = Format(0.7521, "Percent") '75.21%
 101     TestLog_ASSERT TestStr = "75.21%", "Percent: " & TestStr
 102     'MsgBox TestStr
 103
 104     TestStr = Format(myNumber, "Scientific") '5.62E+05
 105     TestLog_ASSERT TestStr = "5.62E+05", "Scientific: " & TestStr
 106     'MsgBox TestStr
 107
 108     TestStr = Format(-3456.789, "Scientific") '-3.46E+03
 109     TestLog_ASSERT TestStr = "-3.46E+03", "Scientific: " & TestStr
 110     'MsgBox TestStr
 111
 112     TestStr = Format(0, "Yes/No") 'No
 113     TestLog_ASSERT TestStr = "No", "Yes/No: " & TestStr
 114     'MsgBox TestStr
 115
 116     TestStr = Format(23, "Yes/No") 'Yes
 117     TestLog_ASSERT TestStr = "Yes", "Yes/No: " & TestStr
 118     'MsgBox TestStr
 119
 120     TestStr = Format(0, "True/False") 'False
 121     TestLog_ASSERT TestStr = "False", "True/False: " & TestStr
 122     'MsgBox TestStr
 123
 124     TestStr = Format(23, "True/False") 'True
 125     TestLog_ASSERT TestStr = "True", "True/False: " & TestStr
 126     'MsgBox TestStr
 127
 128     TestStr = Format(0, "On/Off") 'Off
 129     TestLog_ASSERT TestStr = "Off", "On/Off: " & TestStr
 130     'MsgBox TestStr
 131
 132     TestStr = Format(23, "On/Off") 'On
 133     TestLog_ASSERT TestStr = "On", "On/Off: " & TestStr
 134     'MsgBox TestStr
 135
 136     Exit Sub
 137 errorHandler:
 138         TestLog_ASSERT (false), testName & ": hit error handler"
 139
 140 End Sub
 141
 142 Sub Custom_Datetime_Format_Sample()
 143     Dim myDate, MyTime, TestStr As String
 144     Dim testName As String
 145
 146     myDate = "01/06/98"
 147     MyTime = "05:08:06"
 148
 149     testName = "Test Custom_Datetime_Format_Sample function"
 150      On Error GoTo errorHandler
 151
 152     TestStr = Format("01/06/98 17:08:06", "c") ' 1/6/98 5:08:06 PM
 153     TestLog_ASSERT TestStr = "1/6/98 5:08:06 PM", "c: " & TestStr & " (Test only applies to en_US locale)"
 154     'MsgBox TestStr
 155
 156     TestStr = Format(myDate, "dddddd") ' (Long Date), Tuesday, January 06, 1998
 157     TestLog_ASSERT TestStr = "Tuesday, January 06, 1998", "dddddd: " & TestStr & " (Test only applies to en_US locale)"
 158     'MsgBox TestStr
 159
 160     TestStr = Format(myDate, "mm-dd-yyyy") ' 01-06-19s98
 161     TestLog_ASSERT TestStr = "01-06-1998", "mm-dd-yyyy: " & TestStr & " (Test only applies to en_US locale)"
 162     'MsgBox TestStr
 163
 164     TestStr = Format(myDate, "d") ' 6
 165     TestLog_ASSERT TestStr = "6", "d: " & TestStr & " (Test only applies to en_US locale)"
 166     'MsgBox TestStr
 167
 168     TestStr = Format(myDate, "dd") ' 06
 169     TestLog_ASSERT TestStr = "06", "dd: " & TestStr & " (Test only applies to en_US locale)"
 170     'MsgBox TestStr
 171
 172     TestStr = Format(myDate, "ddd") ' Tue
 173     TestLog_ASSERT TestStr = "Tue", "ddd: " & TestStr & " (Test only applies to en_US locale)"
 174     'MsgBox TestStr
 175
 176     TestStr = Format(myDate, "dddd") ' Tuesday
 177     TestLog_ASSERT TestStr = "Tuesday", "dddd: " & TestStr & " (Test only applies to en_US locale)"
 178     'MsgBox TestStr
 179
 180     TestStr = Format(MyTime, "h") ' 5
 181     TestLog_ASSERT TestStr = "5", "h: " & TestStr & " (Test only applies to en_US locale)"
 182     'MsgBox TestStr
 183
 184     TestStr = Format(MyTime, "hh") ' 05
 185     TestLog_ASSERT TestStr = "05", "hh: " & TestStr & " (Test only applies to en_US locale)"
 186     'MsgBox TestStr
 187
 188     TestStr = Format(MyTime, "n") ' 8
 189     TestLog_ASSERT TestStr = "8", "n: " & TestStr & " (Test only applies to en_US locale)"
 190     'MsgBox TestStr
 191
 192     TestStr = Format(MyTime, "nn") ' 08
 193     TestLog_ASSERT TestStr = "08", "nn: " & TestStr & " (Test only applies to en_US locale)"
 194     'MsgBox TestStr
 195
 196     TestStr = Format(myDate, "m") ' 1
 197     TestLog_ASSERT TestStr = "1", "m: " & TestStr & " (Test only applies to en_US locale)"
 198     'MsgBox TestStr
 199
 200     TestStr = Format(myDate, "mm") ' 01
 201     TestLog_ASSERT TestStr = "01", "mm: " & TestStr & " (Test only applies to en_US locale)"
 202     'MsgBox TestStr
 203
 204     TestStr = Format(myDate, "mmm") ' Jan
 205     TestLog_ASSERT TestStr = "Jan", "mmm: " & TestStr & " (Test only applies to en_US locale)"
 206     'MsgBox TestStr
 207
 208     TestStr = Format(myDate, "mmmm") ' January
 209     TestLog_ASSERT TestStr = "January", "mmmm: " & TestStr & " (Test only applies to en_US locale)"
 210     'MsgBox TestStr
 211
 212     TestStr = Format(MyTime, "s") ' 6
 213     TestLog_ASSERT TestStr = "6", "s: " & TestStr & " (Test only applies to en_US locale)"
 214     'MsgBox TestStr
 215
 216     TestStr = Format(MyTime, "ss") ' 06
 217     TestLog_ASSERT TestStr = "06", "ss: " & TestStr & " (Test only applies to en_US locale)"
 218     'MsgBox TestStr
 219
 220
 221     MyTime = "17:08:06"
 222
 223     TestStr = Format(MyTime, "hh:mm:ss AM/PM") ' 05:08:06 PM
 224     TestLog_ASSERT TestStr = "05:08:06 PM", "hh:mm:ss AM/PM: " & TestStr & " (Test only applies to en_US locale)"
 225
 226
 227     TestStr = Format(MyTime, "hh:mm:ss") ' 17:08:06
 228     TestLog_ASSERT TestStr = "17:08:06", "hh:mm:ss: " & TestStr & " (Test only applies to en_US locale)"
 229     'MsgBox TestStr
 230
 231     TestStr = Format(myDate, "ww") ' 2
 232     TestLog_ASSERT TestStr = "2", "ww: " & TestStr & " (Test only applies to en_US locale)"
 233     'MsgBox TestStr
 234
 235     TestStr = Format(myDate, "w") ' 3
 236     TestLog_ASSERT TestStr = "3", "w: " & TestStr & " (Test only applies to en_US locale)"
 237     'MsgBox TestStr
 238
 239     TestStr = Format(myDate, "y") ' 6
 240     TestLog_ASSERT TestStr = "6", "y: " & TestStr & " (Test only applies to en_US locale)"
 241     'MsgBox TestStr
 242
 243     TestStr = Format(myDate, "yy") ' 98
 244     TestLog_ASSERT TestStr = "98", "yy: " & TestStr & " (Test only applies to en_US locale)"
 245     'MsgBox TestStr
 246
 247     TestStr = Format(myDate, "yyyy") ' 1998
 248     TestLog_ASSERT TestStr = "1998", "yyyy: " & TestStr & " (Test only applies to en_US locale)"
 249     'MsgBox TestStr
 250
 251     Exit Sub
 252 errorHandler:
 253         TestLog_ASSERT (false), testName & ": hit error handler"
 254 End Sub
 255
 256 Sub Custom_Number_Format_Sample()
 257     Dim TestStr As String
 258     Dim testName As String
 259
 260     testName = "Test Custom_Number_Format_Sample function"
 261      On Error GoTo errorHandler
 262
 263     TestStr = Format(23.675, "00.0000") ' 23.6750
 264     TestLog_ASSERT TestStr = "23.6750", "00.0000: " & TestStr
 265     'MsgBox TestStr
 266
 267     TestStr = Format(23.675, "00.00") ' 23.68
 268     TestLog_ASSERT TestStr = "23.68", "00.00: " & TestStr
 269     'MsgBox TestStr
 270
 271     TestStr = Format(2658, "00000") ' 02658
 272     TestLog_ASSERT TestStr = "02658", "00000: " & TestStr
 273     'MsgBox TestStr
 274
 275     TestStr = Format(2658, "00.00") ' 2658.00
 276     TestLog_ASSERT TestStr = "2658.00", "00.00: " & TestStr
 277     'MsgBox TestStr
 278
 279     TestStr = Format(23.675, "##.####") ' 23.675
 280     TestLog_ASSERT TestStr = "23.675", "##.####: " & TestStr
 281     'MsgBox TestStr
 282
 283     TestStr = Format(23.675, "##.##") ' 23.68
 284     TestLog_ASSERT TestStr = "23.68", "##.##: " & TestStr
 285     'MsgBox TestStr
 286
 287     TestStr = Format(12345.25, "#,###.##") '12,345.25
 288     TestLog_ASSERT TestStr = "12,345.25", "#,###.##: " & TestStr
 289     'MsgBox TestStr
 290
 291     TestStr = Format(0.25, "##.00%") '25.00%
 292     TestLog_ASSERT TestStr = "25.00%", "##.00%: " & TestStr
 293     'MsgBox TestStr
 294
 295     TestStr = Format(1000000, "#,###") '1,000,000
 296     TestLog_ASSERT TestStr = "1,000,000", "#,###: " & TestStr
 297     'MsgBox TestStr
 298
 299     TestStr = Format(1.09837555, "#.#####E+###") '1.09838E+000
 300     TestLog_ASSERT TestStr = "1.09838E+000", "#.#####E+###: " & TestStr
 301     'MsgBox TestStr
 302
 303     TestStr = Format(1.09837555, "###.####E#") '1.0984E0 with engineering notation
 304     TestLog_ASSERT TestStr = "1.0984E0", "###.####E#: " & TestStr
 305     'MsgBox TestStr
 306
 307     TestStr = Format(1098.37555, "###.####E#") '1.0984E3 with engineering notation
 308     TestLog_ASSERT TestStr = "1.0984E3", "###.####E#: " & TestStr
 309     'MsgBox TestStr
 310
 311     TestStr = Format(1098375.55, "###.####E#") '1.0984E6 with engineering notation
 312     TestLog_ASSERT TestStr = "1.0984E6", "###.####E#: " & TestStr
 313     'MsgBox TestStr
 314
 315     TestStr = Format(1.09837555, "######E#") '1E0 with engineering notation
 316     TestLog_ASSERT TestStr = "1E0", "######E#: " & TestStr
 317     'MsgBox TestStr
 318
 319     TestStr = Format(123456.789, "###E0") '123E3 with engineering notation
 320     TestLog_ASSERT TestStr = "123E3", "###E0: " & TestStr
 321     'MsgBox TestStr
 322
 323     TestStr = Format(123567.89, "###E0") '124E3 with engineering notation
 324     TestLog_ASSERT TestStr = "124E3", "###E0: " & TestStr
 325     'MsgBox TestStr
 326
 327     TestStr = Format(12, "###E0") '12E0 with engineering notation
 328     TestLog_ASSERT TestStr = "12E0", "###E0: " & TestStr
 329     'MsgBox TestStr
 330
 331     TestStr = Format(12, "000E0") '012E0 with engineering notation
 332     TestLog_ASSERT TestStr = "012E0", "000E0: " & TestStr
 333     'MsgBox TestStr
 334
 335     TestStr = Format(0.12345, "###E0") '123E-3 with engineering notation
 336     TestLog_ASSERT TestStr = "123E-3", "###E0: " & TestStr
 337     'MsgBox TestStr
 338
 339     TestStr = Format(123456, "####E0") '12E4 with interval-4 notation
 340     TestLog_ASSERT TestStr = "12E4", "####E0: " & TestStr
 341     'MsgBox TestStr
 342
 343     TestStr = Format(2345.25, "$#,###.##") '$2.345.25
 344     TestLog_ASSERT TestStr = "$2,345.25", "$#,###.##: " & TestStr
 345     'MsgBox TestStr
 346
 347     TestStr = Format(0.25, "##.###\%") '.25%
 348     TestLog_ASSERT TestStr = ".25%", "##.###\%: " & TestStr
 349     'MsgBox TestStr
 350
 351     Exit Sub
 352 errorHandler:
 353         TestLog_ASSERT (false), testName & ": hit error handler"
 354 End Sub
 355
 356 Sub Custom_Text_Format_Sample()
 357     Dim myText, TestStr As String
 358     myText = "VBA"
 359
 360     Dim testName As String
 361
 362     testName = "Test Custom_Text_Format_Sample function"
 363      On Error GoTo errorHandler
 364
 365     TestStr = Format(myText, "<") 'vba
 366      TestLog_ASSERT TestStr = "vba", "<: " & TestStr
 367     'MsgBox TestStr
 368
 369     TestStr = Format("vba", ">") 'VBA
 370      TestLog_ASSERT TestStr = "VBA", ">: " & TestStr
 371     'MsgBox TestStr
 372
 373     Exit Sub
 374 errorHandler:
 375         TestLog_ASSERT (false), testName & "hit error handler"
 376 End Sub
 377
 378
 379
 380 Sub testFormat()
 381     Dim testName As String
 382     Dim TestDateTime As Date
 383     Dim TestStr As String
 384     testName = "Test Format function"
 385
 386     On Error GoTo errorHandler
 387
 388     TestDateTime = "1/27/2001 5:04:23 PM"
 389
 390     ' Returns the value of TestDateTime in user-defined date/time formats.
 391     ' Returns "17:4:23".
 392     TestStr = Format(TestDateTime, "h:m:s")
 393     TestLog_ASSERT TestStr = "17:4:23", "the format of h:m:s: " & TestStr
 394
 395     ' Returns "05:04:23 PM".
 396     TestStr = Format(TestDateTime, "ttttt")
 397     TestLog_ASSERT TestStr = "5:04:23 PM", "the format of ttttt: " & TestStr
 398
 399     ' Returns "Saturday, Jan 27 2001".
 400     TestStr = Format(TestDateTime, "dddd, MMM d yyyy")
 401     TestLog_ASSERT TestStr = "Saturday, Jan 27 2001", "the format of dddd, MMM d yyyy: " & TestStr
 402
 403     ' Returns "17:04:23".
 404     TestStr = Format(TestDateTime, "HH:mm:ss")
 405     TestLog_ASSERT TestStr = "17:04:23", "the format of HH:mm:ss: " & TestStr
 406
 407     ' Returns "23".
 408     TestStr = Format(23)
 409     TestLog_ASSERT TestStr = "23", "no format:" & TestStr
 410
 411     ' User-defined numeric formats.
 412     ' Returns "5,459.40".
 413     TestStr = Format(5459.4, "##,##0.00")
 414     TestLog_ASSERT TestStr = "5,459.40", "the format of ##,##0.00: " & TestStr
 415
 416     ' Returns "334.90".
 417     TestStr = Format(334.9, "###0.00")
 418     TestLog_ASSERT TestStr = "334.90", "the format of ###0.00: " & TestStr
 419
 420     ' Returns "500.00%".
 421     TestStr = Format(5, "0.00%")
 422     TestLog_ASSERT TestStr = "500.00%", "the format of 0.00%: " & TestStr
 423     Exit Sub
 424 errorHandler:
 425         TestLog_ASSERT (false), testName & ": hit error handler"
 426 End Sub
 427
 428 Sub TestLog_ASSERT(assertion As Boolean, Optional testId As String, Optional testComment As String)
 429
 430     If assertion = True Then
 431         passCount = passCount + 1
 432     Else
 433         Dim testMsg As String
 434         If Not IsMissing(testId) Then
 435             testMsg = testMsg + " : " + testId
 436         End If
 437         If Not IsMissing(testComment) And Not (testComment = "") Then
 438             testMsg = testMsg + " (" + testComment + ")"
 439         End If
 440
 441         result = result & Chr$(10) & " Failed: " & testMsg
 442         failCount = failCount + 1
 443     End If
 444
 445 End Sub
 446