# Thread: populate array with letters of alphabet (VBA excel 2002 SP2)

1. ## populate array with letters of alphabet (VBA excel 2002 SP2)

What is the most efficient way of populating an arrary myarray(26,1) with the letters of the alphabet so that

myarray(1,1)="A"
myarray(2,1)="B"
.
myarray(26,1)="Z"

Is it possible to vary the order so that cycle starts at different point?

myarray(11,1)="A"
myarray(12,1)="B"
.
myarray(10,1)="Z"

I realise that I could create 26 lines of code, 1 for each letter but was hoping that there is another way, more like

for x=1 to 9
myarray(x,1)=x
next x

thanks

Simon

2. ## Re: populate array with letters of alphabet (VBA excel 2002 SP2)

For the first half of your question try:

For intLoop = 1 to 26
MyArray(intLoop,1) = chr\$(64+intLoop)
Next

or if you want lowercase letters use

For intLoop = 1 to 26
MyArray(intLoop,1) = chr\$(96+intLoop)
Next

As for the second half of your question, I can't think of any easy way at the moment. I'm having a "mental code block" as it were. I'm sure there are a couple of ways of doing it, I just can't think at the moment.

3. ## Re: populate array with letters of alphabet (VBA excel 2002 SP2)

OK, as soon as I clicked post it, my brain started to work.

Try this for an offset fill:

For intLoop = 1 To 26
Select Case intLoop + intOffset
Case Is <= 26
myArray(intLoop, 1) = Chr\$(64 + intLoop + intOffset)
Case Else
myArray(intLoop, 1) = Chr\$(64 + intLoop + intOffset - 26)
End Select
Next

where intOffset is an integer that specifies how many characters to offset the start of the list.

0 = A B C ... X Y Z
1 = B C D ... Y Z A
2 = C D E ... Z A B
...
25 = Z A B ... W X Y
26 = A B C ... X Y Z

intOffset also needs to be in the range of 0 - 26 otherwise the code probably won't work.

4. ## Re: populate array with letters of alphabet (VBA excel 2002 SP2)

Excellent job,

I got caught up trying to make for x = a to z work and missed the obvious!!

We should all let are brains warm up before doing this kind of stuff..

5. ## Re: populate array with letters of alphabet (VBA excel 2002 SP2)

Even though my brain was asleep at the wheel for a bit.

6. ## Re: populate array with letters of alphabet (VBA excel 2002 SP2)

Or, slightly more simply:

<pre>Dim I As Integer, iOffset As Integer
Dim strMyArray(1 To 26, 1 To 1) As String
iOffset = 10
For I = 1 To 26
strMyArray(I, 1) = Chr(Asc("A") + ((I - 1 + iOffset) Mod 26))
Next I
</pre>

#### Posting Permissions

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