I have an array (ObjCollArray) that is defined as public.
On a Button Click event, I fill it with text. I check to
see that it is filled with Debug.Print. In the immdediate
window I type ?Application.Index(ObjCollArray, 1, 1) and
also get a result.


However, when I call a private sub and do a lookup in the
array with:

ObjFind = Application.Index(ObjCollArray, 1, 1)

It tells me that the Run Error 13 Type Mismatch. When I
put the cursor over ObjCollArray it says: ObjCollArray="".

Can't figure out why this is. The array is public,
initially has data, have not declared a variable twice.

Here is the code.


Public ObjCollArray As String

Private Sub OKButton_Click()
Dim ObjCollArray(0 To 6)

ObjCollArray(0) = "Comments"
ObjCollArray(1) = "Range"
ObjCollArray(2) = "Range"
ObjCollArray(3) = "Range"
ObjCollArray(4) = "Range"
ObjCollArray(5) = "Range"

Debug.Print ObjCollArray(0)
Debug.Print ObjCollArray(1)
Debug.Print ObjCollArray(2)
Debug.Print ObjCollArray(3)
Debug.Print ObjCollArray(4)
Debug.Print ObjCollArray(5)

UserForm1.Hide

Call ListAuditResults

Private Sub ListAuditResults()
Dim PasteStartCell As String
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim AuditTypes As Integer
Dim AuditShtName As String
Dim ChkbxRowFind As Integer
Dim ChkbxCtrlFind As Integer
Dim ObjFind As String
Dim cmt As Comment
Dim cell As Range

With ActiveWorkbook

For Each sh In .Worksheets
For AuditTypes = 0 To ChkbxArraySum
'Find the row in ChkbxArray array that relates
to this loop #
ChkbxRowFind = Application.Match(AuditTypes +
1, Application.Index(ChkbxArray, 0, 4), 0)
'Upon finding row, use to look up control in
ChkbxArray
'to pass to main sub case select
ChkbxCtrlFind = Application.Index(ChkbxArray,
ChkbxRowFind, 5)
'Use ChkbxCtrl to find the object assoicated
with that
'control in ObjCollArray
ObjFind = Application.Index(ObjCollArray,
ChkbxCtrlFind, 1)

Select Case ObjFind
Case Is = "Comments"
For Each cmt In sh.Comments
Debug.Print cmt.Parent.Parent.Name, sh.Name
ObjType = TypeName(cmt)
CollType = TypeName(sh)
Call MainAudit(ChkbxCtrlFind)
Next
Case Is = "Range"
For Each cell In sh.UsedRange
Debug.Print cell.Parent.Name, sh.Name
ObjType = TypeName(cell)
CollType = TypeName(sh)
Call MainAudit(ChkbxCtrlFind)
Next
End Select
Next
Next

End With