Hi,
Evaluate is a smart instruction ... it will evaluate both standard formulas ...as well as array formulas ...
Hope this will help
Assume I have this data on my worksheet from cell A1 through to B6:
If In cell D1 I type:Code:
1 a 2 b 3 c
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
Hi,
Evaluate is a smart instruction ... it will evaluate both standard formulas ...as well as array formulas ...
Hope this will help
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?
Last edited by tiredofit; Aug 28th, 2019 at 08:25 AM.
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?
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...
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("LEFT(A1:A3,2)")
This outputs 12 in cell B1, 34 in cell B2 and 56 in cell B3 as one would want.Code:Range("B1:B3") = Evaluate("IF(A1:A3="""","""",LEFT(A1:A3,2))")
Last edited by Rick Rothstein; Sep 2nd, 2019 at 04:40 AM.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
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)
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
Thanks
You're welcome
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
Like this thread? Share it with others