difference between WorksheetFunction and Application ?

Is the original post a quote and, if so, from where?

here: ID: 37648175

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27605124.html#a37647141

I'd be interested in that too as I have no idea what he's referring to there.

re-open duly requested.

p.s. while my op is not linked to the original question, the original thread IS linked to the op. I will make these follow ups bi-directional in future. Would be nice if we could have the apparently unused and then dropped "follow up" feature re-instated as I (would) use follow ups a lot. Getting a bunch of questions answered for one lot of points doesn't seem very fair to the experts. ant.

Thanks, Anthony.

I didn't realise that "follow up" was a casualty of V10. It's on the "Request for Change" list, but that's no guarantee. Your idea of making it bi-directional is a good one and it has been included in the request. Again, no guarantees!

No need to reopen the question. The selected post provides additional insight into the differences between Application and WorksheetFunction.

The problem with using WorksheetFunction is that it can occasionally result in the referenced function not being resolved at compile time. It's rare but I've seen it happen. I haven't used WorksheetFunction in years but my notes have this error:

unable to get the ... property of the WorksheetFunction class

Now, I never understood why this happened. It could have just been the function erroring out and VBA throwing up misleading messages. It could have been my use of ActiveX controls.

All I know is that it happened and when I switched to using the Application object I never experienced another problem.

Kevin

Thanks, Kevin.

I'm more than happy for you to get at least a share of this question as that kind of (otherwise poorly documented) experience is worth gold for the rest of us!

hello, this is interesting in the context of gold for the rest of us:

but my notes have this error:

are your notes published anywhere?  
And if not, perhaps you might consider the idea. Anthony

FWIW, that is exactly the message you get if the function errors, and that is the only time I've ever seen it.

Noted :-)

the comment I saw about "follow up" was that it was left on the cutting room floor because no one was using it - which was before I came along with my incessant follow ups.

"because no one was using it"
I think they missed the plot here. Obviously, only a small percentage of questions are follow ups. However, of those that are, my experience is that at least half of them used the "Related Solution" feature.

From what Kevin and Rory have said, it sounds as if WorksheetFunction is the way to go.

That's not my take on the above, and I've re-read it a few times now.
I guess that leaves me non plussed :-)

Anthony,

You are correct. After reading the various posts myself and about Rory's experience, my notes now read:

There are two ways to reference worksheet functions - via the WorksheetFunctions object and via the Application object. There are two differences between them. The WorksheetFunction method provides Intellisense but does not handle errors - a VB error is thrown when the function generates an error. The Application method does not provide Intellisense but does handle errors - an error value is returned if the function generates an error. If the function result is assigned to a Variant variable then error handling can be avoided as illustrated in the example below.

    Dim MatchResult As Variant
    MatchResult = Application.Match(Value, Array("A", "B", "C"), 0)
    If IsError(MatchResult) Then
        MsgBox "Match not found"
    Else
        MsgBox "Match found"
    End If

Or, more concisely:

    If Not IsError(Application.Match(Value, Array("A", "B", "C"), 0)) Then
        MsgBox "Match found"
    End If

Note that when using the WorksheetFunction method, function generated errors can generate the confusing error message:

    "Unable to get the ... property of the WorksheetFunction class."

Also note that even though Intellisense is enabled when using WorksheetFunction, the parameters are listed simply as Arg1, Arg2, etc.

In general, using the Application object to access worksheet functions results in tighter code (no error handling is required).

Kevin

Good summary. I always use the Application version, but I know that others, like Tushar, recommend the Worksheetfunction version.

"like Tushar"
Thanks, Rory, I was beginning to feel a bit lonely. For me, the two main reasons for using WorkSheetFunction are clarity and accuracy (the decimals issue).

Then use Application for all calls (less error handling code results in greater clarity) and WorksheetFunction for those specific cases where you need the accuracy.

Personally, I've never needed/used WorksheetFunction.

Kevin

