Finding the right help in "Help" has been a problem for me. Thank you.

"Norman Jones" wrote:

Hi Cody,

Does the public variable not work between code in worksheets
and code in modules?


In Module1
'-------------

Option Explicit
Public MyCol As Collection

Sub Demo()
Dim i As Long

Set MyCol = New Collection

For i = 1 To 10
MyCol.Add "Book" & i, CStr(i)
Next i

End Sub

In Sheet1 Module
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1, 2).Value = MyCol(Target.Row)
End If
End Sub


Run the Demo sub to initialize the public Collection.

Then in Sheet1, make successive entries in cells A1 to A10. Now look At
cells B1:B10.

Look in VBA help at 'Public Statement' and 'Understanding Scope and
Visibility'.

---
Regards,
Norman



"Cody" wrote in message
...
Does the public variable not work between code in worksheets and code in
modules? The code is started and finished in the worksheets and uses
modules.

"Cody" wrote:

That is basically how my code is running right now. However, when the
code
in the second module is running the collection does not even appear in
the
locals window as a variable in memory. I tried changing the code to an
array
and I am still having the same problem.

In terms of my code:
The first module opens the second module which loads a form. After the
user
inputs a value in the form the value is added to the array. This loops
until
a certain value is reached. The sub is then exited and goes back to the
code
in the first module. The array does not appear in the locals window in
the
first module.

Thanks for any help!

"Norman Jones" wrote:

Hi Cody,

Apparently collection is not a valid type for Public variables so I
need
to
know how to pass collections between modules.


In Module1
'-------------

Option Explicit
Public MyCol As Collection

Sub Demo()
Dim i As Long

Set MyCol = New Collection

For i = 1 To 10
MyCol.Add "Book" & i, CStr(i)
Next i
End Sub


In Module2
'-------------

Option Explicit
Sub TestIt()
Dim i As Long

For i = 1 To MyCol.Count
Debug.Print MyCol(i)
Next i

End Sub

---
Regards,
Norman



"Cody" wrote in message
...
I have created a collection in a module and I would like to use the
values
in
the collection in another module.

Apparently collection is not a valid type for Public variables so I
need
to
know how to pass collections between modules.

Thanks for any help.