User Tools

Site Tools


Excel Workbook Preparation

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.

General Conventions

Choose a Suitable File Name

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:

     < > / | \ : " * ? [ ]

Name All Cells to be Referenced

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.

Apply Proper Number Formats

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:

  • Blank spaces should be formatted as General and should not have data validation.
  • Inputs without default values should not be formatted as General.
  • Text entry cells used solely as annotation may need to have a dummy formula added, referencing the cell to be identified as an input. For example “=text1”, where text1 is the name given to the cell.

Impose Consistent Use of Case

Cells set by macros and referenced by CHOICE LIST's must match the list of choices exactly, including case.

Protected Spreadsheets

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.


Eliminate Modal Forms

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:

  • Requests for passwords set under the Tools→Protection.
  • Error message dialog boxes caused by running a macro that require the user to click on ‘OK’.
  • Explicitly forced request for saving spreadsheet on exit on which the user must either select ‘Yes’ or ‘No’. An example of some Visual Basic for Applications (VBA) code for trapping errors within macros and therefore avoiding error forms to open is shown below:
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

Remove Arguments in Macros and Make Public

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.

Save Excel File Prompts

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 

Macro Scope

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:

Sheets(Sheet_1).Activate

This way, when the macro is run, the specified sheet is focused and the rest of the macro can run as intended.

Avoiding Overlapping Ranges

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:

  • Charts to be displayed can not have overlapping ranges
  • Ranges to be printed by EXPORT TO PDF functionality can not have overlapping ranges