Newton Excel Bach, not (just) an Excel Blog

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).

Copying beam data from Strand7

The data may then be pasted directly into the spreadsheet:

Beam data pasted into Excel

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:

Extracting Beam numbers and property numbers as values

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:

Enabling Microsoft Scripting Library

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

Note that:

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:
BeamEndsA Function
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