Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Function - trying to learn
I often code cells to check for error - something like
=IF(ISERROR(VBLOOKUP(parms........)),"",VBLOOKUP(p arms.........)) but I'd like to try to write a function like "CheckIt" which I could invoke as =CheckIt(VBLOOKUP(parms .........) Checkit should allow any type of value as input - since the VBLOOKUP can return text or numeric or anything. It should see if the value is an error and if it is return a blank string, otherwise return the value of the vlookup. A couple of questions: 1 ) Can this be done ? 2) Will the function call evaluate the Vlookup before passing the result to the function ? 3) Can the function accept arguments of any type - that is text or numeric or whatever? Any hints or suggestions are appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Function - trying to learn
On Sat, 8 Nov 2008 13:23:01 -0800, dhstein
wrote: I often code cells to check for error - something like =IF(ISERROR(VBLOOKUP(parms........)),"",VBLOOKUP( parms.........)) but I'd like to try to write a function like "CheckIt" which I could invoke as =CheckIt(VBLOOKUP(parms .........) Checkit should allow any type of value as input - since the VBLOOKUP can return text or numeric or anything. It should see if the value is an error and if it is return a blank string, otherwise return the value of the vlookup. A couple of questions: 1 ) Can this be done ? 2) Will the function call evaluate the Vlookup before passing the result to the function ? 3) Can the function accept arguments of any type - that is text or numeric or whatever? Any hints or suggestions are appreciated. 1) Yes 2) Yes 3) Yes Try this: Function checkit(x As Variant) As Variant If IsError(x) Then checkit = "" Else checkit = x End If End Function Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Function - trying to learn
Start with something like:
Function CheckIt(v As Variant) As Variant s = Evaluate(v) If Application.WorksheetFunction.IsError(v) Then CheckIt = "" Else CheckIt = s End If End Function So if A1 contains 1 and A2 contains 2 then =CheckIt(A1+A2) will display 3 But if A1 contains =0/0 then =CheckIt(A1+A2) will display a blank -- Gary''s Student - gsnu200812 "dhstein" wrote: I often code cells to check for error - something like =IF(ISERROR(VBLOOKUP(parms........)),"",VBLOOKUP(p arms.........)) but I'd like to try to write a function like "CheckIt" which I could invoke as =CheckIt(VBLOOKUP(parms .........) Checkit should allow any type of value as input - since the VBLOOKUP can return text or numeric or anything. It should see if the value is an error and if it is return a blank string, otherwise return the value of the vlookup. A couple of questions: 1 ) Can this be done ? 2) Will the function call evaluate the Vlookup before passing the result to the function ? 3) Can the function accept arguments of any type - that is text or numeric or whatever? Any hints or suggestions are appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Function - trying to learn
Gary and Lars - Thanks so much - both of these functions worked. I really appreciate the help. David "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Nov 2008 13:23:01 -0800, dhstein wrote: I often code cells to check for error - something like =IF(ISERROR(VBLOOKUP(parms........)),"",VBLOOKUP( parms.........)) but I'd like to try to write a function like "CheckIt" which I could invoke as =CheckIt(VBLOOKUP(parms .........) Checkit should allow any type of value as input - since the VBLOOKUP can return text or numeric or anything. It should see if the value is an error and if it is return a blank string, otherwise return the value of the vlookup. A couple of questions: 1 ) Can this be done ? 2) Will the function call evaluate the Vlookup before passing the result to the function ? 3) Can the function accept arguments of any type - that is text or numeric or whatever? Any hints or suggestions are appreciated. 1) Yes 2) Yes 3) Yes Try this: Function checkit(x As Variant) As Variant If IsError(x) Then checkit = "" Else checkit = x End If End Function Hope this helps / Lars-Ã…ke |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Function - trying to learn
Hi,
Just a little FYI - Microsoft has implimented something very similar in 2007, we have a new function called IFERROR The syntax of this function would be =IFERROR(VLOOKUP(A1,Table,2,False),"") The beauty of this should be immediately apparent - formulas are shorter and they don't need to evaluate the interior function twice if there is no error. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "dhstein" wrote: Gary and Lars - Thanks so much - both of these functions worked. I really appreciate the help. David "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Nov 2008 13:23:01 -0800, dhstein wrote: I often code cells to check for error - something like =IF(ISERROR(VBLOOKUP(parms........)),"",VBLOOKUP( parms.........)) but I'd like to try to write a function like "CheckIt" which I could invoke as =CheckIt(VBLOOKUP(parms .........) Checkit should allow any type of value as input - since the VBLOOKUP can return text or numeric or anything. It should see if the value is an error and if it is return a blank string, otherwise return the value of the vlookup. A couple of questions: 1 ) Can this be done ? 2) Will the function call evaluate the Vlookup before passing the result to the function ? 3) Can the function accept arguments of any type - that is text or numeric or whatever? Any hints or suggestions are appreciated. 1) Yes 2) Yes 3) Yes Try this: Function checkit(x As Variant) As Variant If IsError(x) Then checkit = "" Else checkit = x End If End Function Hope this helps / Lars-Ã…ke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Learn VB | Excel Discussion (Misc queries) | |||
Which material is best to learn | Excel Discussion (Misc queries) | |||
To learn about VBA | Excel Discussion (Misc queries) | |||
Learn formulas | Excel Worksheet Functions | |||
How to learn VBA | Excel Discussion (Misc queries) |