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

 

[Rick Rothstein]

I don't think LEN is considered a "text" function because it returns a numerical result and Excel can handle numerical arrays... I was thinking more of functions like MID, LEFT, and so on as ones that require the array "inducement" (as I have termed it). Usually when I am unsure, I try it first without the array "inducement" and if that does not work, then I add it.

 

[mse330]

I have tested the below code in my old laptop with Excel 2007 and it only gives me 8 for all rows based on my sample in post #1 which is the length of the word "Colorado" ... The reason I asked this question is in Excel 365 it will work either way so that's why I wanted to understand the logic as I can't refer back to older versions all the time to test

Sub test()

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

End Sub

 

[Rick Rothstein]

It appears the LEN function must be induced. This work correctly (assuming no blank cells in the range)...

.Offset(, 1) = Evaluate("IF({1},LEN(" & .Address & "))")

 

[mse330]

Yes Rick, the one with the If({1} works as expected in Excel 2007. I guess the question is which function/cases that must be "induced" to give the correct results as a rule of thumb. I don't know if this is documented anywhere ...

 

[Rick Rothstein]

As far as I know, it is not documented anywhere. Almost all of what I "know" (I am using that term quite loosely) about this was derived from dogged trial and error. As I said earlier, I attempt to do the Evaluate without the "inducement" and if I don't get the answer I am expecting, I then try it with the "inducement".

 

[mse330]

Ok, so I had some time today & I was experimenting in my old laptop with Excel 2007 and I have come up with the below conclusions (please comment if anything is incorrect) ... Hope this thread will be beneficial for others in the future

1. I have used most of the common non-array formulas & all of them will not work without converting the Evaluate to an array formula, hence using If({1} or other method
2. Direct simple calculations perform fine as expected without the need to convert the Evaluate to an array
3. Some of the functions will fail & not work using Evaluate in a range. The same functions will show a #VALUE! error even if used in Excel 365 sheet & will not make a dynamic array like other functions (cells of those functions highlighted in yellow below). If anyone knows why please enlighten us

Evaluate Test.xlsm
	A	B	C	D
1	Input	Input	Output	Formula
2		-100	100	=ABS(B2)
3		65	A	=CHAR(B3)
4		22-Jun-20	22	=DAY(B4)
5		22-Jun-20	22-Aug-20	=EDATE(B5,2)
6		22-Jun-20	31-Aug-20	=EOMONTH(B6,2)
7		11	12	=EVEN(B7)
8	test	Test	FALSE	=EXACT(B8,A8)
9		Test	2	=FIND("e",B9,1)
10		21:20	21	=HOUR(B10)
11		7	FALSE	=ISEVEN(B11)
12		a	FALSE	=ISBLANK(B12)
13		a	TRUE	=ISTEXT(B13)
14		Test	4	=LEN(B14)
15		Test	T	=LEFT(B15,1)
16		Test	test	=LOWER(B16)
17		Test	T	=MID(B17,1,1)
18		1224.543	1220	=MROUND(B18,10)
19	01-Jun-20	15-Jun-20	11	=NETWORKDAYS(A19,B19)
20		test	Test	=PROPER(B20)
21		Test	2	=SEARCH("E",B21,1)
22		0.83214	83%	=TEXT(B22,"##%")
23		This is a test	This is a test	=TRIM(B23)
24		22-Jun-20	2	=WEEKDAY(B24)
25		22-Jun-20	26	=WEEKNUM(B25)
Sheet1

Cell Formulas
Range	Formula
C2	C2	=ABS(B2)
D2:D25	D2	=FORMULATEXT(C2)
C3	C3	=CHAR(B3)
C4	C4	=DAY(B4)
C5	C5	=EDATE(B5,2)
C6	C6	=EOMONTH(B6,2)
C7	C7	=EVEN(B7)
C8	C8	=EXACT(B8,A8)
C9	C9	=FIND("e",B9,1)
C10	C10	=HOUR(B10)
C11	C11	=ISEVEN(B11)
C12	C12	=ISBLANK(B12)
C13	C13	=ISTEXT(B13)
C14	C14	=LEN(B14)
C15	C15	=LEFT(B15,1)
C16	C16	=LOWER(B16)
C17	C17	=MID(B17,1,1)
C18	C18	=MROUND(B18,10)
C19	C19	=NETWORKDAYS(A19,B19)
C20	C20	=PROPER(B20)
C21	C21	=SEARCH("E",B21,1)
C22	C22	=TEXT(B22,"##%")
C23	C23	=TRIM(B23)
C24	C24	=WEEKDAY(B24)
C25	C25	=WEEKNUM(B25)