3 ways to manipulate cell value in Excel (VBA)

3 ways to change the value of a cell (or cells).

Lets say the value on cell B5 currently is 80 and we want to change the value to 100, here are the 3 methods we can use achieve this.

Range()

Range() is probably the most commonly used method and would be the first one to learn if you already learn how to code in Excel VBA. For the above task, it is written as follows

    Range("B5").Value = 100

To use this method you have to provide the address in text form i.e. in double quotation marks " ". You can also use a variable to represent the 5, such as x = 5, in which the code will be written as

    Range("B" & x).Value = 100

This can be useful if you want to loop through a few cells and change the values sequentially. However, "B" will be a fixed reference and so there is no way to make changes across to other columns with this method. This is where Cell() comes in.

Cells()

This could be the second most commonly used method after Range(). The syntax is Cells(row number, column number), so cell B5 would have the coordinate (5, 2), then we write

    Cell(5, 2).Value = 100

Using this method we can represent both column number and row number with variables giving us a 2-dimensional way to manipulate cells' values. So let's say we have x = 5 and y = 2, then we can write

    Cells(x, y).Value = 100

Cells() is difficult to use because it doesn't support IntelliSense. Nonetheless its still a powerful choice.

[Address]

This is the least popular one I suppose, but it is arguably the most elegant one.

    [B5].Value = 100

See how elegant, short and clear the code is. In fact in can be reduced to just [B5] = 100. However using this method we cannot use variable to present the row number or column number. So this method is only used when the context is fixed to a cell. Besides, this method also doesn't support IntelliSense. But it makes your code neat and nice, easy to read and therefore easy to debug.

In Summary, the 3 methods discussed above are essentials and basic to know for Excel Macro programming. Each has its own advantages and disadvantages so using them must be suit according to scenario. But it is ok to simply pick one to use first as you can always switch to other method when you finally realise which is a better one to use.