trouble displaying as text

R

RB Smissaert

As part of a VBA Sub I need to assign an array to the sheet.
The array is declared like this:
Public testArray()

Sometimes this array holds the text:
7E043

Now the trouble is to avoid this being displayed in the sheet as 7.00E+43

Even when I apply the text format to the sheet (by doing range.NumberFormat
= "@") before
assigning the array it still happens.

One solution is to put single quotes before the text, but that has some
drawbacks.
The other solution is to declare the array as string, but that has even more
drawbacks.

What would be the solution to this?
Thanks for any advice.


RBS
 
G

Gjones

Hi Bart;

On possible work around would be to hold the value in a
hidden worksheet that will force the text data type. Then
when you want to retrieve the value just get it from the
sheet instead of the array. Also, if there is just one
value you would not need an array and could dimension the
value as a string for a variable.

Thanks,

Greg
 
D

Dick Kusleika

RBS

Even when I apply the text format to the sheet (by doing range.NumberFormat
= "@") before
assigning the array it still happens.
Click to expand...
This works for me

Sub test()

Dim sString As Variant

sString = "7E043"

Range("a1").NumberFormat = "@"
Range("a1").Value = sString

End Sub

Are you putting that value in the range some other way?
 
Top