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




-----Original Message-----
Declare A as type Variant, not Object.

--

HTH

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


"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


.