Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Empty Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Empty Array
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Empty Array
That creates a new error.
-----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Empty Array
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. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... That creates a new error. -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Empty Array
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 -----Original Message----- 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. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... That creates a new error. -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Empty After Sub Called | Excel Programming | |||
Delete empty array columns | Excel Programming | |||
Is the array empty? | Excel Programming | |||
Is Array Empty | Boolean Answer | Excel Programming | |||
Differentiate between "" and Empty in array elements. | Excel Programming |