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

"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!

Why?

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?

Range("A1:E5")=myArray

JP,

One way is to declare a variant as a private module level variable.
Then assign the array to the variant.
The array then becomes available to the entire module...
'------------------------------
Private varCountry as Variant

Sub MakeTheArray
Dim arrLocations() as String

'fill the array

varCountry = arrLocations
End Sub
'------------------------------

Jim Cone
San Francisco, CA


"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

First: avoid using variants..
define the type as public in a normal module...
then you can work with "typed" arrays

Option Explicit

Public Type myType
name As String
number As Long
End Type

Sub Test()
Dim TestTypes(10) As myType
Dim Result() As myType

FillNames TestTypes
Result = FillNumbers(TestTypes)
stop
End Sub

Sub FillNames(ManyTypes() As myType)
Dim i
For i = LBound(ManyTypes) To UBound(ManyTypes)
ManyTypes(i).name = "Test" & i
Next
End Sub

Function FillNumbers(ManyTypes() As myType) As myType()
Dim i
ReDim tmp(LBound(ManyTypes) To UBound(ManyTypes)) As myType

For i = LBound(ManyTypes) To UBound(ManyTypes)
tmp(i).name = ManyTypes(i).name
tmp(i).number = i
Next
FillNumbers = tmp

End Function






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


JPenSuisse wrote :

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

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

Tom has already shown you how to use global arrays. You can also pass
them as arguments to procedures. This allows for a modular design,
which in itself has tremendous benefits. Below is an example of how to
process an arbitrary number of files (selected by the user). As with
all of my code, start at the bottom (with MainControl).

Option Explicit

Function Initialize(WBNames() As String) As Boolean
Dim I As Integer, fName
fName = Application.GetOpenFilename( _
"Excel workbooks (*.xls),*.xls", _
MultiSelect:=True)
If Not IsArray(fName) Then Exit Function '<<<<<
ReDim WBNames(LBound(fName) To UBound(fName))
For I = LBound(fName) To UBound(fName)
WBNames(I) = fName(I)
Next I
Initialize = True
End Function
Sub processABook(aWBname As String)
'do whatever
Dim aWB As Workbook
Set aWB = Workbooks.Open(aWBname)
Debug.Print aWB.Name & ", " & Worksheets(1).Range("a1").Value
aWB.Close False
End Sub
Sub Shutdown(WBNames() As String)
Dim I As Integer
On Error Resume Next
Debug.Print "Workbooks analyzed:"
For I = LBound(WBNames) To UBound(WBNames)
Debug.Print WBNames(I)
Next I
End Sub
Sub MainControl()
Dim WBNames() As String, I As Integer
If Not Initialize(WBNames) Then
Else
For I = LBound(WBNames) To UBound(WBNames)
processABook WBNames(I)
Next I
Shutdown WBNames
End If
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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

Hi Folks!

Thanks for all those helpful tips. I'm sure that one of these solutions
will
work, but I need a couple days to think about exactly how. I'm somewhat
new to VBA.

I'll post as soon as I've actually implemented a solution.

Thanks lots!

JP

Powerbook G4, Panther 10.3.6, Office 2004

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Tom,

Thanks to you I have attained unto understanding... I guess I wasn't
paying
close attention. I always tried to put the arrays on a workseet or in
the
workbook. Then I would get the error:

"Constants, fixed-length strings and arras are not allowed as Public
members
of object modules."

In none of my 3 books does it say, you just need to create a new module
and
put the arrays there.... I just seemed to have remebered the word "not
allowed."

We're getting there!

JP

Powerbook G4, Panther 10.3.6, Office 2004

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!