Can you, or anyone reading this thread, give me an idea of the "penalty"
incurred, if any, by a macro when it reaches out to the spreadsheet world
in
order to execute one of its functions via the
Application.WorksheetFunction
property?

Rick


Worksheet functions, whilst extremely efficient in cells, are slow when
called in VBA. It's often quicker to recreate the worksheet function.
Although in another part of this thread I suggested
Application.Worksheetfunction.Trim(
personally I wouldn't use it in in anything time sensitive, say as a UDF in
a large number of cells or as part of a long loop. Other ways to replace
multiple spaces with singles besides RegExp.

If using multiple worksheet functions in the same loop use 'wf' as in the
following

Sub foo()
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
s = wf.Trim(" a b c ")
MsgBox s
End Sub

Even setting a ref to WorksheetFunction object only speeds up a little.

Also I wouldn't use RegExp in a UDF and probably not in a function that was
called in a much larger loop. Although RegExp is extremely efficient,
creating the object is (relatively) slow. IOW, great in macro or function
that receives an array to process, but don't send each element to a separate
function that does CreateObject("vbscript.regexp"). I suppose could store
the object at module level.

Regards,
Peter T