= positioning

[snax500]

Why does it matter where you put the "=" in a macro (see line 3). I am range
valuing cells that contain the "If" formula. In the following code:

For Each cll In Range("v10:v44")
If Left(cll.Formula, 3) = "=IF" Then
cll.Value = cll
End If
Next

why does this range value my cells but this next code does not:

For Each cll In Range("v10:v44")
If Left(cll.Formula, 3) = "=IF" Then
cll = cll.Value
End If
Next

Why is cll.Value=cll not the same as cll=cll.Value

Thanks

 

[Juan Pablo González]

The problem arises when you don't declare cll, or in other words, when cll
is declared as a variant.

In the second case, VBA evaluates first

"cll.Value"

where cll is a Range object, and then assigns whatever its value is (a
Double, or a String for example) to a "new" cll variable, not the original
Range object.

In the first case, VBA evaluates first

cll

and since at this point, it is still a Range object, VBA uses the default
property, which is .Value, so that line is equivalent to

cll.Value = cll.Value

however, since you're explicitly calling a property of the object, VBA
doesn't create the "new" variable, but instead uses the same reference to
the Range, therefor changing the formulas into the values, as you would
expect.

Bottom line, always declare your variables, and put "Option Explicit" at the
top of your modules.