Word has a default template - the template that new
documents are based on when you have not explicitly selected one -
normal.dot. Well Excel has a default template - it's called book.xlt
and it can be stored in:
Excel 2000
The XLStart folder:
C:\os\Profiles\user_name\Application
Data\Microsoft\Excel\XLStart
The Alternate Startup Folder as defined in
Tools->Options of Excel 2000
Excel 2002 (XP)
The XLStart folder is usually
C:\Program Files\Microsoft Office\Office10\XLStart
The Templates folder is in C:\Documents and
Settings\user_name\Application Data\Microsoft\Templates (on
Windows 2000 / XP)
Custom
Numeric Formats - When you opt for Format->Cell->Number, you
see a dialog which shows a range of categorised number, date formats.
The last category is "Custom". It's odd but Custom is not
empty, it contains several formats, several of them favoured by
accountants. Custom formats are stored in the Workbook file itself. It
is unclear where the first Workbook inherits it's repertoire from.
There is no Collection or Member(s) in the Excel
Object Model that will allow you to retrieve / append or
update the list in the current workbook.
The list of number formats is limited to 196
built-in and custom formats - each format is stored with a single
byte id as identifier. The "Custom" category contains
different ones for different locales and auto-reassigns when a
workbook traverses machines of different locales. (use Google
Newsgroup search for discussion by Guy Boertje)
A guy named Leo
Hauser wrote a clever VBA routine that manages to extract the
list of number formats without needing a Collection.
After some heartache and finally, help from the Excel
programmer's newsgroup, I learnt that in VBA, although the
intellisense and the F2 Object Browser (i.e. the Excel '97 type
library) defines VLOOKUP as Application.Worksheetfunction.VLOOKUP,
this is in error. You have to revert to previous syntax (Excel '95 and
earlier) where it was just Application.VLOOKUP. There are two bits of
good news -
Excel 2000 VBA VLOOKUP now works as indicated.
VLOOKUP does not have to work with any range - it
takes VBA variables and arrays.
Beware of using what you think is an explicit
criterion in Data->Filter->Advanced Filter in the criteria
range. All plain cell values there are implicitly wildcarded on the
right. You need to create calculated criteria to force Excel to work
with the exact criterion value.
VBA that works through a block of cells, detecting
values and then deleting rows works faster if you start from the
bottom of the range.
The RefeEdit ActiveX control in Excel 2000 is broken
as compared with Excel 97 and previous versions - when you click on
the ellipsis, the cell cursor does get focus for the mouse but the
keyboard cursor is focussed modal on the textbox in the RefEdit
control. This prevents you from doing any quick keyboard tricks to
highlight a contiguous range.
Range Names are less used in Excel as they used to be
in Lotus 1-2-3. Nifty things that you should know about range names
are:
set rng3=Application.Intersect(rng1,rng2)
set rng3=Application.Union(rng1,rng2)
Source: Excel online Help.
Natural Language formulae - "When you create
a formula that refers to data in a worksheet, you can use the
column and row labels in the worksheet to refer to the data. For
example, if a table contains sales amounts in a column labeled
Sales and a row for a division labeled Support, you can find the
sales amount for the Support division by entering the formula =Support
Sales. The space between the labels is the
intersection operator, which designates that the formula
should return the value in the cell at the intersection of the row
labeled Support and the column labeled Sales."
"When you have labels for the columns and rows on your
worksheet, you can use those labels to create formulas that refer
to data on the worksheet. If your worksheet contains stacked
column labels ¾ in which a label in one cell is followed by
one or more labels below it ¾ you can use the stacked labels
in formulas to refer to data on the worksheet. For example, if the
label Projected is in cell E5 and the label 1996 is in cell E6,
the formula =SUM(Projected 1996) returns the total value for the
Projected 1996 column. If row 8 contains sales amounts and the
label Sales is in cell D8, you can refer to the projected sales
amount for 1996 with the formula =Projected 1996 Sales."
To prevent these problems from occurring, enclose labels in your
formulas within apostrophes ('). For example, instead of this
formula =Charlie Tango
