Using XLQ in VBA

[emailing]

Hi everybody,

Looking for support with the following problem:

I am using the XLQ-AddIn (xlquotes.com) to get stock prices from the Internet. The formula is ?=XLQ()?, e.g. for the last stock price of Yahoo you use
=XLQ("YHOO", "LAST")

Now I want to use this in VBA, so that I do not always have to enter the whole formula on the worksheet. Via a userform I enter the stock code (e.g. YHOO) into one column and want to use this code in another column together with the formula. Unfortunately it does not work the way I do it.

I have tried it with

ActiveCell.Offset(0, 14).Formula = "=XLQ(" & Me.txtCode.Text & ", "LAST")"

Me.txtCode.Text refers to the stock code entered into the user form.

Don?t know, maybe it?s not possible to use the XLQ formula in VBA???

Would be great if somebody could help me on this, many thanks in advance!

[Bob Phillips]

ActiveCell.Offset(0, 14).Formula "=XLQ(""" & Me.txtCode.Text & """,""LAST"")"

[emailing]

Thanks xld, that works (at least when I add the equal sign that you had forgotten):

ActiveCell.Offset(0, 14).Formula = "=XLQ(""" & Me.txtCode.Text & """,""LAST"")"

When I use the German version of the formula I have to use

ActiveCell.Offset(0, 14).Formula = "=XLQ(""" & Me.txtCode.Text & """;""LETZTER"")"

Here I still get an error message. Probably because of the semikolon instead of the comma? How can I sail around that? However, at least I can use the English version now, so many thanks for that again!!!!!!!!!

[Bob Phillips]

What equals sign was I missing?

IN VBA you always use a comma, even if your Excel uses a semi-colon, Excel will handle the conversion.

[emailing]

The equal sign after the .formula was missing in your first reply, without it it did not work:

ActiveCell.Offset(0, 14).Formula "=XLQ(""" & Me.txtCode.Text & """,""LAST"")"
ActiveCell.Offset(0, 14).Formula = "=XLQ(" & Me.txtCode.Text & ", "LAST")"

However, thanks a lot for the info concerning the comma!

Best regards from Tansania

[emailing]

Sorry, the second one must of course be

ActiveCell.Offset(0, 14).Formula = "=XLQ(""" & Me.txtCode.Text & """,""LAST"")"

[Bob Phillips]

Best regards from Tansania

Do you mean Tanzania?

Is the semi-colon form of Excel standard out there? I thought it was only on the continent.

[emailing]

Of course, Tanzania. "Tansania" with "s" is the German way to write it. I am living and working here in East-Africa since more than six years, but originally I am German. That's why some of my software is from there, others from here. And as my wife is from french-speaking neighbouring Rwanda, we have a mix of software from three languages from two continents.

Best regards!