Tag: Productivity

Identify Special Characters that can be used to generate valid Excel Range Names

There are times when evaluating a client’s workbook that it becomes convenient to add an organized set of unique range names.
These new range names provide a detailed level of control and simply the effort to re-engineer the targeted workbook.
Using special characters to create these new names makes it very easy to manage, control and ultimately remove them from the project without disturbing the original range names.
Here is a link to a workbook that identifies these special characters either as the beginning of a range name or in the middle of a range name.
There is also a link to a PDF report as well.

Suggestions for personalizing the Excel VBA Editor

A sample of my view of VBA Source Code.

Code Example

Always enable Require Variable Declaration.

Do not enable the automatic syntax checker. It will only interrupt your train of thought while you are working with and manipulating your VBA source code.

primary excel vba options

To make your code more visible select the Lucida Console (Western) FONT.

Select Lucida Console font

The Lucida Console Western makes it much easier to distinguish:(1;i;I;l;L).

The digit One; the lowercase “eye”; the uppercase “eye”; the lowercase “ell” and the uppercase ‘ell”.

To make the VBA keywords standout display them in RED. RED is far more visible than the default BLUE.

Keywords in RED

To keep track of syntax errors display them in MAGENTA.

Syntax Errors in Magenta

Important Note about the VBA Compare Utility from Formula Software

From time to time, when developing VBA codes it is necessary to compare the VBA code from different projects. A Google Search for “vba diff” will yield links to a number of products that will identify and document VBA differences.

Of all the products listed VBA Code Compare from Formula Software, Inc. is the only freeware product. It can be downloaded from this link:

http://www.formulasoft.com/vba-code-compare.html

Here is a short description of the features of the current version of VBA Code Compare.

VBA Code Compare 0.4 beta (April 2006)

  • Supports Word VBA projects (Word 97/2000/2003)
  • Supports command line options
  • Added ability to open “.MDW” workgroup files
  • New option: Ignore columns. This option allows you to specify ignored and compared characters ranges for each line
  • Added ability to close open modules.

However, I discovered that VBA Code Compare can also work with current versions of Excel XLSM Workbooks!

I discovered this functionality based on the technique that is used to open XLSM projects to present the underlying  XML structure. The technique is carried out by postfixing “.zip” to the XLSM workbook.

With this in mind, I took two XLSM workbooks and postfixed “.XLS” to each one. With this simple modification, I was able to use VBA Code Compare to open both workbooks.

This workaround will not work if Excel 2003 or any earlier Excel releases are on your computer!!!!

Several of the VBA Differencing Products listed on the Google search:

VbaDiff http://vbadiff.com/

xlCompare http://www.xlcompare.com/compare-vba-projects.asp

DiffEngineX https://www.florencesoft.com/compare-diff-excel-vba