Excel 2013 VBA Evaluate Function RRS feed

  • Question

  • Hi,

    I am using evaluate array function in excel 2013 with below Syntax.


    In excel it's working fine but if I am using same formula in Excel VBA , it gives me same repeated value in all the cells.

    r = Evaluate("=MAX(IF(DPR!B:B=DPR!B2, DPR!F:F))")

    Any Idea ...

    Wednesday, December 21, 2016 4:22 AM

All replies

  • Hi

    Probably need to let us know a little more about the surrounding function call and how you are calling it from the worksheet:

    1) Is r declared as a variant? (e.g: Dim r as variant) and are you handling r as though it is an array in whatever routine the above statement occurs?

    2) Are you using this line is a function? Does this function return a type variant? e.g.
     Public Function MyMaxFunctin() as variant? If it is anything other than an array and you have used the function as an array call, you will just get the same value repeatedly.

    3) You are calling the function in it's own cell and it is not entered as an array but as individual cells.

    e.g. Formula in A2 is =MyFunction() and A3 =MyFunction() instead of seeing the formulas in A2 & A3 as {=MyFunction()}


    Wednesday, December 21, 2016 6:08 AM
  • The Array formula checks if any Cell of B column equals B2, then returns corresponding cells from F column. MAX takes the result cells and returns the maximum value of the passed cells.

    It will be always same value as the argument of MAx is always same.

    Check if you need to change B2 cell address for all cells.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, December 21, 2016 9:47 AM

    I agree with the suggestion given by Asadulla Javed. so if that suggestion solves your issue then mark it as an answer.

    also I want to suggest you that if evaluating the function in VBA is not necessary then you can try to write code for if else and finding the max value from the column.

    it will help you to understand the things easily.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, December 22, 2016 1:13 AM