To use these events in Microsoft Excel VBA, open the Microsoft Excel Visual Basic Editor and open a code window for a workbook.
Begin by explicitly declaring the CBCOMDevKit.OptRuntime class as a variable with events:
Private WithEvents PortfolioOptRuntime As CBCOMDevKit.OptRuntime
In this example, the variable name PortfolioOptRuntime is declared as on OptRuntime class type.
Then, declare other variables as required. For more information see Coding an Optimization.
Whenever you want to enter an event macro into your code, go to the first dropdown list in the code window and choose the name of the variable assigned to OptRuntime.
Available events are listed in the second dropdown list in the code window. When you click one, it is inserted into your code for that workbook as a private subroutine, for example:
Private Sub PortfolioOptRuntime_AfterIterationEvent(IterationsCompleted As Long) End Sub
The subroutine takes the name of the variable assigned to OptRuntime followed by an underscore and the name of the selected event. Any required and optional parameters appear in parentheses following the name.
You insert code as usual, using any appropriate VBA code and macros from the Crystal Ball and OptQuest Developer Kits. For example, you can set cell values or perform other operations described elsewhere in this Overview document or the Crystal Ball Developer Kit User Manual.
Then, when the selected event occurs, the code runs.
Important Note: In any event handler that modifies values or formulas within a Microsoft Excel workbook (or calls functions that could modify values), you should insert the following code:
’Indicate this event was handled PortfolioOptRuntime.EventHandled
where PortfolioOptRuntime is an OptRuntime object. This code informs the optimization engine that events are being handled and allows correct processing in the case where event handlers modify values in the Microsoft Excel workbook. It does not matter where the code is placed as long as it is within the event handler.