 |
17. 세
가지 시트관련 매크로
자료다운로드 : 오튜공구함017.xls
새해 인사가 늦었습니다. 새해 복 많이 받으시고, 건강하십시오. 오늘은 시트와 관련한 3가지 매크로를 보여드리겠습니다. 여러 장의 워크시트를 정렬하는 매크로, 개별 워크시트를 하나의 통합문서로 따로 저장하는 매크로, 두 개의 시트를 비교하여 다른 부분을 표시하는 매크로 입니다. 오늘은 다루는 내용이 많아 앞의 두 개의 매크로는 간단히 넘어가고 마지막 매크로만 설명할 것입니다.
여러 장의 워크시트를 정렬하는 매크로는 이미 공개된 것이 여럿 있습니다. 그리고 공교롭게도 지난 컬럼의 주제도 정렬에 관한 것이었으니 제가 따로 만들 필요가 없을 것 같군요. 그래서 이번에는 그 유명한 John Walkenbach님의 소스를 그대로 사용할까 합니다. 정렬알고리즘은 간단한 버블소트를 사용했습니다. 그리고 그 순서에 따라 Sheet개체의 Move메소드를 사용합니다.
| modSortSheets.bas |
Sub SortSheets()
' This routine sorts the sheets of the
' active workbook in ascending order.
Dim SheetNames() As String
Dim i As Integer
Dim SheetCount As Integer
Dim VisibleWins As Integer
Dim Item As Object
Dim OldActive As Object
On Error Resume Next
SheetCount = ActiveWorkbook.Sheets.Count
If Err <> 0 Then Exit Sub ' No active workbook
' Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is protected.", _
vbCritical, "Cannot Sort Sheets."
Exit Sub
End If
' Disable Ctrl+Break
Application.EnableCancelKey = xlDisabled
' Get the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count
' Redimension the array
ReDim SheetNames(1 To SheetCount)
' Store a reference to the active sheet
Set OldActive = ActiveSheet
' Fill array with sheet names and hidden status
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
' Sort the array in ascending order
Call BubbleSort(SheetNames)
' Turn off screen updating
Application.ScreenUpdating = False
' Move the sheets
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
ActiveWorkbook.Sheets(i)
Next i
' Reactivate the original active sheet
OldActive.Activate
End Sub
Sub BubbleSort(List() As String)
' Sorts the List array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub |
다음은 개별 워크시트를 하나의 통합문서로 따로 저장하는 매크로를 살펴보겠습니다. 사실 한 줄의 매크로입니다.
Sub CopyActiveSheetAsNewWorkbook()
ActiveSheet.Copy
End Sub |
현재의 워크시트를 가리키는 ActiveSheet개체의 Copy 메소드를 사용하는 것이 전부입니다. 조금 더 기능을 부가하자면 선택한 워크시트에 대해서 통합문서로 저장하는 기능이 있으면 좋을 것입니다.
마지막 소개하는 것은 두 개의 시트를 비교하여 다른 부분을 표시하는 매크로 입니다. 사용방법은 간단합니다. 콤보상자에서 비교할 두 개의 시트를 선택하고, 표시할 색상을 선택합니다. 그리고 Compare버튼을 클릭하면 다른 부분을 지정한 색상으로 표시합니다.
비교하는 방법은 무척 단순무식합니다. 데이터가 기록된 영역에서 동일한 위치의 각 셀을 서로 비교합니다. 따라서 비교할 영역이 무척 크다면 부하가 많이 걸릴 것입니다.
색상을 선택하는 부분은 John Walkenbach님의 ColorPicker라는 프로그램소스를 응용했습니다. 이 부분은 클래스모듈을 사용하였는데, 지난 컬럼중 클래스에 대한 언급이 없어 공부를 위해 일부러 끼워놓았습니다.
| frmCompareSheets.frm |
|
 |
