Thanks Gary for all your help and excellent comments.

With of all of your and Claus' excellent feedback, I figured how to do what
I want (see solution 6) by modifying solution1 a little bit.

Following your good advice on best practices to make the table names unique.
I re-named:
- The table "DY"
- Column A - "D" - column A is the column with dates
- Column B - "Y" - column B is the column for the years

Sub solution6()

' Purpose: write the year of each date in column A in column B

Dim co As Integer
' column offset variable
co = Range("Dy[Y]").Column - Range("Dy[D]").Column ' calculate the
offset between date & year columns

With Range("dy[D]") ' changed from With Range("a2",
Range("a" & Rows.Count).End(xlUp))
.Offset(, co).Value = _
Evaluate("if(" & .Address & "<"""",Year(" & .Address & "),"""")")
End With

End Sub

Have a Happy New Year and thanks again very much for all your help.
Bob

"GS" wrote in message ...

On my computer:

- Solution3 took 1.70873559481424 seconds to process 100,000 dates
- Solution2 took 3.29125182459939 seconds to process 100,000 dates
- Solution1 took 1.53838717548297 seconds to process 100,000 dates

If you repeat your tests you'll find that the times will change. Given
the number of rows, .2 secs diff is insignificant in terms of
performance.

Since Solution1 is slightly faster than Solution3 I think that there is a
real performance benefit in using the Evaluate method to write formula
derived values to a range.

Not entirely true, depending on the complexity of the formula! Fewer
worksheet processes will usually almost always be faster than VBA
processes.

I wish I could figure out (or someone could show me) how to express
Solution1 in terms of structured table references. Since my dates are in a
Table and the columns A and B are named ranges, there has to be some way
to write a macro like Solution1 or Solution3 with structured table
reference terms such as Range("Dates[YY]").Value and
"=Year(Dates[dates])" as in Solution2.

I deliberately avoided doing so due to you deliberately creating a name
conflict that violates 'best practices'. Change your naming convention
so their is only unique names, then try running Solution2 with the new
refs.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion