Active Cell in Excel VBA
The active cell is the currently selected cell in a worksheet. The active cell in VBA can be used as a reference to move to another cell or change the properties of the same active cell or the cell reference provided by the active cell. We can access an active cell in VBA by using the application.property method with the keyword active cell.
Understanding the concept of range object and cell properties in VBA Properties In VBACells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells. is important to work efficiently with VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.. One more concept you need to look into in these concepts is “VBA Active Cell.”
In Excel, there are millions of cells, and you are unsure which one is an active cell. For example, look at the below image.
In the above pic, we have many cells. Finding which one is an active cell is very simple; whichever cell is selected. It is called an “active cell” in VBA.
Look at the name boxName BoxIn Excel, the name box is located on the left side of the window and is used to give a name to a table or a cell. The name is usually the row character followed by the column number, such as cell A1. if your active cell is not visible in your window. It will show you the active cell address. For example, in the above image, the active cell address is B3.
Even when many cells are selected as a range of cells, whatever the first cell is in, the selection becomes the active cell. For example, look at the below image.
Table of contents
#1 – Referencing in Excel VBA
In our earlier articles, we have seen how to reference the cellsHow To Reference The CellsCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1. in VBA. By active cell property, we can refer to the cell.
For example, if we want to select cell A1 and insert the value “Hello,” we can write it in two ways. Below is the way of selecting the cell and inserting the value using the VBA “RANGE” object
Sub ActiveCell_Example1() Range("A1").Select Range("A1").Value = "Hello" End Sub
It will first select the cell A1 “Range(“A1″). Select”
Then, it will insert the value “Hello” in cell A1 Range(“A1”).Value = “Hello”
Now, we will remove the line Range(“A1”). Value = “Hello” and use the active cell property to insert the value.
Sub ActiveCell_Example1() Range("A1").Select ActiveCell.Value = "Hello" End Sub
Similarly, first, it will select the cell A1 “Range(“A1”). Select.“
But here, we have used ActiveCell.Value = “Hello” instead of Range(“A1”).Value = “Hello”
We have used the active cell property because the moment we select cell A1 it becomes an active cell. So, we can use the Excel VBA active cell property to insert the value.
#2 – Active Cell Address, Value, Row, and Column Number
Let’s show the active cell’s address in the message box to understand it better. Now, look at the below image.
In the above image, the active cell is “B3,” and the value is 55. So, let us write code in VBA to get the active cell’s address.
Sub ActiveCell_Example2() MsgBox ActiveCell.Address End Sub
Run this code using the F5 key or manually. Then, it will show the active cell’s address in a message box.
Similarly, the below code will show the value of the active cell.
Sub ActiveCell_Example2() MsgBox ActiveCell.Value End Sub
The below code will show the row number of the active cell.
Sub ActiveCell_Example2() MsgBox ActiveCell.Row End Sub
The below code will show the column number of the active cell.
Sub ActiveCell_Example2() MsgBox ActiveCell.Column End Sub
#3 – Parameters of Active Cell in Excel VBA
The active cell property has parameters as well. After entering the property, the active cell opens parenthesis to see the parameters.
Using this parameter, we can refer to another cell as well.
For example, ActiveCell (1,1) means whichever cell is active. If you want to move down one row to the bottom, you can use ActiveCell (2,1). Here 2 does not mean moving down two rows but rather just one row down. Similarly, if you want to move one column to the right, then this is the code ActiveCell (2,2)
Look at the below image.
In the above image, the active cell is A2. To insert value to the active cell, you write this code.
ActiveCell.Value = “Hiiii” or ActiveCell (1,1).Value = “Hiiii”
Run this code manually or through the F5 key. It will insert the value “Hiiii” into the cell.
If you want to insert the same value to the below cell, you can use this code.
ActiveCell (2,1).Value = “Hiiii”
It will insert the value to the cell below the active cell.
You can use this code if you want to insert the value to one column right then.
ActiveCell (1,2).Value = “Hiiii”
It will insert “Hiiii” to the next column cell of the active cell.
Like this, we can reference the cells in VBA using the active cell property.
We hope you have enjoyed it. Thanks for your time with us.
You can download the VBA Active Cell Excel Template here:- VBA Active Cell Template
This article has been a guide to VBA Active Cell. Here, we learned the concept of an active cell to find the address of a cell. Also, we learned the parameters of the active cell in Excel VBA along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –