This post features an Excel User Defined Function (UDF) to count the number of different element types (beams, plates, bricks and links) and the number of node restraints at each end of listed beams from a finite element model. The example uses data from the FEM package Strand7, but could easily be adapted to data from other packages. The most convenient way to transfer data from a Strand7 data file into an Excel spreadsheet is using the Strand7 API, which allows the data file to be read directly from VBA. This will be examined in more detail in later posts, but for maximum generality this post will describe how the data may be easily transferred by copying and pasting, using the on-line editor. A spreadsheet including full open source code for the described UDF may be downloaded from BeamEnds.xls
The screen shot below shows the Strand7 on-line editor displaying beam details. The details to be displayed may be selected in the “Columns” tab, and for the present purposes the only ones required are the beam property type and the two end node numbers. Having selected these columns for display the data may be selected using Ctrl-A and copied to the clipboard with Ctrl-Shift-C (or Ctrl-C to copy without beam numbers and column headers).
The data may then be pasted directly into the spreadsheet:
Note that the beam numbers and property types are pasted as text strings, rather than numbers. The required values may be extracted with a simple string formula:
A similar procedure is used to import details of the other element types included in the model (plates, bricks, and links) and a list of restrained nodes.
Having imported the data into Excel the nodes defining each element are read into a “dictionary object”. The advantage of the dictionary object is that it has an “exists” method allowing the presence of any specific node in a list to be determined quickly and efficiently. The dictionary object is part of the Microsoft Scripting Library, which is not enabled by default. The library must therefore be selected in the Tools-References dialog of the VBE, as shown below:
Typical use of the dictionary object is shown in the code below:
Dim BeamNodes As Scripting.Dictionary .. ' Set up element node dictionaries Set BeamNodes = New Scripting.Dictionary For i = 1 To NumBeams For j = 3 To 4 sNodeNum = BeamRangeA(i, j) If BeamNodes.Exists(Key:=sNodeNum) = False Then BeamNodes.Add sNodeNum, 1 Else ItemVal = BeamNodes.Item(sNodeNum) + 1 BeamNodes.Remove sNodeNum BeamNodes.Add sNodeNum, ItemVal End If Next j Next i .. ' For each beam node, check if node exits in any element dictionaries For j = 3 To 4 For i = 1 To NumOutBeams BeamOutA(i, 1) = BeamRangeA(i, 1) sNodeNum = BeamRangeA(i, j) If BeamNodes.Exists(sNodeNum) Then BeamOutA(i, (j - 3) * 5 + 2) = BeamNodes.Item(sNodeNum) - 1 End If Next i
- Each entry in the dictionary has a string “key” and an associated “item” which may be any data type. In this application the item is used to store the number of occurrences of the node number. Since there is no provision to edit a dictionary item it must be deleted and recreated.
- The “Exists” method is used to determine if each node is included in the dictionary object. In the case of the beams the nodes at each end of the beams being checked are included in the count, so the node connection count is reduced by 1.
Full code is included in the download file.
Use of the BeamEndsA function is illustrated in the screen show below:
Note that the function returns an array with 1 row for each beam in the input range, and 11 columns and must be entered as an array function:
- Enter the function
- Select the output range
- Press F2 to enter edit mode
- Press ctrl-shift-enter