(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.

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

VBA (Microsoft Scripting Runtime)

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

JsonConverter

Dictionary applied example

Example1 : The basic using method of Dictionary Class.

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.

JsonConvertor Example

This is a simple example of handling JsonConvertor.

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.