VBA - Evaluate throwing error when [] form used

[Johnny C]

I've been idling a dull afternoon away tidying up some VBA that works on ranges using the Evaluate function. More out of curiosity than anything else. I'm keen to understand it, I once came across a piece of horrible spaghetti code that used [Sum(Indirect(formula))] and I just gave up at that point and rewrote it but it would be nice to be able to decipher it.

This works, it trims a whole range of text without a loop:

Sub TrimData()
Set Rng = Selection
Rng.Value = Evaluate("if(row(),Trim(" & Rng.Address & "))")
End Sub

but when I tried to use the [] form it returns a #NAME ! error

Sub TrimData2()
Set Rng = Selection
Rng.Value = ["if(row(),Trim(" & Rng.Address & "))"]
End Sub

Debug.Print shows it's giving an error 2029, the same if I remove the quotes
Rng.Value = [if(row(),Trim(Rng.Address))]

I'm guessing I've not got the hang of the syntax of the [] form, what should I put?

 

[steve the fish]

I dont believe you can use the square brackets with the variable. This for instance works fine:

Range("A1:A10") = [IF(ROW(),TRIM(A1:A10))]

 

[Johnny C]

Cheers. I wouldn't use them myself, just seeing how they work.

https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/

has some interesting tips on using Evaluate efficiently

 

[Rick Rothstein]

I dont believe you can use the square brackets with the variable.

That is correct... the argument to the square brackets is not a text string, so you cannot concatenate variables into it. As a matter of fact, you cannot put anything that requires VBA to evaluate it or parts of it between the square brackets. The square brackets give you a method to evaluate an Excel formula (with some limitations though) within the VBA world and have the evaluated value or array returned to the running code.

 

[Johnny C]

That would explain why the original

[Sum(Indirect([I]formula[/I]))]

threw an error, I guess the author didn't know that and was unable to stop it erroring.