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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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..

Reply | Quote

Glad to help.

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

Reply | Quote

Or, slightly more simply:

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

Reply | Quote