Stored variable from web service in VBA for Excel 2013

Question

I use a webservice function to get commodity prices in an Excel spreadsheet. I have VBA code that references the values of the cells in which these prices are stored. I would like to be able to get this value stored in my macro without having to use the function in a cell. An example of my webservice function:

=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=CLF16.nym&f=l1")

My macro takes the current month and year to build the URL (the ticker codes for futures vary by date, so CLF16.nym is crude oil price for January 2016).

Is it possible to get this commodity price as a value in VBA without using the webservice function?

Do you simply want to avoid using functions directly on the worksheet, or is there something about this particular function you want to avoid? — DanL
– DanL, Commented Oct 29, 2015 at 14:29
I'm trying to avoid using functions directly on the worksheet. — elt2jdavis
– elt2jdavis, Commented Oct 29, 2015 at 14:33

DanL · Accepted Answer · 2015-10-29 14:38:29Z

1

Since you just want to avoid using the function on the sheet, you can use the very same worksheet formula directly in code by using the WorksheetFunction property of Application, e.g.:

Dim s As String
s = Application.WorksheetFunction.WebService("http://finance.yahoo.com/d/quotes.csv?s=CLF16.nym&f=l1")

answered Oct 29, 2015 at 14:38

DanL

9991 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

elt2jdavis Over a year ago

Simple enough. When starting out in VBA I always had problems using worksheet functions in VBA and abandoned that technique. Seems to work fine though!

Collectives™ on Stack Overflow

Stored variable from web service in VBA for Excel 2013

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related