Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Many people around the world enjoy and benefit from our tutorials. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Module 2 - Moving Around in Excel

Lesson 2.1 - Basic Cell Selection

Topic 2.1.1 - Selecting Single Cells

You can't do much in Excel VBA without knowing how to refer to and select cells! In this part of the lesson, you'll learn a few of the basic techniques for selecting single cells.

Files Needed

You can click here to download the file used for this page.

Completed Code

You can click here to download a file containing the sample code.

To get started, extract and open the file linked to in the Files Needed section above. You should see a basic table of information about films:

This is the basic data we'll work with.

Now open the VBE and insert a new module into the project.

Selecting a Cell by Reference

You can select a cell by its reference using the Range property. To demonstrate this, create a new subroutine and begin writing an instruction as shown below:

The tooltip indicates that you need to enter a reference to at least one cell.

In this example, we'll refer to cell which contains the name of the best film in the list:

Enter the cell reference within a set of double-quotes. Feel free to disagree with the choice of best film.

We can now apply a method to select the cell we've referenced:

Choose the Select method from the IntelliSense list or simply type it in.

Once a cell has been selected we can refer to it with the ActiveCell property. We can use this to highlight the selected cell in a different colour:

You can change the cell colour as shown. Type rgb and then press Ctrl + Space to show the list of colours.

You can run the subroutine to check that the correct cell is selected and coloured:

The end result should resemble the example shown here. Feel free to choose a different cell and fill colour!

You can also refer to a cell by writing its reference in a set of square brackets. This is an old-fashioned technique but is quicker to write. The example below would select cell B6:

[B6].Select

Select a Cell by Row and Column Number

If you prefer to, you can select a cell using its row and column number by using the Cells property. Create a new subroutine and begin writing an instruction as shown below:

The tooltip indicates that you can enter a row and column number to reference a cell.

Next, enter the number of the row followed by the number of the column. In the example below we're referring to cell B3:

Enter the two numbers, separated by a comma.

You can then apply the Select method to the cell:

You won't see the IntelliSense list appear when referring to a single cell with the Cells property.

Once the cell has been selected, you can use the ActiveCell property to refer to it and change some of its properties:

Here we're changing the fill colour of the cell.

When you run the subroutine you'll see the specified cell's fill colour has changed:

This time we've highlighted the worst film in the list.

Using Cell References

To practise selecting single cells using cell references:

Using the same module, create a new subroutine as shown below:

Sub Copy_Best_Film()

End Sub

Add two instructions to select and then write a label into cell A14 (use the Value property to change the text in a cell):

Sub Copy_Best_Film()

Range("A14").Select

ActiveCell.Value = "Best"

End Sub

Add two instructions to select and then copy the cell containing the name of your favourite film in the list:

Sub Copy_Best_Film()

Range("A14").Select

ActiveCell.Value = "Best"

Range("B6").Select

ActiveCell.Copy

End Sub

Add two more instructions to select and paste the copied cell into cell B14:

Sub Copy_Best_Film()

Range("A14").Select

ActiveCell.Value = "Best"

Range("B6").Select

ActiveCell.Copy

Range("B14").Select

ActiveCell.PasteSpecial

End Sub

Run the subroutine and check the results:

The selected cell, along with all of its formatting, will be copied to B14.

Using Row and Column Numbers

To practise selecting cells using row and column numbers:

Using the same module, create a new subroutine as shown below:

Sub Copy_Worst_Film()

End Sub

Add instructions to select and write a label into cell A15 using row and column numbers:

Sub Copy_Worst_Film()

Cells(15, 1).Select

ActiveCell.Value = "Worst"

End Sub

Add two instructions to select and copy the cell containing the name of your least favourite film:

Sub Copy_Worst_Film()

Cells(15, 1).Select

ActiveCell.Value = "Worst"

Cells(3, 2).Select

ActiveCell.Copy

End Sub

Add two instructions to select and paste the copied cell into cell B15:

Sub Copy_Worst_Film()

Cells(15, 1).Select

ActiveCell.Value = "Worst"

Cells(3, 2).Select

ActiveCell.Copy

Cells(15, 2).Select

ActiveCell.PasteSpecial

End Sub

Run the subroutine and check the results:

The selected cell will be copied, along with its formatting, into the specified cell.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Selecting a Cell by Reference

Select a Cell by Row and Column Number

Using Cell References

Using Row and Column Numbers

Head office

London

Manchester