Exporting Surrogates to Excel using VB Files

Simcenter STAR-CCM+ allows you to export a surrogate function to the VB (.bas) file format. Exporting surrogates to a file allows you to set the input parameters and calculate the corresponding surrogate response in an external application such as Microsoft Excel. The VB file is imported into Excel as a macro.

To export the surrogate as a Visual Basic file:
  1. Right-click the Design Studies > [Design Study] > Surrogates > [Surrogate] node and select Export to File....
  2. In the Export to File dialog, set Files of Type to Visual Basic for Application Code Files (*.bas).
  3. Navigate to your working directory and click Save.
To import the Visual Basic file into a Microsoft Excel workbook:
  1. Open a blank workbook in Excel.
  2. Select File > Options.
  3. In the Excel Options dialog, click Customize Ribbon.
  4. Under Customize the Ribbon add Developer to the main tabs and click OK.


  5. Select the Developer tab in the ribbon, and click the Visual Basic tool.
  6. In the Microsoft Visual Basic for Applications window, click File > Import File....
  7. In the Import File window, navigate to your working directory, select the .bas file you exported from Design Manager, and click Open.
    In the Microsoft Visual Basic for Applications window, Excel adds a new module to the Modules folder.
  8. To view the contents of the imported .bas file, double-click Modules > [Module1].


    You can see parameters of the surrogate function under the Variables and value ranges heading.

  9. Return to the blank workbook, create a cell for each parameter used in the design manager study and set each parameter value.


To define the surrogate response:
  1. Select an empty cell for the surrogate reponse value.
  2. Select the Formulas tab in the ribbon and click Insert Function.
  3. In the Insert Function dialog, set Or select a category to User Defined.
  4. Select the surrogate response function and click OK.
  5. In the Function Arguments dialog, set X by multiselecting the cells that contain the surrogate parameter values in the order they appear when viewed in the Microsoft Visual Basic for Applications window.


  6. Click OK.
    Excel computes the surrogate response based on the surrogate function and the surrogate parameters selected.
  7. To store the macro within the Excel workbook, save the workbook as a Macro-Enabled (.xlsm) file.
If you are loading an Excel workbook that contains a macro (such as a surrogate function), you may need to change your security settings to allow the macro to execute.
  1. With the workbook open, select File > Options.
  2. In the Excel Options dialog, select Trust Center and click Trust Center Settings.
  3. In the Trust Center dialog, select Macro Settings and activate Disable all macros with notification or Enable all macros.