주말이 참 빨리 흘러 갑니다. 잠깐 책 좀 폈다, 잠깐 읽었다 싶으면 하루가 날아가네요. 오늘은 이전 포스트 '[Excel] VBA - Workbook 완벽 가이드'에 이번에는 VBA 스크립트의 Worksheet에 대해 공부 해보려고 합니다. 원문은 Excel Macro Mastery 사이트의 The Complete Guide To The VBA Worksheet(by Paul Kelly)입니다. 혹시 제가 제대로 이해하지 못하고 헛소리 하는 부분이 있다면 알려주시면 감사하겠습니다.
VBA Worksheet 사용법 요약
NOTE - 아래 간략 예제에서는 코드의 간략성을 위해 워크북(Workbook)을 명시하지 않았습니다. 여러분이 실제 코드에서 사용하실 때는 ThisWorkbook.Worksheets, wk.Worksheets 와 같이 워크시트가 속한 워크북을 명시해주셔야 하며 그렇지 않은 경우 암묵적으로 활성화 된 워크북이 사용 됩니다.
| Task | How to |
| 워크시트에 이름으로 접근하기 | Worksheets("Sheet1") |
| 워크시트를 인덱스로 접근하기 (좌측 부터 우측으로 1부터 1씩 증가) |
Worksheets(2) Worksheets(4) |
| 가장 왼쪽의 워크시트에 접근하기 | Worksheets(1) |
| 가장 오른쪽의 워크시트에 접근하기 | Worksheets(Worksheets.Count) |
| 워크시트 코드 네임을 이용해 접근 | 아래 코드 네임 섹션 참고 |
| 활성화 된 워크시트에 접근 | ActiveWorksheet |
| 워크시트 변수 선언 | Dim sh As Worksheet |
| 워크시트 변수 할당 | Set sh = Worksheets("Sheet1") |
| 워크시트 추가 | Worksheets.Add |
| 맨 앞(왼쪽)에 워크시트 추가하기 | Worksheets.Add Before := Worksheets(1) |
| 맨 뒤(오른쪽)에 워크시트 추가하기 | Worksheets.Add After := Worksheets(Worksheets.Count) |
| 여러 개 워크시트 추가하기 | Worksheets.Add Count:=3 |
| 워크시트 활성화하기 | sh.Activate |
| 워크시트 복사하기 | sh.Copy |
| 뒤에 있는 워크시트 복사하기 | sh1.Copy After:=Sh2 |
| 앞에 있는 워크시트 복사하기 | sh1.Copy Before:=Sh2 |
| 워크시트 삭제하기 | sh.Delete |
| 경고 없이 워크시트 삭제하기 | Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True |
| 워크시트 이름 변경하기 | sh.Name = "New Name" |
| 워크시트 숨기기/보이기 | sh.Visible = xlSheetHidden sh.Visible = xlSheetVisible |
| 전체 워크시트 순회하기 | Dim i As Long For i = 1 To Worksheets.Count Debug.Print Worksheets(i).Name Next I Dim sh As Worksheet For Each sh In Worksheets Debug.Print sh.Name Next |
시작하기
오늘 알아볼 워크시트는 워크북, 셀과 함께 엑셀 VBA 프로그래밍에서 가장 자주 사용되고 중요한 세가지 요소 중 하나 입니다. 그 중 여러분이 작성하는 코드의 대부분은 이 워크시트에 대해 이루어진다고 해도 과언이 아닙니다..어쩌면 과언일 수도 있습니다.
이전 포스트에서 워크북을 하나의 서류철 또는 책이라고 표현했습니다. 그러면 워크시트는 뭘까요? 서류철이나 책 안에 들어 있는 종이 한장 한장이라고 생각하시면 됩니다.
워크시트의 주요 용도는 워크시트가 가지고 있는 셀들에 접근하는 것입니다. 물론 워크시트를 숨기고, 보이고, 복사하고, 생성하고, 삭제 하는 작업도 있지만 대부분의 일은 워크시트에 속해 있는 데이터 셀에 접근하고 데이터를 읽거나 업데이트 하는 것일겁니다.
워크시트에 접근하기
VBA에서 각 워크북들은 여러 개의 워크시트를 가질 수 있습니다. 워크북 내의 워크시트들은 간단하게 Worksheets를 통해 접근할 수 있습니다. 여러분은 단지 저 Worksheets에 워크시트의 이름이나 번호를 넣어 주시면 됩니다.
' Sheet1,Sheet2, Sheet3 워크시트들의 A1셀에 "Hello World" 라고 쓰기
ThisWorkbook.Worksheets("Sheet1").Range("A1") = "Hello World"
ThisWorkbook.Worksheets("Sheet2").Range("A1") = "Hello World"
ThisWorkbook.Worksheets("Sheet3").Range("A1") = "Hello World"워크시트는 반드시 워크북에 속해 있습니다. 만일 아래 처럼 명시적으로 워크북을 지정해주지 않는다면 현재 활성화된 워크북이 기본지정 됩니다.
'ActiveWorkbook.Worksheets 와 동일함
Worksheets("Sheet1").Range("A1") = "Hello World"
Worksheets("Sheet2").Range("A1") = "Hello World"
Worksheets("Sheet3").Range("A1") = "Hello World"워크시트 숨기기/보이기
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden '워크시트 숨기기
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible '워크시트 보이기만일 워크시트에 접근하는 것을 강역하게 막고 싶다면, "very hidden" 옵션을 사용 할 수 있습니다. "very hidden" 옵션은 시트에서는 보이지 않고 오직 코드에서만 접근 할 수 있습니다.
' Hide from user access
ThisWorkbook.Worksheets("Sheet1").Visible = xlVeryHidden
' This is the only way to make a xlVeryHidden sheet visible
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible워크시트 보호하기
워크시트에 패스워드를 걸어 접근으로 부터 보호 할 수 있습니다.
ThisWorkbook.Worksheets("Sheet1").Protect Password:="MyPass"
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="MyPass"인덱스를 이용해 워크시트에 접근하기
지금까지는 이름을 이용해 워크시트에 접근 했습니다. 이번 예제는 인덱스(워크시트 탭의 순서)를 이용해 접근하는 예를 살펴 보도록 하겠습니다.
NOTE - 사실 워크시트의 순서는 사용자에 의해 쉽게 바뀔 수 있으므로 VBA코드에 처음 또는 마지막이 아닌 특정 인덱스를 이용하여 접근하는 방법은 오류를 만들 소지가 있으니 조심해서 사용해야 합니다.
'워크북에 워크시트가 최소 3개 이상 있다고 가정
With ThisWorkbook
' Left most sheet
Debug.Print .Worksheets(1).Name
' The third sheet from the left
Debug.Print .Worksheets(3).Name
' Right most sheet
Debug.Print .Worksheets(.Worksheets.Count).Name
End WithNOTE - 위 예제에서 결과를 확인하기 위해 Debug.Print 라는 것을 사용 헸습니다. Debug.Print에 대한 설명은 [여기]를 참고 해주세요.
워크시트의 코드 네임 사용하기
워크시트에 접근하는 가장 좋은 방법은 '코드 네임'을 이용하는 것입니다. 모든 워크시트는 각각의 '코드 네임'과 '워크시트 네임'을 가지고 있습니다. 워크시트 네임은 엑셀의 워크시트 탭에 보여지는 이름입니다.
'워크시트 네임'과 '코드 네임'은 프로퍼티 창에서 수정이 가능 합니다.
NOTE - 만일 프로퍼티 창이 보이지 않는다면 단축키 F4또는 메뉴에서 '보기 > 속성 창'을 선택 해 활성화 시킬 수 있습니다.
코드에서 워크 시트 네임이 아니라 코드 네임으로 워크시트에 접근한다면 워크시트의 이름을 변경해도 코드에 영향을 미치지 않습니다. 아래는 코드 네임을 이용해 워크시트에 직접 접근하는 예제 입니다.
Debug.Print WorksheetCodeName.Name
WorksheetCodeName.Range("A1") = 45
WorksheetCodeName.Visible = TrueNOTE - CodeName은 프로퍼티로 이미 사용되고 있기 때문에 워크시트의 코드 네임에 'CodeName'이라는 단어는 사용 하실 수 없습니다. 'CodeName'이라는 단어를 사용하게 되면 '컴파일 오류입니다: 수식어가 잘못되었습니다' 오류가 발생합니다. 다른 이름을 사용 하셔야 합니다.
NOTE - 코드 네임을 사용 할 때는 ThisWorkbook과 같이 워크북을 지정하지 않습니다(하면 오류 발생합니다).
워크시트를 코드 네임으로 접근하는 방법은 코드의 직관성과 데이터의 변경으로 인한 코드의 변경을 방지해주지만 한가지 단점이 있습니다. 코드 네임을 이용한 워크시트 접근은 동일 워크북 내에서만 가능합니다. 다른 워크북에서 해당 워크시트를 코드 네임으로 접근하기 위해서는 아래와 같은 별도의 처리가 추가 되어야 합니다.
Public Sub UseSheet()
Dim sh As Worksheet
' Get the worksheet using the codename
Set sh = SheetFromCodeName("WorksheetCodeName", ThisWorkbook)
' Use the worksheet
Debug.Print sh.Name
End Sub
' 이 함수는 코드네임으로 워크시트를 찾아 줍니다.
Public Function SheetFromCodeName(Name As String, bk As Workbook) As Worksheet
Dim sh As Worksheet
For Each sh In bk.Worksheets
If sh.CodeName = Name Then
Set SheetFromCodeName = sh
Exit For
End If
Next sh
End Function위 코드를 사용하면 유저가 다른 워크북에 속해 있는 워크시트 네임을 변경하여도 코드에 영향을 미치지 않습니다. 다른 워크북의 워크시트에 코드 네임으로 접근하는 다른 방법은 워크북의 VBProject 요소를 이용하는 것입니다.
Public Function SheetFromCodeName(codeName As String, bk As Workbook) As Worksheet
' Get the sheet name from the CodeName using the VBProject
Dim sheetName As String
sheetName = bk.VBProject.VBComponents(codeName).Properties("Name")
' Use the sheet name to get the worksheet object
Set SheetFromCodeName = bk.Worksheets(sheetName)
End FunctionActiveSheet
ActiveSheet 개체는 가장 최근 활성화된 워크시트를 가리킵니다. 활성화 된 워크시트는 유저가 어떤 시트를 가장 마지막에 보았느냐에 따라 쉽게 달라 질 수 있으므로 코드에서 사용하려면 주의를 기울여 사용해야 합니다. 만일 특정한 목적이 없다면 명시적으로 워크시트를 지정하여 사용하는 편이 좋습니다.
워크시트 메소드를 사용 할 때 명시적으로 워크시트를 지정하지 않으면, 암묵적으로 현재 활성화 된 워크시트를 가리키게 됩니다. 예를 들어 워크시트 메소드인 Range를 사용 할 때 워크시트를 명시하지 않으면 암묵적으로 ActiveSheet.Range를 호출하는 것과 같습니다.
' Write to Cell A1 in the active sheet
ActiveSheet.Range("A1") = 99
' Active sheet is the default if no sheet used
Range("A1") = 99워크시트 변수 선언하기
개체 변수를 선언하여 사용한다는 것은 코드를 간결하게 만들고 읽기 편하게 만들어 줍니다. 다음 예제는 셀들을 업데이트 하는 예제 입니다. 하나는 변수를 사용하지 않고 워크시트에 접근하고 다른 하나는 워크시트 변수를 사용하여 접근합니다. 두 예제의 차이를 살펴 보시죠.
Public Sub SetRangeVals()
Debug.Print ThisWorkbook.Worksheets("Sheet1").Name
ThisWorkbook.Worksheets("Sheet1").Range("A1") = 6
ThisWorkbook.Worksheets("Sheet1").Range("B2:B9").Font.Italic = True
ThisWorkbook.Worksheets("Sheet1").Range("B2:B9").Interior.Color = rgbRed
End Sub
Public Sub SetRangeValsObj()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed
End Sub그리고 워크시트 개체 변수를 이용한다면 With 키워드를 이용하여 코드를 더 간단히 만들 수도 있습니다.
Public Sub SetRangeValsObjWith()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
With sht
.Range("A1") = 6
.Range("B2:B9").Font.Italic = True
.Range("B2:B9").Interior.Color = rgbRed
End With
End Sub워크시트 추가하기
워크시트를 추가하기 위해서는 Add 메소드를 이용하면 됩니다. 아무런 인자 없이 단순히 Add 메소드만 호출하게 되면 현재 활성화 되어 있는 워크시트 앞에(왼쪽)에 새로운 워크시트가 추가 됩니다.
워크시트는 Sheet4, Sheet5..와 같이 기본 이름을 가진 채로 추가 되고, Name프로퍼티를 이용해 쉽게 변경이 가능합니다.
아래 코드는 새로운 워크시트를 추가하고 "Accounts"라는 이름으로 변경합니다. 만일 기존 "Account"라는 워크시트 네임을 가진 워크시트가 있는 경우 오류를 발생 시킵니다.
Public Sub AddSheet()
Dim sht As Worksheet
' Adds new sheet before active sheet
Set sht = ThisWorkbook.Worksheets.Add
' Set the name of sheet
sht.Name = "Accounts"
End Sub위 예제 처럼 현재 ActiveWorksheet에 상대적 위치에 새로운 워크시트를 생성 할 수도 있지만, 아래 처럼 위치를 지정하여 생성하는 것도 가능합니다.
Public Sub AddSheetFirstLast()
Dim shtNew As Worksheet
Dim shtFirst As Worksheet, shtLast As Worksheet
With ThisWorkbook
Set shtFirst = .Worksheets(1)
Set shtLast = .Worksheets(.Worksheets.Count)
' Adds new sheet to first position in the workbook
Set shtNew = Worksheets.Add(Before:=shtFirst)
shtNew.Name = "FirstSheet"
' Adds new sheet to last position in the workbook
Set shtNew = Worksheets.Add(After:=shtLast)
shtNew.Name = "LastSheet"
End With
End Sub워크시트 삭제하기
워크시트를 삭제하기 위해서는 간단히 Delete 메소드를 호출 하면 됩니다.
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet12")
sh.Delete워크시트가 삭제 될때 엑셀은 아래와 같은 메시지창을 띄우며 삭제 여부를 확인합니다.
만일 저런 메시지창이 불필요 하다면 아래와 같이 경고를 잠시 비활성화 시켜놓고 워크시트를 삭제 할 수 있습니다.
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True워크시트를 삭제 할 때 주의해야 할 점이 두가지 있습니다.
첫째, 삭제된 워크시트에 접근 하려 시도하면 "'9' 런타임 오류가 발생하였습니다: 아래 첨자 사용이 잘못 되었습니다." 에러가 발생합니다.
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet2")
sh.Delete
' 삭제된 워크시트를 찹조하려하므로 런타임 오류를 발생 시킵니다.
Set sh = ThisWorkbook.Worksheets("Sheet2")둘째, 삭제된 워크시트를 가리키고 있는 변수를 사용하려 하면 '424 런타임 오류가 발생하였습니다. 개체가 필요합니다' 오류가 발생합니다.
Public Sub DelWorksheet()
Application.DisplayAlerts = False
Dim wsht As Worksheet
Set wsht = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Delete
Application.DisplayAlerts = True
Debug.Print wsth.Name '424 Runtime error
End Sub전체 워크시트 순회하기
Public Sub LoopForEach()
' 모든 워크시트의 A1 셀에 "Hello World"를 기록한다.
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "Hello World"
Next sht
End Sub
Public Sub LoopFor()
' 모든 워크시트의 A1 셀에 "Hello World"를 기록한다.
Dim i As Long
For i = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(i).Range("A1") = "Hello World"
Next sht
End Sub'Sheets' 컬렉션 이용하기
워크북은 Worksheets외에도 Sheets라는 다른 컬렉션 개체를 멤버로 가지고 있습니다. 이게 이름이 서로 비슷해서 처음 보는 사람들은 시트와 워크시트를 많이 헷깔려 하시는데요. 여기서 Sheets는 워크시트 외에도 '차트 시트'를 추가적으로 참조 하고 있는 컬렉션 개체 입니다. 자세한 설명을 하기 전에 차트 시트를 먼저 만들어 보죠.
- 아무 엑셀 시트에 차트를 생성합니다. 차트 종류, 모양, 데이터 상관 없습니다. 일단 차트를 만드세요.
- 차트의 여백 부분을 우클릭 하여 메뉴가 뜨면 '차트 이동'을 선택 합니다.
또는 차트를 선택하면 나오는 리본 메뉴 '차트 도구 > 디자인 > 차트 이동'을 선택하셔도 됩니다.
- 첫 번째 옵션, '새 시트'를 선택 합니다.
위 과정을 완료하면 차트를 가리키는 시트가 워크북에 추가 됩니다. 워크시트와 용어가 헷깔리니까 차트 시트라고 부르도록 하겠습니다.
- Worksheets 컬렉션은 워크북에 속해 있는 모든 워크시트를 참조하고 있습니다. Worksheets는 차트 시트를 참조 하지 않습니다.
- Sheets 컬렉션은 차트 시트를 포함에 워크북에 속해 있는 모든 시트를 참조 합니다.다.
아래 코드 샘플 중 첫번째 샘플은 Sheets를 이용해 전체 시트들을 순회하며 이름을 출력하며, 두번째는 워크시트들만 순회하며 이름을 출력 합니다.
Public Sub CollSheets()
Dim sht As Variant
' Display the name and type of each sheet
For Each sht In ThisWorkbook.Sheets
Debug.Print sht.Name & " is type " & TypeName(sht)
Next sht
End Sub
Public Sub CollWorkSheets()
Dim sht As Variant
' Display the name and type of each sheet
For Each sht In ThisWorkbook.Worksheets
Debug.Print sht.Name & " is type " & TypeName(sht)
Next sht
End Sub차트 시트를 워크북에 추가한 후 위 예제 두개를 다 돌려 보면 CollSheets 예제에서는 'Chart1 is type Chart' 와 비슷한 출력이 추가 되는 것을 보실 수 있을 것입니다.
이상 워크시트에 대해서 알아 보았습니다. 다음 포스트에선 Cell과 Range에 대해 알아보도록 하겠습니다.
