Example: Excel VBA

VBA

VBA (Visual Basic for Applications) is a programming language from Microsoft, primarily used for macro programming in applications within the Microsoft Office suite (e.g., Excel, Word, PowerPoint, Access). With VBA, users can automate tasks and add custom functionalities to these applications.

Here are the key features and purposes of VBA:

1. Programming Language: VBA is based on the Visual Basic language, supporting programming language features. This allows users to write code using conditions, loops, variables, functions, objects, classes, and modules.
2. Automation: VBA enables the automation of repetitive tasks. For example, processing worksheet data in Excel or generating documents based on specific conditions.
3. User-Defined Functions and Macros: Users can create custom functions and macros using VBA. This is useful for extending the functionality of existing applications or developing solutions for specific requirements.
4. Event Handling: VBA allows users to handle events occurring in applications (e.g., button clicks, worksheet changes), enabling the creation of interactive and responsive applications.
5. Macro Recording: Users can record the actions of everyday tasks to generate some VBA code, which can be edited automatically. This facilitates the quick generation of code.
6. Access Database Programming: VBA provides functionality to interact with databases in applications like Microsoft Access.

To use VBA, users utilize the built-in VBA editor in each Microsoft Office application to write and execute code. VBA is employed for various tasks, including business process automation, report generation, data processing, and implementation of custom forms and functions. While powerful, becoming familiar with writing and debugging code is necessary when working with VBA.

Welcome to VBA

To access VBA in MS Office products, press ALT+F11 or go to the "Developer" tab. There is no particular installation required in VBA to send REST API requests.

JSON in VBA

The user typically needs assistance from external libraries to handle JSON data in VBA (Visual Basic for Applications). VBA itself does not have built-in functions or libraries for processing JSON. To use an external library for handling JSON data, you can leverage libraries like 'VBA-JSON.' The following outlines how to handle JSON data in VBA.

1. Installation of the VBA-JSON Library: The VBA-JSON library provides functions for parsing and generating JSON data. First, you need to download and install this Library. The VBA-JSON library is available on GitHub, and you can download the JsonConverter.bas file and add it to your VBA project.  GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

2. Adding the Library to the VBA Project: To add the JsonConverter.bas file from VBA-JSON to your VBA project, follow these steps:

   1. Open the VBA project and select the 'Module' tab.

   2. Choose the "“File"” menu and click on "“Import File."”

   3. Select the JsonConverter.bas file to import.

3. Adding VBA Reference: To utilize the JsonConverter.bas file, VBA's dictionary data type needs to be used as Early binding. This requires adding a reference to "Microsoft Scripting Runtime."

   Following these steps:

   1. Execute the Reference in the Tools menu.

   2. Locate "Microsoft Scripting Runtime," activate the checkbox, and click " OK."

VBA - JSON

• JSON Data Parsing

You can use the ParseJson function from the above installed Library to parse JSON data in VBA. The following is an example of parsing JSON data.

Option Explicit

Sub jsonParsing()

    'Convert to VBA data by parsing JSON string
    Dim jsonData As String
    Dim data As Scripting.Dictionary
    
    jsonData = "{""name"": ""John"", ""age"": 30, ""city"": ""New York""}"
    Set data = JsonConverter.ParseJson(jsonData)
    
    'Print result
    Debug.Print data("name")  'John
    
End Sub 

• Creating JSON Data

To convert VBA data to JSON in VBA, you can use the ConvertToJson function. The following is an example of converting a VBA dictionary to a JSON string.

Option Explicit

Sub jsonParsing()

    'Convert VBA dictionary to JSON string 
    Dim jsonData As String
    Dim data As Scripting.Dictionary
    Set data = New Dictionary
    
    data.Add "name", "John"
    data.Add "age", 30
    data.Add "city", "New York"
    
    jsonData = JsonConverter.ConvertToJson(data)
    
    'Print result
    Debug.Print (jsonData) '{"name":"John","age":30,"city":"New York"}
    
End Sub 

Exercise Examples

Let's go through an example of creating a simple beam with a rectangular section using VBA.

Considering the characteristics of VBA, we will use Excel sheets for this example.

• First, create an input window where put variables on the Sheets.

• Creating REST API Request as a function.

Option Explicit

Function WebRequest(Method As String, Command As String, Body As String) As String

    Dim TCRequestItem As Object
    Dim baseURL As String
    Dim URL As String
    Dim MAPI_Key As Variant
    
    Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    'SetTimeouts(resolveTimeout, ConnectTimeout, SendTimeout, ReceiveTimeout)
    TCRequestItem.SetTimeouts 200000, 200000, 200000, 200000
    
    baseURL = Cells(2, 7).Value
    MAPI_Key = Cells(3, 7).Value
    
    URL = baseURL & Command
    TCRequestItem.Open Method, URL, False
    TCRequestItem.SetRequestHeader "Content-type", "application/json"
    TCRequestItem.SetRequestHeader "MAPI-Key", MAPI_Key
    TCRequestItem.Send Body
    WebRequest = TCRequestItem.ResponseText
    
    Debug.Print Command & " : " & TCRequestItem.Status & " - " & TCRequestItem.StatusText

