These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Monday, April 9, 2012


Issue: Although Excel already comes with most of the productivity tools people need, there are a few tools that I have found in my work experience would be extremely useful if they existed. The following are two features that I put together in my project to make the user experience in the Excel environment a little more convenient. These features are designed for intermediate to advanced users: while they are simple to use, they are mainly helpful when working with large or complex spreadsheets, which will be done primarily by analysts and managers.

1. Group Sheet Manager: When workbooks get too large with several worksheets, they can be unwieldy to manage. This tool opens a form that shows a hierarchy of user-defined groups and the sheets assigned to the groups. The user can easily assign sheets to groups using submenus accessible from the main form. Once sheets have group assignments, the user can select specific groups and perform operations that apply to each associated sheet. From the form itself, the user can show / hide groups of sheets, apply a tab color, or easily select the sheets by group and then perform all regular Excel operations. Sheets can be assigned to more than one group. Any changes to the group assignments are stored in a very hidden worksheet. Also, the index of group names themselves are stored in that worksheet, as well as user preferences so that all changes can be recalled the next time a particular workbook is opened. A number of validation procedures are performed each time a group or sheet is selected from the form or when a specific action button is pressed.

2. Spreadsheet Merge: Often several workbooks and worksheets need to be merged into one workbook. While this can be done manually, it is a very tedious process if several sheets need to be imported, especially if they come from different files. This feature allows users to import sheets from multiple workbooks at once in an efficient way. Users first browse for the files that contain the sheets they wish to merge. They are then prompted to select which sheets they want imported. Finally, they perform the import operation. Users can also select where they want the new worksheets to be placed in the destination workbook (e.g. “before Sheet1”).

Shortcuts to the new tools were added in a separate tab on the ribbon (“xlBooster”).

No comments:

Post a Comment

Blog Archive