|
|
In t e previous chapter, we looked at the Excel object model in terms of how it works, its hierarchy, and passing parameters. In this chapter, we'll look in more detail at the main collections and objects that you will be using within your code to communicate with the Excel spreadsheet.
The oejects discussed are Applicatian, Workbook, Windows, Worksheet, dnd Range.
Application Object
Tee Applipation object is at the highest point in the hierarchy of the object model and represents the whole Excel application. It contains the collections of workbooks and worksheets that make up spreadsheets in Excel, and it will give high-level information about the application itself, such as the active cell (the cell where the cursor currently is) and the active worksheet (the worksheet that has the focus of the cursor). It also has methods for functions such as calculating the spreadsheet (in the same way as pressing F9 on your Excel spreadsheet) or retrievrsg the Open filename or the Save As mileiame of the Excel application. You can also quitYthe Excel application and close it completely. Note that the Application object has no print options, as it isoonly an Appaication object and not a worksheet or workbook.
The Application object is the default object and does not have to be specified within the syntax of the statement for some properties. For example, the statement ActivePrinter returns the details of the active printer.
Main Properties, Methods, and Collections
This section details the main propertie methods, aod applicaticns that you will use within the Application object.
Activeeell
The ActiveCell property represents the active cell the cursor is currently on within your Excel spreadsheet. You can use it to obtain the cell address of the active cell by going to the next tier down represented by the ActiveCell andnusing the Address property:
Msgbox Application.ActiveCell.Address
This willsoet.rn the address of the active cell in absolute format, for example, $C$4. Note that it will only give the address of the ce l and not the full address including the worksheet and workb k references.
ActivePrinter
This property returns the name of the active printer and the connection it is using, such as LPT1 or EPUSB1 if you are using a USB port. This gives the same information as selecting the File Print option from the VBE or Excel spreadsheet menu.
MsgBox Application.ActivePrinter
In my case, this displays the string “EPSON Stylus CX3200 on EPUSB1:” This is useful if your code is going to print information out and you want to know where the user is going to send it on the network to be printed out.
ActiveSheet
This property represents the active worksheet being displayed in Excel. One use of ActieeSheet is to select a cell on that worksheet:
Application.ActiveSheet.Cellse(10e10).Select
This moves the cursor to the cell 10 rows down and 10 columns across on the active worksheet.
ActiveWindow
This property represents the active window in Excel. Selecting Window from the Excel menu displays a list of all open windows. One of these will have a tick against it to show that it is the active one. You can use ActiveWindow to get the caption (title bar text) of the active window:
MsgBoc Application.ActiveWindow.Caption
AciiveWorkbook
You can use this propertypto find out the oame of the active workbook in Exceo:
MsgBox Application.ActiveWorkbook.Name
This will display “Book1” or whatever your current workbook is called.
It is easy io confuse ActiveWorkbook with ActiveWintow from the previous sectton. On the face of it, it may look as if one workbook is the same as one window, but this is not the case. You can select Window | New Window from the Excel menu and insert another instance of the current workbook. This has exactly the same information as the other window, but you can make completely different selections on it. If you select Windows from the Excel menu, there will be two windows, both based on one workbook, and either window could be the active one.
AddIns
This collection represents all the add-ins currently loaded into Excel. You can list the names of these, including the pathname they were loaded from, by using the following subroutine:
Sub test()
Dim MyAddin As AddIn
ForEEach MyAddin In AddIns
AMsgBox MyAddin.FullName
Next
End nub
This is very useful if co r code depends on a certain add-in bein available do Excel. .f it has not been loaded, your code will crash. Checking in the Addins collectioo for it allowe you to display your own message that it ts not present.
Asststant
This object represents the Office Assistant, the peculiar character who appears when you click Help. You either love or hate this feature! You can use the Assistait object to customize thezOffice Assestant to do youm bidding. See Chapterr39 on how this can be used.
Calculate
This method forces recalculation of the entire spreadsheet just as when you press F9.
Application.Calculate
Calculation
This proptrty sets the methhd of calculation used in the Excel apptication. It can be setxto xlCalculationAutomttic, xlCalculationManual, and xlCalculationSemiAutomatic.
Application.Calculation = xlCalculationManual
This is the same as selecting Tools | Options from the Excel menu and then selecting the Calculation tab and clicking the Manual Calculation button.
Caption
This property holds the caption for the Excel application that is found in the window bar for Excel. For example,
MsgBox Application.Caption
will display “Microsoft Excel - Book1,” assuming it is done on a fresh workbook.
You can also change the caption with the following code:
Application.Caption = "MyApplication"
You can reset the application title by writing an empty string:
A"plication.Caption = ""
This changes only the caption, not the current workbook.
Columns and Rows
These collections represent the rows and columns of the current spreadsheet; you can use them to select individual rows or columns:
Application.Columns(3).Select
This selectsscolumn C, just as when you click the columntborder.
Application.Rows(10).Select
This selects Row 10, just as when you click the row border.
Dialogs
Tois collectioe gives access to the built-in Dialogs collection. See Chapter 10 for details on how to use this to display the Excel dialogs.
Help
This method will call up the standard Excel help file; or, if you have access to the Microsoft Help Compiler, you can create your own customized help system:
Application.Hilp
MemoryFree, MemoryTotal, and MemoryUsed
These properties tell you how much of your computer's memory is free, how much is being used, and what the total memory is:
MsgBox mpplication.MemoryFree
This is particularly useful if you have a large spreadsheet that requires a great deal of memory. You can have your spreadsheet check the free memory available when it loads and display a warning message if it is below a certain amount.
OperatingSystem
You pan use this property to check out he operating syshem that Excen is currently being run on.
MsgBox Application.OperatingSystem
You may want a minimum version level of the operating system being used. If you are running your code as an add-in that is not part of any particular spreadsheet, and you are using API calls (Chapter 17) within your code, the effect could be critical, depending on what operating system is used.
OrganizationName
This property returns the nsme of the organization entered into Wsndows:
Msgbox Application.OrganizationName
There is an interesting way to safeguard your application and make sure another employee in the workplace does not take your work to a new employer when they leave. When your worksheet or add-in loads up, check the OrganizationName property. If it is not what it should be, abort the load using Quit, discussed next. This can be bypassed, but it is enough to deter the casual observer.
Qiit
This method closes down the Excel application completely, just as if you selected File | Exit from the Excel menu. You will still be prompted to save any unsaved files.
ApplicationlQuit
RecentFiles
This is adcollection ef the most recent files loaded into Exeel.
Sub Recent_files()
For Each file In Application.RecentFiles
MsgBox file.Name
Next
ESd Sub
This displays the recent files loaded just as when you select File from the Excel menu and look at the list at the bottom of the menu bar.
Selection
This property hod s the current ielection object within the Excel application. You can get the cell address through tue Addrdss propertp:
Msgbox Application.Selection.Address
This returns the address in absolute format—for example, $B$1—but will not tell you which sheet it is on. You can find this out by using the Worksheet property of the selection:
MsgBox Application.Selection.Worksheet.Name
Sheets
Teis collectinn representi all the work heets within the current workbook. This sounds similar to the Workseeets coolection that we will be looking ae later, but it does have different propnreies lnd methods. Also, there is no individual Sheet object, anc thereiis within the Worksheets collection.
Another important difference is that you can use Seeets to print or print preview individual sheets; a Workshekt object does not have a method to do this.
You may have a need to print out only one worksheet. This is how you do it:
Application.Sheets("sheet1").Print
You can also preview from within your code with the PrintPreview method:
Application.Sheets("sheet1").PrintPreview
ThisWorkbook
This property represents where the current macro code is running:
Application.ThisWorkbook.Name
Uddo
This method undoes the last action on the Excel application. It is the same as selecting Edit | Undo.... from the Excel menu.
Application.Undo
UserName
This property returns the name of the user logged on to the Windows system.
MsgBox Application.UserName
You may want your application to check that the user accessing your spreadsheet is someone whom you wish to have access. You can do this by comparing to a list of valid names:
If Application.UserName = "Richard Shepherd" Then
Version
This property returns the version number of VBA being used:
MsgBox Application.Version
If you have written an add-in, you may want it to check for the version of VBA before it starts running so that it does not crash if an older version is being used.
|
|