This post shows you how to use Response Surfaces previously created by Nexus within an Excel Workbook without requiring any iChrome’s product license.
The Response Surface module of Nexus allows to create, train and validate Response Surfaces moving from an initial set of sample points, either generated within Nexus as a Design of Experiment or imported from file. Once a Response Surface is created, this can be exported and saved to be used outside Nexus and without any need to have or purchase Nexus (or other iChrome’s product) licenses.
A detailed explanation on how this can be done (via command line) can be found in this previous post.
Additionally, the Response Surface Toolbox provide an Excel AddIn that you can simply use and register within your Excel installation. The AddIn register two main functions:
- _text NxInfoRS(_text rssPath): this function receives as input the path pointing at the *.rss file of the Nexus Response Surface to be loaded in memory. Value can be provided either as a textual cell or via a user provided string value.
- double NxEvalRS(_cellRange, int index, _text rssPath): this function allow evaluating a Response Surface and to return computed values to Excel.
More details and examples on how to use the toolbox as an Excel Addin can be found in the toolbox documentation.
In this post we will follow a different and more programmer-oriented approach. Instead of using the already available plugin-in, we aim to use our own Macros so that the Response Surface Toolbox will be embedded in our customized Excel macros (or potentially any other VBA enabled application).
Before We Start
In order to run this tutorial you need to install the version 3 or higher of the Response Surface Toolbox. Make sure you choose the right machine architecture, i.e. you should install the
A Zip file containing the Macro enabled Excel file used in this post and a very simple Nexus Response Surface can be downloaded from here
We now need to define an Excel Macro capable to call the Nexus core libraries, to load the selected Response Surface (saved from within Nexus as a *.rss file). The following sections will guide you throughout the whole process.
The post is organized in the following sections:
- linking the iChrome DLLs within an Excel Macro
- initializing (and releasing) the Response Surface Module
- evaluating the response surface in Excel
- using the response surface in Excel spreadsheets
- some interesting references (for Geek)
Linking the iChrome DLLs within an Excel Macro
The first step required to use a Nexus Response surface within Excel is to load the Nexus DLLs via an Excel Macro.
Hence, create a new Macro for your current Workbook and click to edit it. Then Cut and paste the following section of code at the very beginning of your Macro, in the Microsoft VB IDE.
' ' Import DLL symbols within Excel (make sure path are correct) ' Public Const libPath As String = ''D:\NEXUS_RSTOOL_V3\'' Declare Function wLoadResponseSurface _ Lib ''D:\NEXUS_RSTOOL_V3\nxRspSrfXll.xll'' _ Alias ''_wLoadResponseSurface@16'' (ByVal X As Variant) As Long Declare Function wFreeResponseSurface _ Lib ''D:\NEXUS_RSTOOL_V3\nxRspSrfXll.xll'' _ Alias ''_wFreeResponseSurface@4'' (ByVal id As Long) As Boolean Declare Function wGetNInputs _ Lib ''D:\NEXUS_RSTOOL_V3\nxRspSrfXll.xll'' _ Alias ''_wGetNInputs@4'' (ByVal id As Long) As Long Declare Function wGetNOutputs _ Lib ''D:\NEXUS_RSTOOL_V3\nxRspSrfXll.xll'' _ Alias ''_wGetNOutputs@4'' (ByVal id As Long) As Long Declare Function wEvalResponseSurface _ Lib ''D:\NEXUS_RSTOOL_V3\nxRspSrfLib.dll'' _ Alias ''_wEvalResponseSurface@12'' _ (ByVal id As Long, ByRef X() As Double, ByRef Y() As Double) As Boolean ' used for lazy initialization of the Response Surface module of Nexus Dim rsID As Long
Make sure library path at line 4 and following DLLS declarations are set correctly, pointing to your latest version of the Nexus Response Surface toolbox.
A more detailed explanation of the code lines above can be found on the Microsoft support page How to: Access DLLs in Excel, but basically and without too many details the rows of code above make the functions contained in the Nexus Response Surface redistributable libraries visible in Excel.
Also note the definition al line 36 where a global long variable rsID is used for a lazy initialization of the Response Surface module. The variable will be initialized as soon as a Cell in the Excel workbook will require the evaluation of the response surface.
Initializing (and Releasing) the Response Surface
The Response Surface redistributable module of Nexus requires four main steps to work:
- module initialization: this step loads the Nexus Response Surface (RS) module in memory
- load the Response Surface: this step follows the RS module initialization and aims at loading a predefined Response Surface
- calling the Response Surface: once the module has been initialized and the selected Response Surface has been loaded in memory, users can call the evaluation utilities on the response surface to compute new results
- release the Response Surface module: once you finished to use the Response Surface module, this should be unloaded from memory
The Visual Basic code of this section covers step 1) and 2)
Lazy Response Surface Module initialization is achieved using global variable rsID. We thus define subroutine loadRS() to initialize the Nexus module (see line 42) and to attempt loading the Response Surface from a *.rss file, as reported at line 47.
Sub loadRS() If rsID = 0 Then ChDir libPath lib = ''C:\users\iChrome\Desktop\RS.rss'' rsID = wLoadResponseSurface(lib) If rsID < 0 Then MsgBox ''Unable to Loaded Response Surface '' + lib End If End If End Sub
The Visual Basic code of this section covers step 3), i.e. how to release the resource allocated by Nexus to lo use the Response Surface.
We use the Excel VB event Auto_Close, automatically called when Excel closes up and release resources. Note that we attempt to download the response surface only if this has been previously loaded in memory correctly, i.e. if variable rsID is greater than 0
Sub Auto_Close() ChDir libPath If rsID > 0 Then isOk = wFreeResponseSurface(rsID) End If End Sub
Evaluating the Response Surface in Excel
Once the response surface is loaded in memory, we can define a simple VB function to be used as a Formula within any cell of the open Workbook.
In the example below, we suppose the response surface receives two inputs and compute one output. As you load an existing response surface you should know upfront the number (and nature) of inputs and outputs. However the utility functions wGetNInputs(rsID) and wGetNOutputs(rsID) declared above can be used to check the number of inputs and outputs that a response surface loaded as input expects to receive.
Function evalRS(X As Double, Y As Double) loadRS Dim inps(1) As Double Dim outs(0) As Double inps(0) = X inps(1) = Y isOk = wEvalResponseSurface(rsID, inps, outs) evalRS = outs(0) End Function
Using the Response Surface in Excel Spreadsheets
Now we have all what we need to use the Response Surface within the Excel Workbook.
We can compute a value by calling the Nexus Response Surface directly from within Excel by using the new available formula evalRS(…) and passing as input the values of X and Y. The function will:
- make sure the Nexus RS RS.rss is loaded in memory (if not already loaded) with a lazy call to wLoadResponseSurface from within loadRS() VB subroutine
- evaluate the response surface via a call to wEvalResponseSurface
Function evalRS(…) can now be used in any Excel cell to actually evaluate the Response Surface as shown in the example below where a scatter chart is populated moving from the results returned by the Response Surface.
For computer Geek some interesting references explaining the technical solutions implemented in this post can be found here:
- Microsoft – How to: Access DLLs in Excel
- Jonathan R. Lhost – How to call C functions from Excel using a DLL written in C (or C++)