Three Letter List

[Whelan]

Hello, I hope you can help me with this.

I want to create in Excel a three letter list Starting in field cell A1 with
AAA, cell A2 being AAB, Cell A3 being AAC, and ending up with ZZZ. Rather
than typing each out I was just going to code it in VB. However I've come up
short. Way short. I'm thinking a loop is required or a Do Until command, but
can't seem to get the exact process right.

Any advice would be welcome!

Thanks,

Whelan

 

[Rick Rothstein \(MVP - VB\)]

You can use the code (one subroutine, two functions) following my signature
to do what you ask. You simply execute the InsertThreeLetters macro and it
will call the two functions as needed.

Rick

Sub InsertThreeLetters()
Dim X As Long
Dim StartValue As Long
Dim FinalValue As Long
StartValue = ToNumber("AAA")
FinalValue = ToNumber("AAF")
For X = 0 To FinalValue - StartValue
Cells(X + 1, "A").Value = ToAlpha(StartValue + X)
Next
End Sub

Function ToNumber(Value As String) As Variant
Dim X As Integer
If Format$(Value, "@@@@@@@@@@@@") > "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For X = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, X, 1))) - 64) * _
26 ^ (Len(Value) - X)
Next
End If
End Function

Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) > 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value > 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function

 

[Andrew Taylor]

Try this:

Sub MakeList()
Dim i As Integer, j As Integer, k As Integer
Dim ascA As Integer
ascA = Asc("A")
For i = 0 To 25
For j = 0 To 25
For k = 0 To 25
Cells(i * 26 * 26 + j * 26 + k + 1, 1) = Chr(ascA + i)
& Chr(ascA + j) & Chr(ascA + k)
Next
Next
Next
End Sub

 

[Gary''s Student]

Sub ThreeLetter()
l = 1
For i = 65 To 90
x = Chr(i)
For j = 65 To 90
y = Chr(j)
For k = 65 To 90
z = Chr(k)
Cells(l, 1).Value = x & y & z
l = l + 1
Next
Next
Next
End Sub

 

[stew]

Hi,

The following works for me....probably not the most elegant but I
guess you will only run it once or twice!

Stew


Sub looper()

Application.ScreenUpdating = False


Range("A1").Select
count1 = 65
While count1 < 91
firstvalue = Chr(count1)

Count2 = 65
While Count2 < 91
SecondValue = Chr(Count2)

count3 = 65
While count3 < 91
thirdvalue = Chr(count3)
output = firstvalue + SecondValue + thirdvalue
ActiveCell.Value = output
ActiveCell.Offset(1, 0).Select
count3 = count3 + 1
Wend
Count2 = Count2 + 1
Wend
count1 = count1 + 1
Wend


Application.ScreenUpdating = True

End Sub

 

[PCLIVE]

Another way. This will start at cell A1.


r = 1
For a1 = 1 To 26
b1 = Mid(Cells(1, a1).Address, 2, 1)
For a2 = 1 To 26
b2 = Mid(Cells(1, a2).Address, 2, 1)
For a3 = 1 To 26
Range("A" & r).Value = _
b1 & b2 & Mid(Cells(1, a3).Address, 2, 1)
r = r + 1
Next a3
Next a2
Next a1

Regards,
Paul

 

[Ron Coderre]

Perhaps this:

Sub Build3AlphaSeq()
Dim iChar_1 As Integer
Dim iChar_2 As Integer
Dim iChar_3 As Integer
Dim cStartCell As Range
Dim iCtr As Integer

On Error GoTo errTrap
Set cStartCell = Selection.Cells(1, 1)
iCtr = 0
For iChar_1 = 65 To 90
For iChar_2 = 65 To 90
For iChar_3 = 65 To 90
cStartCell.Offset(RowOffset:=iCtr).Value = _
Chr(iChar_1) & Chr(iChar_2) & Chr(iChar_3)
iCtr = iCtr + 1
Next iChar_3
Next iChar_2
Next iChar_1
Exit Sub
errTrap:
MsgBox "Problem encountered completing list"
End Sub

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Whelan]

