Array variable setup

I want to create a multi dimential dynamic array variable called Status() as shown below.  

Status(Row, MorN, NoOrState, DateModYN)

Sub Test1() works okay. But Test2() errors at:  ReDim Preserve Status1(2, 2, 2, 2) '*********

How can I correct this?

Thank you.


Sub test1()
 
    Dim Status1() As String
    ReDim Preserve Status1(1, 2, 2, 2)
 
    Status1(1, 1, 1, 1) = "101"
    Status1(1, 2, 1, 1) = "N"
    Status1(1, 1, 2, 1) = "No"
    Status1(1, 1, 1, 2) = "ModY"
 
    MsgBox Status1(1, 1, 1, 1)
    MsgBox Status1(1, 2, 1, 1)
    MsgBox Status1(1, 1, 2, 1)
    MsgBox Status1(1, 1, 1, 2)
 
End Sub
 
Sub test2()
 
    Dim Status1() As String
    ReDim Preserve Status1(1, 2, 2, 2)
 
    Status1(1, 1, 1, 1) = "101"
    Status1(1, 2, 1, 1) = "N"
    Status1(1, 1, 2, 1) = "No"
    Status1(1, 1, 1, 2) = "ModY"
 
    MsgBox Status1(1, 1, 1, 1)
    MsgBox Status1(1, 2, 1, 1)
    MsgBox Status1(1, 1, 2, 1)
    MsgBox Status1(1, 1, 1, 2)
 
    ReDim Preserve Status1(2, 2, 2, 2) '*********
 
    Status1(2, 1, 1, 1) = "102"
    Status1(2, 2, 1, 1) = "N1"
    Status1(2, 1, 2, 1) = "No1"
    Status1(2, 1, 1, 2) = "ModY1"
 
    MsgBox Status1(2, 1, 1, 1)
    MsgBox Status1(2, 2, 1, 1)
    MsgBox Status1(2, 1, 2, 1)
    MsgBox Status1(2, 1, 1, 2)
 
End Sub

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
This might be it - from the Help File **** --- ****:

"If you use the Preserve keyword, you can resize only the **** last array dimension *** and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(10, 10, 10)
. . .
ReDim Preserve X(10, 10, 15)

Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error."
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What is the error Mike ?

mx
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Run-time error 9
Subscript out of range.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I want to increment ---v
                      Status(Row, MorN, NoOrState, DateModYN)

The others (MorN, NoOrState, DateModYN) will remain 2 always.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
So, instead of Status(Row, MorN, NoOrState, DateModYN) I can have:

Status(MorN, NoOrState, DateModYN, Row)

Because I will need to resize only Row. I will try it on Monday.

Thanks,

Mike
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
you're welcome Mike ....

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.