Code not working when transfered into Userform

[ExcelMonkey]

I have this code which is not working. As presented
below it works fine. Sub One Sets a public array. It
then Calls Sub Two and I print values to the Immediate
Window.

Now I cannot seem to get this to work when I put this
code into a useform. I put the Option Explicit and
Public Array Declaration at the top of the page. Then I
put Sub One into a Private Sub OKButton_ClickEvent(). I
then put Sub Two into a Private Sub as well on the same
form. When I run it I get an Error 424 Object Required
within what was Sub Two. On the line:

For Each A In vArray(X)

The code can't seem to make sense of the variable A. Yet
it presents no problems when modelled as seen below. Why
does it not work?


Option Explicit
Public vArray As Variant

Sub One()
ReDim vArray(0 To 2)
Set vArray(0) = ActiveSheet.UsedRange
Set vArray(1) = ActiveSheet.Comments
Set vArray(2) = ThisWorkbook.Worksheets
Call Two

End Sub

Sub Two()
Dim A As Object
Dim X As Integer

With ActiveWorkbook
For X = 0 To 2
'Unload public array and pass to A and B as Types
'How do you pass these to A and B??????
For Each A In vArray(X)
' Call Procedure2
Debug.Print X, TypeName(A), TypeName(vArray(X))
Next
Next
End With
End Sub

Immediate Window Values:
0 Range Range
2 Worksheet Sheets
2 Worksheet Sheets
2 Worksheet Sheets

 

[moi]

Dim A as Object

 

[Bob Phillips]

Declare A as type Variant, not Object.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ExcelMonkey]

Have not tried it yet but have a question. Why would it
work Dim As Object in the first scenario and not in the
second? How does changing it to Variant make the
difference?

Thank-you

 

[Tom Ogilvy]

All in the userform Module:

Option Explicit
Public vArray As Variant

Private Sub CommandButton1_Click()
ReDim vArray(0 To 2)
Set vArray(0) = ActiveSheet.UsedRange
Set vArray(1) = ActiveSheet.Comments
Set vArray(2) = ThisWorkbook.Worksheets
Call Two

End Sub

Sub Two()
Dim A As Object
Dim X As Integer

With ActiveWorkbook
For X = 0 To 2
'Unload public array and pass to A and B as Types
'How do you pass these to A and B??????
For Each A In vArray(X)
' Call Procedure2
Debug.Print X, TypeName(A), TypeName(vArray(X))
Next
Next
End With
End Sub

Produced:
0 Range Range
.. . .
0 Range Range
1 Comment Comments
1 Comment Comments
2 Worksheet Sheets
2 Worksheet Sheets
2 Worksheet Sheets

I removed all the comments from the sheet and it worked as well.

 

[ExcelMonkey]

So Tom. You are right. This does work in a userform. I
built it from scratch in a new userform with a new button
and it works fine. But for some reason I cannot get it
to work in my currrent code. My current code is longer
and more extensive than what I am showing here. When I
put a break right before the first For Loop and go to the
immediate window, I get a value for X and I can also type
in ?TypeName(vArray(X)) and get values for all the array
elements. It fails on recognizing A. And I have defined
A as: Dim A As Object right in that very procedure. ?A
gives an error. What can I do to help me understand why
this is not working. I keep thinking its something
obvious. I know the array has transferred successfully,
I know my loop counter works.

Thanks

 

[ExcelMonkey]

Ok. Just figured something out. My routine creates a
worksheet in my file that it summarizes data in. When
the macro runs it checks to see if the sheet exists. If
not it creats it. If it does exist, it deletes it and
creates a new one. The macro fails when the sheet
already exists. When I manually go in an delete the
sheet and then run the macro, it works fine. So when the
sheet exists, the routine does not understand what A is.
Yet when the sheet is not present, it does?

Here is the exact code I am using:

Private Sub ListAuditResults()
Dim cmt As Comment
Dim cell As Range
Dim Row As Range
Dim Column As Range
Dim PasteStartCell As String
Dim Comrng As Range
Dim Hardrng As Range
Dim Errrng As Range
Dim Colrnge As Range
Dim Validrng As Range
Dim ValidErrrng As Range
Dim HiddenRowrng As Range
Dim HiddenColumnrng As Range
Dim HiddenSheetrng As Range
Dim ColKeyrng As Range
Dim CellColour As Boolean
Dim sStr As String
Dim sStr1 As String
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim NumColrs As Integer
Dim Counter As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim NumCells As Integer
Dim CellCounter As Integer
Dim SheetNamePosition As Integer
Dim A As Object 'Object like cell, comment
Dim AuditTypes As Integer
Dim AuditShtName As String

Application.Calculation = xlManual
Application.ScreenUpdating = False

On Error Resume Next
'Set up name of new summary sheet
Set sh1 = ActiveWorkbook.Sheets("Audit Results")
On Error GoTo 0

'If Sheet called "Audit Results" already exists
'then delete it and prepare to create a new one

If Not sh1 Is Nothing Then
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End If


With ActiveWorkbook

'Add a worksheet for results to be pasted to
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = "Audit Results"


For AuditTypes = 0 To 5
For Each A In ObjCollArray(AuditTypes)
'Call MainAudit(AuditTypes)
Debug.Print AuditTypes, TypeName(A), TypeName
(ObjCollArray(AuditTypes))
Next
Next
End With

 

[ExcelMonkey]

As stated in the last post, this is bizarre. When I type
in TypeName(A) in the immediate window it registers as
Nothing up until the line of code:

For Each A In ObjCollArray(AuditTypes)

