Get and Set Cell Value in Excel VBA

In this post, you’ll learn how to use Excel VBA to get or set cell value in your Excel spreadsheet programmatically with some code samples and examples.

Get and Set Cell Value in Excel VBA

To get and set cell value in excel VBA, we can use the Value property of the Range or Cells object.

Set Cell Value

Using the Value property of the Range or Cells object, we can set cell value.

Set Cell Value using Range.Value

To set a cell value using Range.Value,

Code:

Range("A2").Value = 1

Set Cell Value using Cells.Value

To set cell value using Cells.Value command,

Code:

Cells(3,1).Value = 1

Note: The order for entering the value is Row number, Column number. i.e.,

 Cells(Row_num, Col_num)

How to Set Multiple Cell Value ?

To set multiple cell value,

Instead of referencing a single cell, you can reference a range of cells and change all of the cell values at once:

Code:

Range("A2:A5").Value = 1

How to Get Cell Value?

To get cell values in Excel VBA you can use the Value property.

Get ActiveCell Value

To get the ActiveCell value in a message box,

Code:

MsgBox ActiveCell.Value

Assign Cell Value to Variable

To assign  a cell value  to a variable:

Code:

Dim var as Variant

var = Range("A1").Value

A String variable type will store number as text.

Dim var as Double
var = Range("A1").Value

A double variable type stores numbers as number and if the cell value is a text, it will throw an error.