Active Cell in Excel VBA
To work efficiently with VBA coding it is important to understand the concept of range object and cells properties in VBA. In these concepts there is one more concept you need to look into that is “VBA Active Cell”.
In excel there are millions of cells and your doubt for sure is which one is Active Cell. For an example look at the below image.
In the above one pic itself, we have many cells, to find which one is an active cell, it is very simple whichever the cell is selected right now it called as “Active Cell” in VBA.
If your active cell not visible in your window then look at the name box it will show you the active cell address, in the above image active cell address is B3.
Even when the 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.
#1 – Referencing in Excel VBA
In our earlier articles, we have seen how to reference the cells in VBA. By Active Cell property we can refer the cell.
For example, if we want to select the 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 by using VBA “RANGE” object
Sub ActiveCell_Example1() Range("A1").Select Range("A1").Value = "Hello" End Sub
This will first select the cell A1 “Range(“A1”).Select”
Then it will insert the value “Hello” in cell A1 Range(“A1”).Value = “Hello”
Now I will remove the line Range(“A1”).Value = “Hello” and use Active Cell property to insert 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 I have used ActiveCell.Value = “Hello” instead of Range(“A1”).Value = “Hello”
The reason why I have used Active Cell property because the moment I select the cell A1 it becomes an active cell. So we can use Excel VBA Active Cell property to insert the value.
#2 – Active Cell Address, Value, Row, and Column Number
To understand it even better let’s show the address of the active cell in the message box. Now, look at the below image.
In the above image, the active cell is “B3” and the value is 55. Let’s write code in VBA to get the address of the active cell.
Sub ActiveCell_Example2() MsgBox ActiveCell.Address End Sub
Run this code using F5 key or manually then, it will show the address of the active cell in a message box.
Similarly below code will show the value of the active cell.
Sub ActiveCell_Example2() MsgBox ActiveCell.Value End Sub
Below code will show the row number of the active cell.
Sub ActiveCell_Example2() MsgBox ActiveCell.Row End Sub
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
Active Cell property has parameters as well. After entering the property ActiveCell open parenthesis to see the parameters.
Using this parameter we can refer to another cell as well.
For example, ActiveCell (1,1) means whichever the cell active. If you want to move down one row to the below then you can use ActiveCell (2,1) here 2 doesn’t mean move down two rows rather just one row down, Similarly, if you want to move one column to the right then this is the code ActiveCell (2,2)
For an example look at the below image.
In the above image active cell is A2, to insert a value to the active cell you write this code.
ActiveCell.Value = “Hiiii” or ActiveCell (1,1).Value = “Hiiii”
Run this code manually or through F5 key, this will insert the value “Hiiii” to the cell.
Now if you want to insert the same value to the below cell you can use this code.
ActiveCell (2,1).Value = “Hiiii”
This will insert the value to the cell below the active cell.
If you want to insert the value to the one column right then you can use this code.
ActiveCell (1,2).Value = “Hiiii”
This will insert “Hiiii” to the next column cell of the active cell.
Like this, we can reference the cells in VBA using Active Cell Property.
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 has been a guide to VBA Active Cell. Here we learn the Concept of Active cell to find the address of a cell and also we learned Parameters of Active Cell in Excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –