Empty Array

[ExcelMonkey]

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

 

[paul.robinson]

Hi
Could be wrong here, but would you not do

Public ObjCollArray() as string

and then inside the sub

Redim ObjCollArray(0 to 6)

regards
Paul

 

[ExcelMonkey]

That creates a new error.

 

[Tom Ogilvy]

You said:

The array is public,
initially has data, have not declared a variable twice.

and then in the first 3 lines, you declare it twice:

Public ObjCollArray As String

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

The array in OKButton_Click is a local variable and is not the same as you
your public variable with the same name.

If you are doing all this in you userform module, then you would need to do

Public ObjCollArray As Variant

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

then you would need to do

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

since this is a single row array.

Note that if I want the first element of ObjCollArray, then ChkbxCtrlFind
would need to have a value of 1, not zero.

 

[ExcelMonkey]

Dho! Thank-you. I think I spent an an hour staring at
this last night trying to figure out why it wasn't
working. I knew it was something obvious. thanks again
Tom. I am starting to see the light at the end of this
programming tunnel - for now!

Thanks