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

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
-----Original Message-----
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

.

RBS


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

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?


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com