Application vs. Application.WorksheetFunction

[dee101]

I have seen both used in code when using a worksheet function in VBA, is there any difference in them?? The 2 below look like they do the same thing

?Application.Trim(" Apples Oranges ")
Apples Oranges


?Application.WorksheetFunction.Trim(" Apples Oranges ")
Apples Oranges

 

[Smitty]

One of the differences is built-in VBA functions vs. Excel worksheet functions. For instance there's no Text function in VBA, but there is a FORMAT function in VBA, which does the same thing.

Here's a list of the VBA functions: http://msdn.microsoft.com/en-us/library/gg278941.aspx

You can look in the VBA helpfile for the list of worksheet functions.

HTH,

 

[Andrew Poulsom]

They do the same thing but may behave differently. See:

http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

 

[Jonmo1]

2 main differences...

1. You loose intellisence (the popup window with syntax for function) when you just use application.vlookup.


2. Normally, when you use Application.WorksheetFunction.Vlookup(...)
With functions like Vlookup that may result in an error, the code actually stops and goes into debugging mode.

If you drop the worksheetfunction and just use Application.Vlookup(...)
Then the code does not actually stop and go into debug mode.
It just returns the error value to a variable.

Example

Range("A1").Value = Application.WorksheetFunction.Vlookup(.....)

If the Vlookup doesn't find a match (#N/A when written in a cell)
Then your code stops running and you get the debug window.

if you do it like this

x = Application.Vlookup(.....)

It no longer stops with the debug.
Instead, the variable x is assigned the error value and code continues to run.

You can then text x for an error

If IsError(x) Then
    'Do Nothing
Else
    'Range("A1").Value = x
End If

Hope that helps.

 

[shg]

They differ in how errors are generated and can be handled:

    Dim v As Variant
 
    ' v is assigned Error 2042
    v = Application.Match(7, Array(1, 2, 3), 0)
    
    ' trappable error generated, no assignment to v
    v = Empty
    On Error Resume Next
    v = WorksheetFunction.Match(7, Array(1, 2, 3), 0)
    Debug.Print Err.Number
    On Error GoTo 0
    
    ' run-time error, no assignment to v
    v = WorksheetFunction.Match(7, Array(1, 2, 3), 0)

 

[dee101]

Thanks, that clears it up