MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question
Trouble with stockhistory command in workbook open event
Hi everyone,
I have been trying to get the stockhistory function to work with my workbook open event, but whenever the workbook opens, I get a '1004' error.…
www.mrexcel.com
Hi everyone,
I posted this somewhere else and have not received any feedback. I'm hoping someone here may be able to help. I have been trying to get the stockhistory function to work with my workbook open event, but whenever the workbook opens, I get a '1004' error. However when I run the routine manually after the workbook is open, it works fine. Any help would be great. Below is the code in the workbook open routine.
Code
Private Sub Workbook_Open()
Dim aStock As String
Dim aDate As Date
Dim aStock_Price As Variant
'this does not work on workbook open event, but works when run after open
aStock = "TSLA"
aDate = "11/4/2022"
aStock_Price = Application.WorksheetFunction.StockHistory(aStock, aDate, aDate, 0, 0, 1) 'code error here on workbook open event
MsgBox ("Test stockhistory function: " & aStock_Price(1))
'this seems to work on workbook open event
Sheets("Sheet1").Range("C3") = ""
Sheets("Sheet1").Range("C3").Formula = "=" & Sheets("Sheet1").Range("B3").Address(False, False) & ".Price"
MsgBox ("Test stock.price function on sheet1: " & Sheets("Sheet1").Range("C3"))
End SubThanks!