Then it regesters proprerly. However, this only works if
I do not have a sheet in the file called "Audit
Results". That is, if the sheet already exists, the
routine deletes it, and inserts a new one with the same
name. When the code gets down to the line of code:

For Each A In ObjCollArray(AuditTypes)

It fails because ?TypeName(A) is Nothing. Why is this
happening?


Private Sub ListAuditResults()
Dim cmt As Comment
Dim cell As Range
Dim Row As Range
Dim Column As Range
Dim PasteStartCell As String
Dim Comrng As Range
Dim Hardrng As Range
Dim Errrng As Range
Dim Colrnge As Range
Dim Validrng As Range
Dim ValidErrrng As Range
Dim HiddenRowrng As Range
Dim HiddenColumnrng As Range
Dim HiddenSheetrng As Range
Dim ColKeyrng As Range
Dim CellColour As Boolean
Dim sStr As String
Dim sStr1 As String
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim NumColrs As Integer
Dim Counter As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim NumCells As Integer
Dim CellCounter As Integer
Dim SheetNamePosition As Integer
Dim A As Object 'Object like cell, comment
Dim AuditTypes As Integer
Dim AuditShtName As String

Application.Calculation = xlManual
Application.ScreenUpdating = False

On Error Resume Next
'Set up name of new summary sheet
Set sh1 = ActiveWorkbook.Sheets("Audit Results")
On Error GoTo 0

'If Sheet called "Audit Results" already exists
'then delete it and prepare to create a new one

If Not sh1 Is Nothing Then
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End If


With ActiveWorkbook

'Add a worksheet for results to be pasted to
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = "Audit Results"


For AuditTypes = 0 To 5
For Each A In ObjCollArray(AuditTypes)
'Call MainAudit(AuditTypes)
Debug.Print AuditTypes, TypeName(A), TypeName
(ObjCollArray(AuditTypes))
Next
Next
End With

 

[Myrna Larson]

If it fails on the For Each line, yes, A would be Nothing because the line
never executes and returns an object; the root problem is most likely that
*ObjCollArray* is Nothing or it is empty.

Where do you Dim a variable named ObjCollArray, and where do you set it to
point to something? Is this a module-level variable that is set in another
routine?

 

[ExcelMonkey]

Yes I delcare ObjCollArray as a PublicVariable. To avoid
confustion I have changed the name of the variable
from "A" to "ObjCur"

Then on a Button click Event, I set the elements in the
array. Then I call the ListAuditResults routine. This
checks to see if there is a sheet called "Audit Results".
If there is, it deletes it and creates a new one
called "Audit Results". If not, it simply creates on
called "Audit Results".

This routine will not work if the sheet "Audit Results" is
already present. It failes to recognize "ObjCur". Yet if
I manually delete before I run the routine, it works! Why
is this


Public ObjCollArray As Variant

Private Sub OKButton_Click()
Set ObjCollArray(0) = ActiveSheet.Comments
Set ObjCollArray(1) = ActiveSheet.UsedRange
Set ObjCollArray(2) = ActiveSheet.UsedRange
Set ObjCollArray(3) = ActiveSheet.UsedRange
Set ObjCollArray(4) = ActiveSheet.UsedRange
Set ObjCollArray(5) = ActiveSheet.UsedRange
call ListAuditResults
End Sub

Private Sub ListAuditResults()
Dim PasteStartCell As String
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim AuditTypes As Integer
Dim AuditShtName As String


Application.Calculation = xlManual
'Application.ScreenUpdating = False

On Error Resume Next
'Set up name of new summary sheet
Set sh1 = ActiveWorkbook.Sheets("Audit Results")
On Error GoTo 0

'If Sheet called "Audit Results" already exists
'then delete it and prepare to create a new one

If Not sh1 Is Nothing Then
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End If


With ActiveWorkbook

'Add a worksheet for results to be pasted to
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = "Audit Results"

'Set up column headings for summary report
'these will set up based on the numbers
'options chosen.

PasteStartCell = Range("B2").Address

'Set first paste cell and column header for Commented
Cells

If ComChkBx = True Then
Set Comrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(0, 1) * 2 - 2)
Comrng.Offset(-1, 0) = "Cell Comments"
End If

'Set first paste cell and column header for Hard Coded
Cells
If HardCodedChkBx = True Then
Set Hardrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(1, 1) * 2 - 2)
Hardrng.Offset(-1, 0) = "Hard Coded Cells"
End If

'Set first paste cell and column header for Cells with
Errors
If ErrorChkBx = True Then
Set Errrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(2, 1) * 2 - 2)
Errrng.Offset(-1, 0) = "Errors"
End If

'Set first past cell for data validation cells
If DataValChkBx = True Then
Set Validrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(3, 1) * 2 - 2)
Validrng.Offset(-1, 0) = "Validation"
End If

'Set first past cell for data validation cells
If DataValErrChkBx = True Then
Set ValidErrrng = .Worksheets("Audit
Results").Range(PasteStartCell).Offset(0, ChkbxArray(4, 1)
* 2 - 2)
ValidErrrng.Offset(-1, 0) = "Validation Errors"
End If

For Each sh In .Worksheets
If LCase(sh.Name) <> "Audit Results" Then
'After its been determined that the sheet is not the
'comments sheet, code checks various conditions
'For AuditTypes = 0 To 5
For Each CurObj In ObjCollArray(1)
'Debug.Print CurObj.Parent.Name, CurObj.Address
Debug.Print sh.Name, ObjCollArray
(AuditTypes).Parent.Name
ObjType = TypeName(CurObj)
CollType = TypeName(ObjCollArray(1))
Call MainAudit(2)
Next
'Next
End If
Next
End With