Node Positions Example |
 
|
Node Positions Example |
|
This article describes the creation of a VBA macro in Microsoft Excel 2010. The procedure should be similar for other versions of Excel.
The objective of the macro is to write the position of every node in the model to a file. Since the database contains a number of solution times, it is further required that a new file be generated for each solution time. Each file will contain the node number and position of every node in the model for that particular solution time. It is written in a Comma Separated Value (CSV) format to facilitate subsequent parsing. Its name will convey the associated solution time (e.g. “Time=98.2s.csv”).
The database from the dynamic analysis of K01 - Worked Example - Simple will be used for demonstration. This example should be run in Flexcom before proceeding, in order to generate the required database files.
Refer to the VBA section of the Excel Add-in documentation for details on how to enable the Developer tab in Excel and set up a VBA macro.
Next, open a new Excel spreadsheet and proceed through the instructions below. Note that the completed Workbook is named “GetNodePositions.xlsm” in this example folder.
Cell C2 will contain the path to the database to be used and the macro will read this file name when it runs. This allows you to easily change the database to be processed without going in and editing the macro itself.
The code for this example is given below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
Sub GeneratePositionFiles()
Dim DB As New FlexcomDatabaseAccess.DatabaseAccess Dim DatabaseFile As String
DatabaseFile = Range("C2").Value
Dim error As Boolean If ((DB.IsValidDatabase(DatabaseFile))) Then Dim NumTimeSteps As Integer NumTimeSteps = DB.GetSolutionTimeCount(DatabaseFile)
Dim NumNodes As Integer NumNodes = DB.GetNodeCount(DatabaseFile)
For iTime = 1 To NumTimeSteps Dim SolutionTime As Single SolutionTime = DB.GetTime(DatabaseFile, iTime)
Dim FilePath As String FilePath = Application.ActiveWorkbook.Path & "\Time=" & SolutionTime & "s.csv" Open FilePath For Output As #1
For iNode = 1 To NumNodes Dim UserNodeNumber As Integer UserNodeNumber = DB.GetUserNodeNumber(DatabaseFile, iNode) Dim x, y, z As Single x = DB.GetPosition(DatabaseFile, iTime, iNode, 1) y = DB.GetPosition(DatabaseFile, iTime, iNode, 2) z = DB.GetPosition(DatabaseFile, iTime, iNode, 3)
Write #1, UserNodeNumber, x, y, z Next iNode
Close #1
Next iTime
Else MsgBox ("Invalid Database") End If
End Sub |
To specify the path of the database file which contains the necessary information, declare a new string parameter and use the Range( ).Value function as shown on line 6 of the sample VBA code.
An If-Then-Else statement is used to perform a validity check of the database file. If it is valid, the program proceeds with the calculations or else it exits the program and displays an error message. This is shown on lines 9, 39, 40 and 41.
For a valid database file, the number of solution times is extracted using the DB.GetSolutionTimeCount( ) function and the number of nodes stored in the database is extracted using the DB.GetNodeCount( ) function, shown on lines 10 to 14.
The program then loops over all the stored time steps. Within this loop, the current solution time is first found using the DB.GetTime( ) function, given on lines 16 to 18.
Next, the path to the output file is defined as a string using the Application.ActiveWorkbook.Path( ) function and it is opened to allow the program to write to the file, shown on lines 20 to 22.
The program then loops over each node stored in the database. The user-specified node number is obtained using the DB.GetUserNodeNumber( ) function. This is given on lines 25 and 26. This step is performed as the user is able to arbitrarily assign numbers each node during the setup of the model. The output will thus be in agreement with the user node numbering used.
Next, the x, y and z coordinates of each node at the current time is found using the DB.GetPosition( ) function shown on lines 27 to 30.
Finally, the current user specified node number and its coordinates are written to the output file on line 32, before moving to the next node number. Once the program has cycled over each node, the time is increased and the previous steps are repeated.
A full description of each function which may be used is given in the Excel Add-in section.