Array being dim with a variable?

[lownote14]

OK so my title is a little weak, but I'm not exactly sure what to call my question.

From what I know of an array to create you do the same thing when you dimension a variable, just with an added parenthesis and a number to determine how many points of storage on in that array.
Example:

dim array (10) as long

Ok I also know that during a for-next loop you can use a variable to call that a specific point of a variable.
Example?:

dim int as long
for int = 1 to 10
     array (int)= something
next int

Now my question is, when I declare the array can I declare the amount of space with a variable? For example could i do something like this:

dim int as long 'assume that this variable is given a specific number at some point

dim array (int) as long

I have given this a bit of try but can't seem to get it to work.

 

[tusharm]

Search Excel VBA help for ReDim.

 

[lownote14]

I should have clarified, I have a pretty simple understanding of VBA, I think. I am completely self taught so I have no idea how much I truly understand to the average joe programmer.

What I do know is that I find the VBA help to be at times intimidating, overcomplicated, and sometimes just plain confusing.

With that in mind I have to momentarily rant about the internet and it's uses. The internet allows us to do great things, and communicate with people all over the world, and gain knowledge from them, as this website so beautifully shows. One of the few draw backs to the internet is the lack of actual visual or auditory communication, as far as written message boards go. What this means is when you post a comment it is difficult to interpret what a person means unless they are very clear and straight to the point.

So a comment like:

Search Excel VBA help for ReDim.

Could have many interpretations.

What that long rant comes to really is are saying "Yes your question will work if you use that function."

I did look it up on VBA, but it doesn't seem to say anything about the subscripts being able to be a variable.

Also know that I am not trying to insult anyone, I have noticed that this kind of communication becoming more and more prevalent on this site and others. I think it hampers communication and should be avoided as much as possible.

Just food for thought.

 

[tusharm]

No problem. You are entitled to your opinion.

However, since I *know* that Redim is what you have to use *and* I *know* that the Redim explanation in help is quite good, I saw no reason to reinvent the wheel.

But, if you find it "intimidating, overcomplicated, and sometimes just plain confusing" I'm sure someone will be along who will explain Redim to you in a friendly, simple, and straightforward manner.

All the best to you.

I should have clarified, I have a pretty simple understanding of VBA, I think. I am completely self taught so I have no idea how much I truly understand to the average joe programmer.

What I do know is that I find the VBA help to be at times intimidating, overcomplicated, and sometimes just plain confusing.

With that in mind I have to momentarily rant about the internet and it's uses. The internet allows us to do great things, and communicate with people all over the world, and gain knowledge from them, as this website so beautifully shows. One of the few draw backs to the internet is the lack of actual visual or auditory communication, as far as written message boards go. What this means is when you post a comment it is difficult to interpret what a person means unless they are very clear and straight to the point.

So a comment like:

Could have many interpretations.

What that long rant comes to really is are saying "Yes your question will work if you use that function."

I did look it up on VBA, but it doesn't seem to say anything about the subscripts being able to be a variable.

Also know that I am not trying to insult anyone, I have noticed that this kind of communication becoming more and more prevalent on this site and others. I think it hampers communication and should be avoided as much as possible.

Just food for thought.

 

[ukmikeb]

Now my question is, when I declare the array can I declare the amount of space with a variable? For example could i do something like this:

dim int as long 'assume that this variable is given a specific number at some point
 
dim array (int) as long

Hi

You can't do that because VBA expects a constant.

If you know how many elements there are in the the Array you can define it either as a fixed number or as a variable as follows -

dim array (1 to 10) as long

What Tusharm was trying to explain was that having defined an array with a limit on the number of elements you are not tied to that number and that you have the option with ReDim to vary that limit.

I hope that makes the point much clearer.

 

[Richard Schollar]

What Tusharm was trying to explain was that having defined an array with a limit on the number of elements you are not tied to that number and that you have the option with ReDim to vary that limit.

That isn't actually true Mike - if you have the following:

Dim myArr(1 To 100)

Then array myArr is only ever going to be 100 elements in size. MyArr is static. You can't use ReDim on it.

If, however, you dimension it like this:

Dim myArr()  'Dynamic array!
 
 
ReDim myArr(1 To 100)
 
ReDim myArr(0 to 20)

Then myArr is Dynamic and you can redimension it with the ReDim statement.

LowNote, Tusharm was making the point that you can use ReDim rather than Dim and then you can use dynamic elements to specify the size:

Sub MySub()
 
Dim i As Long, j As Long
 
i = 100
j = 4000
 
ReDim myArr(1 To i)  'note not Dim of myArr
 
'other code
'...
 
'redim myArr again:
 
ReDim myArr(0 To j)

Hope that helps!

 

[Sektor]

In addition to Richard Schollar, I'd like to say that if you want to resize AND keep items you should write this:

ReDim [B][COLOR="Red"]Preserve[/COLOR][/B] myArr(0 To j)

Otherwise all your items are lost forever.

 

[lownote14]

HUZZA!! Thank you that has worked beautifully, thank you for clarifying that for me.

Also apologies for the rant before didn't mean to sound hostile at all. I was tired and over heating form the impending heat wave in my area. I'll avoid posting in such a condition again.

Many thanks to all of for enlightening me.

 

[RoryA]

An additional comment regarding Preserve:
If you use Preserve you can only alter the upper bound of the last dimension of the array. You also cannot change the number of dimensions at all (e.g. you can't go from 1D to 2D).

Sub arraytest()
    ' declare dynamic array
    
    Dim aData() As String
    
    ' redimension
    
    ReDim aData(1 To 10) ' this works
    
    ' assign a value to first element
    
    aData(1) = "test"
    
    ' redimension without preserving = all data lost
    
    ReDim aData(0 To 5) ' this works
    
    ' redimension without preserving and change number of dimensions
    
    ReDim aData(1 To 3, 1 To 3) ' this works
    
    ' assign first element again
    
    aData(1, 1) = "test"
    
    ' redimension last dimension and preserve
    
    ReDim Preserve aData(1 To 3, 1 To 100) ' this works - only upper bound of last dimension changed
    
    ' The following all fail with the Preserve keyword
    
    ReDim Preserve aData(1 To 3, 1 To 100, 1 To 1) ' this fails - cannot change number of dimensions if preserving
    
    ReDim Preserve aData(1 To 3, 2 To 100) ' this fails - cannot change lower bound of any dimension if preserving
    
    ReDim Preserve aData(1 To 5, 1 To 3) ' this fails - cannot change any dimension other than the last if preserving

End Sub