View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Error capture mechanism, then 'End' or 'Exit Sub'?

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 wrote:

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