I re-read the thread about the rounding issue and, as far as I can tell, it isn't even an issue. The problem documented has to do with VB's handling of the currency variable type - it uses the system's currency setting to round the value. Excel doesn't have a notion of a currency data type and so keeps the additional digits until such time as the value is manipulated. If you are working with the currency data type (I never use it - I always use double) and as long as you treat the currency values in a consistent manner, you will never have an issue. Note that the difference is how .5 is rounded - up or down - it's a matter of consistency, not accuracy.

Conclusion: use Application for better, tighter code.

Kevin

Thanks, Kevin. I'll agree to disagree!

Do you have any instances other than the currency type "issue"? Because if you don't, then you can't call this an accuracy issue. It's only a matter of how the decimal value is treated when it is exactly in the middle (0.005). There are different ways to treat that value which vary with positive and negative values - and the difference is never a matter of accuracy, it's only a matter of preference, especially when dealing with currencies. Remember that this situation ONLY occurs with the currency data type which you should not be using anyway,

When you have used WorksheetFunction for a bit and had to add the error handling to deal with function errors - which are very common and normal - then we'll see how you feel ;-)

For example, this:

    Dim MatchResult As Variant
    MatchResult = Application.Match(Value, Array("A", "B", "C"), 0)
    If IsError(MatchResult) Then
        MsgBox "Match not found"
    Else
        MsgBox "Match found"
    End If

Becomes:

    Dim MatchResult As Long
    Dim ErrNumber As Long
    On Error Resume Next
    MatchResult = Application.Match(Value, Array("A", "B", "C"), 0)
    ErrNumber = Err.Number
    On Error GoTo 0
    If ErrNumber <> 0 Then
        MsgBox "Match not found"
    Else
        MsgBox "Match found"
    End If

Kevin

From now on, I'm going to use worksheetfunction to ensure that function actually exists, then backspace and change to application and test for IsError, and if not do it another way or use EVALUATE, lol

:P

Kevin,

I'm not sure where this discussion is going. Yourself, Rory and Dave prefer not to use WorkSheetFunction. I prefer to use it. Anthony has a very clear message!

FWIW...

(1) Checking my Personal.Xlsb, I have 17 subs/functions which use WorkSheetFunction. Not one of them needs error handling. Absolutely this is not universal - the next time I use it, I may well need error handling. However, its use is unusual enough that I don't mind the cost of a few extra lines. "Clarity" for me means that when it's used, it sticks out like a sore thumb ("17").

(2) I have six subs using Currency. They're part of a high resolution timer. What type should I use instead?

Thanks,
Brian.

The discussion is not about moving anyone closer to using one technique or the other. It's about the reason why one method was chosen versus the other. Simply put, stating that using WorksheetFunction versus Application for accuracy reasons is an incorrect assumption/conclusion. There are no accuracy issues. What was witnessed in the referenced thread was a difference in how .005 is handled when rounding. We can have the same conversation about Int and Fix - which specifically treat .005 differently. Not for accuracy reasons, but for standards reasons. Both are valid, both are used.

As far as the currency data type, it's a VB thing that limits flexibility - I never use it. I always use double in VB and C# and float in SQL Server.

Kevin

As well move the discussion to using FileSystemObject to get a directory listing versus Dir.

I think at the end of the day its user preference, especially if appropriate error checking is done via either method on error vs IsError().  

My tangent on this, wrt Kevin's "tight code" comment, which in my opinion doesn't always mean less lines of code:

Personally, I've been trying to coerce myself when coding to not use Exit For's and Go To's and On Errors to "tighten" up my code and align with my original training & other languages I want to develop in that don't have this functionality or its not the norm.  I'm not there yet, but getting there and grimace when I'm not doing it.

Dave

Brian,
>>"I have 17 subs/functions which use WorkSheetFunction. Not one of them needs error handling."

What are these functions that could not possibly error? :)

Yes, I was kind of curious about that myself :-)

