How to Set the Cell Value in VBA

By L.P. Klages

Visual Basic for Applications, or VBA for short, is the scripting language used in Microsoft Office to automate tasks in applications such as Microsoft Excel. Since Excel is ultimately used for displaying related data in spreadsheet cells, one of the core features of VBA in Excel is to assign values to those cells after calculations. Not only can you assign values to single cells, but also to ranges, using VBA.

Step 1

Copy and paste the following code into your VBA module:Sub SetValue() Worksheets("Sheet1").Activate ActiveCell.Value = 35End Sub

Step 2

Replace the "Sheet1" text with the cell to which you want to assign a value. For example, if you want to assign a value to the cell in column B, row 24, then you'd change the code from "Sheet1" to "B24."

Step 3

Replace the 35 with the value you want to assign to your cell. For example you could make it a number, such as 25, or a string of text, such as "Hello."

Step 4

Call the SetValue subroutine from your module by typing its name:SetValue()

Step 5

Press "F5" to run the code and see the cell you typed in the code change to the value you specified.

Tips & Warnings

  • The name "SetValue" for the function can be changed to one of your choosing, such as "MyFunction" or "AuntMelinda."