VB Error Codes - where to look?

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Please tell me where to look for error codes I'm
encountering when writing a visual basic function for
excel. The code I'm getting is 2042.

I cannot find them on the microsoft site.
 
I tried it, but the error code seems to be unknown. Here
is the offending line:

Application.VLookup(year, Range("iraTable"), 3)

In debug mode it returns "Error 2042"

when I try to get the error information, I get nothing:
If Err.Number <> 0 Then
Debug.Print "Error # " & Str(Err.Number) "-" &
Err.Description
 
From the immediate window:
? cvErr(xlErrNA)
Error 2042

Error 2042 is the equivalent of #N/A

also you can query the constant:

? xlErrNa
2042



look in the code example for CVERR

Worksheets("Sheet1").Activate
If IsError(ActiveCell.Value) Then
errval = ActiveCell.Value
Select Case errval
Case CVErr(xlErrDiv0)
MsgBox "#DIV/0! error"
Case CVErr(xlErrNA)
MsgBox "#N/A error"
Case CVErr(xlErrName)
MsgBox "#NAME? error"
Case CVErr(xlErrNull)
MsgBox "#NULL! error"
Case CVErr(xlErrNum)
MsgBox "#NUM! error"
Case CVErr(xlErrRef)
MsgBox "#REF! error"

Case CVErr(xlErrValue)
MsgBox "#VALUE! error"
Case Else
MsgBox "This should never happen!!"
End Select
End If
 
Ram gave you advice pertinent to VBA errors. The error you are seeing are
Excel Errors.

See my post for information.

You test these type errors with the iserror function

Dim res as Variant
res = Application.VLookup(year, Range("iraTable"), 3)
if iserror(res) then
msgbox "No match found
Else
msgbox "Results is " & res
End if
 
Hi Jack,

Thanks for posting in the community!

From my understanding to this issue, when you run the VLookup function in
your VB project, the error code 4024 was raised in debug mode.

The definition of VLoopUp function is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

This function is used to looks in the first column of an array and moves
across the row to return the value of a cell. The suggestion and analysis
from Ram and Tom is very helpful on this issue. There are two scenarios the
function will raise #N/A(2042) error.
1) If lookup_value is smaller than the smallest value in the first column
of table_array, VLOOKUP returns the #N/A error value.
2) If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.
You will obtain more information regarding this function from this link.
VLOOKUP
http://msdn.microsoft.com/library/en-us/office97/html/sec6f.asp

As Tom has suggested, this error(2042) is defined as xlErrNA which is a
member of Excel.XlCVError. There are other excel defined errors in
XlCVError. This page will list them for you:
TempErr
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/ht
ml/sf886.asp

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi Jack,

Thanks for posting in the community!

From my understanding to this issue, when you run the VLookup function in
your VB project, the error code 2042 was raised in debug mode.

The definition of VLoopUp function is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

This function is used to looks in the first column of an array and moves
across the row to return the value of a cell. The suggestion and analysis
from Ram and Tom is very helpful on this issue. There are two scenarios the
function will raise #N/A(2042) error.
1) If lookup_value is smaller than the smallest value in the first column
of table_array, VLOOKUP returns the #N/A error value.
2) If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.
You will obtain more information regarding this function from this link.
VLOOKUP
http://msdn.microsoft.com/library/en-us/office97/html/sec6f.asp

As Tom has suggested, this error(2042) is defined as xlErrNA which is a
member of Excel.XlCVError. There are other excel defined errors in
XlCVError. This page will list them for you:
TempErr
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/ht
ml/sf886.asp

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top