converting vba array that returns a long number in excel to a string ie array (67, 1)

Question

I am fledgling VBA macro user.

I have a macro that converts a txt file into specific length fields, and it all works fine.

However, one of those fields is always a 10 digit number.
When the macro runs, this number is converted into Excel, where the last few numbers are returned as zeros as Excel doesn't like long numbers (how infuriating).

What is the best way to convert an array as below into text/string before it gets into excel?

Array(53, 1), Array (67, 1), Array(77, 1)
TrailingMinusNumbers:=True

In Excel, this would be something like =text(Array(67, 1), "general").

I've tried CStr(Array(67, 1)) & String((10),Array(67, 1)) & (Array(67, 1), As String).

Welcome to SO. where the last few numbers are returned as zeros as excel doesn't like long numbers What value is it? Excel can work with several decimals, 15. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jul 28, 2020 at 12:21

Gustav · Accepted Answer · 2020-07-28 13:10:24Z

0

Try with:

Format(Array(67, 1), String(10, "0"))

or, if decimals:

Format(Array(67, 1), "0." & String(10, "0"))

answered Jul 28, 2020 at 13:10

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JQM Over a year ago

HI Thanks for the suggestion. now I get the below error

Gustav Over a year ago

Can't tell what it is.

Collectives™ on Stack Overflow

converting vba array that returns a long number in excel to a string ie array (67, 1)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related