VBA - WIN32 API : Parse strings to numbers

VBA can call into the COM run-time parsing functions to get some serious help with parsing. In this example I show string being parsed to a numeric type. This code also handles exponential notation in case you not sufficiently impressed.

One point of note is that if you pass a number with 4 or fewer decimal places then the COM runtime will by default parse to the Currency type. I think most programmers would expect as Double so I've added some post-processing code whilst keeping the Currency option open.

If you really want a currency then I suggest you read this blog post where I parse currencies.

modVarNumFromStr Standard Module

1. Option Explicit
2.  
3. '*
4. '* Brought to you by the Excel Development Platform blog
5. '* https://exceldevelopmentplatform.blogspot.com/2018/12
6. '*
7.  
8. Private Type NUMPARSE
9.     cDig As Long
10.     dwInFlags As Long
11.     dwOutFlags As Long
12.     cchUsed As Long
13.     nBaseShift As Long
14.     nPwr10 As Long
15. End Type
16.  
17. Private Declare Function VarParseNumFromStr Lib "oleaut32" (ByVal strIn As Long, ByVal LCID As Long, _
18.     ByVal dwFlags As Long, ByRef numprs As NUMPARSE, ByRef rgbDig As Byte) As Long
19.  
20. Private Declare Function VarNumFromParseNum Lib "oleaut32" (ByRef pnumprs As NUMPARSE, _
21.     ByRef rgbDig As Byte, ByVal dwVtBits As Long, ByRef pvar As Variant) As Long
22.  
23. Sub TestVarNumFromStr()
24.     Dim v(0 To 9)
25.     v(0) = VarNumFromStr("14")
26.     Debug.Assert v(0) = 14
27.     Debug.Assert VarType(v(0)) = VbVarType.vbInteger
28.  
29.     v(1) = VarNumFromStr("15.1234")
30.     Debug.Assert v(1) = 15.1234
31.     Debug.Assert VarType(v(1)) = VbVarType.vbCurrency
32.  
33.     v(2) = VarNumFromStr("15.11111")
34.     Debug.Assert v(2) = 15.11111
35.     Debug.Assert VarType(v(2)) = VbVarType.vbDouble
36.  
37.     v(3) = VarNumFromStr("1.512E6")
38.     Debug.Assert v(3) = 1512000
39.     Debug.Assert VarType(v(3)) = VbVarType.vbLong
40.  
41.     v(4) = VarNumFromStr("1.512E-6")
42.     Debug.Assert v(4) = 0.000001512
43.     Debug.Assert VarType(v(4)) = VbVarType.vbDouble
44.  
45.     Stop
46. End Sub
47.  
48. Public Function VarNumFromStr(ByRef sText As String, Optional bConvertCurrencyToDouble As Boolean = True) As Variant
49.  
50.     Dim rgbDig() As Byte
51.     Dim uNUMPARSE As NUMPARSE
52.  
53.     Dim lLen As Long
54.     lLen = VBA.Len(sText)
55.  
56.     If lLen > 0 Then
57.         ReDim rgbDig(lLen - 1)
58.  
59.         '* https://technet.microsoft.com/ru-ru/ms221690(v=vs.90)
60.         With uNUMPARSE
61.             .cDig = lLen
62.             .dwInFlags = &H1FFF& 'NUMPRS_STD
63.         End With
64.  
65.         '* https://docs.microsoft.com/en-us/windows/desktop/api/oleauto/nf-oleauto-varparsenumfromstr
66.         If VarParseNumFromStr(StrPtr(sText), 0, 0, uNUMPARSE, rgbDig(0)) = 0 Then
67.  
68.             '* https://docs.microsoft.com/en-us/windows/desktop/api/oleauto/nf-oleauto-varnumfromparsenum#vtbit_decimal
69.             If VarNumFromParseNum(uNUMPARSE, rgbDig(0), 2047, VarNumFromStr) = 0 Then
70.                 '* do we need any post-processing
71.                 If bConvertCurrencyToDouble And InStr(1, sText, ".", vbTextCompare) > 0 And _
72.                                 VarType(VarNumFromStr) = vbCurrency Then
73.                     VarNumFromStr = CDbl(VarNumFromStr) '* convert decimal currency
74.                 End If
75.  
76.             Else
77.  
78.                 Err.Raise vbObjectError, , "#Error whilst parsing '" & sText & "'!"
79.             End If
80.         Else
81.             Err.Raise vbObjectError, , "#Error whilst parsing '" & sText & "'!"
82.         End If
83.     End If
84. End Function