I have this simple json string in VBA :
{
price_A: "0.172",
price_B: "0.8",
price_C: "1.3515"
}
I would like to extract the value of price_A which is 0.172.
How can this be done in Excel VBA? I am using Microsoft Office 2013.
I don't need an extensive JSON library. Just a simple function to extract the value of price_A.
A Regexp gives you more control on the parsing rather than relying on character length.
As an example:
Sub test_Function()
Dim StrIn As String
StrIn = "{" & _
"price_A: " & Chr(34) & "0.182" & Chr(34) & "," & _
"price_B: " & Chr(34) & "0.8" & Chr(34) & "," & _
"price_C: " & Chr(34) & "1.3515" & Chr(34) & "}"
MsgBox Get_Value(StrIn)
End Sub
extract first set of numbers in "" after Price A. Can be adapted to check for Price B
Public Function Get_Value(StrIn As String) As String
Dim objRegexp As Object
Dim objRegMC As Object
Set objRegexp = CreateObject("vbscript.regexp")
With objRegexp
.Pattern = "price_A: ""([\d\.]+)"""
If .test(StrIn) Then
Set objRegMC = .Execute(StrIn)
Get_Value = objRegMC(0).submatches(0)
End If
End With
End Function
shorted version to extract first set of numbers
Public Function Get_Value(StrIn As String) As String
Dim objRegexp As Object
Set objRegexp = CreateObject("vbscript.regexp")
With objRegexp
.Pattern = "[\d\.]+"
If .test(StrIn) Then Get_Value = .Execute(StrIn)(0)
End With
End Function
