Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


how to redimension array

Posted on 2009-02-24
Medium Priority
Last Modified: 2013-11-28
I have the following:

ReDim Preserve jobCodes(0 To jbMax) As String

and when I compile I get the compile error: Array Already Dimensioned

The message is true: my array *was* previously dimensioned. This is my frist shot at using dynamic arrays, so I'm sure I'm doing something wrong, but I thought one was supposed to use the "Preserve" when the arrary was previously dimensioned. What am I doing wrong?
Question by:jmarkfoley
LVL 85
ID: 23724974
How was your array dimensioned originally? Was it also an array of Strings?
LVL 58

Accepted Solution

harfang earned 750 total points
ID: 23725198
The initial declaration must be made without dimensions. You use 'Preserve' to enlarge a dynamic array which has already been dimensioned once dynamically, if you want to keep the values already entered.

    Dim jobCodes() As String   ' initial declaration for dynamic arrays
    ReDim jobCodes(0 to 3)   ' run-time dimensioning of array
    jobCodes(2) = "foobar"   ' using  the array
    ReDim Preserve jobCodes(0 To jbMax)   ' preserving the first 4

Open in new window

LVL 28

Assisted Solution

TextReport earned 750 total points
ID: 23726441
The Preserve keyword is used when you want to "Preserve" the values already assigned to your Array elements and only works if you are increasing the elements.

You do not need to DIM an array, the ReDim does the job for you.

The following example will print A to F in the Immediate window, if you take out the PRESERVE then you will get 5 blank lines followed by E and F.

Are you changing the defined type, say to string from variant?

Finally, what version of Access are you using my tests were in Access 2007 but I am fairly sure the previous versions were the same.

Cheers, Andrew

Dim cnt As Long
ReDim arya(0 To 4) As String
Dim jbmax As Long
    For cnt = 0 To 4
        arya(cnt) = Chr(65 + cnt)
    Next cnt
    jbmax = 6
    ReDim Preserve arya(0 To jbmax) As String
    For cnt = 5 To jbmax
        arya(cnt) = Chr(65 + cnt)
    Next cnt
    For cnt = 0 To jbmax
        Debug.Print arya(cnt)
    Next cnt

Open in new window

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 58

Expert Comment

ID: 23727382
> You do not need to DIM an array

That is true. Option Explicit will not catch the problem. Formally (from the VB help file), you should not use it that way:

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).

The problem becomes apparent in the snippet. The typo isn't identified, and the code breaks only at run-time. I don't know why the VB compiler was made to imply Dim when reading ReDim, these are quite different operations, and no other language makes that particular confusion. Anyway, you are right:

    ReDim Something(12) As String

Is interpreted as

    Dim Something() As String
    ReDim Something(12)

Stupid, but there you go...

Sub Manipulate(pvarMyArray() As Variant)
    ReDim pvarMyAray(12)
    pvarMyArray(12) = "something"
End Sub
Sub ShowProblem()
    Dim Values() As Variant
    Manipulate Values
End Sub

Open in new window

LVL 28

Expert Comment

ID: 23727495
harfang, I always use Option Explicit and would always recoment it's use for trapping the logic error that are caused by a stupid typo. Everyone should have this option turned ON in your VBA Options (goodnes knows why the default is OFF)
Cheers, Andrew

Author Closing Comment

ID: 31550717
didn't implement any solutions

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question