Since no one said it overtly, there is no reason you can't declare an array
as a public variable (so you basic premise is incorrect). This simple
example uses both a fixed and dynamic array in a fashion similar to what you
describe.

Public MyArray() As Long
Public MyArr(1 To 10) As String

Sub PerformtheAction()
SetArrays
WriteArrays
End Sub

Sub SetArrays()
ReDim MyArray(0 To 9)
For i = 1 To 10
MyArray(i - 1) = i
MyArr(i) = "Item" & i
Next
End Sub

Sub WriteArrays()
Range("A1:A10").Value = _
Application.Transpose(MyArray)
Range("B1:B10").Value = _
Application.Transpose(MyArr)
End Sub

-------------- Now using 2D arrays ----------------

Public MyArray() As Long
Public MyArr(1 To 10, 1 To 10) As String

Sub PerformtheAction()
SetArrays
WriteArrays
End Sub

Sub SetArrays()
ReDim MyArray(0 To 9, 0 To 9)
For i = 1 To 10
For j = 1 To 10
MyArray(i - 1, j - 1) = i * j
MyArr(i, j) = "Item" & i & j
Next
Next
End Sub

Sub WriteArrays()
Range("A1:J10").Value = _
MyArray
Range("K1").Resize(10, 10).Value = _
MyArr
End Sub

Both worked fine for me.

--
Regards,
Tom Ogilvy

"JPenSuisse" wrote in message
om...
Hello all,

I am hoping that somebody can give me an idea about how one can work
around the fact that arrays can not be made "public variables"
availabe to other subs.

Right now I check the records on a worksheet and count the records in
every counry. Then I write this number into an "array of country
details" using a simple loop. This array contains many properties for
each country along with the number that I write into the array for
each country.

The problem comes when I try to pass this array to a sub which is
supposed to again use a loop to write the data to a worksheet.
Obviously this won't work. And it's ashame, because the loop is a real
elgant and easy of doing this!

One possible solution is to write the data to a worksheet and then
read from the worksheet when I am read to display the data. But this
is a little ugly. Do I have to create some kind of custom object so
that the array is always available throughout my project?

Thanks in advance for the tips!

JP