Error capture mechanism, then 'End' or 'Exit Sub'?

[OrientalPearl]

Hi all,

Im in a situation to capture an error with details as follows:
The user may import some data and then the macro will look up some
values according to the import (VLOOKUP function). If the import data
is not found, the default error value '#N/A' will be returned. In this
scenario, the user will get a warning message. But how can this error
be captured?
I tried
If xxx.value = "#N/A" Then //<Runtime error '13' - Type
Mismatch> fired here
<aler msgbox>
(I also checked the value of xxx in the immediate window using '?xxx',
which gives 'Error 2042')

Then I tried
If IsNA(xxx.value) Then //<Compile error: Sub or Function
not defined> fired here
(Also noticed that VBA automatically modified 'ISNA'(my original
typing) to 'IsNA'. Why is this???)

Im also quite confused by 'End' and 'Exit Sub', i.e. what difference
between the following:

If Error Then
<error Handling>
End
End If

If Error Then
<error Handling>
Exit Sub
End if

Thanks in advance and regards
Frank

 

[Dave Peterson]

if xxx.text = "#N/A" then
...
would work

But so would:
if iserror(xxx.value) then
...


End on a line by itself is usually a very bad thing to use--I can't think of a
good reason to use it. It resets any persistent variables (public and static
variables).

 

[OrientalPearl]

Thanks Dave for your prompt help! The two solutions both work well. Re
iserror, I looked up ISNA() and it seems more specialised on working
with '#N/A'. But why doesn't it in this case? Also, '.Text' and
'.Value' do things diffferently? (apparently they make the difference
here)

Ok. I will stick to 'Exit Sub' then. Save a bit of effort in choosing
from the two ;-)

You help is highly appreciated!! Thanks!

 

[NickHK]

OrientalPearl,
I think you'll find ISNA is an Excel worksheet function. As such VBA does
not understand what it means, hence the compile error.
If you want to use it, you need Application.WorksheetFunctions.ISNA(....

NickHK

 

[Dave Peterson]

and .text shows what's in the cell--it's affected by numberformat, too.

1234.5332 could be the value, but $1,234.53 could be the .text.

 

[OrientalPearl]

Thanks Nick. I intend to blame Excel Help since it categorises both
ISNA and ISERROR functions together, however does not mention at all
the difference in their usage as seen from my example!

Regards
Frank

 

[OrientalPearl]

I see. Thanks Dave =)