WebService error in VBA macro.

[Excelerate1]

Hi, I'm new to this Forum.
I'm using Excel 2013, Windows 10.

I'm trying to write a macro (VBA) to use WebService, but it continually errors with: Compile Error Sub or Function not defined.

I can get WebService to work on a Worksheet, by typing in cell B7 =NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&",&f=l1&"))

Here's my simple Macro:

Sub Quote()

Sheets("Sheet1").Cells(7, "B") = NumberValue(WebService("http://finance.yahoo.com/d/quotes.csv?s=" & A1&, f = "l1&"))

End Sub


In cell A1 I have the stock symbol e.g. DLG.L

I wish the value defined by l1 (Current Price) to be put in cell B7.

Please keep your replies simple, as I'm fairly new to this.
Thank you.

[xlnitwit]

Hi,

You cannot simply type formulas into VBA code, I'm afraid. You might enter the formula into the cell and then replace with its value if required

Please Login or Register  to view this content.

[Excelerate1]

Thanks for your reply.

Yes, it now doesn't give a compile error. I can see that the formula is now defined.
However, copying your solution into my Macro, doesn't give any result in cell B7.

[xlnitwit]

If you remove the .Value = .Value line, do you see a result?

[Excelerate1]

Hi

Whether the line Value = .Value is there or not, cell B7 gives the result value of zero (0)

[xlnitwit]

Does the formula in there look correct?

[Excelerate1]

Hi

It'll probably help if I tell you what I've done so far.

When I entered your suggestion (exactly) I got a compile error, so I removed the period before Formula and Value.
That gave no compile errors. But I now see that was not correct.

The (new) compile error is: Application defined or Object defined error. The line .Formula is hi-lited in yellow

[Excelerate1]

Sorry, it's a run-time error 1004

[xlnitwit]

Aha- there is a difference between the original formula and the formula version in your original code, and I copied the latter. Try this

Please Login or Register  to view this content.

[Excelerate1]

Hi xlnitwit

Yes that works lovely. Thank you very much.
Just so I understand, and therefore become slightly more educated in Excel VBA.
What is the significance of double quotes rather than single quotes.

[xlnitwit]

The double quotes are necessary to escape the single ones inside a quoted string.

The syntax for putting a formula in a cell is

Please Login or Register  to view this content.

and the compiler treats everything inside pairs of quotes- the two highlighted quotes- as the string. If the string in between the quotes itself contains quotes, the compiler would treat each pair as separate strings. In the case of your formula, that would look like this

Please Login or Register  to view this content.

so the compiler would see three strings, and some supposed VBA commands which would make no sense to it in terms of VBA syntax.

By doubling up the quotes inside the string, the compiler knows not to treat them as terminating the original string

Please Login or Register  to view this content.

Does my explanation make sense to you?

[Excelerate1]

Thanks xlnitwit,
Yes your explanation makes perfect sense.

Thank you for persevering with me and solving my problem.
All the best
Excelerate1