STAAD.Pro Help

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.
Parent topic: OS. Examples
Related reference

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.

OpenSTAADExample

Sub OpenSTAADExample()

    Dim objOpenSTAAD As Object
    Dim stdFile As String
    Dim BaseUnit As Integer
    Dim strLength As String, strForce As String
    
    Dim PrimaryLCs As Integer, LoadCombs As Integer, totalLoads As Integer
    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
        
    'Launch OpenSTAAD Object
    Set objOpenSTAAD = GetObject(, "StaadPro.OpenSTAAD")
    
    'Load your STAAD file - make sure you have successfully run the file
    objOpenSTAAD.GetSTAADFile stdFile, "TRUE"
    If stdFile = "" Then
        MsgBox "This macro can only be run with a valid STAAD file loaded.", vbOKOnly
        Set objOpenSTAAD = Nothing
        Exit Sub
    End If

    Cells(2, 2).Value = stdFile
    
    MemberNo = Cells(7, 2).Value
    
    'Check the member exists
    Length = objOpenSTAAD.Geometry.GetBeamLength(MemberNo)
    
    If Length = 0 Then
        MsgBox "Please check the member number is valid.", vbOKOnly
        Set objOpenSTAAD = Nothing
        Exit Sub
    End If
    
    'Find out how many primary load cases and load combinations you have:-
    PrimaryLCs = objOpenSTAAD.Load.GetPrimaryLoadCaseCount()
    Cells(4, 2).Value = PrimaryLCs
    
    LoadCombs = objOpenSTAAD.Load.GetLoadCombinationCaseCount
    Cells(5, 2).Value = LoadCombs
    
    totalLoads = PrimaryLCs + LoadCombs
    
    If totalLoads > 10 Then
        MsgBox ("This macro is designed for up to 10 load cases only")
        Set objOpenSTAAD = Nothing
        Exit Sub
    End If
         
    'What are the units?
    BaseUnit = objOpenSTAAD.GetBaseUnit
    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")
        Set objOpenSTAAD = Nothing
        Exit Sub
    End If
    
    Cells(4, 6) = strLength
    Cells(5, 6) = strForce
    
    'Iterate through your load sets to find the results for each load case
    Dim lstLoadPrimaryNums() As Long
    ReDim lstLoadPrimaryNums(PrimaryLCs)
    Dim lstLoadCombinationNums() As Long
    ReDim lstLoadCombinationNums(LoadCombs)
    
    Dim lstLoadNum() As Long
    Dim lstLoadName() As String
    ReDim lstLoadNum(totalLoads)
    ReDim lstLoadName(totalLoads)
    
    objOpenSTAAD.Load.GetPrimaryLoadCaseNumbers lstLoadPrimaryNums
    objOpenSTAAD.Load.GetLoadCombinationCaseNumbers lstLoadCombinationNums
    
    For i = 0 To PrimaryLCs - 1
        lstLoadNum(i) = lstLoadPrimaryNums(i)
        lstLoadName(i) = objOpenSTAAD.Load.GetLoadCaseTitle(lstLoadPrimaryNums(i))
    Next i
        
    For i = 0 To LoadCombs - 1
        lstLoadNum(i + PrimaryLCs) = lstLoadCombinationNums(i)
        lstLoadName(i + PrimaryLCs) = objOpenSTAAD.Load.GetLoadCaseTitle(lstLoadCombinationNums(i))
    Next i
    
    'LoadPrevious = 0
    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
        objOpenSTAAD.Output.GetMemberEndForces MemberNo, lEnd, lstLoadNum(i), EndForceArray
        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
        objOpenSTAAD.Output.GetMemberEndForces MemberNo, lEnd, lstLoadNum(i), EndForceArray
        For j = 0 To 5
            'Print the result values in specified cells
            Cells(j + 20, i + 2).Value = EndForceArray(j)
        Next
         'objOpenSTAAD.GetMinBendingMoment MemberNo, "MZ", LoadNext, MaxSaggingMoment
        objOpenSTAAD.Output.GetMinMaxBendingMoment MemberNo, "MZ", lstLoadNum(i), DMax, dMaxPos, DMin, dMinPos
        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
    
    Set objOpenSTAAD = Nothing
    
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