Using VBA to populate cells with formulas containing user-defined functions

Hi,

As per title, I'm trying to populate some formulas from VBA but having problems. This is the UDF and populating sub:

Function GetDateIfValid(DateCell As Range)
On Error GoTo CleanExit
If IsDate(DateCell.Value) And DateCell.Value <= Date And DateCell.Offset(-1, 1) <> "D" Then
    GetDateIfValid = DateCell.Value
    Exit Function
End If

CleanExit:
GetDateIfValid = ""
Application.Calculate
End Function

Sub populateDatesFormulae()
Dim TC As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In Range("A2:A60")
    TC = Cells(2, (cell.Row - 1) * 3).Address
    cell.Formula = "=GetDateIfValid(INDIRECT(""'Employee Tracking'!" & TC & """))"
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

When I run it I just get the hourglass until I hit escape. It puts the formula into the first cell correctly but it has a '#VALUE!' error and does not continue with the other cells. If I then hit 'calculate' the formula works fine.

edit - I've now found that if I leave the calculation as automatic it will populate all the cells but still hangs afterwards.

Been messing around for a while with this so any suggestions would be gratefully received.

Thanks,

Chinchin

(deleted because it was wrong)

Do you need to use INDIRECT in the formula?

Option Explicit

Function GetDateIfValid(DateCell As Range)
    If IsDate(DateCell.Value) And DateCell.Value <= Date And DateCell.Offset(-1, 1) <> "D" Then
        GetDateIfValid = DateCell.Value
    Else
        GetDateIfValid = ""
    End If
End Function

Sub PopulateDatesFormulae()
    Dim TC As String
    Dim Cell As Range
    
    On Error GoTo ResetApplication
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

    For Each Cell In Range("A2:A60")
        TC = Cells(2, (Cell.Row - 1) * 3).Address
        Cell.Formula = "=GetDateIfValid('Employee Tracking'!" & TC & ")"
    Next Cell
    
ResetApplication:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

In this case the formula will not change when dragged it is inserted as an absolute reference by the code
e.g.

=GetDateIfValid('Employee Tracking'!$C$2)

By using the $ "anchors" you can decide whether the formula changes or not as it is dragged.

INDIRECT() is a volatile function and will recalculate every time your workbook calculates, this can be very memory intense, so it is best avoided if possible.

Best you protect the sheet to avoid users dragging formula at will.

Try the code I posted in Post #5

It will recalculate the volatile functions every time the workbook changes and recalculates anything, regardless if the formula is on the active worksheet or not.
See this link
... Volatile and Non-Volatile Functions

By locking/unlocking cells then protecting the sheet, you can decide what cells the user has access to. You can allow the user to edit/enter data in a cell but not be able to drag it.
See this link
Lock or unlock specific areas of a protected worksheet

Avoid using the volatile formulae where-ever possible, certainly never use them to "protect" formula.

Please find attached.

The 'formulas' table needs to always show the values form the corresponding position in the data entry table regardless of what the user does (for example, try dragging the '1' to another cell - the formulas using INDIRECT show what needs to happen).

Preventing the user from dragging would be ok too, but the way the INDIRECT formulas work is preferable since dragging can speed up the data entry and users are used to it being available.

Thanks,

Chinchin

Looks like Named Ranges and/or Sheet Protection might be your best options.

1/. Create a Named Range for the Data Entry Table
Name:= DataEntryForm
Refers To :=

=Sheet1!$B$3:$D$7

2/. In G3

=INDEX(DataEntryForm,ROW($A1),COLUMN(A$1))

Drag Across to I3 Then Down to Row 7

3/. Protect the sheet but allow manual entry to the Data Table.
Select B3:D7
Format > Format Cells ...
In the Protection Tab Uncheck Locked > Press Okay

Format > Protection:=Protect Sheet
You can enter a password and select the desired options, but for this exercise just Press Okay

Try what you need to do with the Data Table.

Now unprotect the sheet
Format > Protection:=Unprotect Sheet

Select B3:D7 and drag the whole table somewhere else on the sheet.
The new formula should still display correctly, but the Indirect method will fail.

This is just to illustrate what is possible to control, there are many more permutations that you can use.

Way to go buddy ...
Happy to have helped.