Posted by Andrew Schuster on Sep 21, 2013 in Code Snippets | 0 comments
Merging Multiple Workbooks Together by Searching Directories and Sub-Folders
At some point in time most programmers are faced with a task where they need to recursively search through filesystem folders and perform some sort of operation on matched files. For VBA programmers in Excel, this “operation” often involves the merging of matched .xls and .xlsx files together into a single Workbook.
Performance Tunning: ActivateCell vs Cell Referance
Today I’d like to talk about an important issue that pertains to the performance of VBA code in Excel. What I’m referring to is the use of .Activate. To be honest, I can’t think of a time when that function should ever be called in VBA code. The method is very much designed for visual identification of a focused object in the user interface (UI), and not the manipulation of underlying properties. Using the .Activate function carries a curtain amount of overhead that is not required when all you are doing is iterating through a range of cells. As an example I’d like to show you two separate snippets of code. The first example iterates through all the cells in a range using the .Activate approach, while the second merely iterates through the same cells without activating any of them:
Activate vs Select
One thing VBA programmers often grapple with is whether or not to use .Activate or .Select when working with objects like worksheets and cells. So what is the difference, and when would you use one method over another? To put it simply, .Activate sets focus to a single object, while .Select allows for multiple items to be selected. As an example, you can select multiple cells on a worksheet, but only one of those cells can be active at any given time.
Overview of the Excel Object Model
The Excel object model closely follows that of the User Interface (UI) making the transition from end user to developer much easier. At the top of the object model we have the Application object. This object contains application wide settings (ie. many of the options found in the options dialog under the tools menu), as well as properties like ActiveSheet and ActiveCell.
Read More