End Function 

• Creating Sub Module to generate SimpleBeam.

Sub CreateSimpleBeam()
    
    Dim i, j, k As Integer
    
    'input data from sheets
    Dim dist As String
    Dim force As String
    Dim length As Double
    Dim height As Double
    Dim width As Double
    Dim direction As String
    Dim loadValue As Double
    Dim modelID As Range
    Dim loadCase As Range
    Dim matSt As String
    Dim matDB As String
    
    dist = UCase(Cells(5, "E").Value)
    force = UCase(Cells(6, "E").Value)
    
    length = Cells(8, "E").Value
    height = Cells(9, "E").Value
    width = Cells(10, "E").Value
    
    direction = Cells(9, "I").Value
    loadValue = Cells(9, "J").Value
    
    matSt = Cells(5, "J").Value
    matDB = Cells(6, "J").Value
    
    Set loadCase = Range(Cells(12, "I"), Cells(13, "J"))
    Set modelID = Range(Cells(12, "E"), Cells(15, "E"))
    
    'Dictionary
    Dim dicMain As Scripting.Dictionary
    Dim dicSub1 As Scripting.Dictionary
    Dim dicSub2 As Scripting.Dictionary
    Dim dicSub3 As Scripting.Dictionary
    Dim dicSub4 As Scripting.Dictionary
    Dim response As String
    Dim body As String
    
    'Create New File
    response = WebRequest("POST", "/doc/new", "{}")
    Debug.Print response
    
    'Create Unit Body and Request API
    Set dicMain = New Dictionary
    Set dicSub1 = New Dictionary: Set dicSub2 = New Dictionary
    
    dicSub2.Add "DIST", dist
    dicSub2.Add "FORCE", force
    
    dicSub1.Add "1", dicSub2
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("PUT", "/db/unit", body)
    Debug.Print response
    
    Set dicMain = Nothing
    Set dicSub1 = Nothing: Set dicSub2 = Nothing
    
    'Create Material Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    Set dicSub2 = New Dictionary: Set dicSub3 = New Dictionary
    
    dicSub3.Add "P_TYPE", 1
    dicSub3.Add "STANDARD", matSt
    dicSub3.Add "DB", matDB
    
    dicSub2.Add "TYPE", "CONC"
    dicSub2.Add "NAME", matDB
    dicSub2.Add "PARAM", Array(dicSub3)
    
    dicSub1.Add modelID(1, 1), dicSub2
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/matl", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    Set dicSub2 = Nothing: Set dicSub3 = Nothing
    
    'Create Section Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    Set dicSub2 = New Dictionary: Set dicSub3 = New Dictionary: Set dicSub4 = New Dictionary
    
    dicSub4.Add "vSIZE", Array(height, width)
    
    dicSub3.Add "USE_SHEAR_DEFORM", True
    dicSub3.Add "SHAPE", "SB"
    dicSub3.Add "DATATYPE", 2
    dicSub3.Add "SECT_I", dicSub4
    
    dicSub2.Add "SECTTYPE", "DBUSER"
    dicSub2.Add "SECT_NAME", "Rectangular"
    dicSub2.Add "SECT_BEFORE", dicSub3
    
    dicSub1.Add modelID(2, 1), dicSub2
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/sect", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    Set dicSub2 = Nothing: Set dicSub3 = Nothing: Set dicSub4 = Nothing
    
    'Create Node Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    
    Dim num_division As Long
    Dim interval As Double
    
    num_division = 20
    interval = length / num_division
    
    For i = 0 To num_division
        Set dicSub2 = New Dictionary
        
        dicSub2.Add "X", i * interval
        dicSub2.Add "Y", 0
        dicSub2.Add "Z", 0
        
        dicSub1.Add modelID(3, 1) + i, dicSub2
        
        Set dicSub2 = Nothing
    Next i
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/node", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
   
    'Create Element Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
        
    For i = 0 To num_division
        Set dicSub2 = New Dictionary
        
        dicSub2.Add "TYPE", "BEAM"
        dicSub2.Add "MATL", modelID(1, 1)
        dicSub2.Add "SECT", modelID(2, 1)
        dicSub2.Add "NODE", Array(modelID(3, 1) + i, modelID(3, 1) + i + 1)
        
        dicSub1.Add modelID(4, 1) + i, dicSub2
        
        Set dicSub2 = Nothing
    Next i
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/elem", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    
    'Create Boundary Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    Set dicSub2 = New Dictionary: Set dicSub3 = New Dictionary
    
    dicSub3.Add "ID", 1
    dicSub3.Add "CONSTRAINT", "1111000"
    
    dicSub2.Add "ITEMS", Array(dicSub3)
    
    dicSub1.Add modelID(3, 1), dicSub2
    
    Set dicSub2 = Nothing: Set dicSub3 = Nothing
    Set dicSub2 = New Dictionary: Set dicSub3 = New Dictionary
    
    dicSub3.Add "ID", 1
    dicSub3.Add "CONSTRAINT", "0111000"
    
    dicSub2.Add "ITEMS", Array(dicSub3)
    
    dicSub1.Add modelID(3, 1) + num_division, dicSub2
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/cons", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    Set dicSub2 = Nothing: Set dicSub3 = Nothing
    
    'Create Load Cases and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    
    For i = 0 To loadCase.Rows.Count - 1
        Set dicSub2 = New Dictionary
        
        dicSub2.Add "NAME", loadCase(i + 1, 2)
        dicSub2.Add "TYPE", "USER"
        
        dicSub1.Add i + 1, dicSub2
        
        Set dicSub2 = Nothing
    Next i
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/stld", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    
    'Create Self-Weight Load Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    Set dicSub2 = New Dictionary
    
    dicSub2.Add "LCNAME", loadCase(1, 2)
    dicSub2.Add "FV", Array(0, 0, -1)
    
    dicSub1.Add "1", dicSub2
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/bodf", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    Set dicSub2 = Nothing
    
    'Create Beam Load Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    
    For i = 0 To num_division - 1
        Set dicSub2 = New Dictionary: Set dicSub3 = New Dictionary
        
        dicSub3.Add "ID", 1
        dicSub3.Add "LCNAME", loadCase(2, 2)
        dicSub3.Add "CMD", "BEAM"
        dicSub3.Add "TYPE", "UNILOAD"
        dicSub3.Add "DIRECTION", direction
        dicSub3.Add "D", Array(0, 1)
        dicSub3.Add "P", Array(loadValue, loadValue)
        
        dicSub2.Add "ITEMS", Array(dicSub3)
        
        dicSub1.Add modelID(4, 1) + i, dicSub2
        
        Set dicSub2 = Nothing: Set dicSub3 = Nothing
    Next i
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/bmld", body)
    Debug.Print response
    
    Set dicMain = Nothing: Set dicSub1 = Nothing
    
    'Create Load Combinations Body and Request API
    Set dicMain = New Dictionary: Set dicSub1 = New Dictionary
    Set dicSub2 = New Dictionary

    Dim vCOMB() As Object
    ReDim vCOMB(loadCase.Rows.Count - 1)
    
    For i = 0 To loadCase.Rows.Count - 1
        Set dicSub3 = New Dictionary
        
        dicSub3.Add "ANAL", "ST"
        dicSub3.Add "LCNAME", loadCase(i + 1, 2)
        dicSub3.Add "FACTOR", loadCase(i + 1, 1)
        
        Set vCOMB(i) = dicSub3
        
        Set dicSub3 = Nothing
    Next i
    
    dicSub2.Add "NAME", "Comb1"
    dicSub2.Add "ACTIVE", "ACTIVE"
    dicSub2.Add "iTYPE", 0
    dicSub2.Add "vCOMB", vCOMB
    
    dicSub1.Add "1", dicSub2
    
    dicMain.Add "Assign", dicSub1
    
    body = JsonConverter.ConvertToJson(dicMain)
    response = WebRequest("POST", "/db/lcom-gen", body)
    Debug.Print response
   
    Set dicMain = Nothing: Set dicSub1 = Nothing
    Set dicSub2 = Nothing
    
End Sub 

• Let's create a button to execute the sub-module that we've previously created. Select the button from the Developer tab. (Developer tools can be activated in Excel Options under Customize Ribbon settings.)

• After creating the button in a suitable location, you can see the available macro modules below. Select and apply the pre-inserted sub-module.

• Run MIDAS CIVIL NX, connect to the API server, enter the base URL/MAPI-Key in API Settings, and execute the button.

Please refer the attached file that completed the above steps.

Now, the intended structure has been created in MIDAS CIVIL NX.

Additional Example

PSC Beam Girder with VBA in Excel

This PSC Beam Girder is a Steel Crossbeam example using Excel VBA based on Highway Bridge Design Standard, Korea.

The Best Tool for Structural Engineers

VBA can exert its greatest power when integrated with the created Excel structural calculation sheets.

Especially when repeatedly entering analysis results into the calculation sheet, API is an excellent choice.

We look forward to your first step to MIDAS Open API, starting with VBA.