Consulting

Results 1 to 7 of 7

Thread: Solved: How to Declare Public Multi-Dimension Arrays

  1. #1
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location

    Question Solved: How to Declare Public Multi-Dimension Arrays

    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

  2. #2
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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.

  3. #3
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    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:

    [vba]Const DAY180 = 180
    Const DAY120 = 120
    Const DAY90 = 90
    Const DAY60 = 60
    Const DAY30 = 30
    Const EXPIRE_DAY = 1
    [/vba]

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

    [vba]arrayRenewalControlNames = Array("DAY180", "DAY120", "DAY90", _
    "DAY60", "DAY30", "EXPIRE_DAY")
    [/vba]

    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.

    [vba]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
    [/vba]

    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?

    [vba]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))
    [/vba]

  4. #4
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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?
    ~Anne Troy

  5. #5
    VBAX Regular
    Joined
    May 2004
    Location
    Sydney, Australia
    Posts
    36
    Location
    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:-

    [VBA]
    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
    [/VBA]

    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.

    [VBA]
    arrayRenewalControlNames = Array(DAY180, DAY120, DAY90, _
    DAY60, DAY30, EXPIRE_DAY)
    [/VBA]

    Results:-

    180
    120
    90
    60
    30
    1


    HTH
    Dan

  6. #6
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    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

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Great!
    I'm going to mark this one solved.
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •