Evaluate in VBA & If({1},

[mse330]

Hello all,

I am starting to use Evaluate in my VBA codes but looks like since I have office 365 & dynamic arrays, it does something for me that makes it work but it doesn't work for older office versions. I have seen some members here use If({1}, to overcome this issue I guess so is this a guaranteed method that should work in all cases ? Shall I just always use it to ensure that my code doesn't fail if used in older excel versions ? What does If({1}, means/signifies ?

I would appreciate your feedback & in case you have a good link to share, please do so

I have put the below example for illustration which makes not difference in my machine since I am using office 365

Sub test()

With Range("A2:A11")
  .Offset(, 1).Value = Evaluate("len(" & .Address & ")")
  .Offset(, 2).Value = Evaluate("if({1},len(" & .Address & "))")
End With

End Sub

Book1
	A	B	C
1	State	without If({1},	with If({1},
2	Colorado	8	8
3	New York	8	8
4	California	10	10
5	Washington	10	10
6	Texas	5	5
7	Ohio	4	4
8	Arizona	7	7
9	New Jersey	10	10
10	Pennsylvania	12	12
11	North Dakota	12	12
Sheet1

 

[Fluff]

Shall I just always use it to ensure that my code doesn't fail if used in older excel versions ?

Really depends on what formulae you use, but for your example, I would say Yes.
The IF forces the formula to look at a specific row, rather then the complete range & the {1} effectively returns TRUE, so you dont need a false part to the IF.

 

[mse330]

Thanks Fluff for your reply. So what does the curly bracket means here ? Does it convert the formula to array formula ? If the intention is to have a True then would If(1=1, work ?

 

[Fluff]

would If(1=1, work ?

Have you tried it?

 

[Rick Rothstein]

My understanding (probably "guess" is a better word) of it is this. Some functions in Excel (notably text functions) are not natively array-aware, however, if included within a function that is array-aware, that function induces (not sure of the actual word that would apply) the non-array-aware function to become array-aware. The IF function is the array-aware function that does that. The {1} is just an array that is given to the IF function to force it to use its array processing functionality. You would use {1} only if the range of cells being processed has no blanks (otherwise the blanks would become zeros in the output). If you have blanks, the IF structure you should use is IF(range="""","""",formula) instead of IF({1},formula).

 

[mse330]

Have you tried it?

I just did & I am getting the same results but as I said, I have office 365 so perhaps I need to test it in older version

 

[shaowu459]

IF(1,A1:A6) will return a reference of A1:A6, IF({1},A1:A6) will return an array contains the values of A1:A6 which is ={"A";1;"B";2;"C";3}.
See below example, use N function for IF(1,A1:A6) and IF({1},A1:A6) will return different result. N(reference) will get only the top left cell A1 and change it to 0, N(value array) will change each text item of the array to 0.

工作簿3
	A	B	C	D
1	A		0	0
2	1		1
3	B		0
4	2		2
5	C		0
6	3		3
Sheet1

Cell Formulas
Range	Formula
C1:C6	C1	=N(IF({1},A1:A6))
D1	D1	=N(IF(1,A1:A6))
Dynamic array formulas.

 

[mse330]

Thanks @Rick Rothstein for the explanation. You are right, I just deleted one of the cells then I got 0 ... I tired the below code & works as expected but would it work correctly in older version of office without the If({1} ?

Sub test()

With Range("A2:A11")
  .Offset(, 1).Value = Evaluate("len(" & .Address & ")")
  .Offset(, 2).Value = Evaluate("if({1},len(" & .Address & "))")
  .Offset(, 3).Value = Evaluate("if(" & .Address & "="""","""",len(" & .Address & "))")
End With

End Sub

 

[mse330]

Thank you @shaowu459 ... I have tried your formulas & I think it makes more sense now. I just need to get my hands on older excel version to try out different scenarios for better understanding. With office 365, it creates a dynamic array once I pasted your formula in cell C1 as shown below whereas it doesn't in cell D1

Thank you all for your time to explain

 

[mse330]

I tired the below code & works as expected but would it work correctly in older version of office without the If({1} ?

.Offset(, 3).Value = Evaluate("if(" & .Address & "="""","""",len(" & .Address & "))")

In case anyone finds this thread later on, I just tried the option suggested by Rick & it works as expected in Excel 2007 without the If({1},