Databases Reference
In-Depth Information
Bmatrix = Bmatrix &
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name)
.Cells(row, Col).Value & Chr(32)
Else
Bmatrix = Bmatrix &
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).
Cells(row, Col).Value & "; "
End If
Next Col
Next row
'Remove last ";" and add "];"
Bmatrix = Mid(Bmatrix, 1, Len(Bmatrix) - 2) & "];"
Now that Matlab-“friendly” strings have been constructed, the command strings must be sent
to the Matlab server. The following series of commands sends the strings to Matlab, which in turn
assigns the values specified in the strings to the
a
and
b
matrices.
'Execute Matlab Commands for Multiple Linear Regression
SendMatlabCmd = MatLab.Execute(Amatrix)
SendMatlabCmd = MatLab.Execute(Bmatrix)
With the matrices assigned, the system can easily be solved using the following series of
commands. Here the commands are given to calculate the MLRC coefficients and the residuals.
The results of the calculations are passed back to Excel in the form of a string. The string to
capture the results of the calculations is set equal to the command executed. The returned string
will contain all of the results separated by spaces (known as space delimiting). Unfortunately,
the returned string will also contain other information such as matrix identification “x =,” “r =,”
etc. The results must be separated out from the identification information. The first step in
accomplishing this is to utilize the
Split
function, which will tokenize the returned string using
a delimiter specified by the user. The tokens will be returned into an array, in this case,
MLRCArray() and ResidArray().
'Calculate MLRC
MLRC_Results
=
MatLab.Execute("x = a\b")
MLRCArray() = Split(MLRC_Results, " ")
'Calculate Residuals
Residuals
=
MatLab.Execute("r = (a*x-b)'")
ResidArray() = Split(Residuals, " ")
The next section of code loops through each array of tokens in sequential order from the
returned results and determines the start of valid data. This is done by means of a flag that is tripped
(to True) when the valid results are about to begin. The flag is tripped to True when the “=” character
is found within a token. Subsequent tokens are then taken as valid results, provided they are numeric.
The rationale behind this algorithm is that results are returned in a format similar to this:
x =
2.7000
1.3000
0.4000
0
−
