CODEWITHSUNDEEP
excelvbauser-defined-functions
Blitzer
Blitzer

Reputation: 115

How to stop UDF recalculating when workbook is opened?

I have a workbook 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 take time to calculate.

I would like the UDFs to run when I hit F9 but not when I open the workbook.
Is there something like "If running as part of loading up the workbook then do this"?

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

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

Upvotes: 0

Views: 192

Answers (1)

Tin Bum
Tin Bum

Reputation: 1491

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

enter image description here

Upvotes: 1

Related Questions