Question for Developers - Classic Rookie Mistakes??

[Noel Volin]

Hello,
I am wondering what types of mistakes rookie's generally make. As
developers, I'm sure you've had plenty of experience modifying someone
else's code. Outside of lack of documention (comments), what other things
come to mind? Thanks for your reponses.

Noel Volin

[Chip Pearson]

Noel,

One of the 'standard' mistakes is unqualified ranges. E.g., code like

Sub AAA()
Range("A1").Value = 123
End Sub

Here, the developer assumed that some particular workbook and some
particular sheet would be active. Better and safer to fully qualify the
range as ThisWorkbook.Worksheets("SomeSheet").Range("A1").

It is a safe assumption that the user will never have the proper sheet
active, regardless of what your help files may tell them to do.

Another common mistake, one I've made more often than I'm going to admit, is
using the End property without testing whether it is taking you to row 64K.
E.g., writing code like

Dim SomeRange As Range
Set SomeRange = Range(ActiveCell, ActiveCell.End(xlDown))

If the ActiveCell is the last used cell in the column, you've just set
SomeRange to include all cells down to row 65536, which is probably not what
you really want. A typical workaround is something like

Set SomeRange = Range(ActiveCell,
Cells(Rows.Count,ActiveCell.Column).End(xlUp) )
or
Set SomeRange = Application.Intersect(ActiveSheet.UsedRange,
Range(ActiveCell, ActiveCell.End(xlDown)) )

Another common mistake is assuming that the Find method will always find
something. Often, you'll see code like

Range("A1:A100").Find(...).Font.Bold = True

which assumes that Find actually finds something. Better to write code
like

Dim FoundCell As Range
Set FoundCell = Range(...).Find(...)
If Not FoundCell Is Nothing Then

These are a few of the mistakes I've seen (and made!) fairly often. Hope
this helps.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Noel Volin" <prov...@usinternet.com> wrote in message
news:so72h6t...@corp.supernews.com...

[Harald Staff]

Noel

Adding to Chhip a few bad habits that comes to mind:
In the Visual Basic Editor the "Require Variable declaration" is by default
off, and few bother to turn it on. This makes code writing easier and
searching for errors almost impossible. Turning it on (Tools > Options >
Editor menu in VBE) puts Option Explicit on top of every module, and this
will prompt for all errors in variable types and the lack of declarations.

Another common thing is using recorded macros quite unmodified. This will
contain a lot of cell and range selections that are unneccessary, slow and
quite desturbing when it runs.

Best wishes Harald

Noel Volin <prov...@usinternet.com> skrev i

[David McRitchie]

One of the bigger problems for people starting out with
VBE, who are familiar with other languages is that there
are different forms of IF.

Following up on Harald's comments:
One thing I learned about Excel 2000 was that the
"Require Variable declaration" was not turned off by default
so I decided that I should try to define all my variables
rather than turn it off when I switched from XL95 to XL2000.

These are not Rookie mistakes but suggestions in
improving examples for rookies, which also follows
through from what Harald mentioned..

One bad habit of many experienced posters is in
not providing generic coding that can be used with
a selected range rather than some specific range as
would be generated by the macro recorder.

Not much of a problem in the programming group but
not including SUB with the code might confuse those
in the excel.misc group. I might add that when I go
looking for previously posted solutions I include SUB
in my search of the *excel* groups in Deja.

Following is an example of a solution I provided to
someone who did not post the newsgroup, but eliminates
specific coding with row numbers on column G. I fixed
up my RANGE(...) after reading Chip's post. Though this
would really only be used on the active sheet. Since the
columns G to M are to be cleared this would be as generic
as I can provide. Normally would use the current selection
in most of my macros but still limited to the used range.

Sub MoAli1()
'Clear out values in Gx:Mx when value in col G appears empty
'see http://www.geocities.com/davemcritchie/excel/delempty.htm 2000/07/29
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Application.Intersect(ActiveSheet.Range("g:g"), _
ActiveSheet.UsedRange)
If Trim(cell.Value) = "" Then
ActiveSheet.Range(cell, cell.Offset(0, 6)).ClearContents
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Note: Test for ISEMPTY(cell.value) not needed with TRIM(cell.value)
but might be useful for modified code. Certain aspects of code can
be found in examples in slowresp.htm, turning calculation off for speed;
proper.htm, see comments at top, including use of INTERSECT;
join.htm, general information, include MarkCells used to create test data.

HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm

[Noel Volin]

Thank you all for your input. It's always good to know ahead, what's most
commonly done inefficiently.

Noel Volin

Noel Volin <prov...@usinternet.com> wrote in message