VBA EXCEL CODE：当前股票价格(VBA EXCEL CODE: Current Stock Price)

VBA EXCEL CODE：当前股票价格(VBA EXCEL CODE: Current Stock Price)

我是编码和编程的新手，非常感谢您在创建我的第一个真实代码时可以给我的任何建议。

我想要它做的是询问用户股票代码名称是什么，一旦输入，消息框应显示股票的当前价格。

我知道提取当前股票价格的公式。 例如：要查找Netflix的当前值，您只需在电子表格中将“NFLX”放在单元格（A1）和单元格（A2）中，然后输入以下公式： =NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

昨天在学习如何在VBA中定义变量时，我试图尝试这个概念并提出以下代码，这显然不起作用： -

Sub Declaring_Variables() Dim TickerName As String TickerName = InputBox("Ticker Symbol") Dim CurrentPrice As Integer CurrentPrice = Formula ="=NUMBERVALUE(WEBSERVICE("finance.yahoo.com/d/quotes.csv?s="&TickerName&"&f=l1")) MsgBox (CurrentPrice) End Sub

我确定问题在于我已经为CurrentPrice声明了公式。 如果您可以教我如何使用我们在VBA中的电子表格中使用的常规公式，我将非常感激。 我认为这将是一个很好的例子让我变得更好，因为它涵盖了一个非常重要的概念。

感谢您的时间。

此致，InWoods

I am new to coding and programing and would appreciate any suggestions you can give me in creating my first real code.

What I want it to do is ask the user what the Ticker Name is of the stock and once that is input, a message box should display the current price of the stock.

I know the formula to extract the current stock price. For Example: to find the current value of Netflix all you have to do in a spreadsheet is put "NFLX" in cell(A1) and in cell(A2) put the following formula: =NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))

Yesterday while learning how to define variables in VBA I was trying to experiment with this concept and came up with the following code which obviously doesn't work:-

Sub Declaring_Variables() Dim TickerName As String TickerName = InputBox("Ticker Symbol") Dim CurrentPrice As Integer CurrentPrice = Formula ="=NUMBERVALUE(WEBSERVICE("finance.yahoo.com/d/quotes.csv?s="&TickerName&"&f=l1")) MsgBox (CurrentPrice) End Sub

I am sure the problem is with the way I have declared the formula for CurrentPrice. I would really appreciate if you can teach me how to use normal formulas which we use on spreadsheet in VBA. I think this will be a good example for me to get better as it covers a very important concept.

Thank you for your time.

Regards, InWoods

最满意答案

尝试一下：

Option Explicit Sub Declaring_Variables() Dim TickerName As String TickerName = InputBox("Ticker Symbol") Dim CurrentPrice As String CurrentPrice = Application.WebService("finance.yahoo.com/d/quotes.csv?s=" & TickerName & "&f=l1") MsgBox "Text Format: " & CurrentPrice & Chr(10) & _ "Number Format: " & Application.NumberValue(CurrentPrice) End Sub

Give this a try:

Option Explicit Sub Declaring_Variables() Dim TickerName As String TickerName = InputBox("Ticker Symbol") Dim CurrentPrice As String CurrentPrice = Application.WebService("finance.yahoo.com/d/quotes.csv?s=" & TickerName & "&f=l1") MsgBox "Text Format: " & CurrentPrice & Chr(10) & _ "Number Format: " & Application.NumberValue(CurrentPrice) End Sub