Excel Tips
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 workbook without requiring you to open it.
Get a List of Worksheet Names From a Closed Workbook
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.
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 custom class module rather than placing code directly behind the ThisWorkbook and Worksheet document objects:
- When events fire while you are trying to modify your workbook it can be very annoying. Trapping events in a class module means events only fire when your code has been initialized.
- 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 Excel VBA Code Cleaner. Better to simply not place any code behind these objects.
- You can trap events from other workbooks without the need to place any code in them.
Everything you need to do with events can be done using a WithEvents class module, as shown by the example below.
Trap Workbook Events in a WithEvents Class Module
How Can I Create a Correctly Formatted Address String to Load into a RefEdit Control?
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.