Solved: Avoiding a <type mismatch> error using IsError ?

[Dabo]

Hi all,

Within a loop I have to refer to a value contained in an array.
Sometimes the array doesn't exist so I got a "Type mismatch error"
That is to say that when I call the Array that does not exist, instead of the value I get "Type Mismatch"

I am trying to put an If IsError ( MyArray(Coordinates) ) then ...

to avoid that error but it does not work.

How can I avoid it

[Kenneth Hobs]

Is it a design or run-time error? Option Explicit should be the first line of your code. As a designer, you should always compile your code before running it to check for errors like that.

To catch a run-time error.
Option Explicit
Sub t()
Dim a(1)
Dim b As Integer
Dim Msg as String
On Error GoTo ErrMsg
a(1) = "Test"
b = 0
MsgBox a(1)
If IsArray(b) Then MsgBox "b is an array"

'Force error
MsgBox a(2)
Exit Sub
ErrMsg:
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End
End Sub
Edit Note: Added Option Explicit and Dim Msg as String. Otherwise, we get too many rolling on the floor laughing. Just do what I say, not what I do...

[Bob Phillips]

Try checking that the index is within the array bounds



If Coordinates >= LBound(MyArray) And Coordinates <= UBound(MyArray)

[Dabo]

It is a runtime error,

Kenneth, I customized your function to create an IsemptyArray function that allowed me to solve my problem.

Thank you so much !


Option Explicit

Private Function My_IsEmptyArray(vntArray As Variant) As Boolean
' is true if the array is empty, is false if it is not
Dim lngTmp As Long
On Error GoTo ErrHndlr
lngTmp = UBound(vntArray)

My_IsEmptyArray = False
Exit Function
ErrHndlr:
My_IsEmptyArray = True
End Function

[Kenneth Hobs]

Good deal!

For what it is worth, here is another method. You could do it with no function at all if needed.
Sub Test_ArrayIsNotEmpty()
Dim a(), b(1), c(0)
MsgBox "a() is NOT empty?" & " " & ArrayIsNotEmpty(a), vbInformation, "Should be False"
MsgBox "b() is NOT empty?" & " " & ArrayIsNotEmpty(b), vbInformation, "Should be True"
MsgBox "c() is NOT empty?" & " " & ArrayIsNotEmpty(c), vbInformation, "Should be True"
Erase a
Erase b
Erase c
End Sub

'Mikerickson, http://www.mrexcel.com/forum/showthread.php?p=1709702
Function ArrayIsNotEmpty(anyArray As Variant) As Boolean
On Error Resume Next
ArrayIsNotEmpty = IsNumeric(VarPtr(Array(LBound(anyArray))))
On Error GoTo 0
End Function