Using xlCompare to compare Visual Basic Projects in the Excel Spreadsheets.
Every professional VBA developer from time to time needs to compare different versions of his code. Complex
Excel spreadsheets contain thousands lines of Visual Basic Code. Even minor change in code may lead to error
which can take a lot of your working hours to be debugged and resolved. One of the common ways in resolving
such problems is to find version of your spreadsheet where the problem appears and compare it with previous
version. By analysing differences you can quickly find source of the error in your Visual Basic Code. Or you
can remove differences one-by-one and find line of code which produces error.
To perform this operation quickly and effectively you should use VBA Project Comparison Tool, which
finds all differences, displays it in a form which allows quick and effective navigation and allows
to merge them.
xlCompare has all features you need for VBA Project comparison. Even if your projects are
password protected, you should not manually open VBE editor and enter a password to unlock the project.
xlCompare will request a password from you, when it is needed to get access to the VBA code. After entering
password your project will not became unprotected. This is the same password request as shown by Excel
when you are going to open protected project.
Let's look what you can get by using xlCompare. Select Compare VBA Projects command on the ribbon.
On the next step select workbooks, which modules you need to compare, and press Compare button.
xlCompare can be used with any type of the Excel spreadsheet, which has VBA Project - XLS, XLSM, XLT, XLTM, XLA, XLAM.
Comparison report for VBA project is in the Difference Explorer window. As you know xlCompare
shows all found discrepancies in one window.
In this discrepancy tree xlCompare shows all modules, which are different, deleted modules and added modules.
Equal modules are not shown - they will only waste the difference display. Each discrepancy has explanation -
which sub\function was added or in which sub line was added\removed\modified. Activate the discrepancy and you'll
be pointed into the VBA Code:
Now you can see all differences in your VBA modules. To merge lines of code you can use Right Click Menu.
This image shows xlCompare menu for the added lines of code in the VBA Code Window:
Additional commands to Save Visual Basic Project, Switch Projects, Restart the comparison, Add\Delete
module, ... are available in the Difference Explorer toolbar and right-click-menu.
As you can see, xlCompare
has all features you need
to compare Visual Basic Code in your Excel Spreadsheets. This is an ultimate tool for Excel