(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
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
Dictionary applied example
Example1 : The basic using method of Dictionary Class.
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.