Using the magic of DataArrays

[zabolyx]

I've been doing this all wrong... writing to each line of a spread sheet using loops... a good start but extremely slow...

I stumbled upon the beauty of dumping a range into a DataArray a little while back (more like introduced to)... although, to me, Array () () makes no sense... should it not be like any other multidimensional array Array (x,y)... But no.... This is usable and saves countless time when working with data in spread sheets.... I'm even planning to rewrite my 150 pages of macros to use this method in the near future and should have untold beneficial speed improvements to the processes (of course not using OOoBASIC would make those improvements even better... maybe later in life).

Now I've stumbled upon the setDataArray.... this looks even better than dumping data but plugging that back in...

I've tried it and failed... but even better I've tried the other method of just .DataArray = Array() and still getting an error.

My brethren.. masters of the code... I plead for a call to arms to aide me in my quest....

here is the snippet that cracks under pressure.

Code: Select all

			
				'put the data into the write array
				sWriteArray(iArrayRow) = sWriteString
				
				'clear the write string
				sWriteString = ""
				
				'check if the end of file is reached or 100 items in the write array
				If UBOUND(sWriteArray()) = 99 or iByte = iFileLength Then
					
					'write the string into the EML file
'					oEmailSheet.getCellRangeByName("A" & iEmailRow & ":A" & iEmailRow + iArrayRow).DataArray = sWriteArray()
					oEmailSheet.getCellRangeByName("A" & iEmailRow & ":A" & iEmailRow + iArrayRow).SetDataArray(sWriteArray())
					
					'set the new iEmailRow counter
					iEmailRow = iEmailRow + iArrayRow + 1
					
					'clear the converted string for writing
					sWriteString = ""
					
					'clear the write array
					ReDim sWriteArray(0)
				
				'end the current if statement
				End If

				'increment the write array
				ReDim Preserve sWriteArray (iArrayRow + 1)
				
				'increment the 
				iArrayRow = iArrayRow + 1
				

I'm resizing the array sWriteArray as I need to to add the sWriteString data into the last element of the array.... This array then gets to being about 100 elements 0 - 99... and the oEmailMessage range ends up being 1181 - 1280... so they should match (as no matching seems to break these array copies to and from).

This is from the center of a routine... If more details are needed please let me know. Also I have one commented out line... both methods do not work... I'd like to use the SetDataArray as it seems to run faster according to Turtle47 and B Marcelly.

[Charlie Young]

setDataArray is also something I discovered late in the game, and it is greatly preferable to looping through the CellRange. Your code seems incomplete as posted, but it looks like sWriteArray is one dimensional, and setDataArray requires two dimensions, that is, an array of arrays. You might try designing it with a second dimension of 0.

[zabolyx]

Indeed... I shalt give that at try... Thank you Jedi Master