VBA Evaluate Excel Function-CodePudding

I am trying to use "conditional" Large function in my code e.g. to use Large function for values where in the other column there is "Y".

I am using "Evaluate" function as I need only the results in the other part of the code. However, this is not working - I understand that I need to work with Formula2 because otherwise excel will add '@' to the function and it wont work. But still I dont know how to 'repair' evaluate function.

I am using R1C1 formula because later I want to use columns in the loop.

Sub Makro()
    'not working - there is '@' included
    Range("G3") = "=Large(if(c[-4]:c[-4]=""Y"", c[-3]),2)"
    'working
    Range("G4").Formula2 = "=Large(if(c[-4]:c[-4]=""Y"", c[-3]),2)"
    
    'not working
    Range("G5") = Evaluate("=Large(if(c[-4]:c[-4]=""Y"", c[-3]),2)")
End Sub

CodePudding user response：

Using Evaluate in a Function

Sub EvaluateStuffTEST()
    Debug.Print EvaluateStuff("D", Sheet1) ' code name
    Debug.Print EvaluateStuff("E", ThisWorkbook.Worksheets("Sheet1")) ' tab name
    Debug.Print EvaluateStuff("F") ' ActiveSheet
End Sub

Function EvaluateStuff( _
    ByVal ColumnString As String, _
    Optional ByVal SourceWorksheet As Worksheet = Nothing) _
As Variant
    If IsMissing(SourceWorksheet) Then Set SourceWorksheet = ActiveSheet
    EvaluateStuff = SourceWorksheet.Evaluate( _
        "=Large(if(C:C=""Y""," & ColumnString & ":" & ColumnString & "),2)")
End Function