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.

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

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.

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

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