Skip to content
Archive of posts filed under the VBA category.

Excel corrupts certain workbooks in migrating from 2003 to 2007

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree. The following in 2003 Figure 1 becomes, in 2013 (and in 2010), Figure 2 The […]

The Range.Find method and a FindAll function

Two things that could be better about the Range.Find method have been 1) up-to-date and correct documentation, and 2) adding the UI’s ‘Find All’ capability to the Excel Object Model. As of Office 2013 neither has happened. Consequently, every time I want to use the Find method, I continue to have to jump through hoops […]

Model business dependencies in an Excel worksheet

The Civilization game dependency tree How to build a dependency tree when there is no obvious connection between the business model and an Excel model. A long time ago I spend a lot of time playing the strategy game Civilization. For those not familiar with the game, one of its features was “advances.” Each advance […]

Installation Instructions for Office add-ins

I updated the installation instructions for Office VBA add-ins to include Office 2010, Office 2007, and Office 2003. The updated material is at http://www.tushar-mehta.com/excel/software/installation/index.htm. Tushar Mehta

Minimum and maximum values of numeric data types

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them. I decided to “translate” the documentation defining the different data types into code. The […]

TM Excel Dynamic Path Navigator

One of the features that consumers may find useful is retracing their path as they navigate through different workbooks, different sheets, and different windows. This navigation would be equivalent of a web browser’s Previous and Next feature. While Excel does not support this natively, the code in this note enables this capability. For a version […]

Access data in a closed workbook containing a protected worksheet

In a LinkedIn group, someone wanted to access data in a shared server-based workbook that contained a protected sheet with locked cells that were not selectable. In addition to sharing an automated way of doing this, this post contains two other embedded tips. The solution, as many know, is to enter a formula in the […]

Highlight row and column of active cell

By default, when the user selects a cell, Excel highlights the row and column by changing the color of the associated row and column headers. This tip shares multiple ways to highlight the row and column in more obvious ways as well as a way to highlight the cell in a specific column in the […]

In an userform list all available fonts

The motivation for this tip was to share how to 1) dynamically add controls to a userform 2) respond to events for these controls, and 3) specifically respond to events using a callback procedure that is located in another class module! Since this may come across as a fairly technical topic, this tip utilizes the […]

List all folders in a Microsoft Outlook account

Recently, I wanted to create a list of all the folders in my MS Outlook PST file together with the size of each folder. Outlook provides that information through the user interface. Unfortunately, it shows the result in a modal dialog with no way to save the information elsewhere. So, I decided to check if […]