3

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?

Cornelis
  • 445
  • 3
  • 11
  • 27
  • Possible duplicate of [Recordset .value property](http://stackoverflow.com/questions/19200428/recordset-value-property) – GSerg Nov 25 '15 at 13:39
  • You have Text, Value and Value2, text is whats in the cell displayed (Formatted for ex), Value and Value2 are the underlying values, eg. difference between .135 and .134590953 – Nathan_Sav Nov 25 '15 at 13:40
  • see this http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 or this https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/ – Vasily Nov 25 '15 at 13:41
  • For example I'm having one drop down list in cell C5 from "Sheet1", (this can be used to select names of operators from the sheet). When the macro is started (after selceting a name) I'd like to copy the name that was selected in the dropdownlist to, lets say, "Sheet2". Currently it turns out to paste "Oke" in the cell "B12" in "Sheet2" (but you now can click on cell B12 and select one of the names. I'd like the code to (when I've for example selected my own name "Cornelis) to paste Cornelis in B12, preferably without the option using B12 as dropdownlist as well Does this has the same cause? – Cornelis Nov 25 '15 at 14:22

1 Answers1

5

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.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • 1
    Good answer, but I would urge you to expand this a bit with a clear statement of the difference between value and formatting. – Nigel Heffernan Nov 25 '15 at 14:11
  • @Nile The OP is asking about the difference between `.Value` and ``, not between `.Value` and `.Text`. This is a very different kind of difference, and the commenters ([1](http://stackoverflow.com/questions/33917694/when-should-one-use-value#comment55597005_33917694), [2](http://stackoverflow.com/questions/33917694/when-should-one-use-value#comment55597051_33917694)) are adding to the confusion. – GSerg Nov 25 '15 at 14:20
  • First of all I realy appreciate the anwser from John Coleman. (only need to re-read it a second time, due I'm not a native English speaker.) But it allready clearify's a lot. Difference between .text and .value2 indeed wern't part of my question. Allthough... It's kind of corrolated with my question... probably could learn some of it as well. I'll keep reading some more on stackoverflow! Thanks to all of you so far! – Cornelis Nov 25 '15 at 14:30
  • @Nile Good idea. I tried to explain a bit more the difference between a range and simply its value. – John Coleman Nov 25 '15 at 14:39
  • "... Value is the default property of a Range object" may be overstating things a scooch subject to the operation. I never liked that definition since `Set rng = Range("A1")` obviously sets rng to the [Range object](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx), not the [Range.Value property](https://msdn.microsoft.com/en-us/library/office/ff195193.aspx). –  Nov 25 '15 at 22:42
  • [This recent post](http://stackoverflow.com/questions/33927736/vba-sort-key-range-query) may be of interest as it demonstrates both ways that either the .Value or Range Object is interpreted on-the-fly depending upon the ways it is requested. It fails when the Value is returned by the [Range.Cells property](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx) but not when the Range Object is returned. –  Nov 25 '15 at 23:14