Making an Array Public

[ronphx]

I am trying to declare an array as public so that various subs on a form can use it. However, when I enter something like:

Public ARR(4,4) as variant

in the declaration section of the form's code, then try to assign a value to one of the array's items, I get an error message:

...Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules.

I don't understand what the error message means or how to accomplish what I am trying to do. Does anyone have any ideas?

 

[CajunCenturion]

You can only declare public arrays in the declarations section of a standard code module.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ronphx]

I don't know what a standard code module is. I have subroutines attached to my forms, and I know that there is a module section of the database. Is this what a standard module is? If so, how do I have my form's subroutine code make use of the public array declared in this section? If neither of these is what you are referring to, please explain.

Thanks.

 

[edsearl]

When I try to run the following code I get
"Run time error 3265 Item not found in this collection"
at "If xmax < d3y!KVA(i) Then "
There are 36 fields named KVA(1), KVA(2)....KVA(36)in the table.
Where have I gone wrong?

Private Sub Command0_Click()
Dim db As DAO.Database
Dim d3y As Recordset
Dim KVA(36) As Integer
Dim MAX As Single
Dim MIN As Single
Dim xmax As Single
Dim xmin As Single
Dim i As Single

Set db = CurrentDb()
Set d3y = db.OpenRecordset("Devices3YearsT", dbOpenDynaset)

With d3y
Do Until d3y.EOF
xmin = 0
xmax = 0

For i = 1 To 36
If xmax < d3y!KVA(i) Then
xmax = d3y!KVA(i)
End If
If xmin > d3y!KVA(i) Then
xmin = d3y!KVA(i)
End If
Next i
.Edit
!MAX = xmax
!MIN = xmin
.Update
d3y.MoveNext
Loop
End With

End Sub

 

[BNPMike]

If you go into the VBA IDE (Shift -F11?) you can add a new module. That will be a global module. Put functions or procedures in your new module to manage the array and you can share it's data from class modules associated with forms etc. I would suggest you share global procedures which do things for you using the array, rather than try and specifically share the array.

 

[ronphx]

Good advice. That is working much better. Thank you very much.

 

[Zion7]

ronphx, Glad to hear you solved your problem,Excuse me for intruding on your thread here, I'm actually going to answer edsearl's question.

You're referencing the fields incorrectly.

If xmax < d3y("KVA" & i) Then
xmax = d3y("KVA" & i)

And since you used, with d3y, I believe the following syntax will work also.

Do Until .EOF
xmin = 0
xmax = 0

For i = 1 To 36
If xmax < .("KVA" & i) Then
xmax = .("KVA" & i)
End If
If xmin > .("KVA" & i) Then
xmin = .("KVA" & i)


Just for the record edsearl, you know you can start your own thread, from the main page. just curious?