Advanced Techniques

Saving

ActiveWorkbook.SaveAs FileName:="C:\Temp\Wbk1.xls" 
ActiveWorkbook.SaveCopyAs "C:\temp\Temp.xls" 
Workbooks("Book2.xls").SaveCopyAs ("C:\temp\Backup.xls") 

Application Caption

Application.Caption = "message" 

Status Bar

It is possible to add your own messages to the status bar.
This can be used as a way to keep the user informed of the current action when a macro takes quite a long time to run.

Application.StatusBar = "Processing .." 
Application.StatusBar = False 

It is important to reset the Statusbar property to False when your macro has finished.
Otherwise your last message will remain in the statusbar until Excel is closed.

Whenever referencing workbooks or worksheets always enclose them in single speech marks "WshName" as they could contain spaces and/or unusual characters.
When you open a workbook using VBA even if you have the Application.DisplayAlerts = False you will be prompted whether to update links. To prevent being prompted change the "updatelinks" property in the Open method.
You can write an event procedure that will work for any workbook that is open but you need to use a class module
If you want something to happen to all the workbooks in a workbook use the "SheetActivate" event for "ThisWorkbook".
In previous versions of Excel the Auto_Open event was used to execute when a workbook is opened. It has since been supplemented / replaced with the Workbook-Open event which is stored in the ThisWorkbook module. Workbook_Open is executed before the Auto_Open event.
Event sequences are not always in an obvious order. The SheetActiveWorkbook event actually occurs before the WorkbookNewSheet. Application event when a new worksheet is added to a workbook.
Workbook event procedures must be in the Code Module for the "ThisWorkbook" Object. If they are anywhere else they will be ignored.
If you want to obtain the number of workbooks that are open you must remember if you use workbooks.count this will include any hidden workbooks (including your Personal.xls) You must check for visibility as well.
If you are referencing a value in another workbook use Workbook(---.Sheets(---).Range().value instead of "Worksheets".

Application.WindowState = xlMinimised 
ActiveWorkbook.Windows.Arrange ArrangeStyle := xlArrangeStyleTiled, SyncHorizontal := True 

ActiveWorkbook.Visible = False 
ThisWorkbook.CustomViews.Add "my view name" 

This returns the file name

ThisWorkbook.Name 

This returns the folder path

ThisWorkbook.Path 

This returns the folderpath and filename

ThisWorkbook.FullName 

To make sure that code works on both Windows and Macintosh use: Since the windows uses "\" and macs use ":".

Application.PathSeparator 

Application.Workbooks.Count 
sFolderPathAndFileName = ThisWorkbook.FullName 
Workbooks(Range("A2").Worksheet.Parent).Name 
?? = Workbooks.Count 
?? = Workbooks.Item(3) 
ThisWorkbook.ChangeFileAccess xlReadOnly 

Workbooks("Book2.xls").RefreshAll 
Set wbktemp = Workbooks.Filename :="temp.xls" 

Speech (new 2002)

Excel 2002 offers a new Speech object that allows the control of automatic speech output and the output of text via the sound card'