(KR)MIDAS Civil with VBA

VBA는 Excel를 다루는 사용자에게 가장 유용하고 접근이 쉬운 개발언어로, 이를 이용하여 MIDAS-Civil API를 사용하는 방법을 알아보도록 하겠습니다.

(본 컨텐츠는 VBA, Json, Restfull API에 간단한 기본지식이 있는 분들이 유용하게 사용할 수 있도록 작성되었습니다.)

⚒️ Json 다루기!

MIDAS-API에서 데이터교환은 Json format으로 이루지나, VBA에서는 Json format을 공식적으로 지원해주지 않습니다. 따라서, 아래와 같이 Dictionary Object와 JsonConverter Library를 이용하여 Json format을 작성합니다.

Dictionary Object 란?

Dictionary Object는 VBA에서 사용할 수 있는 변수의 일종으로 Key : Item 형태로 쌍의 데이터를 가지는 객체입니다. 이와 관한 MS의 공식 설명은 아래 링크에서 확인할 수 있습니다.

Dictionary object | Microsoft Learn

Dictionary Object는 2가지 방법으로 사용이 가능합니다.

첫번째, Late Binding 방법으로 선언과 작성을 아래와 같은 코드를 사용합니다.

'late binding method
    Dim DicEx As Object 'Declare
    Set DicEx = CreateObject("Scripting.Dictionary") 'Create

둘째로, Early Binding 방법이 있습니다.

우선, 메뉴->도구->참조-> Microsoft Scripting Runtime을 활성화하고, 아래와 같이 선언과 작성을 합니다.

'early binding method
    Dim DicEx As Dictionary 'Declare
    Set DicEx = New Dictionary 'Create

본 컨텐츠는 후자(Early binding)의 방법을 이용하여, 작성하도록 하겠습니다.

JsonConvert Library가 Early Biding이 되어 있으므로, 이를 이용해야 정상적인 동작이 가능합니다
더 유연한으로 코딩 작성이 가능합니다.

JsonConverter 란?

GitHub를 통해 개인개발자가 무료로 배포한 VBA Library 이며, 이를 이용하여 Dictionary Object를 Json format으로 변경하거나, 반대로 Json format을 Dictionary Object로 만들어주는 유용한 툴입니다.

아래 사이트에서 코드를 복사하고, VBA 모듈을 만들고 “JsonConverter” 라는 이름으로 바꾼 후, 복사한 코드를 붙여 넣습니다.

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

⚒️MIDAS Civil과 데이터 주고받기!

MIDAS Civil과의 데이터는 아래의 Function을 통해 주고 받습니다.

입력함수는 다음과 같습니다.

Method as string

“GET” = 데이터를 출력
“POST” = 데이터를 입력
“PUT” = 입력된 데이터를 수정

Commnad as string

/doc/xxxx = 파일과 관련된 동작
/db/xxxx = 데이터베이스 입출력 관련 동작
/view/xxxx = 그림에 관련된 동작
/post/xxxx = 결과테이블 관련된 동작

Body as string

Json format

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

    Dim TCRequestItem As Object
    Dim URL As String
    Dim MAPIKey As Variant
    
    Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    'SetTimeouts(resolveTimeout, ConnectTimeout, SendTimeout, ReceiveTimeout)
    TCRequestItem.SetTimeouts 200000, 200000, 200000, 200000
    
    'Get MAPI-Key from Sheets
    MAPIKey = Range("MAPIKey").Value
    
    'URL & Commend
    URL = "https://api-beta.midasit.com:443/civil" & Command
    
    'Request API
    TCRequestItem.Open Method, URL, False
    TCRequestItem.SetRequestHeader "Content-type", "application/json"
    TCRequestItem.SetRequestHeader "MAPI-Key", MAPIKey
    TCRequestItem.Send Body
    
    'API Response
    WebRequest = TCRequestItem.ResponseText

End Function

⚒️ MIDAS Civil 에서 Data를 얻어보자!

자, 이제 Civil 파일(Arch.mcb) 로부터 Node 정보를 받아온 후, Excel Sheet에 넣어보겠습니다.

“GET” Method로 /db/node의 정보를 요청합니다.
받아온 정보는 String 형태이므로, 이를 JsonConverter를 이용해 Dictionary Object에 할당합니다.
Dictionary 함수를 이용해 각 배열변수에 좌표 및 Node 번호를 할당합니다.
할당한 배열변수를 엑셀시트에 출력합니다.

