This example demonstrates how to duplicate the contents of Multiframes Data window in Microsoft Excel, each of the tables in the window is represented as a separate worksheet in Excel. The spreadsheet associated with this example is distributed as part of the Multiframe application and may be found in the /Automation Samples/Excel/MFDataTable.xls located under the directory to which Multiframe was installed.
The Excel workbook contains worksheets representing each of the tables within Multiframes Data Window. Each of these worksheets contains a number of macros that import the data from Multiframe and present the information in the spreadsheet. As these macros are very similar for each table, this example will concentrate on the macros for importing the Joints table into Excel. The Excel representation of this table is shown in the diagram below.
This worksheet contains two buttons for importing the data from Multiframe. These call different scripts that demonstrate two techniques for importing the data. The first and simplest of these scripts accesses the properties of each node in turn. The execution of this code will be very slow as six (6) calls between the two applications are required for each node in the frame. The second script uses the list objects in Multiframe to extract all the nodal data from Multiframe in a total of only six (6) calls across the automation interface. For small frames, the difference in speed between the two subroutines is small, in the order of a factor of two. For large frames, the difference in speed will be dramatic and the slower of the subroutines will take a long time to execute. Each of these scripts is described in more detail later in this section.
The Joints worksheet contains 5 subroutines; the first two subroutines are command handlers for the two buttons that appear in that worksheet. The next subroutine stored with the Joints worksheet is the SetupUnits subroutine. This subroutine is responsible for setting the unit names displayed at the top of the table in row 5 of the spreadsheet.
'Set units in title of table
Private Sub SetupUnits(mfApp As Multiframe.Application)
Dim s As String
' Update Units in x,y,x columns
s =
mfApp.Preferences.GetUnit(Multiframe.mfUnitLength)
Range("JointData").offset(0,
2).Value = s
Range("JointData").offset(0,
3).Value = s
Range("JointData").offset(0,
4).Value = s
'Update Units in orientation columns
s =
mfApp.Preferences.GetUnit(Multiframe.mfUnitAngle)
Range("JointData").offset(0,
6).Value = s
Range("JointData").offset(0,
7).Value = s
Range("JointData").offset(0,
8).Value = s
End Sub
In this subroutine, the names of the length and angle units are obtained by calling the GetUnit method of the Multiframe Preferences object. These values are used to set the unit names in row 5 of the worksheet. The offset method of the Range object is used to identify a cell, relative to the JointData range, into which each value is inserted. The JointData range is a named range in the excel spreadsheet which contains the single cell A5.
The subroutine GetNodalData_Slow, which is listed below, performs the insertion of the nodal data from Multiframe into Excel.
' Get Joints table
Public Sub GetNodalData_Slow()
Dim mfApp As Multiframe.Application
Dim Node As Multiframe.Node
Dim i As Long
Dim s As String
Dim orient As Variant
'Capture errors elegantly
On Error GoTo ErrorHandler
' Get current Multiframe application
Set mfApp = New Multiframe.Application
'Erase old data and set units in title
ClearTable ("JointData")
Call SetupUnits(mfApp)
' Write data to worksheet
i = 0
For Each Node In mfApp.Frame.nodes
i = i + 1
Range("JointData").offset(i,
0).Value = Node.Number
Range("JointData").offset(i, 1).Value = Node.label
Range("JointData").offset(i, 2).Value = Node.x
Range("JointData").offset(i, 3).Value = Node.xyz(2)
Range("JointData").offset(i, 4).Value = Node.z
If (Node.pinned) Then
s = "Pinned"
Else
s = "Rigid"
End If
Range("JointData").offset(i, 5).Value = s
orient = Node.Orientation
Range("JointData").offset(i, 6).Value = orient(1)
Range("JointData").offset(i, 7).Value = orient(2)
Range("JointData").offset(i, 8).Value = orient(3)
Next
'Set format of cells
With Range("JointData")
Range(.offset(1, 2), .offset(i,
8)).NumberFormat = gFormat
End With
ErrorHandler:
If (Err <> 0) Then
MsgBox Err.Description
End If
End Sub
The first executable line of this subroutine defines the mechanism for reacting to any errors that may arise during the execution of the script. The next statements call subroutines that firstly clear the table of any existing data and then update the names of the units in the heading rows of the table using the SetupUnits subroutine described above. The ClearTable subroutine, which is defined in Module1 of the VBA project, takes a single parameter specifying a named range within the worksheet. It uses this range to construct a region to be erased by firstly selecting the column beneath this range down to the first empty cell. This selection is used to construct a range that contains the first 20 columns of the worksheet which is then is cleared of it content.
'Clear the contents of a specified range
Public Sub ClearTable(ByVal marker As String)
'Select first cell of data in table
range(range(marker).offset(1, 0),
range(marker).offset(1, 0)).Select
'Select first column of data
range(range(marker).offset(1, 0),
Selection.End(xlDown)).Select
'Clear contents of first 20 columns of
data
range(Selection, Selection.offset(0,
20)).ClearContents
End Sub
The next group of lines in the script performs the exchange of data between Multiframe and Excel. Rather than use a more traditional For/Next loop, a For Each loop provides the mechanism to loop over all the nodes in the frame.
For Each Node In mfApp.Frame.nodes
Next
This type of loop has been used as it simplifies the syntax of the code inside the loop and does no require a explicit call to reference the Node object. Inside this loop, the properties of each node are extracted and placed in the appropriate cell of the worksheet. This is performed by accessing cells relative to the range named JointData using the offset property of Excels Range object.
The next small block of code in the script formats the data in the table. This is done by specifying a value for the NumberFormat property of a Range object that contains all the data in the 3rd through to the 9th column of the table.
'Set format of cells
With Range("JointData")
Range(.offset(1, 2), .offset(i,
8)).NumberFormat = gFormat
End With
The constant gFormat, defined in Module1 of the VBA project, specifies a real number format to be used when displaying the tables in this workbook.
'Global real number format
Public Const gFormat As String = "##0.000"
By using a global constant, the format of real number can easily be changed throughout the entire workbook with only a change to a single line of code.
A much faster subroutine for importing the data from Multiframe is listed below. This subroutine utilised Multiframes list objects to significantly reduce the number of call between Excel and Multiframe and this provides a significant reduction in the time it takes to fill the table. The speed of the subroutine has also been improved by disabling the refresh of excel while the data is been inserted and by using Excels Find and Replace features to rapidly synthesis data into a presentable form.
Public Sub GetNodalData_Fast()
Dim mfApp As Multiframe.Application
Dim myNodeList As New
Multiframe.Nodelist
Dim TableData(1 To 9) As Variant
Dim nRows As Long
'Capture errors elegantly
On Error GoTo ErrorHandler
' Get current Multiframe application
Set mfApp = New Multiframe.Application
'Erase old data and set units in title
ClearTable ("JointData")
Call SetupUnits(mfApp)
'Extract nodal data to array
myNodeList.Add mfApp.Frame.nodes
TableData (1) = myNodeList.Number
TableData (2) = myNodeList.label
TableData (3) = myNodeList.x
TableData (4) = myNodeList.y
TableData (5) = myNodeList.z
TableData (6) = myNodeList.pinned
TableData (7) = myNodeList.OrientX
TableData (8) = myNodeList.OrientY
TableData (9) = myNodeList.OrientZ
'Disable refresh of worksheet
Application.ScreenUpdating = False
'Get number of rows of data
nRows = myFrame.nodes.Count
'Set format of cells
With Range("JointData")
'Insert raw data into table
With Range(.offset(1, 0),
.offset(nRows, 8))
.Value =
Application.WorksheetFunction.Transpose(TableData)
End With
'Set cell format in table
Range(.offset(1, 2), .offset(nRows,
8)).NumberFormat = gFormat
'Set joint type as "Pinned"
or "Rigid"
Call
Range(.offset(1,5),.offset(nRows,5)).Replace("FALSE","Rigid")
Call
Range(.offset(1,5),.offset(nRows,5)).Replace("TRUE","Pinned")
End With
'Enable refresh of worksheet
Application.ScreenUpdating = True
ErrorHandler:
If (Err <> 0) Then
MsgBox Err.Description
End If
End Sub
Much of the code at the start and end of this subroutine is the same as described above for the GetNodalData_Slow subroutine. It is only the part of the code that performs that actual exchange of data between the two applications that is significantly different. It is also much more complicated to understand due to the list objects and the use of variant data types.
The subroutine works by firstly defining the variable TableData that is an array of variants. Each variant in this array corresponds to a column in the table. The script then forms a list of elements containing all the nodes in the frame
myNodeList.Add mfApp.Frame.nodes
This list is used to extract the properties of each node as an array of values. This data is inserted into an element of the array TableData so that the TableData becomes essentially a two dimensional array containing all the data to be inserted into the worksheet.
TableData(1) = myNodeList.Number
TableData(2) = myNodeList.label
TableData(3) = myNodeList.x
TableData(4) = myNodeList.y
TableData(5) = myNodeList.z
TableData(6) = myNodeList.pinned
TableData(7) = myNodeList.OrientX
TableData(8) = myNodeList.OrientY
TableData(9) = myNodeList.OrientZ
The data is inserted into the worksheet by setting the Value property of a Range object representing the entire table equal to TableData. As Excel inserts an array in a row wise manner, it is necessary to first transform the data in TableData by transposing the rows and columns.
'Insert raw data into table
With Range(.offset(1, 0), .offset(nRows, 8))
.Value =
WorksheetFunction.Transpose(TableData)
End With
As the data flagging the nodes as pinned is inserted directly into the worksheet as a boolean value it is necessary to transform the boolean values of TRUE and FALSE into the more meaningful values of “Pinned” and “Rigid”. To perform this, the Replace method of the Range object is used to firstly set all occurrences of the word FALSE in the 6th column equal to “Rigid”. Similarly all the values of TRUE are replaced with the value “Pinned”.
'Set joint type as "Pinned" or "Rigid"
Call
Range(.offset(1,5),.offset(nRows,5)).Replace("FALSE","Rigid")
Call Range(.offset(1,5),.offset(nRows,5)).Replace("TRUE","Pinned")
Each of the other worksheets in the Excel workbook contains similar subroutines for importing data from Multiframe and reproducing the other tables in Multiframes Data Window. The structure of the subroutines is similar to those described above. As this worksheet demonstrates how to access most of the data describing a frame it is a good starting point when learning how to access information about a frame via the automation interface.
Handling Errors
When writing any program it is a good practice to elegantly handle any errors that may arise during the execution of the code. When writing scripts in VBA the On Error command is used to specify the action to be taken when an error occurs.
In the subroutines presented above, the On Error command is used to specify that in the event of an error, execution of the script will be redirect to the label ErrorHandler. This label is at the very bottom of the subroutines and is followed by three lines of code that firstly determine if an error occurred and then report the error message to the user using the standard message box dialog. It is important to test for an error, as these three lines will be executed whenever the subroutines are executed irrespective of whether and error occurred or not.