Excel VBA Courses

Excel VBA Course (4): Working with Excel Object Model

Published September 11, 2022 · Updated October 11, 2022

In this article, we will look at how to handle the Excel Object Model.

<<Table of Contents>>

2.4. Understanding the Excel Object Model with simple code

2.4.1. object access primitives

Let's look at the most basic code that accesses files, sheets, ranges, and cells. For reference, the global object Application can be omitted when accessing the object. In the second line of each example from 1 to 6 below, the code omitting Application is expressed.

'1. 이름이 t1.xlsx 인 파일
Application.Workbooks("t1.xlsx")
Workbooks("t1.xlsx") 

'2. 이 파일의 Sheet1 시트
Application.Workbooks("t1.xlsx").Worksheets("Sheet1")
Workbooks("t1.xlsx").Worksheets("Sheet1")

'3. 이 시트의 A1 cell
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1")
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1") 

'4. 이 시트의 A1 cell 에 "TEST" 문자열 입력
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1") = "TEST"
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1") = "TEST"

'5. 이 시트의 A1 cell 글꼴을 굵게(Bold) 설정
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1").Font.Bold = True
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1").Font.Bold = True

'6. 이 시트의 A2:D4 범위
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A2:D4")
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A2:D4")

Let's take a closer look at each one.

'1. 이름이 t1.xlsx 인 파일
Application.Workbooks("t1.xlsx")
Workbooks("t1.xlsx")

▲ This is a syntax that indicates the file whose name is “t1.xlsx” among the files currently open in Excel. Workbooks maintains a collection of open files. When selecting one of several files, access the 'file name' to the Workbooks collection as a parameter. (For reference, in addition to the file name, the order in which the file was opened, such as “Workbooks(1)”, can also be used as a parameter.)

'2. 이 파일의 Sheet1 시트
Application. Workbooks("t1.xlsx").Worksheets("Sheet1")
Workbooks("t1.xlsx").Worksheets("Sheet1")

▲ This is a syntax that indicates the “Sheet1” sheet of the “t1.xlsx” file specified above. Worksheets manage a collection of sheets in a file. When selecting one of several sheets, access the 'sheet name' to the Worksheets collection as a parameter. (Like Workbooks, you can also use the order of sheets as a parameter, such as “Worksheets(1)”.)

'3. 이 시트의 A1 cell
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1")
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1")

▲ This is a phrase that indicates the “A1” cell of the “Sheet1” sheet specified above.

'4. 이 시트의 A1 cell 에 "TEST" 문자열 입력
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1") = "TEST"
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1") = "TEST"

▲ This code inputs a string value to the cell accessed above. You can enter any type of value, including strings and numbers.

'5. 이 시트의 A1 cell 글꼴을 굵게(Bold) 설정
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1").Font.Bold = True
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A1").Font.Bold = True

▲ This code sets the cell format using the Range object. All cell formats such as font name, color, size, background color, cell border type, and border color can be set with VBA code.

'6. 이 시트의 A2:D4 범위
Application.Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A2:D4")
Workbooks("t1.xlsx").Worksheets("Sheet1").Range("A2:D4")

▲ A Range object can point to several contiguous cells. Range (“A2:D4”) represents '2 rows, 1 column' to '4 rows, 4 columns'.

2.4.2. simple object access

When accessing the current file or sheet, you can simply use it as follows. This code is simple and highly readable, but care must be taken when the current file or sheet is changed during execution.

'1. 현재 열려있는 파일
ActiveWorkbook

'2. 현재 열려있는 시트
ActiveWorksheet

'3. 현재 열려있는 시트의 A1 cell
Range("A1")
ActiveWorksheet.Range("A1")

'4. 현재 열려있는 시트의 A1 cell 에 "TEST" 라는 값 할당
Range("A1") = "TEST"

'5. 현재 열려있는 시트의 시트의 A1 cell 글꼴을 굵게(Bold) 설정
Range("A1").Font.Bold = True

'6. 현재 열려있는 시트의 A2:D4 범위
Range("A2:D4")

2.4.3. Object Access & Utilization Examples

Let's look at some more examples of accessing objects.

'1. 현재 열려있는 파일들
Workbooks

'2. 현재 열려있는 파일들의 수
Workbooks.Count

'3. 새로운 파일 만들기
Workbooks.Add

'4. 현재 파일을 C:\test.xlsx로 저장하기
ActiveWorkbook.SaveAs "C:\test.xlsx"

'5. 현재 파일의 이름을 보여주기
MsgBox ActiveWorkbook.Name

'6. 현재 열려있는 파일의 시트들
Worksheets

'7. 현재 열려있는 파일의 시트들의 수
Worksheets.Count

'8. 새로운 시트 만들기
Worksheets.Add

'9. Sheet1을 삭제하기
Worksheets("Sheet1").Delete

'10. 현재 시트의 이름을 보여주기
MsgBox ActiveSheet.Name

'11. 현재 열려있는 시트의 A1 cell 선택
Range("A1").Select

'12. 현재 열려있는 시트에서 사용된 영역을 선택
ActiveSheet.UsedRange.Select

2.5. Excel Object Model Details

The hierarchical structure of the frequently used Excel Object Model is as follows. The objects shown in this figure are a part, not the whole, provided to Excel VBA. Of these, the object with the background color has been dealt with once in the contents so far.

엑셀 Object Model(일부) — Excel Object Model (Part)

You don't need to know all of the white background objects now. You can learn and use it later when you need it. For now, just looking at it to see if there is such a thing is enough.

The entire object is divided into page1, page2, and page3 on the Microsoft Docs site.