I need to intentionally set a variable to 'error' (iserror(x) = true)

[ker_01]

I'm using some match functions in my code, and use the iserror(x) and
not(iserror(x)) to evaluate whether to run certain blocks of code.

The end-user's data source is a mainframe system, and we just learned that
some of the data in that system changed format and now I have to deal with
multiple scenarios while evaluating for duplicate matches.

I think I can do so by setting a new variable to an 'error', but I've tried
xlerror, vberror, error, and googled and still haven't found a useful link
amongst all the search results dealing with errors in other way.

What I'd like is workable syntax for the first line, that will cause the
second line to evaluate to true:

x = vbError
isError(x)

This is probably an easy one, but I'm getting nowhere fast, and haven't run
into the need for this type of command before, so I'm stuck.

Thanks for any help,
Keith

 

[Bob Bridges]

You know, it sounds easy enough but it occurs to me it may not be possible.
Follow my reasoning, here: In worksheet functions we know what happens and
how to evaluate it, but whenever I do a WorkSheetFunctions.VLOOKUP in my
code, like this:

x = Application.WorksheetFunctions.VLOOKUP(MyValue, MyRange, 3, 0)

....if it fails to find MyValue anywhere in the left column of MyRange, I get
not an error code assigned to x but an abend in my program. Combined with my
failure to find any function that returns something like "#N/A" in
VBA/Excel's function list, and I suspect you can't get there from here;
you'll have to do it some other way. And if it's a workaround you want,
you'll need to post some more details.

Of course, it's entirely possible that while I was writing this someone gave
you a workable answer and posted it ahead of me .

 

[Dave Peterson]

Dim x as variant

x = cverr(xlErrNA)

You could use any of the built-in constants:
xlerrdiv0
xlerrna
xlerrname
xlerrnull
xlerrnum
xlerrref
xlerrvalue

or one of your choosing:

Dim x As Variant
x = CVErr(2008)
MsgBox CStr(x)

 

[ker_01]

Excellent, thanks Dave! I spent a few minutes trying to figure out which one
is the equivalent of the error I get from a bad application.match, but then
realized that they all return the desired iserror(x) =true, so it doesn't
even matter which one I use.
Thanks!
Keith

 

[Dave Peterson]

If you mean the #n/a error, look at xlerrna.

Excellent, thanks Dave! I spent a few minutes trying to figure out which one
is the equivalent of the error I get from a bad application.match, but then
realized that they all return the desired iserror(x) =true, so it doesn't
even matter which one I use.
Thanks!
Keith