How to write a long text value to an Excel Cell in a VBA subroutine

Dear Experts,
I have a feeling this is pretty simple.

I have a VBA subroutine that assigns all sorts of small numbers adequately.

However I have some long code numbers that defeat the process.  For example I have a string as follows:

1000170010073528

If I use the following assignment

ActiveCell.Value = array(i)

it results in this:

100017001007352

My array(i) is a string array element.  In the debugger it looks correct but when I view the spreadsheet the final character is screwed up.
Best Wishes,
Phil
LVL 1
PTRUSCOTTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try


ActiveCell.Value2 = array(i)

and


ActiveCell.Value = "'" & array(i)
0
Rob HensonFinance AnalystCommented:
Excel will only handle up to 15 significant figures, your string is 16 in length.

AngelIII 2nd suggestion will convert the string to text so will not be usable in subsequent calculations. Even if subsequent calc's convert back to value the 15 significant figure rule will apply.

Thanks
Rob H
0
NorieAnalyst Assistant Commented:
Format the destination cell(s) as Text before putting the string value in.
 
With ActiveCell 
      .NumberFormat = "@"
      .Value = array(i)
End With

Open in new window



By the way, you should probably avoid using array as a name as it could be mixed up with VBA's Array.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.