Thread: Evaluate formula Thanks:  1 Post #5335844 (1) Likes: 0

1. Evaluate formula

Assume I have this data on my worksheet from cell A1 through to B6:

Code:
 1 a 2 b 3 c
If In cell D1 I type:

Code:
=MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)

and enter it "normally", I get #N/A.

However, if I enter it as an ARRAY formula, (using Ctrl+Shift+Enter), I get 1.

In VBA, if I write:

Code:
Dim a

a = Evaluate("=MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)")

a returns 1.

So my question is:

1. Does using Evaluate ALWAYS execute the formula as an array, IF it is an array formula?

2. What if I wanted it NOT to execute as an array formula but as a "normal" formula?

Thanks  Reply With Quote

2. Re: Evaluate formula

Hi,

Evaluate is a smart instruction ... it will evaluate both standard formulas ...as well as array formulas ...

Hope this will help  Reply With Quote

3. Re: Evaluate formula

Thanks but how smart is it?

If a formula returns different values depending how it is entered, how will Evaluate know which one you want?  Reply With Quote

4. Re: Evaluate formula

Sorry ... but not sure to understand your question ...

Obviously ... if you are dealing with changing values ...they should be stored in a variable ...as usual ...  Reply With Quote

5. Re: Evaluate formula Originally Posted by James006 Sorry ... but not sure to understand your question ...

Obviously ... if you are dealing with changing values ...they should be stored in a variable ...as usual ...
I mean this formula returns two different results:

Code:
=MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)

If you entered it "normally", it will return #N/A.

But if it was entered as an array, it will return 1.

So what will this return?

Code:
Dim a a = Evaluate("=MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)")

Will it return as if it was entered "normally" ie #N/A or 1?  Reply With Quote

6. Re: Evaluate formula

If arrays are involved, the Evaluate function will process the formula as if it were array-entered. There is a proviso for this though... if the function involved cannot process arrays naturally (text functions for example), then array processing will need to be "induced" (probably the wrong term for what is going on, but it works for me) by embedding the function in another function that can process arrays (the IF function for example). For example, put these values in the indicated cells...

A1: 12A
A2: 34B
A3: 56C

Now execute this code line in the Immediate Window...
Code:
Range("B1:B3") = Evaluate("LEFT(A1:A3,2)")
Doing so will put all 12's in cells B1:B3 because, even though one of its arguments contains an array, the LEFT function cannot process arrays naturally. However, if you wrap the LEFT function inside of an IF function that processes an array, it will work as expected...
Code:
Range("B1:B3") = Evaluate("IF(A1:A3="""","""",LEFT(A1:A3,2))")
This outputs 12 in cell B1, 34 in cell B2 and 56 in cell B3 as one would want.  Reply With Quote

7. Re: Evaluate formula Originally Posted by Rick Rothstein If arrays are involved, the Evaluate function will process the formula as if it were array-entered. There is a proviso for this though... if the function involved cannot process arrays naturally (text functions for example), then array processing will need to be "induced" (probably the wrong term for what is going on, but it works for me) by embedding the function in another function that can process arrays (the IF function for example). For example, put these values in the indicated cells...

A1: 12A
A2: 34B
A3: 56C

Now execute this code line in the Immediate Window...
Code:
Range("B1:B3") = Evaluate("LEFT(A1:A3,2)")
Doing so will put all 12's in cells B1:B3 because, even though one of its arguments contains an array, the LEFT function cannot process arrays naturally. However, if you wrap the LEFT function inside of an IF function that processes an array, it will work as expected...
Code:
Range("B1:B3") = Evaluate("IF(A1:A3="""","""",LEFT(A1:A3,2))")
This outputs 12 in cell B1, 34 in cell B2 and 56 in cell B3 as one would want.

Code:
Code:
Range("B1:B3") = Evaluate("LEFT(A1:A3,2)")

When I did this, I got 12,34,56 in cells B1 to B3.

FYI, I am using Excel 365.  Reply With Quote

8. Re: Evaluate formula

Xl365 now processes Evaluate functions differently so you do not need to use the If function.
But if you do not use the IF function it will not work for people using 2016 or earlier (not sure about 2019)  Reply With Quote

9. Re: Evaluate formula

Thanks  Reply With Quote

10. Re: Evaluate formula

You're welcome  Reply With Quote

User Tag List

Tags for this Thread

array, enter, evaluate, execute, formula  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•