Whilst being OK for the occassional macro, VBA has its limits for full projects. The syntax is more limited than that of VB and I've found stability problems with Excel VBA. However, Excel is a very useful data storage medium and the Excel classes were designed to allow this use from within a VB based application.
There are two classes, ExcelApplication and ExcelWorkbook.
ExcelApplicationThis opens Excel if necessary and creates a collection for ExcelWorkbook objects. Ony one ExcelApplication object can exist at a time.
ExcelWorkbookThis allows workbooks to be opened and manipulated. Books are defined as 'With Events' so that use can be made of Excel events.
The class has the following methods and properties.
METHODSName | Parameters | Operation |
open_book | Book name | Opens the specified book. |
clear_sheet | Clears the contents of the current sheet. | |
save_book | Saves the open book. | |
close_book | Closes the open book. |
Name | Parameters | Operation |
get_book | Returns a reference to the open book object. | |
sheet_name | Returns the name of the active sheet. | |
get_sheet | Returns a reference to the active sheet. | |
select_sheet | Sheet name | Selects the sheet and returns a reference to it. |
set_sheet | Sheet name | Returns a reference to the specified sheet object WITHOUT selecting it. |
set_range | Sheet name, (r1, c1), (r2, c2) | Returns a reference to the defined range on the specified sheet WITHOUT selecting it. |
The class contains a procedure for only one event, 'xlBk_SheetActivate', which calls a main program procedure, 'select_sheet_action', whenever a new sheet is selected. However, it is easy to add code for other events using these as templates.