Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this lin e of code
I am getting "Unable to get the VLookup property of the WorksheetFunction
class" error on this line of code. Any ideas? If Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cRegion, rngFSC, 1, False)) = True Then I also tried this, with the same returns: If IsError(Application.WorksheetFunction.VLookup(cReg ion, rngFSC, 1, False)) Then |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this lin e of code
This may help from yesterday's archive:
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! Newsgroups: microsoft.public.excel.programming From: Dave Peterson Date: Mon, 11 May 2009 17:32:47 -0500 Local: Mon, May 11 2009 3:32 pm Subject: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this lin e of code
There is a difference with a few functions on how errors are treated between:
Application.vlookup() (and application.match()) and application.worksheetfunction.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 ========= And since it looks like you're trying to find if there's a match (since you bring back what's in column 1 of the table). I'd use: Dim res as variant res = application.match(cRegion, rngfsc.columns(1),0) if iserror(res) then 'not there else 'it's on row # Res of the first column of rngfsc. end if Ayo wrote: I am getting "Unable to get the VLookup property of the WorksheetFunction class" error on this line of code. Any ideas? If Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cRegion, rngFSC, 1, False)) = True Then I also tried this, with the same returns: If IsError(Application.WorksheetFunction.VLookup(cReg ion, rngFSC, 1, False)) Then -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with this lin e of code
But there was a typo (at least one!).
I wanted the second example to include the .worksheetfunction Application.vlookup() (and application.match()) and application.vlookup() (and application.worksheetfunction.match()) should be: Application.vlookup() (and application.match()) and application.worksheetfunction.vlookup() (and application.worksheetfunction.match()) dan dungan wrote: This may help from yesterday's archive: 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! Newsgroups: microsoft.public.excel.programming From: Dave Peterson Date: Mon, 11 May 2009 17:32:47 -0500 Local: Mon, May 11 2009 3:32 pm Subject: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Programming | |||
What's wrong with this code? | Excel Programming | |||
Wrong code? | Excel Programming | |||
Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with this code??? | Excel Programming |