ActiveCell in Excel VBA – When to Use and When It’s Downright Dangerous
In this example we will see how to use the ActiveCell property of VBA. The ActiveCell property returns a Range object of the active cell in the active workbook. You can then apply any of the properties or methods of a Range object to it.
Though you may find a very practical scenarios to use ActiveCell, it is best to avoid using it as much as possible. The reason being, if you accidentally change the active cell during the execution of the macro or you happen to open / activate another workbook, it will cause run-time errors in your code.
So, in this article, I will also suggest few ways to avoid the use of ActiveCell and also how to minimize some errors that can result from using ActiveCell.
Before looking at a practical example, let us have a look at a few examples on how to use the ActiveCell property and the various methods / properties associated with it.
Example 1: Getting the value of the active cell
This example shows how to display the value of an active cell in a message box
Dim selectedCell As Range Set selectedCell = Application.activecell MsgBox selectedCell.Value
However, please note that if another workbook is active, the ActiveCell from that workbook will be considered, irrespective of workbook in which the code is written. So, always make sure to activate the current workbook before using ActiveCell.
So, a better way of doing it will beDim selectedCell As Range ThisWorkbook.Activate Set selectedCell = Application.activecell MsgBox selectedCell.Value
Example 2: Getting the address / row / column of the active cell
Use the following properties to display the address / row number and column number of the active cellDim selectedCell As Range ThisWorkbook.Activate Set selectedCell = Application.activecell MsgBox selectedCell.Address MsgBox selectedCell.Row MsgBox selectedCell.Column
The address will be displayed as below:
And for row and column, the respective numbers will be displayed.
Example 3: Activating / Selecting a cell
You can make a cell active either by using the activate method or by using the select method on the range you want to activateThisWorkbook.Activate Worksheets("Sheet1").Range("B4").Activate
which is equivalent toThisWorkbook.Activate Worksheets("Sheet1").Range("B4").Select
And now when you run MsgBox selectedCell.Address you will get the output as “$B$4”
Example 4: Select last row in the active column using ActiveCell
Consider the below snapshot. The ActiveCell is in the first column.
If you want to select the last cell that has data in this active column, you can use the code snippet below.ThisWorkbook.Activate Application.activecell.End(XlDirection.xlDown).Select
After the execution of the code, the last cell will be selected like this
Example 5: Selecting the cells that contain data around the ActiveCell using CurrentRegion
The CurrentRegion property returns a range of cells bounded by blank rows and columns. In the following example, the selection is expanded to include the cells adjoining the active cell that contain data and the background color of the current region is modified. The entire range is copied to another sheet at the same location.Sub CurrRegion() Dim curReg As Range ThisWorkbook.Activate 'Get the current region and assign it to a Range variable Set curReg = ActiveCell.CurrentRegion ActiveCell.CurrentRegion.Select 'Format it Selection.Interior.Color = vbCyan 'Select the first cell in the range Application.ActiveCell.End(XlDirection.xlUp).Select Application.ActiveCell.End(XlDirection.xlToLeft).Select 'Paste the current region in another sheet at the same location curReg.Copy Worksheets("Sheet2").Range(Application.ActiveCell.Address) End Sub
The current region can be useful when the range of data is not fixed and you need to perform certain operations on it, like format it, copy it, insert charts or send an email using that range.
Example 6: Using offset with ActiveCell
Now let us look at a more practical example of using ActiveCell. Say, in your Excel sheet you have multiple lines of data as shown below and you need to process data only for a single selected row.
The desired output is that in the column “Gaining”, Y or N should be inputted based on the price level and the background color should be set to Green or Red respectively.
Here, we will assume that the stock name is selected before running the macro. To get the values of the Previous Close and Current Price we will use the Offset MethodSub offset() Dim preClose As Double, currPrice As Double ThisWorkbook.Activate 'Get value from the second column i.e. offset of zero rows and one column preClose = ActiveCell.offset(0, 1).Value 'Get value from the third column i.e. offset of zero rows and two columns currPrice = ActiveCell.offset(0, 2).Value If currPrice &lt; preClose Then 'Set value and format of the forth column i.e. offset of zero rows and three columns ActiveCell.offset(0, 3).Value = "N" ActiveCell.offset(0, 3).Interior.Color = vbRed Else ActiveCell.offset(0, 3).Value = "Y" ActiveCell.offset(0, 3).Interior.Color = vbGreen End If End Sub
For further details on the Offset Method, please refer to the article “How to use VBA Range.Offset Method”
You can easily avoid the use of ActiveCell here, by using the input box to get the stock name / row number from the user. After running the code on the last row the output will look like this.
See also: “VBA, Excel Automation From Other Applications“