SuppressExcelAlerts

SysVar SuppressExcelAlerts

When SuppressExcelAlerts is set to True (1), the Excel property DisplayAlerts is set to false when SpreadsheetOpen is called. When this Excel property is false, Excel does not show message boxes to the end-user. Here are some examples of message boxes that Excel might want to display:

The system variable has an effect only within SpreadsheetOpen. When SpreadsheetOpen returns, the DisplayAlerts will be false, but if anything resets it to true afterwards, other spreadsheet functions will start displaying alerts again. As long as other code or interactions do not clear the Excel property, then future calls made to spreadsheet functions suppress alerts.

When the DisplayAlerts are suppressed, some attempted actions may cause an Analytica evaluation error to be reported. For example, an attempt to open a file having the same filename as an already open workbook, but in a different folder (Excel does not allow this). In other cases, a default action is taken, such as when doing a Save As (i.e., using SpreadsheetSave with a specified filename) and the target file exists. In this case, the default is to replace the existing file.

Purpose

This system variable exists for Analytica Decision Engine developers. It is generally not used by Analytica model builders.

When a model, or an ADE-based application uses Excel as a component to access workbooks, Excel will sometimes display message boxes to the user with information or questions. If these message boxes appear on screen other than the user's screen, then the calling application will hang (i.e., become non-responsive) as it waits for the Excel call to return, which will never happen since the user has no way of knowing the question is even there. By suppressing these, you can prevent such applications from hanging.

Generally in Desktop Analytica you should not set this, since Excel should also be running on your desktop. If Excel needs to display a message box, you should be able to see it, and you probably do want to see it. The main purpose for this is for use in Web Applications (such as Analytica Cloud Player), where the user interacting through the web interface would never see the servers console where the Excel message box would most likely appear.

Usage

In an ADE-based web application, your code should set this system variable just prior to a call to OpenModel, for example:

CAEngine ade = new ADE.CAEngine;

ade->SendCommand("SuppressExcelAlerts : 1");

ade->OpenModel("SomeModelName.ana");

Lifetime

The SuppressExcelAlerts system variable is not saved with your model. If you set its value before a call to OpenModel(), its value will be the same after the model loads, but the value will be reset to false (0) if a model is closed, so your code should set it every time prior to a call to OpenModel().

Setting it prior to OpenModel() ensures that it is set in the event that a model proactively opens a spreadsheet at model load time.

The DisplayAlerts property is a property of the Excel application instance, and not of the individual workbook. So once it is set or changed, all workbook instances are impacted. If you have a web application running, but you use the Excel UI on the same server from a desktop, the two will interfere with each other. Your desktop usage may in fact set the DisplayAlerts again. If you feel you need to hedge against this in your web-based application, you may need to clear the DisplayAlerts property prior to any operation that might result in an Excel message box. For example, during a Save As, a message box will appear if the file name already exists, so to suppress this you would need you model to do the following, where wb is the name of your model variable that holds the workbook.

Var prevDA := wb->Application->DisplayAlerts;

wb->Application->DisplayAlerts := false;

wb->SpreadsheetSave("NewFilename.ana");

wb->Application->DisplayAlerts := prevDA;

Note: The above code requires the Enterprise edition when run on Analytica desktop, since it uses the COM Integration feature.

The preceding code prevents an alert from appearing during Save As even if you have never set the SuppressExcelAlerts system variable. So most potential alerts can be prevented it this fashion. However, the only way to prevent alerts that might occur during SpreadsheetOpen is to set SuppressExcelAlerts before SpreadsheetOpen is evaluated.

If your web-application is the only code that would be using Excel directly on that server, this extra protection around spreadsheet functions is unnecessary. To run your model on ACP, you do not need to worry about protect spreadsheet calls in this fashion.

Limitations

Because DisplayAlerts is a property of the Excel application, and not of an individual workbook, interactions between different applications of Excel are possible. Hence, you may not be able to guarantee that no Excel message boxes appear on a computer that is used by different applications and directly by users from a desktop.

If two or more Excel processes are already running on the computer when SpreadsheetOpen is called, it cannot be guaranteed that a message box during SpreadsheetOpen is suppressed, except in certain cases. If you can guarantee that all the existing instances already have DisplayAlerts set to false, then you are fine. This guarantee will be achieved if all instances were created by your own application, and your application always set SuppressExcelAlerts prior to calling SpreadsheetOpen. In the event that the guarantee does not hold, the situation we know of that might cause an alert to appear is when you open a file having the same name as a file already open in an existing instance of Excel, but not the same file (i.e., same name, but in a different folder).

This system variable has no direct effect on OLE linking. A model with OLE-linked Excel sheets remains susceptible to the problem of Excel alerts appearing while opening or accessing the OLE-linked data. At this time, it is recommended that OLE linking not be used when deploying models with spreadsheets onto ACP.

History

Introduced in Analytica 5.0.