유저폼 상단의 두 개의 콤보상자에서는 비교할 시트를 선택합니다. 이때 기준이 되는 것은 첫번째 콤보상자에 표시된 Sheet1이며 Sheet2와 다른부분이 있다면 Sheet1에 아래애서 선택한 색상이 표시됩니다.
아래의 색상선택버튼은 John Walkenbach님의 ColorPicker라는 프로그램소스를 응용한 것으로 클래스모듈을 사용하였습니다. |
Private Sub CancelButton_Click()
ColorValue = False
Unload Me
End Sub
ColorValue는 사용자가 원하는 색상을 가지는 Variant형 변수입니다. 만일 취소버튼을 클릭하는 경우 위의 이벤트(CancelButton_Click)프로시져가 실행되고 여기에서 ColorValue에 False값이 넣고 폼을 언로드합니다(Unload Me)
Private Sub cboSheet1_Change()
If cboSheet1.Text = cboSheet2.Text Then
MsgBox "선택하려는 시트가 이미 선택되어 있습니다", vbInformation
cboSheet1.Text = ""
End If
End Sub
Private Sub cboSheet2_Change()
If cboSheet1.Text = cboSheet2.Text Then
MsgBox "선택하려는 시트가 이미 선택되어 있습니다", vbInformation
cboSheet2.Text = ""
End If
End Sub
위는 콤보상자의 선택한 항목이 바뀔 때 실행되는 이벤트프로시져입니다. 이 이벤트는 두 개의 콤보상자(cboSheet1,cboSheet2)가 같은 시트를 선택하는 것을 막기 위한 것입니다. Sheet1대 Sheet1처럼 자기 자신과 비교하는 건 쓸데없는 일일 것입니다.
Private Sub OKButton_Click()
If Len(cboSheet1.Text) = 0 Or Len(cboSheet2.Text) = 0 Then
MsgBox "시트를 선택하지 않았습니다.", vbCritical
Else
CompareSheetsMain cboSheet1.Text, cboSheet2.Text
Unload Me
End If
End Sub
Compare버튼(OKButton)을 클릭하면 시트를 비교하는 CompareSheetsMain을 호출하고 여기에 cboSheet1과 cboSheet2의 선택한 내용(Text)을 전달합니다. |
| modCompareSheets.bas |
Public ColorValue As Variant
ColorValue는 사용자가 선택한 색상을 저장하는 변수입니다. 이것은 모듈수준에서 선언하였고 Public이므로 다른 모듈에서도 이용가능합니다(frmCompareSheets.frm모듈에서 사용한 것을 보셨죠?)
Dim Buttons(1 To 56) As New ColorButtonClass
Buttons 은 색상버튼 56개를 저장하는 배열입니다. 다만 데이터형이 VBA에서 제공하는 것이 아닌 사용자정의 개체(ColorButtonClass)입니다. 이것에 대해서는 다음에 설명드리죠.
Sub CompareSheetsStart()
Initialize
PaintColor
frmCompareSheets.Show
End Sub
위의 프로시져는 메뉴에 걸어둔 것입니다. 이것은 폼을 화면에 띄우기 전에 미리 준비하는 내용을 실행합니다. 무슨 준비를 하느냐구요, 현재의 워크시트목록을 cboSheet1, cboSheet2에 저장하고(Sub Initialize()), 색상버튼에 색을 입히는 등(Sub PaintColor()) 준비할 것이 많습니다.
Sub PaintColor()
Dim ctl As Control
Dim ButtonCount
As Integer
For Each ctl In frmCompareSheets.Controls
If ctl.Tag = "ColorButton" Then
ButtonCount = ButtonCount + 1
Set Buttons(ButtonCount).ColorButton = ctl
Buttons(ButtonCount).ColorButton.BackColor = _
ActiveWorkbook.Colors(ButtonCount)
End If
Next ctl
End Sub
 위의 프로시져는 56개의 색상버튼에 색상을 입히는 것입니다. ctl은 컨트롤을 대신하는 개체변수입니다.
