CODEWITHSUNDEEP
jsonvbaexcel
guagay_wk
guagay_wk

Reputation: 28050

How can I extract a value from this simple JSON string in Excel VBA?

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.

Upvotes: 2

Views: 3116

Answers (1)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 3

Related Questions