CODEWITHSUNDEEP
excelvba
Cornelis
Cornelis

Reputation: 435

When should one use ".Value"

I'm a beginner with Visual Basic, and mainly use it to edit MS Excel files. When copying/pasting (Cell) content or other values, in some cases .value is added.

When should this be added?

When not?

Is it necessary?

Could it harm my code if I use it in places where it isn't needed?

Upvotes: 3

Views: 2184

Answers (1)

John Coleman
John Coleman

Reputation: 51998

In Excel VBA a Range object is a fairly rich thing which corresponds to either a cell or a range of cells. As such it has all sorts of properties (it is in a given row, accessible via the .Row property, it has interior color, possible borderlines, formulas, etc. -- all accessible via the right properties after the dot.) Value is one of these properties. It refers to the value in the cell -- typically a number or a text. It wouldn't be used when using Copy and Paste since those methods are used on whole Range object -- as can been seen by the fact that they are able to copy formatting and not just raw values.

You can assign the value in one cell to another. Even though this looks like copy/pasting it really is quite different and is in some sense a low-tech solution when all you want to do is transfer the values. It can be done using either e.g. Range("A1").Value = Range("B1").Value or Range("A1") = Range("B1"). The reason the later works is that Value is the default property of a Range object -- hence .Value is implicit in any context in which you aren't treating the Range as an actual object.

Personally, I always explicitly use Value when I want to either read or set the value in a cell (or range of cells) even though I could rely on the fact that Value is the default property. Most Excel VBA code makes heavy use of both Range objects and the values in the Range objects. For reasons of readability it is a good idea for your code to be explicit about when it is using the range vs. when it is using the value. If you follow the excel-vba tag on SO you will see that it is relatively rare for programmers to rely on Range's default property.

Upvotes: 5

Related Questions