How to Declare Public Multi-Dimension Arrays

[jamescol]

Look like a cool new forum! I'm developing an Outlook 2003 VBA application. The app contains (so far) 3 custom forms.

There are three multi-dimension arrays I need to make Public to the entire app. I get the following error when I declare the Public array:

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

Here is how I am trying to declare the array:


'These CONSTs define the MAX values of rows and columns in the arrayRenewalDates array
Const MAXROWS_RENEWAL_DATES = 6
Const MAXCOLS_RENEWAL_DATES = 2

Public arrayRenewalDates(1 To MAXROWS_RENEWAL_DATES, 1 To MAXCOLS_RENEWAL_DATES) As Variant

What am I doing wrong?

Thanks,
James

[Richie(UK)]

Hi James,

I'm more of an Excel man myself, but I suspect the same principles apply.

Your public variables should be in a general module rather than a form. Give that a try and let us know how you get on.

[jamescol]

Richie,
Thank you for the response. I did not catch the nuance of the help file about using a module instead of a form. I was able to declare the arrays public after adding a module to my project.

Interestingly, though, now one of my other arrays is not working properly. I am declaring it as follows:

Const DAY180 = 180
Const DAY120 = 120
Const DAY90 = 90
Const DAY60 = 60
Const DAY30 = 30
Const EXPIRE_DAY = 1

'This array contains the names of the Renewal Reminder controls we loop through within the code

arrayRenewalControlNames = Array("DAY180", "DAY120", "DAY90", _
"DAY60", "DAY30", "EXPIRE_DAY")

I am using the array for a couple of purposes. In one instance I use the array elements to build the names of the form controls in a For Next loop, as follows. This code still works after moving the array to the module.

With "txt" & arrayRenewalControlNames(i)
    .Value = arrayRenewalDates(i, 1)
    .Enabled = vbFalse
    .BackColor = vbGrayText
End With
With "cbox" & arrayRenewalControlNames(i)
    .Enabled = vbFalse
    .Value = arrayRenewalDates(i, 2)
    .BackColor = vbGrayText
End With

I am also using the CONSt definitions of the elements to test calculate and dates. This is the code that now does not work since moving the array to the module. BTW I also moved the CONST declarations as well. When I step through the code, arrayRenewalControlNames(i) now evaluates to 0 instead of the CONST value. I've tried casting the value with CInt() and Val(), but I get type mismatch errors when using those functions.

When I was declaring this array inside the procedure, this code worked fine. Any ideas?

If DateDiff(Day_IntervalType, Date, dtRenewalDate) >= arrayRenewalControlNames(i) Then
    ' There is suffucient time to schedule the appointment, so calculate the date and set the checkbox flag to True
    arrayRenewalDates(i, 1) = AdjustWeekendDate(DateAdd(Day_IntervalType, -(arrayRenewalControlNames(i)), dtRenewalDate))

[Anne Troy]

JamesCol: See what happens when I use the VBA tag on your code above? Just hit the little green and white VBA button above the reply box and paste your code between the two little vba and /vba tags that are created.

Cool, huh?

[Daniel Klann]

Hi James,

You're surrounding each of your constants with " marks. Therefore VBA is creating an array of strings; this is what it looks like when you loop through each element in the array:-

Sub Test()
    Dim arrayRenewalControlNames
    Dim i As Long
    arrayRenewalControlNames = Array("DAY180", "DAY120", "DAY90", "DAY60", "DAY30", "EXPIRE_DAY")
    For i = LBound(arrayRenewalControlNames) To UBound(arrayRenewalControlNames)
        Debug.Print arrayRenewalControlNames(i)
    Next
End Sub

Results:-

DAY180
DAY120
DAY90
DAY60
DAY30
EXPIRE_DAY

If you remove the speech marks then the array will be created with the numeric values that you need e.g.

arrayRenewalControlNames = Array(DAY180, DAY120, DAY90, DAY60, DAY30, EXPIRE_DAY)

Results:-

180
120
90
60
30
1


HTH
Dan

[jamescol]

Daniel - thanks for the pointer; it was exactly the problem. It created another challenge for me, though. I posted it as a separate thread.

Dreamboat - thanks for pointing out the VBA tag! It does make the post so much more readable!

James

[Anne Troy]

Great!
I'm going to mark this one solved.