콤보박스 활용 테크닉3

Step 1: 콤보 박스에 정렬해서 채우기

왼쪽과 같은 데이터가 있습니다. 우선 이것을 콤보박스에 채워넣어 보도록 하지요. 버튼을 누르세요.

이것은 지난 시간에 설명을 드린 것이니까 그냥 넘어가도록 하고…

문제는 단순히 콤보박스에 채워넣는 것이 아니라 정렬을 해서 채워넣고 싶다는 (혹은 채워넣은 다음 정렬) 것이지요. 다시 아래의 버튼을 눌러 보세요.

어떻습니까? 바로 이렇게 되도록 하고 싶다는 질문이신 듯 합니다. 어떻게 하면 될까요? 코드를 보시기 전에 머리 속으로 과정을 먼저 그려보시기 바랍니다. 학교에서 수학 문제를 풀 때에도 그렇지요. 약간 어렵다고 무조건 풀이과정부터 읽어 나가면 당장은 편할 지 모르지만 효과가 반감되지 않습니까? 프로그래밍을 할 때에도 마찬가지입니다.

FillComboAscendingOrder 프로시저

Sub FillComboAscendingOrder()
    Dim rngCell As Range, rngTarget As Range
    Dim i As Integer, j As Integer, intNum As Integer
    Dim strItem() As String, Msg As String
    Dim cmbCombo As DropDown
    Dim varTemp As Variant
    Set cmbCombo = Sheets("Preface").DropDowns("MyCombo2")
    Set rngTarget = [Item].SpecialCells(xlTextValues)
    intNum = rngTarget.Cells.Count
    ReDim strItem(1 To intNum)

    MsgBox "기존의 리스트들을 지웁니다", , "리스트 제거//By Exceller"
    cmbCombo.RemoveAllItems
    For Each rngCell In rngTarget
        i = i + 1
        strItem(i) = rngCell
    Next rngCell
    '''배열 변수 strItem에 rngTarget, 즉 Item 영역 중에서 값이 들어있는 셀의 모든 값을 하나씩
    '''담아둡니다.

    For i = intNum - 1 To 1 Step -1
        For j = 1 To i
            If strItem(j) > strItem(j + 1) Then
                varTemp = strItem(j)
                strItem(j) = strItem(j + 1)
                strItem(j + 1) = varTemp
            End If
        Next j
    Next i
    '''strItem이라는 배열 변수에 저장된 값들을 하나씩 비교하면서 가나다순으로 정렬하는
    '''과정입니다. 아주 오래 전에 토끼와 거북이 위치 바꾸기를 하는 예제를 하나 보여드린
    '''기억이 납니다. 아래의 버튼을 누를 때마다 토끼와 거북이가 서로 자리를 바꿉니다.

    '''코드는 보시는 바와 같이 간단합니다.
    '''임시 저장공간(변수)에 토끼라는 값을
    '''담아 둡니다.
    '''거북이를 토끼가 있던 자리로 옮깁니다.
    '''마지막으로 임시저장 공간에 담아두었던
    '''토끼를 거북이가 있던 자리로 옮깁니다.

    '''바로 이 원리입니다.

    For i = 1 To intNum
        cmbCombo.AddItem strItem(i)
        '''strItem에 들어있는 값들을 콤보박스에 추가합니다.

    Next i
    Msg = Msg & "콤보 박스에 아이템을 오름차순으로 채워 넣었습니다" & vbCr
    Msg = Msg & "콤보 박스를 클릭해 보세요"
    MsgBox Msg, , "작업 완료//By Exceller"
End Sub

Step 2: 중복 없이 정렬해서 채우기

그런데 이 코드도 문제가 있습니다. 어떤 문제일까요? 위의 목록에서 빨간 색으로 표시한 셀에 기존에 입력되어 있는 값을 넣어보세요. 예를 들어 강남, 부산, 제주를 입력한 다음 다시 버튼을 눌러 보세요. 콤보박스에 아이템이 중복되어 나타나지요? 이런 프로그램을 만들어서 팔아먹었다간 테러 당합니다. ^^; 아래 버튼을 눌러 보세요.

이제 제대로 나타나지요? 코드를 보도록 합니다.

FillComboAscendingOrder2 프로시저

Sub FillComboAscendingOrder2()
    Dim rngCell As Range, rngTarget As Range
    Dim i As Integer, j As Integer, intNum As Integer
    Dim strItem() As String, Msg As String
    Dim cmbCombo As DropDown
    Dim varTemp As Variant
    Dim X As New Collection
    Set cmbCombo = Sheets("Preface").DropDowns("MyCombo3")
    Set rngTarget = [Item2].SpecialCells(xlTextValues)
    On Error Resume Next
    '''반드시 이 구문을 넣어 주어야 합니다.

    MsgBox "기존의 리스트들을 지웁니다", , "리스트 제거//By Exceller"
    cmbCombo.RemoveAllItems
    For Each rngCell In rngTarget
        X.Add rngCell.Value, CStr(rngCell.Value)
        '''중복되는 데이터 중에서 고유한 것만 골라내기 위해 바로 Collection 오브젝트를
        '''사용하였습니다.
        '''컬렉션 오브젝트의 기본적인 사용형태는
        '''[오브젝트명.Add Item, key, (before), (after)] 입니다.
        '''여기서 key는 중복되지 않는 값일 경우에만 유효하게 됩니다. 즉 중복되는 값이 나타날
        '''경우에는 에러가 발생하게 되어 아이템에 추가가 되지 않게 됩니다.
        '''컬렉션 오브젝트에 대한 보다 자세한 설명 및 예제는 VB0029, VB0030 강좌를 참고하세요.
        '''나머지 코드는 위에서 설명드린 것과 거의 비슷합니다.

    Next rngCell
    On Error GoTo 0

    intNum = X.Count
    ReDim strItem(1 To intNum)
    For i = 1 To intNum
        strItem(i) = X(i)
    Next i

    For i = intNum - 1 To 1 Step -1
        For j = 1 To i
            If strItem(j) > strItem(j + 1) Then
                varTemp = strItem(j)
                strItem(j) = strItem(j + 1)
                strItem(j + 1) = varTemp
            End If
        Next j
    Next i

    For i = 1 To intNum
        cmbCombo.AddItem strItem(i)
    Next i
    Msg = Msg & "콤보 박스에 아이템을 중복없이, 오름차순으로 채워 넣었습니다" & vbCr
    Msg = Msg & "콤보 박스를 클릭해 보세요"
    MsgBox Msg, , "작업 완료//By Exceller"
End Sub

Step 3: 다른 파일의 범위를 콤보 박스에 지정하기

두번째 질문하신 것은 해당 파일, 해당 시트로 이동한 다음에 작업을 하시면 되겠습니다. 아래의 코드를 응용해 보세요.

Private Sub UserForm_Initialize()
   Workbooks.Open Filename:="C:\Temp.xls"
   Application.Goto Sheets("Sheet1").Range("a1"), True
   ComboBox1.RowSource = "a1:a10"
End Sub

마무리

콤보박스에 대한 여러 가지 응용 방법들을 확실하게 정리해 두시기 바랍니다.

다음 시간에…

정리 — 콤보 박스 정렬·중복 제거 핵심

자주 묻는 질문 (FAQ)

마치며

배열 정렬과 Collection 오브젝트를 조합하면 콤보 박스에 정렬되고 중복 없는 목록을 채울 수 있습니다.