Hi Tom,

Tom Ogilvy wrote:
From everything I have read, as far as speed of execution, Method 1
and Method 2 are equal and both are more efficient than Method 3.

I know this is an older thread, but I found something interesting when I was
testing the three methods set forth by Tom. I found that method 2 (using a
With) is quite a bit faster than method 3 (no With, no object reference),
but that method 1 (setting a reference to the object and utilizing the
object variable) is even faster than method 2 when the object variable is
explicitly typed (eg, declared as MSForms.TextBox as opposed to Object).
However, when method 1 uses As Object (late binding the textbox), I found
that it is the slowest of all other methods.

This is interesting to me because I was under the impression that a With
statement and setting a reference were pretty much equivalent behind the
scenes. But that does not seem to be the case, at least with ActiveX
controls on a Worksheet.

FWIW, using a With in conjunction with method 1 didn't speed things up at
all.

So I guess I'll use object variables when I'm accessing several methods of
an ActiveX control from a Worksheet. Has anyone done any testing to
corroborate these results? Has anyone tested this with native Excel objects
or with ActiveX controls on UserForms?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]