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

Posted on 2013-08-28
Medium Priority
Last Modified: 2013-08-28
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:


If I use the following assignment

ActiveCell.Value = array(i)

it results in this:


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,
Question by:PTRUSCOTT
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39444876
please try

ActiveCell.Value2 = array(i)


ActiveCell.Value = "'" & array(i)
LVL 35

Expert Comment

by:Rob Henson
ID: 39444987
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.

Rob H
LVL 37

Accepted Solution

Norie earned 2000 total points
ID: 39445246
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.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question