Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
Hi All,
I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
The type of error returned is different.
"Application" returns a Variant containing an error which allows the use of IsError... If IsError(Application.Match...) "WorksheetFunction" returns a VBA error which requires an error handler... On Error Resume Next Application.WorksheetFunction.Match... If Err.Number < 0 then -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message ... Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
Hi Jim,
Tnx for the clarification. Can I conclude that both syntaxes are equal, except of course from the returned error, but decision depends who the programmer will handle errors? Any idea which is the fastest way? AFAIK, calling the application or accessing objects is slow compared e.g. accessing a memory variable. If you have a lot of errors to process, the VBA way can be much faster then the application way or do I see this wrong? Wkr, JP "Jim Cone" wrote in message ... The type of error returned is different. "Application" returns a Variant containing an error which allows the use of IsError... If IsError(Application.Match...) "WorksheetFunction" returns a VBA error which requires an error handler... On Error Resume Next Application.WorksheetFunction.Match... If Err.Number < 0 then -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message ... Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
The difference between the two approaches is how errors are handled.
For example, if you include the WorksheetFunction reference and an error occurs, VBA will raise a runtime error that you must trap and deal with using standard error handling techniques: Dim V As Variant On Error Resume Next V = Application.WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False) If Err.Number < 0 Then Debug.Print "not found" Else Debug.Print "found: " & V End If If you omit the WorksheetFunction reference, the function returns a Variant of subtype Error that you test with IsError. No runtime error is raised. E.g., Dim V As Variant V = Application.VLookup(1, Range("A1:B10"), 2, False) If IsError(V) = True Then Debug.Print "not found" Else Debug.Print "found" End If In the first approach, you can declare the variable V to be the type that should be returned by the VLOOKUP (e.g., a Long or a String). In the second approach, V must be declared as a Variant. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 23 Aug 2009 14:24:47 +0200, "JP Ronse" wrote: Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
Application.Worksheetfunction seems to be faster for the few Functions I
have tested... but may not be faster for all. regards Charles Williams Decision Models "JP Ronse" wrote in message ... Hi Jim, Tnx for the clarification. Can I conclude that both syntaxes are equal, except of course from the returned error, but decision depends who the programmer will handle errors? Any idea which is the fastest way? AFAIK, calling the application or accessing objects is slow compared e.g. accessing a memory variable. If you have a lot of errors to process, the VBA way can be much faster then the application way or do I see this wrong? Wkr, JP "Jim Cone" wrote in message ... The type of error returned is different. "Application" returns a Variant containing an error which allows the use of IsError... If IsError(Application.Match...) "WorksheetFunction" returns a VBA error which requires an error handler... On Error Resume Next Application.WorksheetFunction.Match... If Err.Number < 0 then -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message ... Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
Hi Chip,
Thank you very much. Wkr, JP "Chip Pearson" wrote in message ... The difference between the two approaches is how errors are handled. For example, if you include the WorksheetFunction reference and an error occurs, VBA will raise a runtime error that you must trap and deal with using standard error handling techniques: Dim V As Variant On Error Resume Next V = Application.WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False) If Err.Number < 0 Then Debug.Print "not found" Else Debug.Print "found: " & V End If If you omit the WorksheetFunction reference, the function returns a Variant of subtype Error that you test with IsError. No runtime error is raised. E.g., Dim V As Variant V = Application.VLookup(1, Range("A1:B10"), 2, False) If IsError(V) = True Then Debug.Print "not found" Else Debug.Print "found" End If In the first approach, you can declare the variable V to be the type that should be returned by the VLOOKUP (e.g., a Long or a String). In the second approach, V must be declared as a Variant. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 23 Aug 2009 14:24:47 +0200, "JP Ronse" wrote: Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
Tnx Charles.
Wkr, JP "Charles Williams" wrote in message ... Application.Worksheetfunction seems to be faster for the few Functions I have tested... but may not be faster for all. regards Charles Williams Decision Models "JP Ronse" wrote in message ... Hi Jim, Tnx for the clarification. Can I conclude that both syntaxes are equal, except of course from the returned error, but decision depends who the programmer will handle errors? Any idea which is the fastest way? AFAIK, calling the application or accessing objects is slow compared e.g. accessing a memory variable. If you have a lot of errors to process, the VBA way can be much faster then the application way or do I see this wrong? Wkr, JP "Jim Cone" wrote in message ... The type of error returned is different. "Application" returns a Variant containing an error which allows the use of IsError... If IsError(Application.Match...) "WorksheetFunction" returns a VBA error which requires an error handler... On Error Resume Next Application.WorksheetFunction.Match... If Err.Number < 0 then -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message ... Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
The choice would usually be determined by the type of error trapping you wanted to do.
Application.WorksheetFunction.Match takes about 1/2 the time of Application.Match, however if you are only calling the function a few times then you couldn't measure the time difference. -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message Hi Jim, Tnx for the clarification. Can I conclude that both syntaxes are equal, except of course from the returned error, but decision depends who the programmer will handle errors? Any idea which is the fastest way? AFAIK, calling the application or accessing objects is slow compared e.g. accessing a memory variable. If you have a lot of errors to process, the VBA way can be much faster then the application way or do I see this wrong? Wkr, JP "Jim Cone" wrote in message ... The type of error returned is different. "Application" returns a Variant containing an error which allows the use of IsError... If IsError(Application.Match...) "WorksheetFunction" returns a VBA error which requires an error handler... On Error Resume Next Application.WorksheetFunction.Match... If Err.Number < 0 then -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message ... Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.worksheetfunction
Hi Jim,
I understand. If you know that I have written some code to generate a monthly report based on a CSV dump of about 40MB, over 20.000 rows and x columns and only about 1000 rows are valuable input, each gain in runtime is most appreciated. Tnx! Wkr, JP "Jim Cone" wrote in message ... The choice would usually be determined by the type of error trapping you wanted to do. Application.WorksheetFunction.Match takes about 1/2 the time of Application.Match, however if you are only calling the function a few times then you couldn't measure the time difference. -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message Hi Jim, Tnx for the clarification. Can I conclude that both syntaxes are equal, except of course from the returned error, but decision depends who the programmer will handle errors? Any idea which is the fastest way? AFAIK, calling the application or accessing objects is slow compared e.g. accessing a memory variable. If you have a lot of errors to process, the VBA way can be much faster then the application way or do I see this wrong? Wkr, JP "Jim Cone" wrote in message ... The type of error returned is different. "Application" returns a Variant containing an error which allows the use of IsError... If IsError(Application.Match...) "WorksheetFunction" returns a VBA error which requires an error handler... On Error Resume Next Application.WorksheetFunction.Match... If Err.Number < 0 then -- Jim Cone Portland, Oregon USA "JP Ronse" wrote in message ... Hi All, I see that most of the MVP's are using: x = application.worksheetfunction.<function(arguments) while x = application.<function(arguments) is also working. Is there a reason to use worksheetfunction, except that the syntax of the function is given? Wkr, JP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
application.worksheetfunction | Excel Programming | |||
Application.worksheetfunction | Excel Programming | |||
Application.WorksheetFunction.MMult | Excel Programming | |||
Using Application.WorksheetFunction.Ln(...) in VBA | Excel Programming |