Значение функции жесткого кода, если функция ложна в VBA

Question

Я хочу вернуть значение функции в виде специального значения вставки, если функция имеет значение false (например, жестко закодированный ответ). Кто-нибудь может мне помочь? Я думал, что было проще использовать функцию вместо sub, потому что я должен сделать это с довольно большим набором данных.

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

Спасибо за ваше время!

Answer 1

UDF не может напрямую изменять рабочий лист, он может только возвращать значение. Существуют обходные пути, которые инициируют выполнение кода вне самого вызова UDF.

Одним из таких является использование Evaluate вызвать код, который изменяет лист (другой - использовать события листа, как ответил Гэри)

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

В стандартный модуль введите:

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

и в области кода рабочей таблицы введите:

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

Когда условие "заморозить" является Истиной. функция сигнализирует макрос события. Макрос события видит сигнал и замораживает ячейку, содержащую функцию.