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:
- 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.
- Automation: VBA enables the automation of repetitive tasks. For example, processing worksheet data in Excel or generating documents based on specific conditions.
- 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.
- 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.
- 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.
- 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.
-
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
-
Adding the Library to the VBA Project: To add the JsonConverter.bas file from VBA-JSON to your VBA project, follow these steps:
-
Open the VBA project and select the 'Module' tab.
-
Choose the "“File"” menu and click on "“Import File."”
-
Select the JsonConverter.bas file to import.
-
-
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:
-
Execute the Reference in the Tools menu.
-
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.
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.
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.
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(15, "H").Value
MAPI_Key = Cells(16, "H").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.
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.