What are the limitations of the function subprogram in VBA

[torsteri]

Hey.

I'm trying to write a code that gathers a lot of information from many sources, but I'm discovering that this seems problematic when trying to get information from the internet.

Example:
I would like to write a function called temperature that would return the temperature for a specific city.

Let's say: Chicago

=temp(chicago)

This should lookup chicago on weather.com and return the current temp.

How would I do this?

[Kyle123]

Getting stuff from the web can be tricky. Whilst the methods are similar, the various technologies in a web environment can make getting the info you want hard.

It helps to be flexible where you get your data from because of the above, for example does your data actually have to come from weather.com

Do you actually need the above macro or was this an example as code has to be tailored to the web source

[torsteri]

Actually the page I am getting data from is a simple text base page in json format.

The problem I am having is that the UDF cannot open and import the data fr the page. Is there a workaround for this?

My question is quite simple. Let's say you have a text based web page, how do you return something from this page using a function?

Thanks for your help

[Kyle123]

There are no inbuilt methods for parsing JSON in Excel so you need to build your own, without knowing what you actually want here are some pointers:

This sub will get you the text of a given url, the .responseText is the text of the value returned in your case your json array. You'd then parse out the bits you want with normal string manipulation techniques, split/filter/instr/left/mid/right etc

Please Login or Register  to view this content.

For a different approach for a complex nested json object, I wrote the below for someone on a different site:

Please Login or Register  to view this content.