Convert values to strings

C

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.
 
J

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.
 
J

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.
 
Top