(KR) MIDAS Civil with VBA

VBA는 Excel를 다루는 사용자에게 가장 유용하고 접근이 쉬운 개발툴로, 이를 이용하여 MIDAS Civil API를 사용하는 예제를 작성해봅니다. 이미 다수의 문서에서 API 명령어에 대한 내용을 수록하였으므로, 여기서는 VBA를 다루는 것에 집중하도록 하겠습니다.

Json 데이터 다루기

VBA에서 Json 데이터를 손쉽게 다루기 위해서는 Dictionary Class 를 이용하는게 경험상 좋습니다.

초창기 예제 작성시 String 데이터로 작성하였으나, 이는 Json Data가 커질수록 사용하기 어렵고 (Hell of Chr(34)), 특히 Get Method를 이용하여 불러온 데이터를 다루기가 어렵습니다. 하지만 다행스럽게 Dictionary Class를 Json 포맷으로 Converter 해주는 Code가 있으므로, 이를 사용하면 더욱 더 편하게 작성이 가능합니다.

Dictionary Class

Dictionary Class는, 메뉴->도구->참조-> Microsoft Scripting Runtime을 활성화하여 사용합니다.

아래와 같이 활성화하지 않고 사용할 수 있지만, 불편하므로 활성화하고 사용하시길 추천드립니다.

'활성화하지 않고 사용할 시 선언 방법(late binding)
    Dim DicEx As Object 'Declare
    Set DicEx = CreateObject("Scripting.Dictionary") 'Create
  
'활성화 후 선언하는 방법(early binding)
    Dim DicEx As Dictionary 'Declare
    Set DicEx = New Dictionary 'Create

VBA (Microsoft Scripting Runtime)

Dictionary Class는 다음과 같은 설정값을 가집니다.

속성

Count : 현재 저장된 개체의 갯수를 반환합니다.
Item(“KeyValue”) : 지정한 키의 Value를 불러오거나, 대체할 수 있습니다.
Key(“KeyValue”) : 지정한 키의 Key를 불러오거나, 대체할 수 있습니다.
CompareMode : Dictionary 개체에서 문자열 키를 비교하는 모드를 설정하여 반환합니다.(Binary, text 모드가 있으나, 굳이 변경할 필요는 없습니다.)

메소드

Add : 키와 값을 추가합니다. 존재하는 키이면 오류가 발생합니다.
Remove : 지정한 키와 할당되어 있는 값을 제거합니다. 존재하지 않는 키이면 오류가 발생합니다.
RemoveAll : 모든 키와 값을 제거합니다.
Exists : 지정한 키가 존재하면 True를 반환하고, 아니면 False를 반환합니다.
keys : 모든 키들을 배열로 반환합니다.
Items : 모든 값들을 배열로 반환합니다.

JsonConvertor

Json 양식을 VBA에서는 공식 지원하는 것이 없으므로, 아래 링크에 있는 JsonConvertor를 이용합니다.

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

JsonConverter

Dictionary 사용 예제

예제1 : 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
    
    'Key와 Value를 할당합니다.
    Set dicExm = New Dictionary
    
    dicExm.Add 1, "Apple"
    dicExm.Add 2, "Banana"
    dicExm.Add "3", "Cherry"
    
    'Dicrionay Class를 Json 포맷으로 변환하고 출력합니다.
    Debug.Print JsonConverter.ConvertToJson(dicExm)
    Debug.Print "=================================================="
    
    '객체의 갯수를 반환합니다.
    lngCnt = dicExm.Count
    Debug.Print lngCnt
    Debug.Print "=================================================="
    
    '특정 key를 가지는 value를 반환합니다.
    varItm = dicExm.Item(1)
    Debug.Print varItm
    Debug.Print "=================================================="
    
    '키값을 모두 변경합니다.
    dicExm.Key(1) = 3
    dicExm.Key("3") = 4
    Debug.Print JsonConverter.ConvertToJson(dicExm)
    Debug.Print "=================================================="
    
    '지정한 Key의 존재 유무를 파악합니다.
    blnKey = dicExm.Exists(4)
    Debug.Print blnKey
    Debug.Print "=================================================="
    
    'Key를 배열로 반환합니다.
    varMKey = dicExm.Keys
    For i = LBound(varMKey) To UBound(varMKey)
        Debug.Print varMKey(i)
    Next i
    Debug.Print "=================================================="
    
    'Value를 배열로 반환합니다.
    varMItm = dicExm.Items
    For i = LBound(varMItm) To UBound(varMItm)
        Debug.Print varMItm(i)
    Next i
    Debug.Print "=================================================="
    
    '모든 Key와 Value를 삭제합니다.
    dicExm.RemoveAll
    Debug.Print dicExm.Count
    Debug.Print "=================================================="
    
End Sub

예제2 : 여러단계의 깊이를 가지는 Dictionary Class를 만드는 예제입니다.

VBA는 DeepCopy를 실행하는 명령어가 따로 없습니다(제가 알기론). 따라서 아래 예제와 같이 DeepCopy 형태의 Function을 이용할 수 있습니다. 혹은 아래와 같이 만들고 버리는 반복 작업을 통해 변수의 개수를 최소화하여 작성할 수 있습니다.

'예를 들어
Set dic = new dictionary 'Create

'dic 상위 클래스에 등록

Set dic = nothing 'Release

반복

이에 대한 내용은 깊은 복사 VS 얕은 복사 (velog.io) 이걸 참조하시면 이해가 빠르실 겁니다.

