# 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.

## Working with Cells

Read value from ActiveCell

Code:

``MsgBox ActiveCell.Value``

Result:

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:

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:

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:

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

Change Cell color

Code:

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

Result:

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:

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:

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:

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

Code:

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

Result:

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

## Working with Worksheets

Get name of ActiveSheet

Code:

``MsgBox ActiveSheet.Name``

Result:

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

Select a Worksheet

Code:

``Worksheets("dummy data").Select``

Result:

Explanation: The code selects worksheet named ‘dummy data’

Code:

``Worksheets.Add``

Result:

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:

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.

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

Code:

``MsgBox Application.UserName``

Result:

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

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