애들센스


없으면 빌려쓰는 ArrayList 오피스/VBA/Office.JS

VBA에는 남다른 철학이 있다. '없으면 빌어다 써라~'. 그런데 '한 푼 줍쑈~'라도 하려면 뭔가 있어야 하는데, 그것이 CreateObject()함수이다. CreateObject()함수를 사용하여 닷넷의 ArrayList를 빌어다 쓸 수 있는데, 다음은 ArrayList를 이용한 정렬이다.
Sub demoArrayList()
Dim it

With CreateObject("System.Collections.ArrayList")
For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")
.Add it: Next

'// 오름차순
.Sort
Debug.Print Join(.toarray, vbLf)
Debug.Print

'// 내림차순
.Sort
.Reverse
Debug.Print Join(.toarray, vbLf)
End With

End Sub
이를 이용하여 다음과 같이 워크시트의 셀 범위를 정렬할 수 있는 데
Sub demoSortArrayList()
Dim sn, j, jj, sp, ai
Dim bln As Boolean

sn = Sheet1.Range("B3:N18")

With CreateObject("System.Collections.Arraylist")
For j = 1 To UBound(sn)
.Add sn(j, 3)
Next

.Sort

sp = .ToArray
.Clear

For j = 0 To UBound(sp)
For jj = 1 To UBound(sn)
If sn(jj, 3) = sp(j) Then
.Add Application.Index(sn, jj)
sn(jj, 3) = ""
Exit For
End If
Next
Next

For j = 0 To .Count - 1
Sheet1.Range("B3:N18").Item(j + 1, 1).Resize(, UBound(sn, 2)) = .Item(j)
Next
End With
End Sub
사실 이렇게 복잡하게 할 필요는 없다. Range개체의 Sort메서드를 이용하는 것이 훨씬 간결하다. 응? 근데 왜 했지?
Sub demoSortRange()
With Sheet1.Range("B3:N18")
.Sort Key1:=.Item(1, 3), Order1:=XlSortOrder.xlDescending, Header:=xlNo
End With
End Sub

덧글

  • 우왕 2019/07/15 15:00 # 삭제 답글

    혹시 닷넷의 클래스 중에 System.Text.RegularExpressions.Regex 이걸 VBA에서 사용할수 있는 방법을 아시나요?
    Vbscript.Regexp 대체재가 있긴한데, lookbehind를 할수 없어서요.
  • 타임버드 2019/07/16 10:03 #

    VBA 만으로는 가능한 방법은 없어 보입니다. Shell scripting을 이용하여 powershell의 정규표현식 기능을 이용할 순 있지만 좀 궁색해보입니다.
    'strCommand = "Powershell -File ""C:pathtoMy PS File.ps1"""
    strCommand = "powershell.exe 'Ziggy stardust' -match 'X*star'"

    Set wsh = CreateObject("WScript.Shell")
    strOutput = wsh.Exec(strCommand).StdOut.ReadAll
    Debug.Print strOutput
댓글 입력 영역