CODEWITHSUNDEEP
excelvbaexcel-formula
Marvin-wtt
Marvin-wtt

Reputation: 489

Excel persistent evaluate if condition is met

I am trying to evaluate an expression only if a certain condition is met. The reason I am doing this is to allow the user to "lock" a value so that changes to other variables in the formula have no effect anymore.
I tried using the function below which works great until I close the sheet and open it again.
I already tried to use an additional cell passed as parameter to copy the value to it when it's not locked and copy it back if so however excel does not allow other cell modifications within a function. Is there any way to achieve this functionality?

Function EvaluateIf(expression As String, condition As Boolean) As Variant
    Application.Volatile
    Dim myText As String
    Dim myVal As Variant
    If condition Then
        myVal = Application.Evaluate(expression)
    Else
        myText = Application.Caller.Text
        If IsNumeric(myText) Then
            myVal = Val(myText)
        Else
            myVal = myText
        End If
    End If
    EvaluateIf = myVal
End Function

EDIT1:
I need to apply this function onto multiple cells so I cannot hard code the cells

EDIT2:
I currently call the function like this in excel: =EvaluateIf(N$7*IF(ISBLANK(P$7);1;P$7)*IF(ISBLANK(R$7);1;R$7);NOT(V$7))

Upvotes: 0

Views: 174

Answers (1)

Tim Williams
Tim Williams

Reputation: 166456

Try this out - normal cautions apply to using this method to skirt around the restrictions applied to the use of UDF when called from a worksheet.

Function EvaluateIf(expression, condition As Boolean, backup As Range) As Variant
    Dim myText As String
    Dim myVal As Variant
    Dim bak
    
    bak = backup.Value
    If condition Then
        myVal = expression
        If myVal <> bak Then 'update cached value?
            Application.Evaluate "SetBackup(""" & backup.Parent.Name & """,""" & _
                                            backup.Address & """,""" & myVal & """)"
        End If
    Else
        myVal = bak
    End If
    EvaluateIf = myVal
End Function

Sub SetBackup(ws As String, addr As String, v)
    Application.Calculation = xlCalculationManual 'avoid infinite loop!
    ThisWorkbook.Sheets(ws).Range(addr).Value = v
    Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 1

Related Questions