Limitations and Workarounds for Compiled Workbooks
Why does a compiled EXE have limitations in functionality?
When it comes to protecting intellectual property, an EXE file compiled with the DoneEx XCell Compiler can be an effective solution. The process of compiling Excel formulas into binary format provides a higher level of security than simply hide and lock formula protecting an Excel workbook. With a compiled EXE, the formulas are converted into machine code that is executed directly by the XCell Compiler run-time calculation engine. This means that the formulas cannot be viewed or edited by anyone, providing a secure environment for all protected algorithms.
However, there are certain limitations that are applied to a compiled EXE. One of the most notable reasons of such limitations is the separation between Excel and the XCell Compiler run-time calculation engine. This separation can cause compatibility and synchronization issues with certain Excel features or functions that rely on direct access to Excel’s core functionalities.
Overall, it is important to carefully consider the trade-offs between security and functionality when deciding whether or not to use a compiled EXE for protecting Excel workbooks. While the added security can be valuable for certain use cases, it is important to be aware of the potential limitations and to plan accordingly to ensure the desired level of functionality.
The DoneEx XCell Compiler is designed to protect workbooks by compiling Excel formulas into binary format. At run time the compiled formulas are calculated by the XCell Compiler run-time calculation engine, which is an effective way to prevent formulas from being copied. However, this separation between Excel and the calculation engine can result in several limitations, which can be overcome with workarounds.
List of the limitations along with some possible workarounds
Dynamic Arrays
The run-time calculation engine does not support dynamic arrays.
Workaround: Enter formulas that use arrays as static arrays using Ctrl+Shift+Enter key combinations, or enter the cell range address with such formulas into the “Exceptions from Compilation” list.
Sorting Area with Formula
The area of cells containing formulas before compilation cannot be sorted.
Workaround: Enter the cell range address of the area into the “Exceptions from Compilation” list. The formulas in that area will not be compiled and can be sorted manually or with the use of VBA code.
Inserting Sheets
New sheets can be inserted into a compiled workbook under certain circumstances.
Workaround: New sheets can only be added as the latest sheet in the sheet list. It should be done in such a way that the new worksheet would have the biggest worksheet index number in the workbook.
Removing Sheets
Removing sheets from a compiled workbook is not supported and can cause calculation failures.
Workaround: There are currently no workarounds for this limitation.
Inserting Rows
Inserting new rows in a compiled workbook is not supported as it can damage the cell relations in the workbook.
Workaround: Enter the cell range address of the entire worksheet into the “Exceptions from Compilation” list. The formulas in the worksheet will not be compiled and, as a result, inserting rows will work.
Removing Rows
Removing rows from a compiled workbook is not supported as it can damage the cell relations in the workbook.
Workaround: Enter the cell range address of the entire worksheet into the “Exceptions from Compilation” list. The formulas in the worksheet will not be compiled and, as a result, removing rows will work.
Inserting Columns
Inserting new columns in a compiled workbook is not supported as it can damage the cell relations in the workbook.
Workaround: Enter the cell range address of the entire worksheet into the “Exceptions from Compilation” list before compilation. The formulas in the worksheet will not be compiled and, as a result, inserting columns will work.
Removing Columns
Removing columns from a compiled workbook is not supported as it can damage the cell relations in the workbook.
Workaround:
Enter the cell range address of the entire worksheet into the “Exceptions from Compilation” list before compilation. The formulas in the worksheet will not be compiled and, as a result, removing columns will work.
VBA GoalSeek() Function
The VBA GoalSeek() method is not supported.
Workarounds:
a) Add the goal cell address into the “Exceptions from Compilation” list before compilation. The formula in the goal cell will not be compiled and, as a result, GoalSeek() will work.
b) Use Excel Solver methods instead of the GoalSeek() method.
To overcome the limitations of protected workbooks, we have developed another product called DoneEx VbaCompiler for Excel. By using this tool you can to protect your VBA code and eliminate the restrictions mentioned above and ensure that they don’t apply to your protected workbook.