One of the 'standard' mistakes is unqualified ranges. E.g., code like
Range("A1").Value = 123
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,
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
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
"Noel Volin" <prov...@usinternet.com> wrote in message
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
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.
'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"), _
If Trim(cell.Value) = "" Then
ActiveSheet.Range(cell, cell.Offset(0, 6)).ClearContents
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
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.
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Noel Volin <prov...@usinternet.com> wrote in message