Newton Excel Bach, not (just) an Excel Blog

A previous post looked at using VBA to generate more VBA code.  This post looks at automatic code generation on the spreadsheet, which may be transferred into the a VBA project using the techniques discussed in the earlier post, or simply copied and pasted.

I will use as an example generation of a number of functions for the Strand7 (finite element analysis program) API, but don’t be put off if this is not your area of interest.  The methods discussed are useful for any case where a number of similar routines are required, with differences that can be summarised in tabular form.

The Strand7 API allows external programs (including VBA) to communicate with and control the analysis routines within the Strand7 package.  This is potentially of great value, but this approach has the drawback of requiring the implementation of a large number of functions in the chosen language; over 1250 for the complete package.  Documentation for a typical function is shown in the screenshot below:

St7GetNodeResponseFunction documentation

To implement a function such as this in VBA so that it can be called as an Excel User Defined Function (UDF) the necessary steps are:

1. Read the required input data from the spreadsheet.
2. Convert it to the required data type for the API function.
3. Set up variables or arrays of the correct data type (and size for arrays) for the API output.
4. Call the API function.
5. Check that the API return value (iErr) equals 0.
6. If not, convert the error number to a text message, and assign this to the VBA function return value.
7. If iErr equals 0 then extract the required API function results from the appropriate array or value and assign them to the VBA function return value.

In the case of API functions with similar output the required code can be very similar, with differences only in the number, name and data type of function parameters, size of arrays, and the name of the function to be called.  Generation of the required code can be quickly and efficiently set up on a spreadsheet, allowing a large number of API functions to be generated in much reduced time.

The first stage is to tabulate the names and data types of the required function parameters, and the size of any output arrays.  For the Strand7 API I have first converted the manual pdf file to a text file, which I have then read into a spreadsheet.  From this I have tabulated the parameters and data types in the form shown below:

API Function Table (click for full size view)

This table lists parameter names, followed by their data type.  For Code generation purposes it is more convenient to extract only those functions with a similar purpose (all functions returning node attributes for instance), and to list all the parameter names in sequential columns, followed by the data types.  This rearrangement can be conveniently achieved using the Excel Index() function, as shown in the screenshot below:

Table of selected functions with rearranged parameters

The columns in this table are read directly from the main function table except for:

• Col C: VBA function name – shortened version of the API function name.
• Col B: Number of parameters – count of non-blank cells in columns F to K.
• C0l R: API function call – generated by the UDF CallS7Funcr() (see below).

Code for CallS7Funcr function:

Public Function CallS7FuncR(FuncName As Variant) As Variant
' Generate an S7 function call with data arranged in rows
Dim NumRows As Long, NumCols As Long, RtnString As String, NumParam As Long
Dim i As Long

FuncName = Range2Array(FuncName, NumRows, NumCols)

If NumRows < 1 Then
CallS7FuncR = "FuncName range must be a one row range"
Exit Function
End If
NumParam = FuncName(1, 1)
RtnString = "iErr = " & FuncName(1, 2) & "("
For i = 3 To NumParam + 2
RtnString = RtnString & FuncName(1, i)
If i < NumParam + 2 Then
RtnString = RtnString & ", "
Else
RtnString = RtnString & ")"
End If
Next i

CallS7FuncR = RtnString

End Function

The code used to generate the VBA functions is shown in the screenshots below:
An index number is entered in Cell D27, which returns the VBA function name, the number of parameters in the API function, and the number of values in the output array (or 0 if the output is a single value).  Row 30 is generated by a simple string function:
=”Function “&E27&”(uID As Long, NodeList As Variant, Optional ParamList As Variant) As Variant”
The following lines are either common to all the functions (e.g. lines 37 to 48) or use string functions to either return the required data, or a blank line if the line is not required for the current function:

Function code generator - Part 1

The UDF uses two functions to collect the data from the spreadsheet.  GetInput() (Row 47) simply reads the data from the specified range (Nodelist) and converts it into an array, returning the number of rows and number of columns in the array.  GetParam() (Row 52) reads the value at the specified position from the range specified in ParamList.  If this is a numerical value this is assigned to the function return value, but if it is a text string the function reads the data in the specified range, and returns that as a variant array.

The GetParam function is called up to 3 times, followed by a For, Next loop calling the API function for each node specified in NodeList:

Function code generator - Part 2

Function code generator - Part 3

The line calling the API function (Row 92) is simply called from the range R3:R24 using an Index function.

The code generated by the spreadsheet as presented here contains many blank lines when the function has less than the maximum number of input parameters.  This may be simply copied and pasted into a Visual Basic Editor code module, where it may be edited by hand.  Alternatively an edited code range could be set up on the spreadsheet, omitting blank lines, and this code could then be copied automatically using the techniques discussed in the earlier post, making the process entirely automatic.

The next post will look at how the code generated by this technique can be checked and then used in conjunction with the Strand7 FEA package.