# Question Arr() Vs. Loop

#### Evagrius Ponticus

##### Well-known Member
Hi,

can anyone explain when is it appropriate to us arr(1 to 10) Vs. For i = 1 to 10. I know this may sound like a silly question and expose my ignorance - but I suppose there is no other way to learn :|

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Richard Schollar

##### MrExcel MVP
Hi BT

The only dumb question is the one that isn't asked

arr(1 to 10) is a syntax only used when dimensioning (or Redim'ing) an array. It doesn't loop thru the contents.

#### Evagrius Ponticus

##### Well-known Member
Thanks RichardSchollar, so, if that's the case, can I use arr(1 to 10) but then use a loop for i = 1 to 10 to cycle through arr()?

If it is not an imposition, can you offer an example of how I can do this - if it can be done?

Thanks

#### Richard Schollar

##### MrExcel MVP
Sure:

Code:
``````Dim arr(1 to 10)
'populate array:
For i = 1 to 10
arr(i) = Chr(64+i)
Next i

For i = 1 to 10
Msgbox arr(i)
Next i``````

#### Evagrius Ponticus

##### Well-known Member
Okay, Rschollar, is this how I interpret the code.

arr = (1 to 10)

You assign the 1 to 10 in the arr() to a variable - that would be i

then use a loop to cycle through i, right.

But why doesn't this work?

Code:
``````For i = 1 To 10
i = Chr(64 + i)
Next i

For i = 1 To 10
MsgBox i
Next i
End Sub``````

#### Richard Schollar

##### MrExcel MVP
i isn't an array - it is a simple variable. What is happening in the first elements of your code is reasonably complex: When you run thru:

Code:
``````For i = 1 To 10
i = Chr(64 + i)
Next i``````

You initially start off storing the value 1 in i (from For i = 1 to 10). But then you assign a new value to i with i = Chr(64+i) . Here you are storing a character ("A") in i (Chr(64+1) represnts the letter "A"). This proceeds OK, but on the next line (Next i) you will get a type mismatch error. I believe this results because, even though i is implicity (or explicitly, depending on whether or not you have defined i) defined as a variant, the For Next loop constrains the type of i as a numeric at each iteration of the loop. When it encounters i = "A" on the Next i line it thus errors out with Type Mismtach error.

Anyway, the important point to draw is that i is NOT an array.

Only arr() is defined as an array. Therefore i is not capable of storing multiple values at the same time - it can only hold one value at any one time (although you can modify this single value by assigning another value to i eg via:

Code:
``````i = 1
i = 2
i = 3
'etc``````

#### shamas21

##### Active Member
Hi Richard

Can you tell me what I could do if i didnt know the size of my array, i.e. sometimes i need to populate my array with 5 values, sometimes more, or less, so whats the way around this?

i.e. Arr(5) - this assings 6 values to it
but sometimes i dont know how many values i need to assign to it.

Thanks

#### Evagrius Ponticus

##### Well-known Member
Thanks Schollar - that makes some sense - I will have to read this several times for it to take hold in my mind - trying to learn this material at times leaves me feeling like my brain is in knots - or convoluted loops

#### Richard Schollar

##### MrExcel MVP
Hi Shamas

If you declare your array as a dynamic array like so:

Code:
``Dim myArray()``

then later on when you want to use it you can use the Redim statement to give it dimensions. But you can also subsequently use Redim again with the optional Preserve keyword to resizze the array 9eg to make it bigger) but also to keep the existing values already loaded into the array:

Code:
``````Redim myArray(1 to 10)

'do stuff with array
'....

'Realise you actually need 20 placeholders - use Redim again AND Preserve to keep exisiting values:

Redim Preserve myArray(1 to 20)``````

Great thanks

Replies
10
Views
122
Replies
0
Views
206
Replies
5
Views
187
Replies
5
Views
208
Replies
0
Views
75

1,191,717
Messages
5,988,259
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back