AET VBE Tools v1.6.1

Over the last week, I made some changes to my AET VBE Tools.

Although still free, I want to ensure they are as good as I can make them for you, before I release a paid version.

This is what has been done –

An indenting bug was fixed for Select Case constructs when working with Projects and Modules.

I also improved the indenting of Add Line Numbers code.

When adding the date and time to text files being exported, the code has been adjusted to show the correct time.

I made a change to the Copy Code and Compare Code userforms. Sometimes duplicate file names were showing.

I edited Highlight Code In Excel so that individual Case statements are also highlighted with Select Case constructs.

Case Statements

Note: It is a big help with regards to visibility to see them highlighted this way. Unfortunately I had to hard code that part, so it will only work if your tab settings are set to 4. (In the VBE, Tools, Options, Editor, Tab Width). If I find a way to determine this programatically, I will adjust the code to suit. Alternatively, if you know a way, please leave a comment.

As a small bonus, I also added the functionality to delete Debug.Assert and Stop to the Cleanup Project code.

Download the new version here.

AET VBE Tools

Late last year, I started work on a new set of VBE tools. It’s an extension of a code indenter that I made several years ago. I thought it would be nice to update it.

So far I’m up to Version 1.6. It’s freeware, but I’m starting work on a shareware version that will have more stuff. Anyway, here’s what I blogged about it on my site. Give it a try if you like!

Access AET VBE Tools by right clicking within the active code pane.

Here are the tools.

Indent Code
Indent code within the active VB project, module, procedure or selected text.

Add Line Numbers
Add line numbers to code within the active VB project, module, procedure.

Delete Line Numbers
Delete line numbers from code within the active VB project, module, procedure.

Export Code
Export code from the active VB project, module, procedure to text files.

Compare Code
Select modules from projects.

Code from both modules will be exported to worksheets in a new workbook.

Code that exists in one module, but not the other (and vice versa) will be highlighted.

Copy Code
You can copy code/modules between projects.

Standard modules, class modules and userforms will be be replaced if they exist (have the same name), or added if they don’t exist.

ThisWorkbook code will be replaced.

Sheet module’s code will be replaced if the sheet exists, or worksheets will be added with the new code if they don’t exist.

Macro Comment Tools
A handy way to add generic comments to all macros in the active project or module.

Insert Code Snippets
Tired of entering the same code all the time? This makes it easier.

Run Favourite Macros
Yes, you can already run them from your Personal workbook.

But now you can also export your favourite code to text files. Note: Not all code will run. This is a experimental tool, but I have found it quite useful when coding myself.

Multiple Find And Replace
You can find and replace code with several fields at once. Fields are saved between sessions.

Cleanup Project
Just 2 options at the moment. You can delete lines of code that have “Debug.Print”, and also delete excess blank lines. (Only a single blank line will remain)

Highlight Code In Excel
Export your code to a worksheet in a new workbook. Selecting cells in Column A that have keywords like If, With, Select, etc will be highlighted so you can see where that part of the code begins and ends. This is very beneficial to your mental health when trying to figure out what connects with what in those long, long procedures.

Last, but not least, there are various settings available.

Download AET VBE Tools v1.6 here.

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

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 in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1110%20Workbook%20Navigation%20Retrace.shtml

Tushar Mehta

Introducing TM Retro Slicer

Those who have used a slicer in Excel 2010 slicer may want to see that capability in earlier versions of Excel. I like what Microsoft did with slicers in 2010 and have wanted to extend that capability backwards. For those who haven’t used a slicer, it is a filter on a particular pivot field shown as an independent object.

So, with TM Retro Slicer, one can create a slicer in any version of Excel from 2003 to 2010.

TM Retro Slicer works with versions of Excel from 2003 to 2010. Once enabled, a slicer will be visible on the worksheet.

Switching worksheets will hide the slicer and it will reappear when the worksheet is reactivated.

The slicer will remain across a workbook close and re-open.

For more on this shareware solution, please visit http://www.tushar-mehta.com/excel/software/retro_slicer/index.htm

Tushar Mehta

Introducing TM Match Target

The TM Match Target add-in analyzes a list of numbers and finds combinations that sum to a given total. This has applications in a range of disciplines including processing receipts, reconciling payments such as health care insurance reimbursements or payments by a customer for many outstanding invoices, operations management and operations research, and supply change management.

There are a number of posts, easily found through Google or Bing, that address the same issue, including my own Find a set of amounts that match a target value. This add-in packages the analysis in an easy-to-use interface, includes a means to stop the analysis if it is taking too long and preserve the combinations found so far, together with various options on what results should be shown.

For more please visit http://www.tushar-mehta.com/excel/software/match_target/.

Tushar Mehta

TM Custom Radar Chart add-in

A Radar Chart, also known as a Spider Chart, visually compares several entities (products, organizations, investment opportunities, or even people) on multiple dimensions. For example, a manager of a diagnostic imaging (radiology) center might want to compare her facility with the competition on dimensions related to patients such as Time To Appointment, Report Turnaround Time, No Show Rate, and Wait Time.

Or, one might want to compare 6 products A, B, C, D, E, and F on 6 different attributes: Aesthetic Appeal, Compatibility, Strength, Market Size, Durability, and Reliability. The Custom Radar Chart lets one easily compare all the products along each of the dimensions on interest. This is the example used in the documentation. With the axes normalized, the resulting chart would be

For more on this shareware product, please visit www.tushar-mehta.com/excel/software/custom_radar/index.htm

Tushar Mehta