Re the coding, I'm a believer in using what the language gives you. I don't see any point trying to write VBA like C# and trying to code in VBA without On Error is something only a loon would attempt. Or Kevin. :)

Rory, Kevin.

Apologies, I hadn't seen your posts on "non error-handling".

Already cleaned data.

Regards,
Brian.

Kevin,

I happily accept your points about using Currency ... for accounting type calculations.

Regards,
Brian.

I was wanting to break free and come up to speed with C# in the near future, having coded my last line of C in 1990, which is why I wanted to tighten up.  Lately I've stopped using For/Exit For and I'm going - geez. (Dr. Z - one of the reasons my TypeLibe Information routine is 150 lines line is due to that :).

Instead of

on error goto 0
set wksOut = ThisWorkbook.Worksheets("Output") and then creating it if I got an error, I'm using a function like:

Function getWks(wkb,wksName)
then doing a do loop to find it, instead of exit the for each
end Function

then I could use:

set wksOut  = getWks(wkb,"Output")
if wksOut is Nothing 'create the worksheet...

rorya/zorvek - am  I overdoing it here?

Is Exit For bad coding?

Is using On Error and Go To bad coding?

Dave

No error handling:

Public Function IsExistingSheet( _
      ByVal SheetName As String _
   ) As Boolean

' Return True if the named sheet exists, False otherwise.
   
   Dim TargetSheet As Object
   
   For Each TargetSheet In ThisWorkbook.Sheets
      If UCase(TargetSheet.Name) = UCase(SheetName) Then
         DoesSheetExist = True
         Exit For
      End If
   Next TargetSheet
   
End Function

GoTo was generally considered bad form when structured programming became the rage about 30 years ago. Better coding practices encouraged the use of loops with breaks - always flowing downward. I've cheated a little now and then with constructs like:

    Dim Index As Long
    Do
        ...
        For Index = 1 To 10
            ...
            If Index = 5 Then Exit Do
        Next Index
    Loop While False

The adoption of object oriented programming further emphasized the benefits of structure code. These days GoTos are pretty much considered about the worst thing you can do - even if you always flow downward.

And, guess what, you have to use GoTos to roll your own VB error handling! And part of the override process is an implied GoTo back upstream! How ugly is that?

Kevin

While I was hacking in the mid 70's on my Apple II + with Apple basic and fortran, etc., shortly thereafter, I was trained in the structured code era - pre object oriented - but now it maps fairly easily thanks to me helping out on E-E.  But, I quickly adopted the Exit For.  That's about the only cheat I do (have done) as I've built my VBA competence (other than On Error handling).  I think I'll follow you and rorya's advise about using what the language offers one.

I guess you have to TAKE THE TIME to learn the error conditions you'd be facing with each command and if there is none use the On Error, and if there is you use that.  I assume that would be the best practice, tho I've been in the habit of trapping On Error when I just didn't know what to look for but releasing that to Goto 0 afterward.  Today, I find more and more methods that don't error and you have to investigate the results to determine if there was an error or not.

like the Application.FileDialog method, where you have to check if .Show = -1 to see if anything was selected.

@ Dave: I absolutely agree with Rory here. You have to use error handling. I cannot imagine my code without it. IMHO, there are 3 types of programming. "Aggressive/Passive/Proactive"

What Zorvek demonstrated above is Aggressive programming which is in fact very favorite among coders. I personally prefer proactive programming. There is no way you can think if every error that might occur on a user pc. So error handling is a must.

@Kevin: I would respectfully disagree with you here.

This

