Before starting to build an EASAP around a spreadsheet, there are a few preparation tasks that should be performed on the spreadsheet to minimize potential compatibility issues with EASA.
Once an Excel file is part of an EASAP, its file name should not be changed. Choose an appropriate, generic file name, avoiding the use of version numbers. An alternative way to use version numbering on spreadsheets could be to display the number in a cell.
Forbidden characters in file names are:
< > / | \ : " * ? [ ]
Cells that will be selected as inputs or outputs in range reference parameters should be given names. Additionally, cells in the first row or column of tabular data which are referenced in a READ EXCEL ACTION or WRITE EXCEL ACTION should be named.
In order for EASA to process input cells effectively, special attention should be paid to cell formatting in Excel. Please ensure that cells are formatted appropriately and take note of the following caveats:
Cells set by macros and referenced by CHOICE LIST's must match the list of choices exactly, including case.
EASA is capable of interacting with password protected spreadsheets (i.e. using Tools→General Options on the Save As form), provided that the password is known. If the password is known, then it can be entered into the Password: parameter of the protected SPREADSHEET or SHEET within EASAP Builder. If the password is not known to the author, then the protection must be removed prior to use with EASA.
Tip: It's often true that an EASA implementation of a workbook will fundamentally remove the need to protect the source file altogether. We recommend that, wherever possible, all protections are removed from workbooks before integration with EASA.
You should eliminate any pop-up modal forms that require an interactive response from a user under normal usage of the spreadsheet in Excel. When using an EASAP interacting with a spreadsheet, the user will not see the spreadsheet itself and will not have the opportunity to click on any pop-up form. Examples of pop-up forms are:
On Error GoTo Err_Handler Err_Handler: Error_Handling “Procedure", Err.Number, Err.Description Public Sub Error_Handling(Procedure_Name As String, Error_Number As String, Error_Desc As String) ThisWorkbook.Range("Error_Description") = "Procedure : " & Procedure_Name & Chr(10) & "Error Number: " & Error_Number & Chr(10) & "Error Description :" & Error_Desc End Sub
Currently, the RUN MACRO ACTION cannot pass arguments to macros. To work around this limitation, you can remove arguments from the macro routine called and instead access the values previously sent as arguments by pointing to cell values somewhere in the spreadsheet. Then, the EASAP can supply new values by changing the values of the new cell ranges containing what used to be argument values with WRITE EXCEL ACTION. In addition, for RUN MACRO ACTION to work properly, the macro being run must be ‘Public’, which means it will be shown on the list of macros in Excel.
EASA suppresses the prompt to save a file in all cases except when a macro changes a cell value. The following VBA routine should be added to ThisWorkbook object in VBA project:
Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Saved = True End Sub
Some macros work only if the workbook is open at a specific sheet. Since EASA opens the Excel process in the background, there's no way to manually select sheets. For these macros, it is necessary to adjust the VBA code so the correct sheet is selected every time the macro is run.
For example, if a macro is meant to be used on the sheet called Sheet_1, the following line should be added to the beginning of the macro's VBA code:
This way, when the macro is run, the specified sheet is focused and the rest of the macro can run as intended.
Certain functions of EASA related to Excel do not support overlapping ranges. Please make sure that the following ranges do not overlap to avoid unexpected behaviour: