Excel In Depth
Home ] Access - Start Here ] [ Excel In Depth ] MS Office ] Visio Info ] Creating Web Pages ] Technical References ] Viruses ] Points of View ] Gadgets ] Just Links ] Monash ]

   Search this site or the web       powered by FreeFind
 
  Site search Web search

Participate in Ananda's Discussions
Post a message

Monitor page
for changes
    
   it's private  

by ChangeDetection

Excel Topics

Add-Ins
Macros
User Forms
Misc

Excel sites

Excel Reading List

VBA for Dummies  This is a fun introduction to VBA.
Microsoft Visual Basic for Applications Language Reference (Office 97) Free MSDN web resource.
Microsoft® Excel 97 Developer's Handbook The definitive Excel VBA series of books by Eric Wells and Steve Harshbarger.
Excel 2000 VBA Programmer's Reference By John Green, Stephen Bulleen, Felipe Martins, Brian Johnson
Excel 2002 VBA Programmer's Reference By Rob Bovey, Stephen Bulleen, John Green, Robert Rosenberg
Excel 2002 Power Programming with VBA A Walkenbach classic
Advanced modelling in finance using Excel and VBA Haven't seen this one - looks like a niche book
Business Analysis with Microsoft Excel (2nd Edition) Not only does it go through Accounting, Investment, it also covers Quality Control

Excel Frequently Asked Questions

Quickies

FAQs
Reading List
XLEvents

Math & Modelling

Interval Solver Office Update
Optimization using Solvers - Optimizers from Frontline Systems
Universal Technical Systems - TK Solver
The Multi-Cell Goal Seeker
The Sensitivity Analyzer
The Risk Analyzer
Excel Business Tools - Purpose built Excel templates for financial analysis and business decision-making.

Nifties and Tips

  • 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)

    See:

  • 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

      use this formula:

         ='Charlie' 'Tango'
    • Cell references examples:
      Range("A:A")
      Range("1:1")
      Cells(1,"A")

     

  • To work with file dialogs that interact with the user but don't perform the actual action, instead, returning the filename, use
    strfilename=Application.GetOpenFilename (see XL97 How to Use the GetOpenFilename Method)
    strfilename=Application.GetSaveAsFilename (see XL97 GetSaveAsFilename Method Returns Extra Character)
    Also, see XL97 Macro to Change File Type to . in Open Dialog Box
  • bytePageCount = ExecuteExcel4Macro("Get.Document(50)")