OS. Microsoft Excel Macro Example

A Microsoft Office Excel spreadsheet which can be used to check the capacity of a rectangular, reinforced concrete beam.

This spreadsheet file checks the bending capacity of a rectangular concrete beam with the reinforcement already laid out. The capacity is checked against the maximum sagging moment produced from a series of load cases. The beam is analyzed in STAAD.Pro. The results are extracted and linked into Excel using OpenSTAAD and VBA.

1. In STAAD.Pro, select the Sample Models link on the Open tab of the Start page.
2. Select US/US-8 Concrete Design for a Space Frame.STD and the click Open.
3. Selet the Run Analysis tool in the Analysis group on the Analysis and Design ribbon tab. The macro relies on having analysis results.
4. Open the C:\Users\Public\Public Documents\STAAD.Pro CONNECT Edition\Samples\OpenSTAAD\Rectangle-Beam.xls file in Microsoft Office Excel.
Note: Excel will warn of macros present in the file. Simply click the Optionsâ€¦ button in the Security Warning message area to open the Microsoft Office Security Options dialog, where you will select the Enable this Content option and click OK.
5. Select the STAAD.Pro Output worksheet.

The calculations for checking the capacity are located on the sheet marked "Concrete" while the extraction of the values from STAAD occurs on the sheet marked "STAAD.Pro Output."

6. Type a member number in cell B7. Member 14 is entered by default.
7. Click Results. The STAAD.Pro results are read into the spreadsheet along with the beam size. Then the Concrete worksheet is selected for you.
The rest of the Concrete worksheet performs concrete beam design for the selected member based on the moment and dimensions.

Excel Macro Code

To see what the OpenSTAAD macro is doing, use the developer tools to open the Visual Basic Editor. A macro named OpenSTAADExample has been created to retrieve the maximum sagging moment along the span of the beam as well as the width and depth of the cross-section of the beam. The maximum sagging moment for each load case is extracted, and the governing moment is the largest compression moment of the maximum moments. A second macro named Clear is provided which clears the results from the spreadsheet cells.

``````Sub OpenSTAADExample()

Dim stdFile As String
Dim BaseUnit As Integer
Dim strLength As String, strForce As String

Dim EndForceArray(6) As Double
Dim lEnd As Long

Dim MemberNo As Long
Dim Length As Double, Depth As Double, Width As Double
Dim DMin As Double, dMinPos As Double, DMax As Double, dMaxPos As Double
Dim Ax As Double, Ay As Double, Az As Double, Ix As Double, Iy As Double, Iz As Double

Dim i As Integer, j As Integer

If stdFile = "" Then
MsgBox "This macro can only be run with a valid STAAD file loaded.", vbOKOnly
Exit Sub
End If

Cells(2, 2).Value = stdFile

MemberNo = Cells(7, 2).Value

'Check the member exists

If Length = 0 Then
MsgBox "Please check the member number is valid.", vbOKOnly
Exit Sub
End If

'Find out how many primary load cases and load combinations you have:-
Cells(4, 2).Value = PrimaryLCs

MsgBox ("This macro is designed for up to 10 load cases only")
Exit Sub
End If

'What are the units?
If BaseUnit = 1 Then ' English
strLength = "Feet"
strForce = "KIP"
Else                 ' Metric
strLength = "Metre"
strForce = "KN"
MsgBox ("This macro is designed for up English units. Change STAAD.Pro>Configuration>Base Unit")
Exit Sub
End If

Cells(4, 6) = strLength
Cells(5, 6) = strForce

For i = 0 To PrimaryLCs - 1
Next i

For i = 0 To LoadCombs - 1
Next i

For i = 0 To totalLoads - 1
Cells(10, i + 2).Value = lstLoadNum(i)
Cells(11, i + 2).Value = lstLoadName(i)

'Get forces at start of member
lEnd = 0
For j = 0 To 5
'Print the result values in specified cells
Cells(j + 13, i + 2).Value = EndForceArray(j)
Next

'Get forces at end of member
lEnd = 1
For j = 0 To 5
'Print the result values in specified cells
Cells(j + 20, i + 2).Value = EndForceArray(j)
Next
Cells(27, i + 2).Value = DMin
Next

'Write the appropriate dimensions (for the beam) in the correct cells
Sheets("Concrete").Select

'Get Depth and Width of Concrete Beam
objOpenSTAAD.Property.GetBeamProperty MemberNo, Width, Depth, Ax, Ay, Az, Ix, Iy, Iz
'Depth
Cells(31, 7).Value = Depth
'Width
Cells(32, 7).Value = Width

End Sub``````

Clear

``````Sub Clear()
Dim i As Integer
Dim j As Integer

Cells(2, 2) = ""
Cells(4, 2) = 0
Cells(5, 2) = 0

Cells(4, 6) = ""
Cells(5, 6) = ""

For i = 1 To 10
For j = 1 To 18
Cells(j + 9, i + 1) = " "
Next j
Next i

End Sub``````