UDF not calculated in VBA code

Question

I have a UDF that returns a text depending on time and date of each row.

This UDF-formula is placed in X2, and when I run a data update code that replaces all data in the sheet i use filldown on X2:Y & LastRow.
My plan was to filter the data after the UDF is done and delete unwanted data.
Because Excel recalculates the data when you filter, I thought I could copy -> paste values to make sure the UDF does not run again.
The full code is below, but the "interesting" part is in between the ''''''.

Sub importera()

    Dim mainWB As Workbook
    Dim srcWB As Workbook
    Set mainWB = ThisWorkbook

    'Application.ScreenUpdating = False

    Sheets("XCFIL").Activate
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A3:Y" & LastRow).ClearContents
    Range("A2:W2").ClearContents


    Workbooks.Open Filename:="C:\Textfiler\XCFIL.TXT"
    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True


    Set srcWB = ActiveWorkbook



    ActiveWorkbook.Sheets(1).Range("A2:W" & ActiveSheet.UsedRange.Rows.Count).Copy
    ThisWorkbook.Activate
    ActiveSheet.Paste Destination:=Worksheets("XCFIL").Range("A2")

    Application.DisplayAlerts = False
    srcWB.Close
    Application.DisplayAlerts = True

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("X2:Y" & LastRow).FillDown

    ' To make sure the UDF does not run again turn off calculations
    Application.Calculation = xlManual
    Range("X3:Y" & LastRow).Copy
    Range("X3").PasteSpecial xlPasteValues
    Application.Calculation = xlAutomatic
    ' Turn on calculations again when formulas is replaced with values

    ActiveSheet.Range("A1:Y" & LastRow).AutoFilter Field:=24, Criteria1:="0"
    With ActiveSheet.AutoFilter.Range
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    End With
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ThisWorkbook.Activate

    Worksheets("Resultat").PivotTables("Pivottabell3").PivotCache.Refresh
    Worksheets("Resultat").PivotTables("Pivottabell2").PivotCache.Refresh
    Worksheets("Resultat").PivotTables("Pivottabell1").PivotCache.Refresh

End Sub

The problem is the FillDown does not run the UDF on all cells.
After the code has done the FillDown the calculation starts counting in the statusbar (?) and when it reaches 100% it's not done yet. It has calculated less than 50% and the rest of the values are #VALUE! errors. (If I stop the data update code, the UDF runs and finnish correct with not Value errors)

But this means I need some way to detect when all UDF cells are done before I switch off the calculations and do the copy paste.
Any suggestions on how to change the code?

Answer 1

I would suggest you do the relevant calculation in your VBA code at the appropriate time, and then enter those results directly into your worksheet. No need to worry about UDF's completing. The coding should be both simpler and faster.