Set a cell value using VBA in Microsoft Excel

By SpreadCheaters
Page last updated: 30/06/2023 | Next review date: 30/06/2025
close

Author bio


SpreadCheaters
View More north_east

VBA is a programming language embedded in Microsoft Excel that enables automation and data manipulation. When setting a cell value using VBA, involves programmatically assigning a value to a specific cell in Excel through VBA code.

In this tutorial, we will learn to set a cell value utilizing VBA in Microsoft Excel. There are multiple methods to set a cell value utilizing VBA. Mainly “Range(“A1”).Value” and “Cells(1,1).Value” are the two methods adopted to set a cell value. 

Method 1: Utilizing the “Range.Value” Function

Step 1 – Press ALT+F11 Keys

  • Press the ALT+F11 keys to open the Visual Basic editor.

Step 2 – Insert a New Module

  • Insert a new module, by performing a right-click on the sheet name.
  • Click on the “Insert” option and then choose “Module”.

Step 3 – Input the VBA Code

  • Input the VBA code.
Sub SetCellValue()
  Worksheets(“Sheet1”).Range(“B2:B10”).Value = “ABC”
End Sub
  • Where “A1” is the range of cells in which the value will be inserted.
  • “Hello World!” is the value.

Step 4 – Run the Code

  • Run the VBA code.

Method 2: Utilizing the Cells.Value Function

Step 1 – Press ALT+F11 Keys

  • Press the ALT+F11 keys to open the Visual Basic editor.

Step 2 – Insert a New Module

  • Insert a new module, by performing a right-click on the sheet name.
  • Click on the “Insert” option and then choose “Module”.

Step 3 – Input the VBA Code

  • Input the VBA code.
Sub SetCellValue()
  Worksheets(“Sheet1”).Cells(1,1).Value = “ABC School”
End Sub
  • The Cells(1,1) represent the cell in which the value would be inserted. The first digit i.e. “1” is the row number and the second digit i.e. “1” is the column number.
  • “Hello World!” is the value.

Step 4 – Run the Code

  • Run the VBA code.