Convert values to strings

[Chris]

I noramlly use the Text function to convert values. How do
I do it in VBA?

I tried ActiveCell.Value =
Application.WorksheetFunction.Text(ActiveCell.Value, "0")
but it didn't work.

Thanks in advance for any help.

 

[Jim Rech]

You'd use any technique that would work for you manually. For example, make
the cell's number format to be Text before making the entry, or by preceding
the entry with an apostrophe.

With ActiveCell
.NumberFormat = "@"
.Value = 123
End With

or

ActiveCell.Value = "'123"

--
Jim Rech
Excel MVP
|I noramlly use the Text function to convert values. How do
| I do it in VBA?
|
| I tried ActiveCell.Value =
| Application.WorksheetFunction.Text(ActiveCell.Value, "0")
| but it didn't work.
|
| Thanks in advance for any help.

 

[JE McGimpsey]

One way:

With ActiveCell
.NumberFormat = "@"
.Value = Application.Round(.Value, 0)
End With

Since XL's parser interprets what VBA hands it as if the value were
typed, it doesn't do any good to use VBA's Format() method - the text
value will simply be interpreted as a number. Hence setting the number
format to Text first, so the value won't be parsed.