Chapter 6 – Excel Object Model

Chapter 6 – Excel Object Model

In this chapter, we will learn how to read and write values in Excel Cells, Range, Worksheets, Workbooks and Application Objects.

Excel Object Model

VBA for Beginners

Working with Cells

Read value from ActiveCell

Code:

MsgBox ActiveCell.Value

Result:
VBA for Beginners

Explanation: Currently Cell B3 is selected, hence it is considered as Active Cell. The code reads the value from the cell and returns it in message box.

Read value from a cell

Code:

MsgBox Sheet1.Range("B3").Value & " " & Sheet1.Range("C3").Value

Result:
VBA for Beginners

Explanation: The code concatenates/joins values from B3 and C3 cells and return the value in message box. Note that a space is also added between the values.

Write value in ActiveCell

Code:

ActiveCell.Value = "World is beautiful"

Result:
VBA for Beginners

Explanation: The code writes “World is beautiful” in the active cell of the sheet

Write value in a Cell

Code:

Sheet1.Range("B2").Value = "World is beautiful"

Result:
VBA for Beginners

Explanation: The code writes “World is beautiful” in cell B2 of Sheet1

Change Cell color

Code:

Sheet1.Range("B3").Interior.Color = vbGreen

Result:
VBA for Beginners

Explanation: The codes changes the color of cell B3 to green. You can learn from about colors from VBA Code to Change Cell Color post

Write Formula in a cell

Code:

Sheet1.Range("A8").Value = "=SUM(A2:A7)"

Result:
VBA for Beginners

Explanation: The code writes sum formula in cell A8

Working with Ranges

Copy paste a range

Code:

Sheet1.Range("A1:C14").Copy
Sheet1.Range("F1").PasteSpecial xlPasteAll

Result:
VBA for Beginners

Explanation: The code copies Excel range A1 to C14 from Sheet1 and paste it in cell F1

Clear content of range

Code:

Sheet1.Range("A2:C14").ClearContents

Result:
VBA for Beginners

Explanation: The code clears content from Excel Range A2 to C14 in Sheet1

Add Borders

Code:

Sheet1.Range("A1:E14").Borders.LineStyle = xlContinuous

Result:
VBA for Beginners

Explanation: The code add border in Excel Range A1 to E14

Working with Worksheets

Get name of ActiveSheet

Code:

MsgBox ActiveSheet.Name

Result:
VBA for Beginners

Explanation: The code reads active sheet name and returns it in message box

Select a Worksheet

Code:

Worksheets("dummy data").Select

Result:
VBA for Beginners

Explanation: The code selects worksheet named ‘dummy data’

Add a Worksheet

Code:

Worksheets.Add

Result:
VBA for Beginners

Explanation: The code adds new worksheet in the workbook. Read more about adding worksheet in the beginning or end of the workbook in VBA Code to Add New Sheet at Beginning or End of Excel File post

Delete a Worksheet

Code:

Worksheets("Sheet5").Delete

Result: The code deletes worksheet named ‘Sheet5’ from the workbook

Working with Workbooks

Get name of ActiveWorkbook

Code:

MsgBox ActiveWorkbook.Name

Result:
VBA for Beginners

Explanation: The code reads active workbook name and returns it in message box

Open a Workbook

Code:

Workbooks.Open "D:\Dummy Data\Dummy Data.xlsx"

Result: The code opens the Excel file saved at “D:\Dummy Data\Dummy Data.xlsx”

Select a Workbook

Code:

Workbooks("Dummy Data.xlsx").Activate

Result: The code activates the Excel file named “Dummy Data”. Note that the file must be already open.

Add new Workbook

Code:

Workbooks.Add

Result: The code adds new Excel file

Close a Workbook

Code:

Workbooks("Dummy Data.xlsx").Close True

Result: The code closes Excel file named “Dummy Data”

Explanation: In the code, SaveChanges parameter is passed as True to ensure that all the change are saved before closing the file. If you do not want to save the changes then pass the parameter as False

Working with Application Object

Close Excel Application

Code:

Application.Quit

Result: The code closes Excel application

Application UserName

Code:

MsgBox Application.UserName

Result:
VBA for Beginners

Explanation: The code reads application user name of logged in user and returns it in message box.

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs

Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business