Example 2

Private Sub DicExample2()
    
    Dim i, j As Integer
    Dim dicSub2, dicSub1, dicMain As Dictionary
    
    '배열을 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
    
    '여러단계의 깊이를 가지는 Dicionary 구조 예제입니다
    'GRUP를 예제로 다음 구조를 가지는 Dictionary를 만들어 봅니다
    '"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) '간단한 데이터는 Array 함수로 각각 처리할 수도 있습니다.
        
        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
    
    'LCOM 과 같이 Dictionary 구조를 Array로 가지는 것에 대한 예제입니다.
    '다음 구조를 예제로 한번 작성해 보겠습니다.
    '    {
    '    "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

JsonConvertor 예제

JsonConvertor를 다루는 간단한 예제입니다.

Private Sub JsonConverterEX()

    Dim Json As String
    Dim dicJson As Dictionary
    
    'Json 포맷의 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
    
    'Json 포맷의 String Data를 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
    
    'Dictionay Data를 Json String 데이터로 변환
    Dim dicTojson As String
    dicTojson = JsonConverter.ConvertToJson(dicJson)
    
    Debug.Print dicTojson
    
End Sub

추가

(1) Runtime error - Timeout

아래 코드는 위에 예제에서 활용한 HttpRequest Function 입니다.

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

    Dim TCRequestItem As Object
    Dim URL As String
    
    Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "http://localhost:10024"
    URL = URL & Command
    TCRequestItem.Open Method, URL, False
    TCRequestItem.SetRequestHeader "Content-type", "application/json"
    TCRequestItem.Send Body
    WebRequest = TCRequestItem.ResponseText
    
End Function

간혹, 제품으로부터 Response를 받기까지 시간이 지연되어, 아래와 같은 경고창과 함께 VBA 작동이 멈추는 경우가 있습니다. (절점과 요소수가 많을 경우 등등)

그럴 경우 아래와 같이 코드 한 줄을 삽입해주시면 됩니다. 단위는 Milliseconds 입니다.

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

    Dim TCRequestItem As Object
    Dim URL As String
    
    Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    'SetTimeouts(resolveTimeout, ConnectTimeout, SendTimeout, ReceiveTimeout)
    TCRequestItem.SetTimeouts 60000, 60000, 60000, 60000
    
    URL = "http://localhost:10024"
    URL = URL & Command
    TCRequestItem.Open Method, URL, False
    TCRequestItem.SetRequestHeader "Content-type", "application/json"
    TCRequestItem.Send Body
    WebRequest = TCRequestItem.ResponseText
    
End Function

(2) float point error (부동소수점 에러)

이 항은 수치해석에 잘 알려진 에러인 부동소수점 에러를 VBA에서 어떻게 다루는지에 대해 설명해봅니다.

부동소수점의 에러는 컴퓨터가 받아들이는 숫자를 처리하는 방식에 의해서 생기는 걸로 이해하고 있습니다.

(관심이 있으신 분은 Floating-point arithmetic - Wikipedia 여길 참고해보세요.)

예를 들어 다음과 같은 코드를 실행시켜 보겠습니다.

Option Explicit

Sub Test()

    Dim i As Integer
    Dim sngVar As Single
    Dim dblVar As Double
    
    For i = 1 To 10000
        sngVar = sngVar + 0.0001
    Next i
    
    Debug.Print TypeName(sngVar) & " : " & sngVar
    
    For i = 1 To 10000
        dblVar = dblVar + 0.0001
    Next i
    
    Debug.Print TypeName(dblVar) & " : " & dblVar
    
End Sub

0.0001를 만번 더했는데, 원했던 결과인 1이 나오질 않습니다. 게다가 자료형에 따라 그 오차도 차이가 있습니다. 이런 문제는 프로그램이면 모두 가지고 있는 문제입니다. 아래는 엑셀 예제입니다. 간단한 연산임에도 불구하고 9번째 행은 우리가 원하는 결과를 보여주지 못합니다.

이런 에러는 간단하게 유효 자릿수를 주는 방식으로 해결할 수도 있지만, 조금도 근본적인 해결방법이 있는 경우가 있습니다. 바로 자료형을 바꿔주는 겁니다.

VBA에서 이런 문제에 쓸 수 있는 자료형은 바로 Decimal 자료형입니다.

아래와 같이 0.0001를 Decimal 자료형으로 바꿔주어 더해주거나, Decimal 자료형으로 선언해서 쓰는 방법 등이 있습니다.(Decimal 은 Variant로 선언합니다.)

Option Explicit

Sub Test()

    Dim i As Integer
    Dim sngVar As Single
    Dim dblVar As Double
    Dim decVar1 As Variant
    Dim decVar2 As Variant
    
    For i = 1 To 10000
        sngVar = sngVar + CDec(0.0001)
    Next i
    
    Debug.Print TypeName(sngVar) & " : " & sngVar
    
    For i = 1 To 10000
        dblVar = dblVar + CDec(0.0001)
    Next i
    
    Debug.Print TypeName(dblVar) & " : " & dblVar
       
    For i = 1 To 10000
        decVar1 = decVar1 + CDec(0.0001)
    Next i
    
    Debug.Print TypeName(decVar1) & " : " & dblVar

    For i = 1 To 10000
        decVar2 = decVar2 + 0.0001
    Next i
    
    Debug.Print TypeName(decVar2) & " : " & dblVar

End Sub