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

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




"Whelan" wrote in message
...
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

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


On 8 Apr, 14:49, Whelan wrote:
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

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

--
Gary''s Student - gsnu200777

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

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

--

"Whelan" wrote in message
...
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

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" wrote in message
...
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

Yes!

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

Whelan

"Ron Coderre" wrote:

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" wrote in message
...
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

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?

"Whelan" wrote in message
...
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

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)


"Shatin" wrote in message
...
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?

"Whelan" wrote in message
...
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

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


On Apr 8, 2:46*pm, "Shatin" wrote:
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?

"Whelan" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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" wrote in message
...
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)


"Shatin" wrote in message
...
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?

"Whelan" wrote in message
...
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

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

Regards,

Ron
Microsoft MVP (Excel)

"Shatin" wrote in message
...
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" wrote in message
...
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)


"Shatin" wrote in message
...
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?

"Whelan" wrote in message
...
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

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

Of course, this line...

FinalValue = ToNumber("AAF")

should have been this...

FinalValue = ToNumber("ZZZ")

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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




"Whelan" wrote in message
...
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

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.

--

"Shatin" wrote in message
...
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" wrote in message
...
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)


"Shatin" wrote in message
...
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?

"Whelan" wrote in message
...
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

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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200777

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" wrote in message
...
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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200777

Below:

"Ron Coderre" wrote:

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


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

Regards,

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


"Charlie" wrote in message
...
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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200777

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)



"Charlie" wrote in message
...
Below:

"Ron Coderre" wrote:

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


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

Regards,

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


"Charlie" wrote in message
...
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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200777

I was mostly just pulling your chain. :)

Yes, I do use Option Explicit, loops such as...
For iRow = iStartRow to iEndRow Step iRowIncr
....and I almost never use hardcoded range references (except as examples).
I primarily use named ranges.

Small loops such as...
For i = 1 To 10
....are fine for a few lines of code. I've never written 400 lines of code
in one sub or function. That's taboo. Either I'm doing something wrong or I
need to break it up into smaller tasks.

Error trapping, on the other hand, is another subject. After 28 years of
programming mostly engineering apps I've found it necessary to invoke the
"Law of Diminishing Returns": The more time I spend writing error-trapping
code the less time I have for problem solving. Most of the time if (when) an
error is trapped I need to debug something, thereby rendering the
error-trapping code obsolete. So I pick and choose those places needing
error trapping carefully.

Funny story regarding your mention of "400 lines down": Eleven years ago at
my last company I was asked to look at someone's VAX Basic (not VB) program
after he had left. It was on the order of 19,000 lines of code! Yes,
19,000... with No comments and No subroutine calls! Sections of code being
reused were cut and pasted appropriately rather than put into a sub or
function. Unbelievable!

Needless to say I declined to "add some features" to the program.

"Ron Coderre" wrote:

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)



"Charlie" wrote in message
...
Below:

"Ron Coderre" wrote:

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


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

Regards,

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


"Charlie" wrote in message
...
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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200777

Well, if I wasn't due for a 'chain yanking',
then I'm just paid ahead now. <g

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

Best Regards, Charlie

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




"Charlie" wrote in message
...
I was mostly just pulling your chain. :)

Yes, I do use Option Explicit, loops such as...
For iRow = iStartRow to iEndRow Step iRowIncr
...and I almost never use hardcoded range references (except as examples).
I primarily use named ranges.

Small loops such as...
For i = 1 To 10
...are fine for a few lines of code. I've never written 400 lines of code
in one sub or function. That's taboo. Either I'm doing something wrong
or I
need to break it up into smaller tasks.

Error trapping, on the other hand, is another subject. After 28 years of
programming mostly engineering apps I've found it necessary to invoke the
"Law of Diminishing Returns": The more time I spend writing
error-trapping
code the less time I have for problem solving. Most of the time if (when)
an
error is trapped I need to debug something, thereby rendering the
error-trapping code obsolete. So I pick and choose those places needing
error trapping carefully.

Funny story regarding your mention of "400 lines down": Eleven years ago
at
my last company I was asked to look at someone's VAX Basic (not VB)
program
after he had left. It was on the order of 19,000 lines of code! Yes,
19,000... with No comments and No subroutine calls! Sections of code
being
reused were cut and pasted appropriately rather than put into a sub or
function. Unbelievable!

Needless to say I declined to "add some features" to the program.

"Ron Coderre" wrote:

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)



"Charlie" wrote in message
...
Below:

"Ron Coderre" wrote:

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


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

Regards,

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


"Charlie" wrote in message
...
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


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200777