Excel VBA - how to stop UDF recalculating when workbook is opened?-CodePudding

I have a workbook which I use to value my investment portfolio. It includes UDFs which scrape data from some websites where Excel's stocks data type is not available. The UDFs work well but they take a little time to calculate. I would like for the UDFs to only run when I hit F9 but not when I open the workbook but I can't work out how to achieve this. Is there something like "If running as part of loading up the workbook then do this..."?

I have Calculation=Automatic and Application.Volatile(False).

Thank you for any suggestions.

I'm not sure it's necessary, here is one of the UDFs:

Function GetLSEPrice(ticker As String) As Double

Application.Volatile (False)

Dim driver As New ChromeDriver
Dim url As String
Dim y As Selenium.WebElement

url = "https://www.londonstockexchange.com/stock/" & _
      ticker & _
      "/united-kingdom/company-page"

driver.AddArgument "--headless"
driver.Get url
Set y = driver.FindElementByClass("price-tag")
GetLSEPrice = CDbl(y.Text)

End Function

CodePudding user response：

The following should do it;

Step 1) - Create a New Property in your Workbook - to count how many times the various UDFs are called

Step 2) - Add Code in the UDF's to Test that Counter

I've Added Code to the SheetSelectionChange Event (Just to help trigger the UDF's)

AS follows;

Public UDFCallCtr As Long

Private Sub Workbook_Open()
   ThisWorkbook.UDFCallCtr = 1  ' Not Really Reqd
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Application.CalculateFull
End Sub

This is the UDF I've tested it with

Public Function UDFTester() As String
   If ThisWorkbook.UDFCallCtr > 5 Then  ' Change 5 to any value that works for you
      ' Put you Code here
        UDFTester = "Time is now " & Now()
      ' Done
   Else
      UDFTester = "NOT READY YET"
      ThisWorkbook.UDFCallCtr = ThisWorkbook.UDFCallCtr   1
   End If
End Function

The following may help show where the code goes