Public Function IsExistingSheet(ByVal SheetName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = Sheets(SheetName)
    On Error GoTo 0
    
    If Not ws Is Nothing Then IsExistingSheet = True
End Function

Select allOpen in new window

is definitely better than the below. The very fact that the above is faster. Please feel free to correct me if I am wrong. BTW there is a typo in your above code. DoesSheetExist should be IsExistingSheet.

Public Function IsExistingSheet(ByVal SheetName As String) As Boolean
    ' Return True if the named sheet exists, False otherwise.
   
    Dim TargetSheet As Object
   
    For Each TargetSheet In ThisWorkbook.Sheets
        If UCase(TargetSheet.Name) = UCase(SheetName) Then
            IsExistingSheet = True
            Exit For
        End If
    Next TargetSheet
End Function

Select allOpen in new window

HTH

Sid

There is nothing wrong with Exit statements in structured coding. All they do is break the loop and exit at the bottom - a perfectly legal flow. It's virtually impossible to avoid using Exit statements.

>I guess you have to...

Which is why I don't override the default error handling and let all the errors be found while real users are using the program. Without that phase the application will always be substandard with masked bugs. Most errors are simple coding mistakes - easily fixed when found. Some are not and have to be handled - a good set of proven libraries help a lot with this. Some simply require the use of error handling to catch problems and do something specific to address them. But these are rare.

Kevin

>>>Is Exit For bad coding? Is using On Error and Go To bad coding?

Nope. Like Kevin Rightly mentioned, It's virtually impossible to avoid using Exit statements.

I have already mentioned about "On Error". Kevin has beautifully covered "Go To". I echo the same thoughts.

There is one thing where I would like to draw your attention to and that is "Exit Sub". This can be bad programming if not used judiciously.

>There is one thing where I would like to draw your attention to and that is "Exit Sub". This can be bad programming if not used judiciously.

Exit Sub/Function is just as valid as Exit Loop/Do/While. A sub/function is just another structured construct. I use Exit Sub/Function often.

Kevin

Sid - how can exit sub be hazardous?

Anthony - I hope you are enjoying this ;)  Its certaining helping to form some first principles (and in my case challenge mine) which will be reinforced when you do apps that are significant in module/line count.

Dave

>>>>A sub/function is just another structured construct. I use Exit Sub/Function often.

Agreed but you missed my point Kevin... (@Dave: This will answer your question as well)

"This can be bad programming if not used judiciously."

Consider this example which is 'Bad Practice'.

Sub Sample()
    Dim oXLApp As Object, wb As Object, ws As Object
        
    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    
    '~~> If not found then create new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    '~~> Open files
    Set wb = oXLApp.Workbooks.Open("C:\Test01.xls")
    
    If IsExistingSheet("Sid") = False Then Exit Sub
    
    Set ws = Sheets("Sid")
    
    '
    '~~> Rest of the code
    '
End Sub

Select allOpen in new window

>>>>But, seriously, does anyone really give a shit how fast a routine runs to check if a sheet exists?

Yes there are many users who import data into new sheets in a master workbook almost every week for the entire year and rename them as per their requirements. There are scenarios where they need to format the data once it is imported and the formatting routine might take several minutes. In such a scenario the For Loop to check if the sheet exists will further slow down the code.

>>>Error handling such as what you have described above can significantly - and I mean significantly - slow down the VB runtime.

Again it depends in how an error handling is done. IMHO it never slows donw the code if done properly. In fact you can use it to your advantage. Imagine using ERL in Error handling can take most of the pain away ;)

>Consider this example which is 'Bad Practice'.

Why is that bad? You are exiting a structured construct flowing down and out the end of the construct.

>In such a scenario the For Loop to check if the sheet exists will further slow down the code.

Looping through a 50 sheets will not be noticeable. Put another way, the about of time to loop though 50 sheets versus inserting a new sheet is huge.

>Again it depends in how an error handling is done.

You have no idea what I am talking about. EVERY time you instruct VB to override the default error handling takes significant time in compiled code.

>Imagine using ERL in Error handling can take most of the pain away

Today, yes. And then when weird things start happening in production and you are displaying stupid but pretty alerts telling the user some error occurred at a location in the code that is most likely not the line at which the error occurred, then you will pay big time.

Kevin

>>>Why is that bad? You are exiting a structured construct flowing down and out the end of the construct.

You seriously don't mean it right? What happens to all those object which are left in the memory? What happens to the Excel application which is still running in the background?

