Interact with an Excel Spreadsheet Using VBA

VBA can retrieve data from cells in an Excel spreadsheet,store data in them, and rearrange data as well. Recall the Fundamental Spreadsheet Structure in which columns in the grid are designated by letters and rows are designated by numbers. When addressing cells using VBA, you may also need to know the column number depending on which of two methods you use.

Basic Cell Address

R1C1 Notation

Cells(N,M) - where N is the row number and M is the column number

' Cells(8,5) refers to the cell value in row 8, column 5 (E)

Sub addressCells()
Dim i, j As Long
Dim X As Currency

X = Cells(8, 5)

MsgBox X

End Sub

Select a cell in a spreadsheet

A1 Notation

Range("XN") - where X is the column letter and N is the row number, double quotes around the Range arguments are required

' Range("E8") refers to the cell value in row 8, column 5 (E)

Sub addressCells()
Dim i, j As Long
Dim X As Currency

X = Range("E8")

MsgBox X

End Sub

Address a Range of Cells

Use Range to refer to more than one cell in a row or column. Specify the first cell in the range followed by a ":" then the last cell in the range.

' Range("A5:E5") refers to all cells in the first 5 columns of the 5th row

Sub addressRange()

' Select cells in Range("A5:E5")

Range("A5:E5").Select

End Sub

Address a range of cells in VBA

Address a Block of Cells

Range can also refer to a block of cells using the R1C1 notation. Specify the first cell in the block followed by a ":" then the last cell in the block. Or Range can be used with the A1 notation for cell references separated by a comma.

' Range("A5:E10") refers to cells in the first 5 columns of rows 5-10

Sub addressBlock()

Range("A5:E10").Select

End Sub

' Range(Cells(5, 1), Cells(10, 5)) refers to all cells in the first 5 columns of rows 5-10

Sub addressBlock()

Range(Cells(5, 1), Cells(10, 5)).Select

End Sub

Address a block of cells using VBA

Write to Cells in an Excel Spreadsheet

In the above examples, we were selecting cells (*.select) or reading from cells (variable = cell_value). Writing from VBA to an Excel spreadsheet involves using the "reading" method in reverse.

' Write to the cell in row 8, column 5 (E) using a value from a subroutine

Sub writetoCells()
Dim X As Currency

X = 17782.55

Cells(8, 5) = X

End Sub

Sub writetoCells()
Dim X As Currency

X = 17782.55

Range("E8") = X

End Sub

Write to Ranges of Cells in an Excel Spreadsheet

Set the range of cells to a value that is specified in a routine. Each of the range cells will be populated with this value.

' Write to each cell in row 8, columns 1- 5 (A-E) using a value from a subroutine

Sub writetoRanges()
Dim X As Currency

X = 17782.55

Range("A5:E5") = X

End Sub

Write to ranges of Excel spreadsheet cells using VBA

Notice we defined X as a currency variable. When we wrote that value to each specified cell in the spreadsheet, VBA automatically formatted those cells as Excel currency values. If we had defined X as a date variable, those numbers would have been converted to date values. Here, 9/6/1948. Try this with other variable types!

Write to Non-contiguous Cells in an Excel Spreadsheet

Range can also be used to write to non-contiguous cells, as shown below.

Sub writetoNonContCells()
Dim X As Integer

X = 7

' Write 7 each cell in row 5, from column A to E

Range("A5:E5") = X

' Write 14 in column A, row 1, 2, and 4 (Cells(1,1), Cells(2,1) and Cells(4,1))

Range("A1,A2,A4") = 2 * X

' Write 21 in column B, rows 1-3, and the block in columns C to D, rows 2-3

Range("B1:B3, C2:D3") = 3 * X

End Sub

Using VBA to write to non-contiguous cells

Copy Cells and Write to a Different Spreadsheet Location

The ability to copy and paste in a spreadsheet is easy with VBA.

Sub copyCellstoAnotherLocation()

' Select a range of cells to copy

Range("C3:D3").Select

Selection.Copy

' Select a beginning cell to paste the copied values to

Range("G2").Select

ActiveSheet.Paste

' Select a cell to clear the clipboard

Range("G3").Select

End Sub

Although we only select one cell for the pasting process, VBA will fill out the cells required to paste all.

Copy and paste contents of Excel spreadsheet cells

If we had only selected one cell to copy and several cells for the pasting process, VBA will fill out the cells with the copied value.

Sub copyCellstoAnotherLocation()

' Select a cell to copy

Range("C3").Select

Selection.Copy

' Select a range of cells to paste the copied value to

Range("G2:I2").Select

ActiveSheet.Paste

' Select a cell to clear the clipboard

Range("G3").Select

End Sub

Select a range of cells to copy and paste into a range of cells in a new location

If we select a range of cells to copy and several cells for the pasting process, VBA will fill out the cells with the copied values to occupy the selected cells up to an even multiple of the copied cells.

Sub copyCellstoAnotherLocation()

' Select a range of cells to copy

Range("C3:D3").Select

Selection.Copy

' Select a range of cells to paste the copied value to

Range("G2:K2").Select

ActiveSheet.Paste

' Select a cell to clear the clipboard

Range("G3").Select

End Sub

In this example, values from C3:D3 will only be copied to G2:J2. Try it!