OS. Write an OpenSTAAD Macro in Excel
This example will introduce you to several concepts necessary to writing OpenSTAAD macros in VBA.
OS. Instantiate OpenSTAAD in VBA
In the this set of steps, you will instantiate OpenSTAAD and have the spreadsheet macro retrieve information from the open STAAD.Pro model.
Within your subroutine, type Dim
objOpenSTAAD As Object.
Notice how as you begin typing the word "Object", a small list opens with relevant terms. This is called IntelliSense and it can help you quickly and accurately complete code lines.
- Press <Return>.
- Type Set objOpenSTAAD = GetObject(,StaadPro.OpenSTAAD) This assigns the OpenSTAAD application object to the variable you defined in Step 1.
- Press <Return>.
OS. Use geometry functions in spreadsheet
"Nodes:" and then press <Return>.
Much like the "Hello World" example, this simply populates a cell with a text string. Here, it’s useful to provide a label for your data.
objOpenSTAAD.Geometry.GetNodeCount and then press <Return>.
Here, you are assigning the value of a cell to the returned value of the GetNodeCount function, which is in the Geometry class within the OpenSTAAD object.
This approach accesses the GetNodeCount function directly within the objOpenSTAAD object.
- Type Dim objGeometry As OSGeometryUI and then press <Return>. If you connected the VBA Editor to STAAD, then you will notice that the editor will begin auto-completion when you start typing OSGeometryUI. You can press <Tab> to accept the highlighted result in the auto-completion pop-up list and move to the end of that entry.
- Type Set geometry = objOpensTAAD.geometry and then press <Return>. This will assign the object geometry to the geometry class within the OpenSTAAD object.
- Type Sheet1.[A2].Value = "Members:" and then press <Return>.
- Type Sheet1.[B2].Value = geometry.GetMemberCount and then press <Return>. Here, you will notice that the auto-completion menu opens again once you type geometry. to show you all of the functions and methods within the geometry class in OpenSTAAD. This is the benefit of first setting the object to that class.
- Run your macro. The spreadsheet populates with the number of nodes and members in the currently open STAAD.Pro model.
The spreadsheet will now populate with the number of nodes and members in the currently open STAAD.Pro model.
Sub OpenSTAADTutorial() Dim objOpenSTAAD As Object Dim stdFile As String Set objOpenSTAAD = GetObject(, "StaadPro.OpenSTAAD") Sheet1.[A1].Value = "Nodes:" Sheet1.[B1].Value = objOpenSTAAD.geometry.GetNodeCount Dim geometry As OSGeometryUI Set geometry = objOpenSTAAD.geometry Sheet1.[A2].Value = "Members:" Sheet1.[B2].Value = geometry.GetMemberCount End Sub
OS. Run Your Macro from within the Spreadsheet
To add a button or other interactive tools in your spreadsheet to activate your macro, use the following procedure.
- In your spreadsheet, select the Controls group on the Developer ribbon tab. tool in the
Click-and-drag a rectangle anywhere in your spreadsheet.
This will form the size and shape of the button, so something slightly less than 2x2 spreadsheet cells is useful.The Assign Macro dialog opens.
- Select the name of your subroutine from the Macro name list and then click OK. This assigns the button click action to run this subroutine.
- Click the new button (labeled Button 1) in your spreadsheet. The macro runs and the spreadsheet is populated with the node and member counts.
- Right-click on the button and select Edit Text to change the button label to something descriptive. Click anywhere outside of the button to exit the text editing state.