Copy a 1-dimension array to part of a 2-dimension array? 1

[VBAjedi]

THE QUESTION:
Given a 1-dimensional array like:

RowIdentifiers(1 To 10) As String

And multiple two-dimension arrays, each in the form:

ArrayA(1 To 50000, 1 To 100) As String

Is there a short (1-line?) way to copy the contents of RowIdentifiers into ArrayA(1, 1-10). Something like:

ArrayA(1, 1:10) = RowIdentifiers

BACKGROUND SITUATION: I have a tool that, for reasons I've been unable to determine yet, suddenly started taking ridiculous amounts of time to write values to cells on a worksheet (yes, during code execution screen updating is off, calculation is set to manual, etc).

Anyway, I'm exploring writing all the values to two-dimension arrays and then assigning them to the appropriate ranges on the worksheet in one swoop (MUCH faster). And I need something like this to simplify writing some identical data to all of the two-dimension arrays.

VBAjedi

 

[mintjulep]

I don't think so, but it should be too difficult to write a procedure. Something like.

Public OnetoTwo (row as integer, StartColumn as Integer, EndColumn as integer, DestinationArray as something, SourceArray as something)

Count = 1
For i = StartColumn to EndColumn 
DestinationArray(row,i) = SourceArray(Count)
Count = Count +1
Next i
End

 

[N1GHTEYES]

I can think of three different possibilities:

1 You could perhaps arrange it so that your 2d arrays are of variants, then you could simply copy the whole of each of your sub-arrays into successive elements of the variant array.

2 Skip the 2d array step - go straight from your 1d arrays to wherever they would eventually have ended up on the sheet. You can certainly assign them directly to ranges exactly as you suggest. Normally, if calculation is set to manual and screenupdating is off, this process should really not be slow.

3 I think you could actually do exactly what you requested if the smaller vectors are going to be copied into the secondary dimension of the 2d array by using the Copymemory API function. However, if you get it wrong you can screw it up royally, so I'd be a bit wary of that option.

Tony

 

[VBAjedi]

Mintjulep: that idea has promise... for some reason I hadn't thought of being able to pass both arrays as arguments to a function. My only concern would be with potential performance hits... I've got ten arrays, each 20000 long in the dimension I'd be iterating through. So this function would get called 200,000 times (10 x 20000) each code run.

N1GHTEYES: Option 2 is what makes the most sense to me, but it hinges on "this process should not be really slow". And that's the problem that started all of this. I've run my original code (which wrote the data I'm creating to the sheet one row at a time) hundreds of times and it was pretty fast (about 5 minutes to create 200,000 rows of data spread across ten sheets in a workbook). Then last week for reasons I haven't been able to identify it starts taking about a second per row... a 200,000 second run time is NOT gonna cut it. Needless to say, fractions of a second MATTER on this one...

VBAjedi

 

[VBAjedi]

I guess I should do a better job articulating what my code is accomplishing big-picture here in case someone can think of a better way.

My code iterates through 20,000 rows of data on a sheet in the workbook, creating ten rows of new data from each source row (and adding the ten identifier fields from the original data row to each of the ten new rows). The user then has the option to direct the output to an Access database (using Excel 2003 so I can't fit all 200,000 rows on a single sheet) or to ten sheets in an Excel workbook.

The performance of the Access output option is as fast as ever, but last week the write to Excel option got an order of magnitude slower even though I didn't touch that part of the code. Argh!

VBAjedi

 

[N1GHTEYES]

Forgive me for asking the obvious (it's not meant as an insult!), but are you running it with the VBA editor window open? That can massively slow things down.

Is it possible that something in here thread707-1628705
might help?

 

[VBAjedi]

N1GHTEYES: thanks for that... the source link you gave in that thread had some stuff I'd never seen before - great resource! Have a star... not sure if it will solve my issue, but it gives me some new things to try. Should keep my Monday interesting.

VBAjedi

 

[N1GHTEYES]

Thanks for the star.

I sometimes find that my workbooks are running a lot slower than I would expect as well. I usually manage to figure out the reason, but not always - so if you do manage to sort it out, please let us know what was going wrong.

BTW, have you got any array formulae in there? Or UDFs which are Application.Volatile? Before now, for me, both of the above have sometimes been part of the problem.

Tony

 

[VBAjedi]

No array formula or UDF's (volatile or otherwise) in this tool. Actually almost no formulas at all. Just several sheets with source data and about 1500 lines of VBA code to turn that data into way more data.

I've reworked the code in the last few days to hold all the calculated data in multi-dimensional arrays and not dump it out to the output workbook until the end. As you suggested, I used a separate little procedure that I can call to avoid repetitive code. Performance so far is acceptable. Still tweaking and tuning...



VBAjedi