On 2002-03-28 00:25, Dave Hawley wrote:
Ivan and Juan
Can't say I agree. While most VBA UDF's will simplify a function greatly for the user, it would be very unlikely that a worksheetfunction equivalent would be as slow, even if nesting is needed. It may well look a lot shorter and be much more user friendly but it is rare that it would be as efficient. Excels standard Worksheet functions are not bounded by the same rules as we are when using Excel VBA to write UDF's. There are of course occasions that a UDF is the only option. IMO a well written UDF will incorporate the use of one or more WorsheetFunctions, but as soon as you start putting in Loops etc you are well behind the eight ball.
I would be very interested to see some examples of a well written UDF being faster in calculating than a Worksheet Function equivalent.
Dave I agree on the worksheet functions...
I probably didn't explain properly.
The UDF I'm talking about would be used in
conjuction with a macro to populate
arrays and then pasted as a value only.
These are typically Functions that a formula
can't get eg.
and this is just one of many.
Get files in folder
Populate with File info.
I'd use a UDF to get the info
populate the cells etc......
eg.
UDF
Function ShowFileAccessInfo(sFileName As String)
Dim Fso, F, Info
Set Fso = CreateObject("Scripting.FileSystemObject")
Set F = Fso.GetFile(sFileName)
Info = UCase(sFileName) & " "
Info = Info & "Created:= " & F.DateCreated & " "
Info = Info & "Last Accessed:= " & F.DateLastAccessed & " "
Info = Info & "Last Modified:= " & F.DateLastModified
ShowFileAccessInfo = Info
End Function
Ivan
The Functions I use can't be done via typical
worksheet functions...and I wouldn't in most
cases use it, so I agree with you here.
As I said before It's using the right tools
and codes depending I what is required.