Putting strings in the 'next' position in an array without indexing manually + fix video

[niels007]

Hello, I hope I described it well!

I'm writing a lot of text files to the harddrive with my spreadsheet.
Currently I do so by using VBA to fill cells in a sheet in subsequent rows.
Then another bit of VBA writes this as a file somewhere on the harddrive.

For neatness and possibly speed, I would prefer to bypass writing the rows to a sheet, and instead write directly from a VBA array to a text file.
That means I have to find a neat way to store each next string into the 'next' position in the array.
I may often insert lines halfway through a 500 line 'text file' so that makes manual indexing a pain, if I insert a line at line 4, I have to increment
496 entries +1.

I have attached an example of how I do it now, how I can do it manually indexing the array, and then with what I want... But can't figure out..

Anyone have a neat solution for my (luxury) problem?

[hrlngrv]

If the big problem is adding rows which should be inserted between existing rows, you could always use a linked list for printing in the desired order.

If you'd never need more than 1,000 lines, you'd create an array with 1,000 rows and however many columns you need as well as creating another array with 1,001 entries (indices 0 to 1000). Initialize that 2nd array as

Please Login or Register  to view this content.

This is an array of following line indices. When you add, say, row 567 which you want between rows 331 and 332, you'd need

array2(331) = 567
array2(567) = 332

which could be handled by

Please Login or Register  to view this content.

Fill out the 1st array however you want, just handle inserting lines with the lnklstinsert procedure above. Then print using the 2nd array. You should know how many lines you want to print, call that numlines. Since the 2nd array's 1st index is 0, that'd point to the index of the 1st line, meaning you could insert rows which should appear at the top of the text file. Handle printing using

Please Login or Register  to view this content.

[dangelor]

Not sure if this is what you're looking for, but this has one line of code that will create an array as large as there are lines on the worksheet. Insert a new line anywhere in the first column and rerun the sub.

Please Login or Register  to view this content.

[Joske920]

hi,

i would use a sheet to write and edit
then read the sheet to array and write it to file

why you would hard code the tekst into your vba code is a mistery to me

here is what i would do

Please Login or Register  to view this content.

[niels007]

Hello, thanks for the suggestions. As is so often the case, the fix turned out to be simpler than I imagined!

I didn't know that you can use a : to do multiple operations on one line of VBA code. So now I get the effect by doing this:

Please Login or Register  to view this content.

Obviously there isn't just text in my real project, it is a mix of text with VBA calculated numbers.
I am most curious about an eventual speedup achieved by doing this versus my old method. When I'm a bit further along I'll update this post with some results on that front.

Thanks again for your time,
Niels

[niels007]

Here is a video where you can see how this 'straight from array to file' is 4x quicker than the 'array to sheet to file' I used before.

Always nice to get big speed improvements, even though it doesn't matter for actual time savings, it makes one feel clever (not necesarily rightfully so :P)

https://youtu.be/bDvCyCHCzaE

[jindon]

Use of ArrayList

Please Login or Register  to view this content.