Yes!

In fact they all work. Thank you so much for you help on this, guys.

Whelan

 

[Shatin]

I tried all the subs posted. While they work, they are all very very slow. I
wonder if someone can come up with a much faster algorithm?

 

[Ron Coderre]

Try this amended code....it now includes lines that
set Excel Calculation to "manual" while building the list
then restores the setting to its previous value

Sub Build3AlphaSeq()
Dim iChar_1 As Integer
Dim iChar_2 As Integer
Dim iChar_3 As Integer
Dim cStartCell As Range
Dim iCtr As Integer
Dim vCalcSetting
'Record the current Calculation setting
vCalcSetting = Application.Calculation

On Error GoTo errTrap
Application.Calculation = xlCalculationManual
Set cStartCell = Selection.Cells(1, 1)
iCtr = 0

For iChar_1 = 65 To 90
For iChar_2 = 65 To 90
For iChar_3 = 65 To 90
cStartCell.Offset(RowOffset:=iCtr).Value = _
Chr(iChar_1) & Chr(iChar_2) & Chr(iChar_3)
iCtr = iCtr + 1
Next iChar_3
Next iChar_2
Next iChar_1
'Restore the Calculation setting
Application.Calculation = vCalcSetting
Exit Sub
errTrap:
'Restore the Calculation setting
Application.Calculation = vCalcSetting
MsgBox "Problem encountered completing list"
End Sub

Does that work better?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Susan]

only tried Ron's & Gary's Student's but both took about 3
seconds...........
susan

 

[Shatin]

What a big difference the change makes! Works much faster than before! The
previous code takes forever, this code one only a few seconds!

 

[Ron Coderre]

You're very welcome.....I'm glad that worked.

Regards,

Ron
Microsoft MVP (Excel)

 

[Rick Rothstein \(MVP - VB\)]

Here is my code which implements the same speed up that Ron posted... it
took less than 3 seconds for my code to fill the column.

Rick

Sub InsertThreeLetters()
Dim X As Long
Dim StartValue As Long
Dim FinalValue As Long
Dim vCalcSetting As Long
'Record the current Calculation setting
vCalcSetting = Application.Calculation
On Error GoTo errTrap
Application.Calculation = xlCalculationManual
StartValue = ToNumber("AAA")
FinalValue = ToNumber("ZZZ")
For X = 0 To FinalValue - StartValue
Cells(X + 1, "A").Value = ToAlpha(StartValue + X)
Next
Exit Sub
errTrap:
'Restore the Calculation setting
Application.Calculation = vCalcSetting
MsgBox "Problem encountered completing list"
End Sub

Function ToNumber(Value As String) As Variant
Dim X As Integer
If Format$(Value, "@@@@@@@@@@@@") > "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For X = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, X, 1))) - 64) * _
26 ^ (Len(Value) - X)
Next
End If
End Function

Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) > 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value > 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function

 

[Rick Rothstein \(MVP - VB\)]

Of course, this line...

FinalValue = ToNumber("AAF")

should have been this...

FinalValue = ToNumber("ZZZ")

Rick

You can use the code (one subroutine, two functions) following my
signature to do what you ask. You simply execute the InsertThreeLetters
macro and it will call the two functions as needed.

Rick

Sub InsertThreeLetters()
Dim X As Long
Dim StartValue As Long
Dim FinalValue As Long
StartValue = ToNumber("AAA")
FinalValue = ToNumber("AAF")
For X = 0 To FinalValue - StartValue
Cells(X + 1, "A").Value = ToAlpha(StartValue + X)
Next
End Sub

Function ToNumber(Value As String) As Variant
Dim X As Integer
If Format$(Value, "@@@@@@@@@@@@") > "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For X = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, X, 1))) - 64) * _
26 ^ (Len(Value) - X)
Next
End If
End Function

Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) > 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value > 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function

Hello, I hope you can help me with this.

