Hardcode function value if function is false in VBA

Question

I want to return the function value as a paste special value if the function is false (like a hard coded answer). Can anyone help me please? I thought it was easier to use function instead of sub, because i have to do this to a fairly big data set.

Function produktion(d2, sum1, sum2)

    Dim j As Integer
    j = Application.Sum(sum1) - Application.Sum(sum2)


    If d2 = Date Then

        produktion = j

    Else
        'here hardcode the produktion = j forever like the paste special value

Thank you for your time!

Answer 1

A UDF cannot directly modify a worksheet, it can only return a value. There are work arounds that trigger code to execute outside the UDF call itself.

One such is to use Evaluate to call the code that modifies the sheet (another is to use worksheet events as Gary answered)

Function produktion(d2, sum1, sum2)
    Dim j As Long
    j = Application.Sum(sum1) - Application.Sum(sum2)

    If d2 = Date Then
        produktion = j
    Else
        With Application.Caller
            'here hardcode the produktion = j forever like the paste special value
            .Worksheet.Evaluate "HardCodeValue(""" & .Address & """)"
        End With
    End If
End Function

Sub HardCodeValue(rng As String)
    With Range(rng)
        .Value = .Value
    End With
End Sub

Answer 2

In a standard module enter:

Public FreezeWho As String
Public FreezeNow As Boolean

Public Function produktion(d2 As Date, sum1 As Range, sum2 As Range) As Long
    Dim wf As WorksheetFunction, j As Long
    Set wf = Application.WorksheetFunction
    j = wf.Sum(sum1) - wf.Sum(sum2)
    produktion = j
    If d2 = Date Then
        FreezeWho = Application.Caller.Address
        FreezeNow = True
    End If
End Function

and in the worksheet code area enter:

Private Sub Worksheet_Calculate()
    If FreezeNow Then
        Range(FreezeWho).Value = Range(FreezeWho).Value
        FreezeNow = False
    End If
End Sub

When the "freeze" condition is True. the function signals the event macro. The event macro sees the signal and freezes the cell containing the function.