아래의 Code는 위에 설명을 가장 기초적인 방법으로 구현한 것입니다. Dictionary Object의 Items,Keys Method 혹은 3차원배열 변수 등을 이용하면 더욱 간결한 Code로 구현이 가능합니다.

Example Code (Get Node Information)

Private Sub GetNode()
    
    Dim dicNode As dictionary
    Dim Response As String
    
    'Get Node Inofrmation
    Response = WebRequest("GET", "/db/node", "")
    Debug.Print Response
    
    'Parsing Data
    Set dicNode = JsonConverter.ParseJson(Response)
    Debug.Print "Check======================="
    Debug.Print dicNode("NODE")("42")("X")
    
    'Parsing Data
    Dim i As Integer
    Dim NodeID As Variant
    Dim NbNode As Long
    
    NbNode = dicNode("NODE").Count - 1
    NodeID = dicNode("NODE").Keys
    
    Dim NodeX(), NodeY(), NodeZ() As Double
    ReDim NodeX(NbNode), NodeY(NbNode), NodeZ(NbNode)
    
    For i = 0 To NbNode
        NodeX(i) = dicNode("NODE")(NodeID(i))("X")
        NodeY(i) = dicNode("NODE")(NodeID(i))("Y")
        NodeZ(i) = dicNode("NODE")(NodeID(i))("Z")
    Next i
    
    With Sheet1
        For i = 0 To NbNode
            .Cells(5 + i, "B") = NodeID(i)
            .Cells(5 + i, "C") = NodeX(i)
            .Cells(5 + i, "D") = NodeY(i)
            .Cells(5 + i, "E") = NodeZ(i)
        Next i
    End With
    
End Sub

⚒️ MIDAS Civil 에 Data를 보내보자!

위에 예제에서 알 수 있듯이 (직접실행창 확인) Node 데이터는 아래와 같이 구성됩니다.

{
  "NODE":{
    "1":{
        "X":0,
        "Y":0,
        "Z":0
        }
  },
    "2":{
        "X":5,
        "Y":0,
        "Z":3.6
        }
}

자 이제는 Civil의 새파일을 열고, 엑셀에 입력된 Node 정보를 한번 보내 보도록 하겠습니다.

Sheet의 Node 정보를 배열변수로 변환합니다.
배열변수를 Dictionary Object에 할당합니다.
Dctionary Object를 JsonConvert를 이용해 Json format의 String data로 변환합니다.
이를 Post Method를 이용해 Civil에 정보를 보내줍니다.

아래의 Code는 위에 설명을 구현한 것입니다.

Example Code (Post Node Information)

Private Sub PostNode()

    Dim i As Integer
    Dim NodeInfo As Variant
    Dim dicMain As Dictionary
    Dim dicNdID As Dictionary
    Dim dicCoor As Dictionary
    Dim strBody As String
    Dim Response As String
    
    NodeInfo = Sheet1.Range(Cells(5, "B"), Cells(60, "E")).Value
    
    Set dicMain = New Dictionary: Set dicNdID = New Dictionary
    
    For i = 1 To UBound(NodeInfo)
        Set dicCoor = New Dictionary 'Create
        
        dicCoor.Add "X", NodeInfo(i, 2)
        dicCoor.Add "Y", NodeInfo(i, 3)
        dicCoor.Add "Z", NodeInfo(i, 4)
        
        dicNdID.Add NodeInfo(i, 1), dicCoor
        
        Set dicCoor = Nothing 'Release
    Next i
    
    dicMain.Add "Assign", dicNdID
    
    strBody = JsonConverter.ConvertToJson(dicMain)
    Debug.Print strBody
    
    Response = WebRequest("POST", "/db/node", strBody)
    Debug.Print Response
    
End Sub

⚒️ MIDAS Civil 에 Data를 수정해보자!

자 이제 새파일에 입력된 Node 정보를 직선의 형태로 바꿔보겠습니다.

우선, Excel 데이터를 1m 간격의 직선 형태로 바꾼 후, Post에서 사용한 Code를 그대로 복사한 후 WebReqeust Fuction의 Method만 PUT으로 교체해서 작동시켜보겠습니다.

Example Code (Put Node Information)

Dim i As Integer
Dim NodeInfo As Variant
Dim dicMain As Dictionary
Dim dicNdID As Dictionary
Dim dicCoor As Dictionary
Dim strBody As String
Dim Response As String

NodeInfo = Sheet1.Range(Cells(5, "B"), Cells(60, "E")).Value

Set dicMain = New Dictionary: Set dicNdID = New Dictionary