>>>Today, yes. And then when weird things start happening in production and you are displaying stupid but pretty alerts telling the user some error occurred at a location in the code that is most likely not the line at which the error occurred, then you will pay big time.

Isn't it much better then leaving them with a code which breaks on them and they have no clue what to do next?

Sid

>What happens to all those object which are left in the memory? What happens to the Excel application which is still running in the background?

You are talking about cleanup code which should NOT be avoided. Your example didn't have any cleanup code. But, frankly, cleanup  code is very overrated. I've never found the lack of cleanup code to cause memory problems. VB and VBA has very good built in cleanup code. Yes, there are a few exceptions, but they are rare and do not result in excessive consumption of memory. These days memory is so cheap, computers so fast, and Windows memory management so efficient, the effort to clean up handles just isn't worth the benefit. I never use cleanup code in my applications.

>Isn't it much better then leaving them with a code which breaks on them and they have no clue what to do next?

And what do you propose as the alternative to the VB error dialog? A pile of dog shit with a pretty red bow around it is still a pile of dog shit.

Kevin

I respectfully again disagree with both your points but I rest my case. :)

Sid

How far off topic are we now? ;)

@Rory: With my last comment we should be back now :P

I think we're at a breakpoint.  A lot to absorb, and the debate/discussion for me was worth it - lots to consider.  Hopefully, Anthony is getting a feel for perspective and he (and I - since I helped get us down this rabbit trail) will understand the tradeoffs and make our own decisions.

Yes I'm still quietly here, with interest. a.

it's very challenging not to lose the plot while reading all this.. 12 hours today.. and counting


These statements are so similar in choices of words as appear to contradict when read by me, I am underlining the bits to which I refer:

