Loading VBA JSON variable then referencing it in Cells using Excel Formulas

Question

I have an event button in my excel document that loads a JSON document using a technique from this link: Is There a JSON Parser for VB6 / VBA?

This creates a JSON variable in the vb script that I can use based on this JSON string:

{
    "a": 1,
    "b": [
        13,
        1.4142135623730951,
        15
    ]
}

I can easily use VBA script to load the page. But...

How can I use Json inside a formula within a Cell? Does something exist that does this? $VARIABLE_LOOKUP(Json)("a") to get a value of 1 in the cell.

Can anyone get a working hello world scenario working of this scenario?

Click command button
It loads the Json
The formula in A5 should now evaluate to 1.

Can it be done?

An example of the code is inside this excel document here: https://drive.google.com/file/d/0B540RQ1Nj-KINnVQS3cwMDFsc1E/view?usp=sharing

Can you re-read the question? my first go at it was awful i added most of the beef second edit — Nicholas DiPiazza, Mar 13 '16 at 17:49
Your question would be improved by adding your existing code. Right now it's difficult to answer without knowing exactly what you've already done. You have loaded the json - where is it once you've loaded it? If you store it in a global variable then you should be able to write VBA functions which can be used to return specific parts of it as values, and those functions can be called from worksheets cells. — Tim Williams, Mar 13 '16 at 21:53
I figured it out. you can use functions and the functions can access the global variables. — Nicholas DiPiazza, Mar 13 '16 at 22:01

score 1 · Accepted Answer · edited May 23 '17 at 12:14

The answer: you can use custom functions defined in your own module to do this.

Create a module, add global variable for json and a method to set it (that your button will hit):

Private jsObj As Object

Public Function SetJsObj(jsonString As String)
    jsObj = JSON.parse(jsonString)
End Function

Public Function GetJsObj()
    GetJsObj = jsObj
End Function

Using the json parser Is There a JSON Parser for VB6 / VBA?

Then you use the function in the cell:

=GetJsObj()("a")

will now eval to

1

score 1 · Answer 2 · answered Mar 14 '16 at 08:33

1

A solution would be to create an UDF to eval any expression:

Public Function Eval(expression As String)
  Application.Volatile
  Eval = Application.Evaluate(expression)
End Function

Then in your formula:

=Eval("JSON.parse(Json)(""a"")")

answered Mar 14 '16 at 08:33

Florent B.

41,537
7
86
101

Eval is another way to do it. +1 – Nicholas DiPiazza Mar 14 '16 at 15:19

Loading VBA JSON variable then referencing it in Cells using Excel Formulas

2 Answers2