VBA Evaluate Excel Function

Question

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

Btw, it would probably be more appropriate to use .Formula2R1C1 instead of .Formula2. — BigBen
– BigBen, Commented Mar 20, 2022 at 21:20
Does this ActiveSheet.Range("G4").Value = ActiveSheet.Evaluate("LARGE(IF(C:C=""Y"",D:D),2)") work? — VBasic2008
– VBasic2008, Commented Mar 20, 2022 at 21:34
Yes,, Evaluate("=Large(if(C:C=""Y"", D:D),2)") is working for me. I understand that I need to work with funtion in that form (A1 instead of R1C1). What is the best solution to re-write my function to adjust for the loop? Evaluate("=Large(if(C:C=""Y"", D:D),2)"), Evaluate("=Large(if(C:C=""Y"", E:E),2)"), Evaluate("=Large(if(C:C=""Y"", F:F),2)"), ...., Evaluate("=Large(if(C:C=""Y"", Z:Z),2)") — Lohengrin
– Lohengrin, Commented Mar 21, 2022 at 9:42
You need to share on which range you mean to apply it: G4:AC4 or G4:G27 or...? In the same worksheet? Also, you may want to restrict the columns to e.g. D5:DLastRow to not severely slow down your workbook. — VBasic2008
– VBasic2008, Commented Mar 21, 2022 at 10:00

VBasic2008 · Accepted Answer · 2022-03-21 11:51:21Z

2

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

answered Mar 21, 2022 at 11:51

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lohengrin Over a year ago

Thanks a lot - it is working as expected. Quick question: is it possible to write a loop in EvaluateStuffTEST()? Let's say we have variable columns D, E, F, ...., ZZ?

VBasic2008 Over a year ago

It is possible (but it is kind of natural to have it this way). Then the result could be e.g. an array or a dictionary. Then again, you would need to loop it in another procedure. So you wouldn't gain much. It would make more sense if you would want to sum up those values. If you could explain the idea behind it maybe I could come up with a more accurate conclusion.

Collectives™ on Stack Overflow

VBA Evaluate Excel Function

1 Answer 1

Using Evaluate in a Function

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using Evaluate in a Function

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related