Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice in qualifying Excel worksheet function invocation
So I finally got VBA working with VLookup, Worksheet names, and a
lookup table referred to by list name. I had to do the proper prefixes: temp = WorksheetFunction.VLookup( ...) temp = Application.WorksheetFunction.VLookup( ...) Which of the two are best practice, and why? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice in qualifying Excel worksheet function invocation
There are some worksheet functions where it doesn't really matter (maybe speed
is slightly affected???) if you use: Application.somefunction() or application.worksheetfunction.somefunction() or worksheetfunction.somefunction() But one of the nice things is that if you use worksheetfunction (with or without the application object), you'll get a hint of what comes next with the VBE's intellisense and autocomplete. If you type: application.worksheetfunction. (with the dot) you'll see all the worksheet functions that you can use (autocomplete) If you type: application.worksheetfunction.vlookup( you'll see (not too informative) 4 arguments (arg1, arg2, arg3, arg4) You won't see that if you use application.vlookup. But (you didn't ask about this), there is a difference with a few functions on how errors are treated between: Application.vlookup() (and application.match()) and application.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then msgbox "no match" else msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then err.clear msgbox "no match" else msgbox res end if on error goto 0 ============ Personally, I like using the if iserror(res) then version And I hardly ever (almost never??) use worksheetfunction. If you continue to hang out in the excel newsgroups, I bet you'll find that the people who use application.vlookup() are the ones who have been using excel longer (application.worksheetfunction.vlookup() was added in xl97 (IIRC))--or they've picked up this nasty <vbg habit from the longer time users. wrote: So I finally got VBA working with VLookup, Worksheet names, and a lookup table referred to by list name. I had to do the proper prefixes: temp = WorksheetFunction.VLookup( ...) temp = Application.WorksheetFunction.VLookup( ...) Which of the two are best practice, and why? Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice in qualifying Excel worksheet function invocation
My brain was fried yesterday, but I looked at it today. It makes
sense (your preference of being able to test for errors rather than trap for them). Thank you kindly for sharing your insightful expertise. On May 11, 6:32*pm, Dave Peterson wrote: There are some worksheet functions where it doesn't really matter (maybe speed is slightly affected???) if you use: Application.somefunction() or application.worksheetfunction.somefunction() or worksheetfunction.somefunction() But one of the nice things is that if you use worksheetfunction (with or without the application object), you'll get a hint of what comes next with the VBE's intellisense and autocomplete. If you type: application.worksheetfunction. (with the dot) you'll see all the worksheet functions that you can use (autocomplete) If you type: application.worksheetfunction.vlookup( you'll see (not too informative) 4 arguments (arg1, arg2, arg3, arg4) You won't see that if you use application.vlookup. But (you didn't ask about this), there is a difference with a few functions on how errors are treated between: Application.vlookup() (and application.match()) and application.vlookup() (and application.worksheetfunction.match()) If you don't use the .worksheetfunction portion, then you can test the returned results for an error: Dim res as variant 'could be an error res = application.vlookup("someval", somerangehere, 2, false) if iserror(res) then * msgbox "no match" else * msgbox res end if =========== But when you use worksheetfunction.vlookup(), then you'll have to program against a run time error. dim res as long 'or string or even variant on error resume next res = application.vlookup("someval", somerangehere, 2, false) if err.number < 0 then * err.clear * msgbox "no match" else * msgbox res end if on error goto 0 ============ Personally, I like using the if iserror(res) then version And I hardly ever (almost never??) use worksheetfunction. If you continue to hang out in the excel newsgroups, I bet you'll find that the people who use application.vlookup() are the ones who have been using excel longer (application.worksheetfunction.vlookup() was added in xl97 (IIRC))--or they've picked up this nasty <vbg habit from the longer time users. wrote: So I finally got VBA working with VLookup, Worksheet names, and a lookup table referred to by list name. *I had to do the proper prefixes: temp = WorksheetFunction.VLookup( ...) temp = Application.WorksheetFunction.VLookup( ...) Which of the two are best practice, and why? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice in qualifying Excel worksheet function invocation
Dave, I tried the iserror function on the variable in which I stuck
the VLookup return value. IsError never becomes true when VLookup fails to find something. I resorted to using the Err object as described at http://www.cpearson.com/excel/Callin...ionsInVBA.aspx. It turns out that if there is an error, the left-hand-side does not actually get a result. I suuppose I could always try putting the VLookup expression inside IsError as an argument, but then I'd have to repeat that expression to assign to the destination variable when IsError evaluates to False. Anyway, I'm sure this is motherhood to most, but it feels like I'm finally starting to "operate" in the VBA environment. Thanks again for the leads. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use ADOX on Excel? How to automate Excel invocation on existing xls file from vbs? | Excel Programming | |||
Qualifying Commandbars - Excel 2000 | Excel Programming | |||
SQL practice with Excel? | Excel Programming | |||
Macro invocation after data enrtry | Excel Programming | |||
Automatic Macros Invocation | Excel Programming |