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







-----Original Message-----
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












-----Original Message-----
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.
--
Regards,
Tom Ogilvy

"ExcelMonkey"
wrote in message
...
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


.

.