엑셀, 곽승주님의 오튜공구함 제작으로 배워보는 VBA 이야기, Excel

Sub Macro1()
'
' Macro1 Macro
' .이(가) 2001-12-17에 기록한 매크로
'

'
     Range("A3:C7").Sort Key1:=Range("B4"), Order1:=xlAscending, Key2:=Range( _
          "A4"), Order2:=xlAscending, Key3:=Range("C4"), Order3:=xlDescending, _
          Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
          xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
          DataOption3:=xlSortNormal
End Sub

위의 코드에서 보면 Range개체를 사용하였습니다. 정렬할 범위("A3:C7")를 Range개체로 설정하고 Sort 메소드를 사용하였습니다. 그리고 Key1,Key2,Key3는 [정렬]대화상자의 [첫째기준], [둘째기준],[셋째기준]을 가리키는 걸 알 수 있습니다. Order1, Order2, Order3는 오름차순으로 정렬할 것인가(xlAscending), 내림차순으로 정렬할 것인가(xlDescending)를 지정하는 인수입니다. 그외 정렬과 관련한 여러 사항(머리글행,옵션)등을 코드로 만들어 두었습니다. Range개체의 Sort메소드에 대한 자세한 사항은 도움말을 참조하시길 바랍니다(사실 오늘의 본론은 이게 아니거든요)

종종 엑사모에 올라오는 질문중에 4개 이상의 키를 가지는 경우 어떻게 정렬을 하는 가를 물어보는 질문이 있더군요. 이런 경우 정렬을 두번 하면 되는데 정렬기준중 우선 순위가 낮은 것을 먼저 정렬한 뒤, 그 다음에 정렬하면 된다고 하더군요 (저는 그런 정렬을 해볼 일이 없어 답변하신 분들의 말씀을 옮겼습니다)

오늘은 이런 애로사항을 일부나마 해결할 수 있는 정렬을 만들어 보는 시간을 가져보겠습니다. 미리 말씀드리자면 오늘 만드는 기능은 4개 이상의 열을 정렬할 수 있습니다. 테스트를 모두 해보질 않아 어디까지가 한계인가는 모르겠습니다.

그리고 아직 미미한 점은 첫 번째 키, 두 번째 키, 세 번째 키등을 따로 지정할 수 는 없고 다만 가장 왼쪽 컬럼이 첫번째 키, 다음 오른쪽 컬럼이 두번째 키, 그 다음 오른쪽이 세번째 키등 컬럼의 순서대로 키가 정해집니다. 그리고 각 키마다 오름차순 또는 내림차순을 각각 지정할 수는 없고 전체적으로 오름차순 또는 내림차순등을 지정할 수 있습니다.

