Application vs. Application.WorksheetFunction

dee101

Active Member
Joined
Aug 21, 2004
Messages
270
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Code:
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
Code:
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
Code:
If IsError(x) Then
    'Do Nothing
Else
    'Range("A1").Value = x
End If


Hope that helps.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
They differ in how errors are generated and can be handled:

Code:
    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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,726
Messages
5,638,016
Members
416,999
Latest member
smulttjukken

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top