VBA Excel Populate Cells .Text or .Value

[dprayner]

Hi good people. I sometimes get confused about whether to use Range(" ").Text or Range(" ").Value when populating cells from a VB form via VBa code. What is the general rule of thumb? Thanks DAVE

 

[xlbo]

depends on data type

if you want a string, use .TEXT. If you want a value, use .VALUE.

There is a more subtle difference however in that .TEXT will return what you SEE in the cell. .VALUE will return the actual value so

10.2543, formatted to 2dp in the cell to look like 10.25

.TEXT will give you 10.25
.VALUE will give you 10.2543

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Errmm ...

.Text of a Range object is read-only.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

thought it was about reading cells into code not vice versa....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Yeah, I guessed that from the tone of your whole post which is all good information for Dave about the differences between the two properties.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Hello again. I am working with text, date, and number values from a VB (Editor) form. When a user clicks a command button it populates pre-formatted cells on an Excel Spreadsheet. Sometime a user will recieve see a cell and after the contents they will see a couple of squares. I am trying to eliminate this problem. I thought it may have something to do with spaces tacked on after the data.

 

[PHV]

the contents they will see a couple of squares
Probably some Cr and/or Lf character(s), I guess.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

it won't be spaces it will be "Non printable characters"

you can use thh CLEAN function to strip these off

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question