For i = 1 To UBound(NodeInfo)
    Set dicCoor = New Dictionary 'Create
    
    dicCoor.Add "X", NodeInfo(i, 2)
    dicCoor.Add "Y", NodeInfo(i, 3)
    dicCoor.Add "Z", NodeInfo(i, 4)
    
    dicNdID.Add NodeInfo(i, 1), dicCoor
    
    Set dicCoor = Nothing 'Release
Next i

dicMain.Add "Assign", dicNdID

strBody = JsonConverter.ConvertToJson(dicMain)
Debug.Print strBody

Response = WebRequest("PUT", "/db/node", strBody)
Debug.Print Response

⚒️ Dictionary Object 더 파보기

Dictionary는 Key:Item라는 쌍의 데이터를 가지는 변수입니다.

Key와 Item를 어떤 방식으로 등록하고 사용할 수 있는지 예제를 통해 알아보도록 하겠습니다.

우선 Dictionary Object에 어떤 Function이 있는지 알아보겠습니다.

Fuction	Example
item 더하기 (Key가 존재하면 안됩니다.)	dict.add Key, Value (e.g. dict.add “Apples”, 50)
Item 수정하기 (만약, Key가 존재하지 않으면, 자동으로 더해줍니다.)	dict(Key) = Value (e.g. dict(“Oranges”) = 60)
Key 값으로 Value 가져오기	Value= dict(Key) (e.g. applecount = dict(“Apples”)
Key값이 존재하는지 확인하기 (return as boolean)	dict.Exists(Key) (e.g. if dict.Exists(“Apples”) then)
Item 지우기	dict.Remove Key e.g. dict.Remove “Apples”
모든 Item 지우기	dict.RemoveAll
Items 개수 가져오기	dict.Count
모든 Item을 확인하기 (for each loop)	Dim key As Variant For Each key In dict.Keys Debug.Print key, dict(key) Next key
모든 Item을 확인하기 (for loop - early binding only)	Dim i As Long For i = 0 to dict.Count - 1 Debug.Print dict.Keys(i), dict.Items(i) Next i
모든 Item을 확인하기 (for loop - early and late binding only)	Dim i As Long For i = 0 to dict.Count -1 Debug.Print dict.Keys()(i), dict.Items()(i) Next i
Key 에서 대소문자를 구분하기 (Dictionary 는 비어져 있어야 합니다.) defalut입니다.	dict.CompareMode = vbBinaryCompare
Key 에서 대소문자를 구분하지 않기 (Dictionary 는 비어져 있어야 합니다.)	dict.CompareMode = vbTextCompare
모든 Key을 배열로 반환하기	Dim KeyValues as Variant KeyValues = dict.Keys
모든 Item을 배열로 반환하기	Dim ItemValuesas Variant ItemValues= dict.Items

🔨 Item 등록하고 출력하기

Example Code

    Dim dict As Dictionary
    Set dict = New Dictionary
    
    dict.Add "Apple", 50
    dict.Add "Banana", 60
    dict.Add "Orange", 100
    
    Dim i As Long
    For i = 0 To dict.Count - 1
        Debug.Print dict.Keys(i), dict.Items(i)
    Next i

Apple 50
Banana 60
Orange 100

🔨 Item 지우기

Example Code

    Dim dict As Dictionary
    Set dict = New Dictionary
    
    dict.Add "Apple", 50
    dict.Add "Banana", 60
    dict.Add "Orange", 100
    
    dict.Remove "Banana"
    
    Dim i As Long
    For i = 0 To dict.Count - 1
        Debug.Print dict.Keys(i), dict.Items(i)
    Next i

Apple 50
Orange 100

🔨 Key값 확인하기

Example Code

    Dim dict As Dictionary
    Set dict = New Dictionary
    
    dict.Add "Apple", 50
    dict.Add "Banana", 60
    dict.Add "Orange", 100
        
    If dict.Exists("Apple") Then
        Debug.Print dict("Apple")
    End If

50

🔨 Key,Items 값을 모두 배열로 반환하기

Example Code

    Dim dict As Dictionary
    Set dict = New Dictionary
    
    dict.Add "Apple", 50
    dict.Add "Banana", 60
    dict.Add "Orange", 100
    
    Dim i As Integer
    Dim KeyValues, ItemValues As Variant
    
    KeyValues = dict.Keys
    ItemValues = dict.Items
    
    For i = 0 To UBound(KeyValues)
        Debug.Print KeyValues(i), ItemValues(i)
    Next i

Apple 50
Banana 60
Orange 100

🔨 단계 구조로 만들기

Example Code

우선 Dictionary를 단계구조로 만들기 위해서는 Shallow/Deep Copy의 개념을 이해할 필요가 있습니다. Dictionary Object는 Shallow Copy를 기본으로 하기 때문입니다.

이를 이해하기 위해서 아래의 코드를 살펴봅니다.

    Dim A As Double
    Dim B As Double
    
    'Assign Value at A
    A = WorksheetFunction.PI()
    
    'Assign A to B
    B = A
       
    'Print Results
    Debug.Print "A : " & A
    Debug.Print "B : " & B
    
    'Modify B Value
    B = 10.56548
    
    'Print Results
    Debug.Print "A : " & A
    Debug.Print "B : " & B

A : 3.14159265358979
B : 3.14159265358979
A : 3.14159265358979
B : 10.56548

VBA에서 사용하는 대부분의 변수는 위와 같이 DeepCopy를 수행합니다. 즉, A와 B가 참조하는 메모리가 다르게 되므로, 복사한 이후로는 서로의 값에는 영향을 미치지 않습니다.

이를 Dictionary Object로 비슷하게 구현해보겠습니다.

    Dim A As Dictionary
    Dim B As Dictionary
    
    'Assign Value at A
    Set A = New Dictionary
    A.Add "Apple", 50
    A.Add "Banana", 60
    A.Add "Orange", 100
    
    'Assign A to B
    Set B = A
    
    'Print Results
    Debug.Print "A : " & A("Apple")
    Debug.Print "B : " & B("Apple")
    
    'Modify B Value
    B("Apple") = 30
    
    'Print Results
    Debug.Print "A : " & A("Apple")
    Debug.Print "B : " & B("Apple")

A : 50
B : 50
A : 30
B : 30

18열에서 B의 “Apple”에 해당되는 값만 수정했지만, 그 영향은 A에도 적용됩니다. 즉, A와 B가 참조하는 메모리가 같습니다. 이것을 Shallow Copy라고 합니다. (같은 메모리를 다른 이름의 2개로 참조하고 있습니다.)

이 부분을 알아야 하는 이유는 변수관리에 그 이유가 있습니다.

예를 들어 5개의 Node를 Json 구조를 짠다고 가정해봅니다. 우선 X, Y, Z를 Key로 갖는 5개의 Dictionary가 필요합니다. 그리고 이 Dictionary를 Item으로 가지고, Node ID를 Key로 갖는 5개의 Dictionary 가 필요합니다. 마지막으로 이걸 “Assign”이라는 Key를 가지는 Dictionary 1개가 필요합니다.

5개의 소수의 Node 정보를 가지는 Json 구조를 만드는데 11개의 Dictionary 변수가 필요하게 됩니다.

이 부분을 해결하기 위해서는 첫번째로 생각할 수 있는 부분은 변수를 계속해서 Copy 해서 쓰는 방법이 있지만, 위에서 살펴본 바와 같이 Dictionary Object는 Shallow Copy를 기본적으로 하기 때문에, 따로 Deep Copy Fuction을 만들어야 합니다.

다른 방법으로 아래와 같이 Dictionary 변수를 Create, Release를 반복하면서 해결할 수 있습니다.

    Dim i As Integer
    Dim ID, Xc, Yc, Zc As Variant
    ID = Array(1, 2, 3, 4, 5)
    Xc = Array(1, 2, 3, 4, 5)
    Yc = Array(6, 7, 8, 9, 10)
    Zc = Array(0, 0, 0, 0, 0)
    
    Dim dicMain As Dictionary
    Dim dicSub1 As Dictionary
    Dim dicSub2 As Dictionary
    
    Set dicMain = New Dictionary
    Set dicSub1 = New Dictionary
    
    For i = LBound(ID) To UBound(ID)
        Set dicSub2 = New Dictionary 'Create
        dicSub2.Add "X", Xc(i)
        dicSub2.Add "Y", Yc(i)
        dicSub2.Add "Z", Zc(i)
        
        dicSub1.Add ID(i), dicSub2
        Set dicSub2 = Nothing 'Release
    Next i
    
    dicMain.Add "Assign", dicSub1
    
    Debug.Print JsonConverter.ConvertToJson(dicMain)

{"Assign":{"1":{"X":1,"Y":6,"Z":0},"2":{"X":2,"Y":7,"Z":0},"3":{"X":3,"Y":8,"Z":0},"4":{"X":4,"Y":9,"Z":0},"5":{"X":5,"Y":10,"Z":0}}}

본 컨텐츠는 개발자가 아닌, Excel VBA에 대한 사용경험이 있는 일반유저가 작성하였습니다.