VBA Sheets – 최종 정리

이 튜토리얼에 VBA에서 Excel 시트/워크시트로 작업하는데 필요한 사항을  최종 정리하였습니다.

이 튜토리얼의 맨 아래에는 시트 작업을 위한 일반적인 명령에 대한 치트 시트가 있습니다.

Sheets 와 Worksheets

VBA를 사용하여 시트를 참조하는 방법에는 두 가지가 있습니다. 첫 번째는 Sheets 객체를 사용하는 것입니다:

Sheets("Sheet1").Activate

두 번째 방법은 워크시트 객체를 사용하는 것입니다:

Worksheets("Sheet1").Activate

99%의 경우 이 두 개체는 동일합니다. VBA 코드 예제를 검색해 보면 두 객체가 모두 사용된 것을 확인할 수 있습니다. 차이점은 다음과 같습니다:

Sheets 컬렉션은 워크시트와 차트 시트를 포함합니다.

따라서 일반 워크시트와 차트 시트를 포함하려면 Sheets를 사용해야 합니다. 차트 시트를 제외하려면 Worksheets를 사용합니다. 이 가이드의 나머지 부분에서는 시트와 워크시트를 같은 의미로 사용합니다.

시트 참조하기

시트를 참조하는 방법에는 여러 가지가 있습니다:

• 활성시트(ActiveSheet)
• 시트의 이름
• 시트의 인덱스 번호
• 시트의 코드명

활성시트(ActiveSheet)

활성 시트(ActiveSheet)는 현재 활성 상태인 시트입니다. 즉, 코드를 일시 중지하고 Excel을 보면 표시되는 시트입니다. 아래 코드 예제에서는 활성시트의 이름을 메시지 박스에 표시합니다.

MsgBox ActiveSheet.Name

시트의 이름

대부분은 탭 이름으로 시트를 참조하는 것이 가장 익숙할 것입니다:

Sheets("TabName").Activate

사용자에게 표시되는 시트 이름으로 시트를 참조합니다. Sheets 객체에 따옴표로 묶은 텍스트 문자열로 입력합니다.

시트 인덱스 번호

시트 색인 번호는 통합 문서에서 시트의 위치입니다. 인덱스 번호 1은 아래와 같이 첫 번째 시트를 의미합니다. 2는 두 번째 시트 등입니다:

Sheets(1).Activate

시트 인덱스 번호 – 통합문서의 마지막 시트

통합 문서에서 마지막 시트를 참조하려면 Sheets.Count를 사용하여 마지막 인덱스 번호를 가져와 해당 시트를 활성화합니다:

Sheets(Sheets.Count).Activate

시트의 코드명

시트 코드 이름은 VBA에서 사용할 수 있는 해당 시트의 객체 이름입니다:

코드명.Activate

다른 통합 문서에서 시트 참조하기

다른 통합 문서에서 시트를 참조하는 것도 WorkBooks 객체를 사용하여 간단하게 구현할 수 있습니다:

Workbooks("VBA_Examples.xlsm").Worksheets("Sheet1").Activate

중요: 통합 문서가 열려 있어야 해당 시트를 참조할 수 있습니다.

Activate와 .Select

다른 문서에서 시트 활성화(Activate) 와 선택(.Select)에 대한 모든 것을 설명하였습니다. 간단하게 요약하면 다음과 같습니다:

시트를 활성화(Activate)하면 해당 시트가 활성 시트가 됩니다. 이 시트는 Excel에서 볼 수 있는 시트입니다. 한 번에 하나의 시트만 활성화할 수 있습니다.

시트 활성화하기(Activate)

Sheets("Sheet1").Activate

시트를 선택하면 해당 시트도 활성 시트가 됩니다. Select 기능을 사용하면 한 번에 여러 시트를 선택할 수도 있습니다. 한 번에 여러 시트를 선택하면 가장 앞의 시트가 활성 시트가 됩니다. 선택한 시트 내에서 활성 시트를 전환할 수도 있습니다.

시트 선택하기(Select+)

Sheets("Sheet1").Select

여러 시트 선택하기

배열을 사용하여 한 번에 여러 시트를 선택할 수 있습니다:

Worksheets(Array("Sheet2", "Sheet3")).Select

Worksheet 변수

워크시트를 객체 변수에 할당하면 변수 이름으로 워크시트를 참조할 수 있습니다. 이렇게 하면 코드의 가시성과 가독성을 높일 수 있습니다. 변수를 사용하는 다른 이유도 있습니다.

워크시트 변수를 선언하기 위한 코드는 다음과 같습니다:

Dim ws as worksheet

아래 코드를 활용하여 워크시트를 변수에 할당합니다:

Set ws = Sheets("Sheet1")

이제 코드에서 워크시트 변수를 참조할 수 있습니다:

ws.Activate

통합 문서의 모든 시트 반복하기

워크시트 변수는 통합 문서의 모든 워크시트를 반복할 때 유용합니다. 가장 쉬운 방법은 다음과 같습니다:

Dim ws as Worksheet

For Each ws in Worksheets
  MsgBox ws.name
Next ws

이 코드는 통합 문서의 모든 워크시트를 반복하여 각 워크시트 이름을 메시지 박스에 표시합니다. 통합 문서의 모든 시트를 반복하여 여러 워크시트를 한 번에 잠금/잠금 해제하거나 숨기기/숨기기 해제 기능을 사용할 수 있습니다.

워크시트 보호

통합 문서 보호하기

통합 문서 보호는 워크시트 추가, 삭제, 이동 또는 숨기기와 같은 구조적 변경으로부터 통합 문서를 잠급니다.

VBA를에서 통합 문서 보호를 설정할 수 있습니다:

ActiveWorkbook.Protect Password:="Password"

또는 아래와 같이 통합 문서 보호를 해제할 수 있습니다:

ActiveWorkbook.UnProtect Password:="Password"

참고: Password 인수를 생략하면 암호 없이 보호/보호 해제할 수도 있습니다:

ActiveWorkbook.Protect

 

워크시트 보호

워크시트 보호는 개별 워크시트에 대한 변경을 방지합니다.

워크시트 보호하기

Worksheets("Sheet1").Protect "Password"

워크시트 보호 해제하기

Worksheets("Sheet1").Unprotect "Password"

워크시트를 보호할 때 다양한 옵션(서식 변경 허용, 사용자 행 삽입 허용 등)이 있습니다. 매크로 기록 기능을 사용하여 원하는 설정을 코드로 변환할 수 있습니다.

워크시트 보호에 대한 자세한 내용은 여기를 참조하세요.

 

워크시트의 Visible 속성

워크시트를 숨길 수 있다는 것은 이미 알고 계실 것입니다:

워크시트 Visible 속성은 세 가지가 있습니다: 표시, 숨김과 한번 더 숨김(VeryHidden)입니다. 일반 Excel 사용자는 워크시트 탭 영역(위 그림 참조)을 마우스 오른쪽 버튼으로 클릭하여 숨겨진 시트를 해제할 수 있습니다. 한번 더 숨겨진 시트는 VBA 코드를 사용하거나 VBA 편집기 내에서만 숨기기를 해제할 수 있습니다. 다음 코드 예제를 사용하여 워크시트를 숨기거나 숨기기를 해제할 수 있습니다:

워크시트 숨기기 해제하기

Worksheets("Sheet1").Visible = xlSheetVisible

워크시트 숨기기

Worksheets("Sheet1").visible = xlSheetHidden

워크시트 한번 더 숨기기(VeryHidden)

Worksheets("Sheet1").Visible = xlSheetVeryHidden

 

워크시트 이벤트

이벤트는 “이벤트 프로시저”를 실행할 수 있는 트리거입니다. 예를 들어 워크시트의 셀이 변경될 때마다 또는 워크시트가 활성화될 때마다 코드가 실행되도록 할 수 있습니다.

워크시트 이벤트 프로시저는 워크시트 모듈에 배치해야 합니다:

수많은 워크시트 이벤트가 있습니다. 전체 목록을 보려면 워크시트 모듈로 이동하여 첫 번째 드롭다운에서 ‘Worksheet’를 선택합니다. 그런 다음 두 번째 드롭다운에서 이벤트 프로시저를 선택하여 모듈에 삽입합니다.There are numerous worksheet events. To see a complete list, go to a worksheet module, select “Worksheet” from the first drop-down. Then selecting an event procedure from the second drop-down to insert it into the module.

Worksheet Activate 이벤트

워크시트가 열릴 때마다 워크시트 활성화 이벤트가 실행됩니다.

Private Sub Worksheet_Activate()
  Range("A1").Select
End Sub

이 코드는 워크시트를 열 때마다 A1 셀(워크시트의 왼쪽 상단으로 표시 영역을 재설정)을 선택합니다

Worksheet Change 이벤트

Worksheet Change 이벤트는 워크시트에서 셀 값이 변경될 때마다 실행됩니다. 자세한 내용은 Worksheet Change 이벤트에 대한 튜토리얼을 참조하세요

Worksheet 치트 시트

아래에는 VBA에서 사용할 수 있는 일반적인 코드 예제가 포함된 치트 시트가 있습니다.

VBA Worksheets Cheatsheet

VBA worksheets Cheatsheet

Description	Code Example
Referencing and Activating Sheets
Tab Name	Sheets("Input").Activate
VBA Code Name	Sheet1.Activate
Index Position	Sheets(1).Activate
Select Sheet
Select Sheet	Sheets("Input").Select
Set to Variable	Dim ws as Worksheet Set ws = ActiveSheet
Name / Rename	ActiveSheet.Name = "NewName"
Next Sheet	ActiveSheet.Next.Activate
Loop Through all Sheets	Dim ws as Worksheet For each ws in Worksheets Msgbox ws.name Next ws
Loop Through Selected Sheets	Dim ws As Worksheet For Each ws In ActiveWindow.SelectedSheets MsgBox ws.Name Next ws
Get ActiveSheet	MsgBox ActiveSheet.Name
Add Sheet	Sheets.Add
Add Sheet and Name	Sheets.Add.Name = "NewSheet"
Add Sheet With Name From Cell	Sheets.Add.Name = range("a3").value
Add Sheet After Another	Sheets.Add After:=Sheets("Input")
Add Sheet After and Name	Sheets.Add(After:=Sheets("Input")).Name = "NewSheet"
Add Sheet Before and Name	Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet"
Add Sheet to End of Workbook	Sheets.Add After:=Sheets(Sheets.Count)
Add Sheet to Beginning of Workbook	Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet"
Add Sheet to Variable	Dim ws As Worksheet Set ws = Sheets.Add
Copy Worksheets
Move Sheet to End of Workbook	Sheets("Sheet1").Move After:=Sheets(Sheets.Count)
To New Workbook	Sheets("Sheet1").Copy
Selected Sheets To New Workbook	ActiveWindow.SelectedSheets.Copy
Before Another Sheet	Sheets("Sheet1").Copy Before:=Sheets("Sheet2")
Before First Sheet	Sheets("Sheet1").Copy Before:=Sheets(1)
After Last Sheet	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Copy and Name	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = "LastSheet"
Copy and Name From Cell Value	Sheets("Sheet1").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Range("A1").Value
To Another Workbook	Sheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1)
Hide / Unhide Sheets
Hide Sheet	Sheets("Sheet1").visible = False or Sheets("Sheet1").visible = xlSheetHidden
Unhide Sheet	Sheets("Sheet1").Visible = True or Sheets("Sheet1").Visible = xlSheetVisible
Very Hide Sheet	Sheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete or Clear Sheets
Delete Sheet	Sheets("Sheet1").Delete
Delete Sheet (Error Handling)	On Error Resume Next Sheets("Sheet1").Delete On Error GoTo 0
Delete Sheet (No Prompt)	Application.DisplayAlerts = False Sheets("Sheet1").Delete Application.DisplayAlerts = True
Clear Sheet	Sheets("Sheet1").Cells.Clear
Clear Sheet Contents Only	Sheets("Sheet1").Cells.ClearContents
Clear Sheet UsedRange	Sheets("Sheet1").UsedRange.Clear
Protect or Unprotect Sheets
Unprotect (No Password)	Sheets("Sheet1").Unprotect
Unprotect (Password)	Sheets("Sheet1").Unprotect "Password"
Protect (No Password)	Sheets("Sheet1").Protect
Protect (Password)	Sheets("Sheet1").Protect "Password"
Protect but Allow VBA Access	Sheets("Sheet1").Protect UserInterfaceOnly:=True
Unprotect All Sheets	Dim ws As Worksheet For Each ws In Worksheets ws.Unprotect "password" Next ws