Slow Macros in Excel 2007

[JE McGimpsey]

FWIW, one can flatten the array of arrays to a two-dimensional array
suitable for assigning to a range using Application.Transpose:

Dim v As Variant
v = Array(Array(1, 4), Array(3, 9))
Range("A1:B2").Value = Application.Transpose(v)

 

[gimme_this_gimme_that]

Wow! Thanks JE. That's *really* getting close.

OK. Suppose I have

Dim v As Variant
v = Array(Array(1, 4), Array(3, 9))

How do I change v from its current state so that it equals:

Array(Array(1,4),Array(3,9),Array(7,"hello world")

This would show how in an iteration you could build up something like
you'd do with a result set or from reading data in a file. (I have a
hunch that redims are expensive.)

Not that this would necessarily be efficent. It might be. It might
not. I just want to know so I can try it.

Thanks.

 

[Peter T]

Another FWIW (I haven't read the thread)

Dim v As Variant
v = Array(Array(1, 10, 100), Array(2, 20, 200))
Set r = Range("A1")
For i = LBound(v) To UBound(v)
r.Resize(, UBound(v(i)) + 1) = v(i)
Set r = r.Offset(1, 0)
Next

as written assumes(?) all arrays are zero base

Regards,
Peter T

 

[gimme_this_gimme_that]

Peter,

Your solution is off base. But thanks for trying ...

Change:

Dim v As Variant
v = Array(Array(1, 10, 100), Array(2, 20, 200))

to

v= Array(Array1,10,100),Array(2,20,200),Array(1,30,"hello")

Using redim.

The solution should *not* include a range or range reference.

 

[JE McGimpsey]

One way:

Dim v As Variant
v = Array(Array(1, 4), Array(3, 9), Array(7, "hello world"))
With Application
Range("A1:B3").Value = .Transpose(.Transpose(v))
End With


or

Dim v As Variant
v = Array(Array(1, 4), Array(3, 9), Array(7, "hello world"))
Range("A1:C2").Value = Application.Transpose(v)

depending on which direction (horizontal or vertical) you want the array
of arrays to be entered.

 

[Jon Peltier]

Sorry to have offended you with my useless example.

- Jon

 

[gimme_this_gimme_that]

Oh come on Jon,

That was just one post.

Guys like you make the internet a cool place to be.

You probably post dozens of helpful messages each week.

If you're more comfortable with a thank you, then ok. thank you.

But I didn't get the impression you were following along on the topics
in the thread.

(On that thread, that true with about everyone except JE.)

 

[NickHK]

Jon,
Seems the only acceptable solution is we all buy Macs. <g>

NickHK

 

[gimme_this_gimme_that]

Good one! Thanks for the laugh NickHK!