VBA, or Visual Basic for Applications, is a programming language developed by Microsoft. It is used to automate repetitive tasks, enhance functionality, and create custom solutions within Microsoft Office applications like Excel, Word, and Access. VBA allows users to write scripts or small programs to perform tasks such as data manipulation, complex calculations, and generating reports, making work more efficient and customized to specific needs.
We use VBA to automate our tasks in Excel. The idea of using VBA is to connect the interface of Excel with the programming. One of the very most important connections between them is by changing the cell values. The change in cell value by programming shows the power of VBA. In this article, we will see how to set, get, and change the cell value.
Get, Set, or Change Cell value in Excel VBAHow to Set Cell Value in Excel VBA
Assigning a cell with a value can be achieved by very two famous functions in VBA i.e. Range and Cells function.
Range Function in VBA
The range function helps access the cells in the worksheet. To set the cell value using the range function, we use the .Value.
Syntax:
Range(cell_name).Value = value_to_be_assinged.
1. Set the value to a single-cell
If you want to assign '11' to cell A1, then the following are the steps:
Step 1: Use the Range function, in the double quotes type the cell name. Use .Value object function. For example, Range("A1").Value = 11.
Applying Range FunctionStep 2: Run your macro. The number 11 appears in cell A1.
Running macro2. Set the value to multiple cells at the same time
Remember the days, when your teacher gives you punishment, by making you write the homework 10 times, those were the hard days, but now the effort has exponentially reduced. You can set a value to a range of cells with just one line of code.
If you want to write your name, for example, "Arushi" 10 times, in the range A2 to A11. Use range function. Following are the steps:
Step 1: Use the Range function, in the double quotes, write "Start_of_cell: End_of_cell". Use .Value object function. For example, Range("A2:A11").Value = "Arushi".
Applying Range FunctionStep 2: Run your macro. The text "Arushi" appears from cell A2 to A11 inclusive.
Running MacroCells Function in VBA
The Cells function is similar to the range function and is also used to set the cell value in a worksheet by VBA. The difference lies in the fact that the Cells function can only set a single cell value at a time while the Range function can set multiple values at a time. Cells function use matrix coordinate system to access cell elements. For example, A1 can be written as (1, 1), B1 can be written as (1, 2), etc.
Syntax:
Cells(row_number, column_number).Value = value_to_be_assign
For example, you want to set the cell value to "Arushi cleared CA with Rank ", in cell B1. Also, set cell C1, to '1'. Following are the steps:
Step 1: Open your VBA editor. Use cells function, as we want to access cell B1, the matrix coordinates will be (1, 2). Type, Cells(1, 2).Value = "Arushi cleared CA with Rank" in the VBA code.
Using Cell FunctionStep 2: To access cell C1, the matrix coordinates are (1, 3). Type, Cells(1, 3).Value = 1 in the VBA code.
Accessing Cell C1Step 3: Run your macro. The required text appears in cell B1, and a number appears in C1.
Running Macro1. Setting Cell values by Active cell and the input box
There are other ways by which you can input your value in the cell in a worksheet.
Active Cell
You can set the cell value of a cell that is currently active. An active cell is the selected cell in which data is entered if you start typing. Use ActiveCell.Value object function to set the value either to text or to a number.
Syntax:
ActiveCell.Value = value_to_be_assigned
For example, you want to assign the active cell with a text i.e. "Arushi is practicing CA", also want to change the color of the cell to yellow. Following are the steps:
Step 1: Use the ActiveCell object to access the currently selected cell in the worksheet. Use ActiveCell.Value function object to write the required text.
Step 2: Color the cell by using ActiveCell.Interior.Color function. For example, use vbYellow to set your cell color to yellow.
using ActiveCell.Interior.Color functionStep 3: Run your macro. The currently selected cell i.e. B1 has attained the requirements.
Running MacroInput Box
You can use the input box to set the cell value in a worksheet. The input box takes the custom value and stores the result. This result could further be used to set the value of the cell. For example, set the cell value of A1, dynamically by taking input, from the input box.
Following are the steps
Step 1: Open your VBA editor. A sub-procedure name geeks() is created. Use the Range function to store the value given by the input box.
Step 2: Run your Macro. A dialogue-box name Microsoft Excel appears. Enter the value to be stored. For example, "geeks for geeks". Click Ok.
Microsoft Excel dialogue-boxStep 3: Open your worksheet. In cell A1, you will find the required text is written.
How to Get Cell Value in Excel VBA
After setting the cell value, it's very important to have a handsome knowledge of how to display the cell value. There can be two ways two get the cell value either print the value in the console or create a message box.
Print Cell Value in Console
The console of the VBA editor is the immediate window. The immediate window prints the desired result in the VBA editor itself. The cell value can be stored in a variable and then printed in the immediate window. For example, you are given a cell A1 with the value '11', and you need to print this value in the immediate window.
Following are the steps:
Step 1: Press Ctrl + G to open the immediate window.
Press Ctrl + GStep 2: The cell value in A1 is 1.
value in A1 is 1Step 3: Open your VBA editor. Declare a variable that could store the cell value. For example, Val is the variable that stores the cell value in A1. Use the Range function to access the cell value. After storing the cell value in the val, print the variable in the immediate window with the help of Debug.Print(val) function.
Printing variable in immediate windowStep 4: Run your macro. The cell value in A1 is printed in the immediate window.
Running macroPrint Cell Value in a Message Box
A message box can also be used to show the cell value in VBA. For example, a random string is given in cell A1 of your string i.e. "Arushi studies in Hansraj". Now, if you want to display the cell value in A1, we can use Message Box to achieve this.
Text in cell A1Following are the steps
Step 1: Open your VBA macro. Create a message box by using MsgBox. Use the Range(cell).Value function to access the cell value.
Creating a message box by using MsgBoxStep 2: Run your macro. A message box appears, which contains the cell value of A1.
How to Change Cell Values in Excel VBA
The value, once assigned to the cell value, can be changed. Cell values are like variables whose values can be changed any number of times. Either you can simply reassign the cell value or you can use different comparators to change the cell value according to a condition.
1. By reassigning the Cell Value
You can change the cell value by reassigning it. In the below example, the value of cell A1 is initially set to 1, but later it is reassigned to 2.
Following are the steps
Step 1: Open your VBA code editor. Initially, the value of cell A1 is assigned to 1. This initial value is printed in the immediate window. After that, we changed the value of cell A1 to 2. Now, if we print the A1 value in the immediate window, it comes out to be 2.
Step 2: The immediate window shows the output as 1 and 2.
2. Changing cell value with some condition
We can use if-else or switch-case statements to change the cell value with some condition. For example, if your age is greater than 18 then you can drive else you cannot drive. You can output your message according to this condition.
Following are the steps
Step 1: A code is written in the image below, which tells whether you are eligible for a driving license or not. If your age is greater than 18 then cell A1 will be assigned with the value, "You are eligible for the driving license", else A1 will be assigned with "You are not eligible for driving license".
Text assigned to A1Step 2: Run your macro. An input box appears. Enter your age. For example, 19.
Running macroStep 3: According to the age added the cell value will be assigned.
Cell Value AssignedConclusion
In conclusion, learning how to work with cell values in Excel VBA is key to making your work easier and faster. Whether you're getting data, setting it, or changing it, knowing these basics helps you manage information effectively. With these skills, you can automate tasks, boost efficiency, and make the most of Excel's features.