WorkSheet, WorkBook, Application Object - Heeyoung-Ahn/Excel_VBA GitHub Wiki
Worksheet Object
- 시트 선택
Sheets(“시트이름”).Select, Sheet1.Select, Sheets(1).Select
- 복수의 시트 선택
Sheets(Array(“시트이름1”, “시트이름2”)).Select
- 워크시트 추가
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = “시트이름”
- 워크시트 복사
Sheets(“시트이름”).Copy After:=Sheets(Sheets.Count)
Sheets.Copy
- 워크시트 삭제
Sheet1.Delete
- 워크시트 숨기기/보이기
Sheet1.Visible = False / True
Sheet1.Visible = xlSheetHidden / xlSheetVeryHidden / xlSheetVisible
- 워크시트 보호 / 보호해제
Sheet1.Protect Password:=“12345”
Sheet1.Unprotect Password:=“12345”
Workbook Object
- 열기
Workbooks.Open Filename:=ThisWorkbook.Path & “test.xlsx”, Password:=“12345”
Workbooks.Open Filename:=“D:\폴더명\test.xlsx”, Password:=“12345”
- 저장하기
ActiveWorkbook.Save / ThisWorkbook.Save
ActiveWorkbook.SaveAs Filename:=“test.xlsm”, Fileformat:= xlOpenXMLWorkbookMacroEnabled
Sub saveFile()
Sheet1.Copy
ActiveWorkbook.SaveAs Filename:=“test.xlsx”, Password:=“12345”
End Sub
- 통합문서 이름, 경로
ActiveWorkbook.Name
ActiveWorkbook.FullName
Sub gerWorkbookPath()
Dim strFileName As String
Dim strFilePath As String
strFileName = ActiveWorkbook.FullName
strFilePath = Left(strFileName, InStrRev(strFileName, “\”))
MsgBox strFileName & vbNewLine & strFilePath
End Sub
- 통합문서 닫기
ActiveWorkbook.Close saveChanges:=False
Applicatoin Object
- 스크린 업데이트 설정
Application.ScreenUpdating = True / False
- 계산 옵션 설정
Application.Calculation = xlCalculationManual / xlCalculationAutomatic
- 경고 메시지 표시 설정
Application.DisplayAlerts = True / False
Sub DisplayAlertsDemo()
Dim wb As Workbook
Application.DisplayAlerts = False
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close
End If
Next wb
Application.DisplayAlerts = True
End Sub
Sub DisplayAlertsDemo2()
Dim sht As Worksheet
Application.DisplayAlerts = False
For Each sht In Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Visible = xlSheetHidden
End If
Next sht
Application.DisplayAlerts = True
End Sub
- 이벤트 활성화 설정
Application.EnableEnvets = True / False
- 엑셀 프로그램 종료
Application.Quit