모든 것은 뿌린대로 거두기 마련입니다

Step 1: 데이터베이스 구조부터 생각하기

EXCEL로 자료를 정리하시는 분이 많으신 것 같습니다. 이것은 아주 바람직한 현상이라고 할 수 있습니다. 그런데 문제는… 데이터베이스를 작성하기 전에 어떤 식으로 테이블을 구성할 것인가, 작성된 DB를 이용해서 어떤 형태로 자료를 가공/분석할 것인가 하는 것과 관련해서 많은 생각을 해야한다는 것입니다.

EXCEL에서 사용하는 1차원의 평면적인 데이터베이스(이것을 Flat Database 라고 부릅니다)라면 덜하지만 ACCESS와 같이 좀더 형태가 복잡한 관계형 DB (Relational Database)나 SyBase, Oracle 같은 회사의 기간 DB를 구축하고자 하는 경우에는 이 부분에 훨씬 많은 시간과 노력을 들여야 합니다. 마치 훌륭한 영화를 만들기 위해서는 탄탄한 시나리오가 있어야 하는 것처럼…

오늘 질문하신 분의 경우에도 마찬가지라고 할 수 있습니다. 애초에 DB(사실은 DB라고 할 것까지도 없이 간단한 형태입니다만)를 구축할 때 이러한 점을 고려하지 않으셨기 때문에 이런 곤란한 일이 생기는 것입니다. 아래 버튼을 눌러 예제를 실행시켜보고 오시기 바랍니다.

Step 2: 유저폼 만들기

(1) VB Editor 상태에서 UserForm을 하나 삽입하고 아래와 같이 컨트롤들을 작성합니다.

아이엑셀러

Step 3: cmbNation_Change 이벤트

(2) 어느 국가를 선택하느냐에 따라서 서로 다른 도시명이 Listing되도록 하기 위해 cmbNation 컨트롤을 더블클릭하여 코드 입력 창을 띄운 후, Change 이벤트를 선택하고 아래의 코드를 입력합니다.

Private Sub cmbNation_Change()
    Dim i As Integer
    i = cmbNation.ListIndex
    '//I는 첫번째 콤보박스에서 몇 번째 항목, 그러니까 어느 나라가 선택되었는지 결과값을
    '//담아두기 위한 변수입니다.

    With UserForm1
    '//아래의 코드는 첫번째 콤보박스에서 어떤 국가가 선택되었느냐에 따라 그 다음
    '//콤보박스에 서로 다른 도시가 리스팅되도록 하기 위한 부분입니다. 이것은 예전에
    '//이미 설명을 드렸고 코드도 별로 까다로운 것이 없으므로 설명은 생략합니다.
    '//첫번째 항목이 선택되면 그 값이 1이 아니고 0이 할당된다는 점에 유의!

        Select Case i
            Case 0
                .lblCity = "미국의 도시"
                .cmbCity.Value = ""
                cmbCity.RowSource = "USA"
            Case 1
                .lblCity = "유럽의 국가"
                .cmbCity.Value = ""
                cmbCity.RowSource = "Europe"
            Case 2
                .lblCity = "동남아시아의 국가"
                .cmbCity.Value = ""
                cmbCity.RowSource = "Asia"
            Case 3
                .lblCity = "러시아의 도시"
                .cmbCity.Value = ""
                cmbCity.RowSource = "Russia"
        End Select
    End With
End Sub

Step 4: OK 버튼 Click 이벤트

(3) Shift + <F7>키를 눌러 다시 유저폼으로 돌아간 다음, 이번에는 OK버튼을 더블클릭하고 Click 이벤트 프로시저에 아래와 같은 코드를 작성합니다.

Private Sub btnOK_Click()
    Dim rngTarget As Range
    Dim rngCell As Range
    Dim shtSheet As Worksheet
    Dim i As Integer
    Dim r As Long
    Dim intNum As Integer
    Set rngTarget = Range("AllCities")
    intNum = rngTarget.Columns.Count
    '//먼저 등장인물들과 배역을 설정해 주고…

    For Each shtSheet In Sheets
    '//만약 Temp라는 시트가 이미 있으면 지웁니다.

        If shtSheet.Name = "Temp" Then
            Application.DisplayAlerts = False
            shtSheet.Delete
        End If
    Next shtSheet
    Worksheets.Add.Name = "Temp"
    Range("a1") = "검색 결과"

    For Each rngCell In rngTarget
    '//이제 지정된 영역을 돌면서 콤보박스에 의해 선택되어진 도시명과 값이 같으면
    '//해당 셀의 맨 아래 부분으로 가서 거기있는 내용들을 복사/붙여넣기 합니다.
    '//이름 상자(Name Box)로 가서 AllCities, Start라고 이름지어준 영역이 어디인지
    '//확인해 보시기 바랍니다.

        If rngCell.Value = UserForm1.cmbCity.Value Then
            Application.Goto Range("Start")
            Range("Start").Offset(0, rngCell.Column - 1).Select
            Range(Selection, Selection.End(xlDown)).Copy
            Sheets("Temp").Select
            r = Application.CountA(Range("a:a")) + 1
            '//rngTarget 셀을 차례대로 검색해서 콤보박스에서 선택한 값과 같은 것이 발견되면
            '//Temp 시트에 있는 자료의 마지막 부분에 계속 추가해 나갑니다.

            Sheets("Temp").Cells(r, 1).Select
            ActiveSheet.Paste
        End If
    Next rngCell
    Application.CutCopyMode = False
    Unload Me
    Range("a1").Select
    Columns(1).AutoFit
    MsgBox "자료추출을 완료하였습니다", , "작업 완료//Exceller"
    Application.DisplayAlerts = True
End Sub

Step 5: UserForm_Initialize 이벤트

한 가지가 빠졌는데… 유저폼이 처음 화면에 나타날 때 각 컨트롤의 초기값들을 설정해 주기 위해 아래와 같은 Initialize 이벤트를 사용했습니다.

Private Sub UserForm_Initialize()
    With Me
        .cmbNation.Value = ""
        .cmbCity.Value = ""
        .lblCity.Caption = " 도시"
        .cmbNation.RowSource = "a13:a16"
    End With
End Sub

마무리

오늘 설명드린 예제는 사실 테이블만 잘 구성하셨다면 별도의 VBA 코딩없이 피벗테이블이나 다른 EXCEL 고유의 분석기능만으로도 충분히 가능했을 것입니다. 잘못 구성된 테이블에 대한 예제를 하나를 보내주셔서 좋은 경험 했습니다. ^^

다음 시간에 또…

정리 — 연동 콤보박스 핵심

자주 묻는 질문 (FAQ)

마치며

표를 잘 설계하는 것이 우선이며, 잘못 구성된 표는 VBA로도 해결할 수 있지만 훨씬 번거롭습니다.