(EN) MIDAS Civil with VBA

Let's start to use midas Civil API with VBA.
The VBA is powerful but easy to learn and access for Excel users. I already showed many examples before, so now I focus on handling the VBA.

1️⃣ JSON Data Handling

I recommend using Dictionary Class for handling the JSON data in VBA.

For example, I started to make a script with String data foam initially; however, when JSON data was getting longer, it was hard to use.
Especially, handling the data using Get Method is difficult. Fortunately, there are codes to convert Dictionary class to JSON format, and we can use these codes to make scripts.

Dictionary Class

Dictionary Class can be activated following steps: Menu → Tool → Reference → Microsoft Scripting Runtime

You don’t need to active Dictionary Class as below, but I recommend using with activating for convenience.

'A Method Declaration with deactivate (late binding)
    Dim DicEx As Object 'Declare
    Set DicEx = CreateObject("Scripting.Dictionary") 'Create
  
'A Method Declaration with activate (early binding)
    Dim DicEx As Dictionary 'Declare
    Set DicEx = New Dictionary 'Create

Dictionary Class has the setting value as below.

Properties

Count : Return the number of saved objects currently.
Item(“KeyValue”) : Call or substitute the designated key value.
Key(“KeyValue”) : Call or substitute the designated key value.
CompareMode : Return after setting a mode to compare String in the Dictionary objects (There are Binary and text mode, but you don’t have to change it)

Method

Add : Add key and value. The error will occur when you add existed key.
Remove : Remove key and designated value. The error will occur when a key does not exist.
RemoveAll : Remove all keys and values.
Exists : When the designated key exists, then return True, nor return False.
keys : Return all keys as the sequence.
Items : Return all keys as the sequence.

JsonConvertor

VBA does not support JSON format officially, so use JsonConvertor through the below link.

GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

2️⃣ Dictionary applied example

Example1 : The basic using method of Dictionary Class.

Example 1

Option Explicit
Option Base 1

Private Sub DicExample()

    Dim i As Integer
    Dim varItm, varMItm, varMKey As Variant
    Dim lngCnt As Long
    Dim blnKey As Boolean
    Dim dicExm As Dictionary
    
    'Designated Key and Value.
    Set dicExm = New Dictionary
    
    dicExm.Add 1, "Apple"
    dicExm.Add 2, "Banana"
    dicExm.Add "3", "Cherry"
    
    'Convert and print Dictionary Class to JSON format.
    Debug.Print JsonConverter.ConvertToJson(dicExm)
    Debug.Print "=================================================="
    
    'Return the number of objects.
    lngCnt = dicExm.Count
    Debug.Print lngCnt
    Debug.Print "=================================================="
    
    'Return specific key value.
    varItm = dicExm.Item(1)
    Debug.Print varItm
    Debug.Print "=================================================="
    
    'Convert all keys.
    dicExm.Key(1) = 3
    dicExm.Key("3") = 4
    Debug.Print JsonConverter.ConvertToJson(dicExm)
    Debug.Print "=================================================="
    
    'Check existence and non existence of designated Key.
    blnKey = dicExm.Exists(4)
    Debug.Print blnKey
    Debug.Print "=================================================="
    
    'Return the Key as the sequence.
    varMKey = dicExm.Keys
    For i = LBound(varMKey) To UBound(varMKey)
        Debug.Print varMKey(i)
    Next i
    Debug.Print "=================================================="
    
    'Return the Value as the sequence.
    varMItm = dicExm.Items
    For i = LBound(varMItm) To UBound(varMItm)
        Debug.Print varMItm(i)
    Next i
    Debug.Print "=================================================="
    
    'Remoce all the keys and values.
    dicExm.RemoveAll
    Debug.Print dicExm.Count
    Debug.Print "=================================================="
    
End Sub

Example2 : This is the Dictionary Class example that has several different levels of deep.

VBA does not have the DeepCopy command independently, and you can use DeepCopy foam as the example below. Or possible to minimize the number of variables by repeating create and remove works.

'For example
Set dic = new dictionary 'Create

'Registe dic upper class

Set dic = nothing 'Release

Repeat

It would be easy to understand if you find Deepcopy VS Shallow Copy materials.

Example 2

