Re: Slow Macros in Excel 2007

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Charles,

The concept of using an array or an array of arrays applies went you
need to iterate through a result set and insert all the data into a
Work*** using a *SINGLE* VBA instruction. You have an Array of rows
and each row has an array of data. This would be an alternative to
executing a VBA instruction for each iteration or row .)

Using Applescript with Excel, for example, with the example I posted,
you can do it.

In Applescript you have a list, and each element of the list is
another list with row data.

AppleScript lists translate into Arrays in VBA - but not always.

The topic came up because I explained how in optimizing Excel it's
faster to insert data a row at a time instead of a cell at a time.

Jon, suggested doing it even faster, going for an nxm matrix at a
time.

I asked him to show me.

He posed an example where data was copied from one Work*** to
another matrix at a time.

Which is fine. But as an example it's useless for improving the
preformance of SQL processing.

His example assumes data already exists in a Work*** and isn't
applicable to cases where one iterates through a result set and then
inserts the data into VBA with a single instruction.

Essentially it was a.Range("A1:D4").value = b.Range("A1:D4").value
where a and b are different sheets.

It may be possible. It might be possible to create a string, delimite
cells with tabs and rows with semi colons.

Something like this ..

Constant CELL_DELIMITER = chr(?) ' I don't know the value of ?
Constant ROW_DELIMITER = chr(?) ' I don't know the value of ?

'Then
Dim a as String
a = "1" + CELL_DELIMITER + "20" + ROW_DELIMITER
a = a + "30" + CELL_DELIMITER + "11" + ROW_DELIMITER
Range("A1:B2").value = a

' puts 1 in A1, 20 in A2, 30 in A2, and 11 in B2 not a in each of the
four cells

There would be a trade off with building a gigantic string and in
inserting the data with a single instruction.

It may turn out that nxm wouldn't be faster. But we can't even get nxm
going.


What does an Array() function have to do with reading/writing a database?

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now availablewww.DecisionModels.com


.

Quantcast