결국 엑셀의 정렬과 비교해볼 때 4개이상의 키를 정렬할 수 있다는 점외에는 장점은 없습니다. 정렬을 하는 핵심소스코드는 외국에서 수입하였습니다. Dave Steppan(http://www.geocities.com/SiliconValley/Network/1030/ExcelTop.html)이라는 분이 만든 MultiColumnSort(...)함수를 가져다가 제가 만든 폼에서 붙였습니다. (MultiColumnSort(...)함수는 나중에 설명하도록 하겠습니다)

먼저 폼을 보도록 하죠. 생긴 것은 매우 단순합니다. 정렬할 범위를 입력받는 RefEdit컨트롤과 정렬순서를 입력받는 OptionButton버튼, 그리고 정렬을 실제 수행하도록 하는 CommandButton이 있습니다. RefEdit컨트롤은 워크시트의 영역을 입력 받습니다. 이컨트롤의 오른쪽끝을 보면 밑줄있는 버튼이 보일 것입니다. 이것을 클릭하면 대화상자는 잠시 사라지고 범위를 입력하는 납작한 대화상자가 나타납니다. 사실 이걸 누르지 않고 그냥 워크시트에 마우스를 드래그하여 셀을 입력해도 됩니다. 그러면 Value프로퍼티에 입력한 범위의 셀주소에 저장됩니다. 이 범위의 값을 통채로 MultiColumnSort(...)함수의 첫번째 매개변수로 전달합니다.

그리고 정렬순서를 지정하는 옵션버튼에 따라 정렬순서가 정해지는데, 오름차순인 경우 정수값 1을, 내림차순인 경우 -1을 MultiColumnSort(...)함수에 두번째 매개변수로 전달합니다.

프로그램 소스

Option Explicit

Dim SortMultColsTemp

Private Sub Sort_Click()
     Dim rngRange As Range
     Dim Order As Integer
     Dim i As Integer
     Dim j As Integer

     Set rngRange = Range(RefEdit.Value)

     Order = IIf(opAsc.Value, 1, -1)

     Sort rngRange, Order

     For i = 1 To rngRange.Columns.Count
          For j = 1 To rngRange.Rows.Count
               rngRange.Cells(j, i) = SortMultColsTemp(j, i)
          Next j
     Next i
     Debug.Print i
     Set rngRange = Nothing
     Unload Me
End Sub

Private Sub Sort(InMatrix1 As Range, SortOrder As Integer)
     Dim InMat1 As Variant

     Application.Volatile
     InMat1 = InMatrix1.Value
     SortMultColsTemp = MultiColumnSort(InMat1, SortOrder)
End Sub

Dim SortMultColsTemp
SortMultColsTemp는 정렬된 결과를 MultiColumnSort(...)함수로부터 넘겨받는 Variant형식의 전역변수입니다.

Private Sub btnSort_Click()
     Dim rngRange As Range
     Dim Order As Integer
     Dim i As Integer
     Dim j As Integer

     Set rngRange = Range(RefEdit.Value)
rngRange는 Range형식의 변수로서 RefEdit컨트롤의 Value프로퍼티 값을 받아 사용자가 선택한 범위를 입력받습니다.

     Order = IIf(opAsc.Value, 1, -1)
Order는 오름차순인가 또는 내림차순인가 여부를 돌려받습니다.

     Sort rngRange, Order
Sort()프로시져는 사용자가 지정해준 정렬할 범위(rngRange)와 사용자가 지정한 정렬순서(Order)를 매개변수로 받습니다. 받은 후 어떻게 처리하는지를 나중에 보도록 하죠.

     For i = 1 To rngRange.Columns.Count
          For j = 1 To rngRange.Rows.Count
               rngRange.Cells(j, i) = SortMultColsTemp(j, i)
          Next j
     Next i
정렬될 결과를 되돌려주는 부분입니다.

     Set rngRange = Nothing
rngRange 개체변수를 다 사용했으므로 메모리를 해제합니다.

     Unload Me
사용자정의 폼을 메모리에서 해제하여 화면에서 없앱니다.
End Sub

Private Sub Sort(InMatrix1 As Range, SortOrder As Integer)
     Dim InMat1 As Variant

     Application.Volatile
엑셀에 재계산하도록 명령을 내립니다. 혹시 계산이 수동인 경우 계산 안된 결과를 가지고 정렬하는 것을 막기 위한 것입니다.

     InMat1 = InMatrix1.Value
InMatrix1 개체변수에 저장된 값을 InMat1 변수에 저장합니다. 이렇게 저장된 값을 MultiColumnSort(...)함수에 넘깁니다.

     SortMultColsTemp = MultiColumnSort(InMat1, SortOrder)
MultiColumnSort(...)함수에 정렬할 값과 정렬순서를 매개변수로 넘겨주고 결과를 전역변수인 SortMultColsTemp에 저장합니다.
End Sub

프로그램을 실행한 결과를 다음과 같이 그림으로 잡아 보여드리고 이만 마치고자 합니다. 4개 컬럼과 5개 컬럼을 정렬하는 경우를 실행해보았는데, 이 예는 Dave Steppan의 파일에서 가져와 제가 다시 만든 프로그램으로 실행한 결과입니다.

015-3.gif(15860바이트)

오늘은 여기까지입니다. 얼마전에 예제파일을 보니 메뉴를 구성하는 Sheet1시트에 메뉴레벨에 엉뚱한 숫자가 들어가 메뉴구성이 엉뚱하게 된 것을 보았습니다. 이번에 이를 고쳤는데, 혹시 지난 컬럼 예제파일을 다운받으신 분들이 당황하지 않았나 싶군요. 그리고 중복데이터 처리를 하기 위해 여러가지 ADO개체를 참조하다 보니 종종 참조가 풀려 예제파일을 불러오는 도중 LCASE() 또는 FORMAT()등에서 에러를 만나실 수 있습니다. 그런 경우 VBE에서 [도구]-[참조]로 가셔서 누락으로 표시된 개체를 꺼버리시구, 참조할 개체 목록상자의 하단에서 다시 개체를 참조해두시길 바랍니다.

목차 | 이전 | 다음