I want to create in Excel a three letter list Starting in field cell A1
with
AAA, cell A2 being AAB, Cell A3 being AAC, and ending up with ZZZ. Rather
than typing each out I was just going to code it in VB. However I've come
up
short. Way short. I'm thinking a loop is required or a Do Until command,
but
can't seem to get the exact process right.

Any advice would be welcome!

Thanks,

Whelan

 

[PCLIVE]

Maybe it was your system. Though I didn't try the other suggested codes,
when I tried my code, it completed in less than 3 seconds.

--

 

[Charlie]

Several slick methods here. I thought yours was the simplest, cleanest. I
shortened it even more and it ran in about one second.

Application.ScreenUpdating = False
Range("A1").Activate

For i = 65 To 90
For j = 65 To 90
For k = 65 To 90
ActiveCell = Chr(i) & Chr(j) & Chr(k)
ActiveCell.Offset(1, 0).Activate
Next k
Next j
Next i

Application.ScreenUpdating = True

 

[Ron Coderre]

Not to pick on anybody in particular, but
I cringe when I see code with:
- Undeclared variables
- Cryptic variables (declared or not)
- Hardcoded references
- Unnecessary range selections
- and no error traps if there's a risk of crashing

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Charlie]

Below:

Not to pick on anybody in particular, but
I cringe when I see code with:
- Undeclared variables

How do you know they were undeclared? The code submitted was a "snippet."
I figured readers could declare (or not) their own variables.

- Cryptic variables (declared or not)

i, j, k, real cryptic for loop counters. Long Live "For i = 1 To 10"

- Hardcoded references

if you're referring to Range("A1"), once again, snippet (example). At least
according to the OP's request it will work, whereas Selection.Cells(1, 1)
might not.

- Unnecessary range selections

you may win on this one. FTR, I didn't select the cells I activated them.
Maybe you can explain the difference. I don't know the answer to this one.

- and no error traps if there's a risk of crashing

how do you know there wasn't an error trap set? Snippet, remember? Let me
go back and review the snippet for a "risk of crashing"... Nope, I see none.

But thanks for the critique.

 

[Ron Coderre]

Hi, Charlie

I *did* say that I wasn't criticizing anybody
in particular...and I'm still not.

However, since this is a peer-help newsgroup, I feel it's important
to be as instructional as possible. People are reading what we
suggest and may be assuming that all pertinent information is being
provided and that the code offered is "professional".

So....
Undeclared variables:
I don't know that the variables weren't declared and neither
does anyone else....but being an experienced programmer I *know*
that they should be. It's easy enough to just declare them to
avoid any ambiguity...or..even better..include Option Explicit
at the top of the module to force variable declarations. It
may not matter for a 10 line program, but we develop our coding
habits when learning to build small programs. As the code gets
more complex, undeclared variables can be a nightmare to debug.
Especially when typos find their way into the code:
iStart vs iStrt vs iStrat

Cryptic variables:
400 lines down in the code you see:
For x = y to z Step j
For a = b to c

IMHO...that's a habit that comes from taking programming courses,
where all that matters is compilation and execution and no thought is
given to code maintenance or transfer of code ownership.

If I inherited somebody else's code, I would certainly prefer to see:
For iRowCtr = iStartRow to iEndRow Step iRowIncr
For EmpCtr = StartEmp to EndEmp

An added benefit is that I could search the project for instances
of "StartEmp"...searching for "b" would be impractical.

Hardcoded references:
They have their place....but, best practice is to avoid them.
Then, if the ref changes you don't have to scour the code to find all
the instances to edit. It was a general commment, not a critique.
Specific circumstances dictate usage.

Unnecessary range selections:
You had a question about the difference between SELECT and ACTIVATE.
Type this in the Immediate Window: Range("z5").Activate
now try: Range("z5").Select
Both versions *select* cell Z5 on the active sheet.

Error Traps:
Generally a good practice. Regarding the post, though, we are
building a long list. It's possible the sheet may be protected
or the list is to start near the bottom of the sheet with not enough
cells to complete the list.

Thanks for your comments.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)