frmCompareSheets.Controls는 폼의 각종 컨트롤을 대신할 수 있는데, cboSheet1, cboSheet2같은 ComboBox, Label, CommandButton등을 대신할 수 있습니다.
그런데 색상을 입혀야 할 것은 모든 컨트롤이 아니라 아래 그림과 같이 흰색상자로 표시한 버튼들입니다.
이 버튼은 모두 Tag프로퍼티에 "ColorButton"이라는 값을 가지고 있어 다른 컨트롤과 코드상으로 구분이 됩니다( If ctl.Tag = "ColorButton" Then )
그리고 컬러버튼으로 분류된 컨트롤은 Buttons컬렉션 개체의 ColorButton이라는 개체로 저장됩니다. ( Set Buttons(ButtonCount).ColorButton = ctl )
또한 버튼의 배경색상을 아래와 같이 지정합니다. 아래의 코드는 ColorButton버튼의 BackColor프로퍼티를 현재통합문서의 색상버튼색을 지정합니다.
Buttons(ButtonCount).ColorButton.BackColor = _
ActiveWorkbook.Colors(ButtonCount)
Public Sub Initialize()
Dim cltSht() As String
Dim sht As Worksheet
Dim i As Byte
If ActiveWorkbook.Sheets.Count <= 1 Then
MsgBox "비교할 대상의 시트가 2개이상이어야 합니다", vbInformation
Unload frmCompareSheets
End If
ReDim cltSht(ActiveWorkbook.Sheets.Count - 1)
For Each sht In ActiveWorkbook.Sheets
cltSht(i) = sht.Name
i = i + 1
Next
frmCompareSheets.cboSheet1.Clear
frmCompareSheets.cboSheet2.Clear
For i = LBound(cltSht) To UBound(cltSht)
frmCompareSheets.cboSheet1.AddItem cltSht(i)
frmCompareSheets.cboSheet2.AddItem cltSht(i)
Next
End Sub
위의 프로시져는 작업대상인 현재통합문서의 워크시트목록을 콤보상자 cboSheet1,cboSheet2에 저장하는 프로시져입니다. 이때 몇 가지 주의할 점은 현재통합문서에 시트의 갯수가 최소 2개이상인지를 체크하고(If ActiveWorkbook.Sheets.Count <= 1 Then), frmCompareSheets.cboSheet1.Clear와 같이 콤보상자에 기존의 내용이 없도록 지우는 일을 해야 한다. 그리고 AddItem메소드를 사용하여 cboSheet1,cboSheet2의 목록을 시트이름으로 채웁니다.
Public Sub CompareSheetsMain(sht1 As String, sht2 As String)
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim Row As Integer
Dim Col As Integer
Dim Cmp1
Dim Cmp2
Dim r As Integer
Dim c As Integer
Set sh1 = ActiveWorkbook.Sheets(sht1)
Set sh2 = ActiveWorkbook.Sheets(sht2)
Row = sh1.Range("A1").End(xlDown).Row
Col = sh1.Range("A1").End(xlToRight).Column
For r = 1 To Row
For c = 1 To Col
Cmp1 = sh1.Cells(r, c)
Cmp2 = sh2.Cells(r, c)
If Cmp1 <> Cmp2 Then
sh1.Cells(r, c).Interior.Color = ColorValue
If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
End If
Next
Next
Set sh1 = Nothing
Set sh2 = Nothing
End Sub
두 개의 워크시트를 비교하는 부분입니다. 비교대상을 저장하도 있는 Cmp1과 Cmp2는 별다른 형을 지정하지 않고 Variant형이 되도록 했습니다. 비교하는 것이 정수일 수도 있고, 문자열일 수도 있으며, 혹은 날짜형일 수도 있기 때문입니다.
비교하려는 범위는 시트전체가 아닙니다. 사용자가 데이터를 기록해던 부분까지만 비교하는 것이 효율적입니다. 그래서 행과 열의 끝부분을 다음과 같이 찾습니다.
Row = sh1.Range("A1").End(xlDown).Row
Col = sh1.Range("A1").End(xlToRight).Column
이제 값이 서로 일치하는 경우 선택한 색상을 칠해줍니다.
sh1.Cells(r, c).Interior.Color = ColorValue |
여기에서 클래스모듈을 사용하는 이유는 프로그램의 장황함을 줄일 수 있기 때문입니다. 가령 클래스모듈을 사용하지 않는 경우에 각 56개의 버튼마다 속성(BackColor)을 지정하고 이벤트를 만들어야 한다(물론 속성지정과 이벤트는 거의 동일한 내용이지만)
| ColorButtonClass.cls |
Public WithEvents ColorButton As msforms.CommandButton
ColorButton을 유저폼의 CommandButton 개체변수로 지정하였다. 이것은 ColorButtonClass클래스의 멤버변수가 된다. 그리고 WithEvents를 붙여 CommandButton이 가지는 이벤트를 상속하도록 하였다. 덕분에 아래의 이벤트프로시져와 같은 이벤트를 만들수 있게된다.
Private Sub ColorButton_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
frmCompareSheets.SampleLabel.BackColor = ColorButton.BackColor
ColorValue = ColorButton.BackColor
End Sub
ColorButton버튼을 클릭하는 경우 버튼의 배경색(backColor) 프로퍼티를 ColorValue변수에 넘겨준다 |
오늘은 시트와 관련한 매크로를 모두 정리해봤습니다. 앞서 두 개의 것은 그다지 크게 설명할 것이 없어 같이 묶어 해버렸습니다. 또 시간을 너무 낭비한 것 같아서요... 다음시간에는 통합문서와 관련한 매크로를 가지고
오겠습니다. 그럼....
|
