CODEWITHSUNDEEP
excelvba
Ádám Bukovinszki
Ádám Bukovinszki

Reputation: 389

VBA (Excel) - Get error number from a variable that became equal to an error

Scenario:

dim Result as variant
Result = Application.Match(string, Wb.Sheets("UD_Base").Range("UD_Base[U_ID]"), 0)

Now if i check the values of -Result- it can either be a number or an error.: Debug.Print Result -> 1 Debug.Print result -> Error 2042

I can test -Result- with: IsError(result) and it gives True or False ... but even if -Result- IS an error, I cannot get its number.

Result.Number -> Object or Type error message comes so seemingly i cannot use it as in Err.Number

How can I get the error number of -Result- variable? What is in this case -Result- really equal with?

If i say If Result = ??? Then ... when will it be true if i know that its Error 2042? I have tested Case "Error 2042" ; Case Err ; Case Error ; Case Err(2042) ; ... None of these showed True for -Result- when it debug.printed Error 2042

Please enlighten me guys.

Upvotes: 1

Views: 1734

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Here is one way:

Sub adam()
    Dim s As String, result As Variant
    s = "happy"
    result = Application.Match(s, Range("A:A"), 0)
    MsgBox CLng(Split(CStr(result), " ")(1))
End Sub

enter image description here

EDIT#1:

Based on Ron's comment, use this instead:

Sub adam()
    Dim s As String, result As Variant
    s = "happy"
    result = Application.Match(s, Range("A:A"), 0)
    If InStr(1, CStr(result), " ") = 0 Then
        MsgBox "Match worked; result is:  " & result
    Else
        MsgBox "Match failed; error is:  " & CLng(Split(CStr(result), " ")(1))
    End If
End Sub

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

The value in Result is of type Error. So you need to compare it with an error value. eg:

Result = CVErr(2042)

will return True if Result contains that error value

But, in order to run the test, you first have to test if Result contains an error type. If not, you will get a type mis-match on the comparison statement.

You can test in a variety of ways:

IsError(Result)
VarType(Result) = 10
VarType(Result) = vbError
TypeName(Result) = "Error"

If you need to pull out the particular number, you can do it with something like

Mid(CStr(Result),7)

converting the error value to a string.

Upvotes: 4

Related Questions