|
|
|
|
How Can I Get a List of Worksheet Names From a Closed Workbook?This task can be accomplished using ADO to query the workbook for a list of the worksheets it contains. ADO treats a workbook as a database and each worksheet as a table, and with a few additional tricks shown in the example below, it will return a list of all the worksheets from a closed workbook. This example requires Excel 2000 or higher. Get a List of Worksheet Names From a Closed Workbook (11 KB) How Can I Ensure an External Add-In Required by My Application is Loaded?If your application relies on an external add-in such as the Analysis Toolpak or Solver, you must ensure this add-in is loaded prior to running your application. The example below provides a generic function that will determine if a specified add-in is currently loaded and attempt to load it if the add-in is not already loaded. The function returns True if the add-in was successfully loaded or False if it wasn't, allowing you to gracefully error handle cases in which the add-in you need is not installed on the user's computer. Load Specified Add-In Example (10 KB) How Can I Trap Events from a Workbook Using a Class Module?There are a number of good reasons to trap workbook events in a class module rather than placing code directly behind the ThisWorkbook and Worksheet document objects. First, when you use this method, trapped events don't get in your way. Unwanted events firing while you are trying to modify your workbook can be very annoying. Second, like all VBA code in an Excel workbook, code behind document objects is subject to corruption. However, when the corruption occurs in the code behind a document object there may not be a simple fix such as the one provided by the code cleaner, located elsewhere on this site. Better to simply not place any code behind these objects. Everything you need to do can be done using a WithEvents class module, as shown by the example below. Trap Workbook Events in a WithEvents Class Module (20 KB) How Can I Create a Correctly Formatted Address String to Load a RefEdit Control With?When using a RefEdit control it is not unusual to want to populate it with the address of the currently selected range. Determining the correct address string to use is not a simple process due to the wide variety of ways the current workbook and worksheet can be named. This example provides a function that encapsulates all of the logic required to return a correct RefEdit string for the current selection. Determine the Correct Address String for a RefEdit Control (14 KB) |
|
Copyright Application Professionals © 1999 - 2008 |
