Evaluate formula

[tiredofit]

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

[TABLE="width: 128"]
 <colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
  [TD="width: 64, align: right"]1     [/TD]
  [TD="width: 64"]a[/TD]
 [/TR]
 [TR]
  [TD="align: right"]2     [/TD]
  [TD]b[/TD]
 [/TR]
 [TR]
  [TD="align: right"]3     [/TD]
  [TD]c[/TD]
 [/TR]
</tbody>[/TABLE]

If In cell D1 I type:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=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:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]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



[/FONT]


[/FONT]

 

[James006]

Hi,

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

Hope this will help

 

[tiredofit]

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?

 

[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 ...

 

[tiredofit]

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:

=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?

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?

​

_{^{<strike>
</strike>}}​

 

[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...

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...

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.

 

[tiredofit]

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...

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...

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.

Thanks for your reply.

	
	
	
	
	
	



	
	

		
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.


​

 

[Fluff]

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)

 

[tiredofit]

Thanks

 

[Fluff]

You're welcome