(Kevin's msg i.d. is as far as I have progressed and I have been working on this for hours so far)
-----------------------------------------------------------------------------------------------------------------------------
Kevin: ID: 37666324

The WorksheetFunction method
provides Intellisense but
does not handle errors - a VB error is thrown when the function generates an error.

The Application method
does not provide Intellisense but
does handle errors -
an error value is returned if the function generates an error.


-----------------------------------------------------------------------------------------------------------------------------

Chip http://www.cpearson.com/Excel/CallingWorksheetFunctionsInVBA.aspx

If you do include the WorksheetFunction property,
errors will manifest themselves as runtime error that need an On Error statement
and a test of the Err.Number value.


If you do not include the WorksheetFunction property, you must declare the result variable as a Variant type and test that variable with the IsError function.

-----------------------------------------------------------------------------------------------------------------------------

My tentative grip of this is that one method generates an error code/value each of which have their own meaning that can be looked up, whereas the other is simply a run time error which means the code stops running unless there is a general error precondition.

This means if an error code is useful is relevant to one's choice of object.

I am continuing to study this thread, but meanwhile at this point it might help me to be clear about the above.

I have assumed that "throwing an error" means an error at run time which means a "run time error" which means the code stops running, with no error codes offered.

The heart of error handling in VBA is the On Error statement. This statement instructs VBA what to do when an run time error is encountered.  The On Error statement takes three forms.........

I am not sure if it is apparent from the above but both parties are saying iserror must be used with Application and the datatype must be Variant, which I now know is because only Variant has subtypes and that these include all strong types plus a further three being null, empty and a boolean error subtype. Decimal is also apparently only a sub type of Variant. Variant is a "sub-computer" in its own right, so specifying (making "strong") datatypes means the  sub-computer does not have to be run.

Anthony

notes

You can test and respond to trappable errors using the On Error statement and the Err Object (Visual Basic).

http://msdn.microsoft.com/en-us/library/5hsw66as(v=vs.80).aspx

Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.

Without an On Error statement, any run-time error that occurs is fatal: an error message is displayed, and execution stops.

Whenever possible, we suggest you use structured exception handling in your code, rather than resorting to unstructured exception handling and the On Error statement



interesting summary

From now on, I'm going to use worksheetfunction to ensure that function actually exists,

IF IT EXISTS IN VBA THEN IT SHOULD NOT BE AVAILABLE AS WF

then backspace and change to application and test for IsError, and if not do it another way or use EVALUATE,

Evaluate has its own set of requirements

ah, some of my text is quotes and not me, I still prefer the application object approach.

I was in the belief the error values were not the 7 excel ones, but the huge number of vb ones.

so, the

application object returns the 7 worksheet errors and the

worksheet object just crashes with a run time error?

Correct.

Kevin

and so the WorksheetFunction object gives the following error response:

"When using the WorksheetFunction object and trapping VB/VBA generated errors, the error code is usually a 1004 Unable to get the ... property of the WorksheetFunction class - not a very useful error code."

which amounts to one code 1004, which as I recall (can't find it now) is in fact a catch all code given that means vba is reporting an error from "some other" (foreign)  application. I think.

Whereas the application object gives a choice of 7 as listed above.

Both can be trapped, but if not trapped WorksheetFunction Object stops the program running whereas the Application Object delivers a result, albeit an error value between 1 and 7 inc (to the program in the form of Variant sub type error value) and the literal responses (n/a ref etc) into the formula result.

so

Application Object is native vba meaning vba knows what to do, so author error handling not required because run time halt will not occur, error results showing in sheet.

Worksheet Function Object is not native vba and vba does not know what to do so author must handle the error to avoid run time halt, error results not showing in sheet.

Is it as clear as this?

Anthony

I'm not sure the reasoning at the end is necessarily true, but the outcomes are accurate.

@rorya any chance of hearing how the reasoning should run? In fact posting this as a follow up question.
  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27616012.html

@everyone

Here's how my involvement is working:

Took me 15 hours yesterday to get to "here". I think much of what makes it hard to follow for me as a novice, is the terminology sounding the same and meaning different things in changing contexts taken for granted by those with experience. So I go and research each phrase and assertion  (using a mix of EE, Safari books and google) to find out the contexts and then re-read for meaning - which is perfectly ok and I am happy doing all this, in fact grateful for the opportunity to see such discussions.  

About Points

How on Earth one allocates points is something I have yet to ponder, there aren't really enough to go round! However, I will be posting follow ups rather than adding further questions here.

@Experts' Exchange feedback

A version question for EE is around the idea that if I want to ask a question of a particular Expert in response to his input, if I post it in-thread there are no more points to be had and yet if he replies (usefully) he has earned them, versus if I post a new question, someone else usually replies and it becomes very hard to maintain the flow -  the quality of this thread would not have survived follow up questions if I had left it closed and posted follow ups. I grant that the experts in this thread would probably have posted anyway, but that's not the point.

- Anthony

I have allocated points with a split over re-reading the thread and selecting those posts that added to my understanding, as best I can. I hope everyone got some. I will be posting follow ups and will add links here. My notes are in my blog, which I am using as an open private notes pad.

Thanks, Anthony!

and another follow up, about error handling, or in fact, not error handling.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27616082.html

and do I understand: proof of the pudding? (copy of the link two posts above)

 http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27616012.html

FWIW, Using Worksheet Functions from VBA Code in Excel 2010 appears to use "WorksheetFunction" as the Excel "default", while limiting "Application" to other applications. However, the only "explanation" for the Excel approach is "the ease of using the WorksheetFunction object directly", which I assume is a reference to intellisense.

That's a different thing altogether as it relates to using just Worksheetfunction rather than Application.worksheetfunction

Dammit you're too fast - I was editing my post as follows...

FWIW, Using Worksheet Functions from VBA Code in Excel 2010 appears to use "WorksheetFunction" as the "default". However, the only "explanation" (while using Excel) is "the ease of using the WorksheetFunction object directly", which I assume is a reference to intellisense. (Although, I suppose, it could simply be contrasting "WorksheetFunction" to "Application.WorksheetFunction".)