MrExcelForMe309

[Ronaldj]

Today,

use of WorksheetFunction.StockHistory in VBA gives an error: Unable to get the StockHistory property of the WorkSheetFunction class.It was fine yesteday and my uses of it a month ago were good.
Is anyone else having this issue?
I repaired and then re-Installed Office - No help/change.
I am not located in the US.

 

[Anthony47]

That happens also when the formula you emulated by vba returns an error. So try using the function on the worksheet, with the same parametres used by the vba code, and check that you dont get an error.
In general most of the WorksheetFunctions may be invoked as Application properties, so try using

Dim myVariable as Variant
myVariable = Application.StockHistory(stock, start_date, [end_date])

With this syntax in case StockHistory returns an error it will not trigger a run time error but will be stored into the variable and you can test it before continuing your code; something like

myVariable = Application.StockHistory(Stock, start_date, [end_date])
If Not IsError(myVariable) Then
    '
    'your code to handle the returned history
    '
Else
    MsgBox ("StockHistory error with " & Stock)
End If

Try...

 

[Ronaldj]

Thanks for the reply
After much time spent, discovered that I had to re-enter my password to activate the license for Office365.
That cured the problem

 

[Ronaldj]

That happens also when the formula you emulated by vba returns an error. So try using the function on the worksheet, with the same parametres used by the vba code, and check that you dont get an error.
In general most of the WorksheetFunctions may be invoked as Application properties, so try using

Dim myVariable as Variant
myVariable = Application.StockHistory(stock, start_date, [end_date])

With this syntax in case StockHistory returns an error it will not trigger a run time error but will be stored into the variable and you can test it before continuing your code; something like

myVariable = Application.StockHistory(Stock, start_date, [end_date])
If Not IsError(myVariable) Then
    '
    'your code to handle the returned history
    '
Else
    MsgBox ("StockHistory error with " & Stock)
End If

Try...

Thanks for the reply.
I have tried putting the Formula into a cell as another approach and found that I had slow response and the @Busy error showing, but I have never had that happen when using the VBA code approach you suggest. Most recently I have changed to the following

Set TabWorkSheet = Worksheets(TabName)
        With TabWorkSheet
            .Range(.Cells(1, SheetStkDataCol + 1), .Cells(MaxStkDataRows, SheetStkDataCol + 6)).Clear
            .Range(.Cells(1, SheetStkDataCol + 1), .Cells(MaxStkDataRows, SheetStkDataCol + 6)) = _
                WorksheetFunction.StockHistory(TabName, PeriodStartDate, PeriodEndDate, 0, 1, 0, 5, 2, 3, 4, 1)
            .Range(.Cells(1, SheetStkDataCol + 1), .Cells(MaxStkDataRows, SheetStkDataCol + 6)).Replace "#N/A", ""
            StkDataRows = .Cells(MaxStkDataRows, SheetStkDataCol + 1).End(xlUp).Row
            StkDataCols = 6 'Cells(2, Columns.Count).End(xlToLeft).Column

This puts the data directly into the cells of the worksheet. No memory consumed by a variable in the VBA code.
I have not had the @Busy issue with this. The columns of the spreadsheet data need to be formatted.
The issue I do have is that after the StockHistory function executes, attempting to save gives an error

Typically I get a longer error message, but this morning only this.

MIcrosoft documentation I have seen doesn't tell what the return data type is.
I think this Function is somehow special because there is description about data being automatically updated upon opening the Workbook.
Maybe it isn't intended to be used from VBA?