Private Sub DicExample2()
    
    Dim i, j As Integer
    Dim dicSub2, dicSub1, dicMain As Dictionary
    
    'Register sequence in Value.
    Dim intItm(8) As Integer
    Set dicMain = New Dictionary
    
    For i = 1 To 8
        intItm(i) = i
    Next i
    
    dicMain.Add "Integer", intItm
    Debug.Print JsonConverter.ConvertToJson(dicMain)
    
    Set dicMain = Nothing
    
    'This is the example that has several depths in Dictionary
    'This is the GRUP coding example of Dictionary that has several depths
       '"GRUP":{
    '    "1":{
    '       "NAME":"G1"
    '       "E_LIST":[1,2,3,4,5]
    '       },
    '    "2":{
    '       "NAME":"G2"
    '       "E_LIST":[6,7,8,9,10]
    '       }
    '      }
    Dim intElmList(5) As Integer
    Set dicSub1 = New Dictionary: Set dicMain = New Dictionary
    
    For i = 1 To 2
        
        For j = 1 To 5
            intElmList(j) = 5 * (i - 1) + j
        Next j
        
        Set dicSub2 = New Dictionary    'Setting dicSub2 as new dictonary
        dicSub2.Add "NAME", "G" & i
        dicSub2.Add "E_LIST", intElmList
        
        'dicSub2.Add "E_LIST", Array(1, 2, 3, 4, 5) 'The simple data can handle with Array function independently.
        
        dicSub1.Add i, dicSub2
        Set dicSub2 = Nothing           'Release dicsub2
        
    Next i
            
    dicMain.Add "GRUP", dicSub1
    Debug.Print JsonConverter.ConvertToJson(dicMain)
    Set dicSub1 = Nothing: Set dicMain = Nothing
    
    'This is an example of converting Dictionary structure to an Array like LCOM.
    'This is an example that mentioned right before.
    '    {
    '    "LCOM": {
    '        "1": {
    '            "NAME": "DC",
    '            "KIND": "GEN",
    '            "ACTIVE": "ACTIVE",
    '            "bES": false,
    '            "iTYPE": 0,
    '            "DESC": "",
    '            "iSERV_TYPE": 0,
    '            "nLCOMTYPE": 0,
    '            "nSEISTYPE": 0,
    '            "vCOMB": [
    '                {
    '                    "ANAL": "CS",
    '                    "LCNAME": "Dead Load",
    '                    "FACTOR": 1
    '                }
    '            ]
    '        },
    '        "2": {
    '            "NAME": "CR/SH",
    '            "KIND": "GEN",
    '            "ACTIVE": "ACTIVE",
    '            "bES": false,
    '            "iTYPE": 0,
    '            "DESC": "",
    '            "iSERV_TYPE": 0,
    '            "nLCOMTYPE": 0,
    '            "nSEISTYPE": 0,
    '            "vCOMB": [
    '                {
    '                    "ANAL": "CS",
    '                    "LCNAME": "Creep Secondary",
    '                    "FACTOR": 1
    '                },
    '                {
    '                    "ANAL": "CS",
    '                    "LCNAME": "Shrinkage Secondary",
    '                    "FACTOR": 1
    '                }
    '            ]
    '        }
    '    }
    
    Dim dicComb(), dicCmn As Dictionary
    
    Set dicCmn = New Dictionary
    Set dicSub1 = New Dictionary: Set dicMain = New Dictionary
   
    dicCmn.Add "NAME", ""
    dicCmn.Add "KIND", "GEN"
    dicCmn.Add "ACTIVE", "ACTIVE"
    dicCmn.Add "bES", False
    dicCmn.Add "iTYPE", 0
    dicCmn.Add "DESC", ""
    dicCmn.Add "iSERV_TYPE", 0
    dicCmn.Add "nLCOMTYPE", 0
    dicCmn.Add "nSEISTYPE", 0
    dicCmn.Add "vCOMB", ""
    
    dicSub1.Add 1, DeepCopy(dicCmn) 'for DeepCopy
    dicSub1.Add 2, DeepCopy(dicCmn) 'for DeepCopy
    
    ReDim dicComb(1)
    Set dicComb(1) = New Dictionary
    
    dicComb(1).Add "ANAL", "CS"
    dicComb(1).Add "LCNAME", "Dead Load"
    dicComb(1).Add "FACTOR", 1
    
    dicSub1.Item(1)("NAME") = "DC"
    dicSub1.Item(1)("vCOMB") = dicComb
    
    ReDim dicComb(2)
    For i = 1 To 2: Set dicComb(i) = New Dictionary: Next i
    
    dicComb(1).Add "ANAL", "CS"
    dicComb(1).Add "LCNAME", "Creep Secondary"
    dicComb(1).Add "FACTOR", 1
    
    dicComb(2).Add "ANAL", "CS"
    dicComb(2).Add "LCNAME", "Shrinkage Secondary"
    dicComb(2).Add "FACTOR", 1
    
    dicSub1.Item(2)("NAME") = "CR/SH"
    dicSub1.Item(2)("vCOMB") = dicComb
    
    dicMain.Add "LCOM", dicSub1
    
    Debug.Print JsonConverter.ConvertToJson(dicMain)
    
End Sub

Private Function DeepCopy(dic As Dictionary) As Dictionary
    
    Dim Key As Variant
    
    Set DeepCopy = Nothing
    
    Set DeepCopy = New Dictionary
    For Each Key In dic.Keys
        DeepCopy.Add Key, dic(Key)
    Next Key
    
End Function

3️⃣ JsonConvertor Example

This is a simple example of handling JsonConvertor.

JsonConverter

Private Sub JsonConverterEX()

    Dim Json As String
    Dim dicJson As Dictionary
    
    'JSON Format String Data 
    Json = "{" & Chr(34) & "NODE" & Chr(34) & ":{" & Chr(34) & "1001" & Chr(34) & ":{" _
            & Chr(34) & "X" & Chr(34) & ":1.1," & Chr(34) & "Y" & Chr(34) & ":2.2," & Chr(34) & "Z" & Chr(34) & ":3.3}}}"
    
    Debug.Print Json
    
    'Converting JSON Format String Data to Dictionary
    Set dicJson = JsonConverter.ParseJson(Json)
    Debug.Print dicJson.Item("NODE")("1001")("X")
    Debug.Print dicJson.Item("NODE")("1001")("Y")
    Debug.Print dicJson.Item("NODE")("1001")("Z")
    
    dicJson.Item("NODE")("1001")("X") = 4.4
    dicJson.Item("NODE")("1001")("Y") = 5.5
    dicJson.Item("NODE")("1001")("Z") = 6.6
    
    'Converting Dictionary Data to JSON String Data
    Dim dicTojson As String
    dicTojson = JsonConverter.ConvertToJson(dicJson)
    
    Debug.Print dicTojson
    
End Sub

4️⃣ Simple Beam Example

In the VBA use Http Request: “WinHttp.WinHttpRequest.5.1”

This is a simple beam example that used API as many as possible. Please referred this attachment.

VBA Ex.mp4