Writing to a range of cells

[Guest]

Please see this example code:

Public Function helloworld()
Dim hello(3) As Variant
hello(0) = 24
hello(1) = 33
hello(2) = 1
Me.Range("A1,B4,C5").Name = "YAY"
Me.Range("YAY") = hello
End Function

When you run this code, cells A1, B4, and C5 will all be set to the value 24.

Is it possible to write a variant out to a non-linear range of cells in this
fashion and have it so that A1 = 24, B4 = 33, and C5 = 1?

What's the purpose of me trying to do this? From Access I am trying to
automate a report which writes out to a range of cells.

The report right now takes several minutes (~30 at month end) to write
because I am writing it cell by cell. This seems trivial, but try doing this
with about 5000 lines of data, and about 50 value cells and 60 formula cells
per row. It bumps up the time to run the report to roughly 30 minutes,
because each time you make a call to excel to write out a cell it
significantly increases the time to write the report. That's got something
to do with overhead whenever you make a call to OLE Automation I assume, but
I'm not 100% sure if that's a correct statement.

What I would like to do, is copy the formulas and formats from a "template"
row (I do this already and it speeds up the execution somewhat to about 5-10
minutes faster than before), and then write out just the values at one time
per row as a variant array. This I know would speed up execution heavily.
To test this theory, I tried reformatting the report to have all of the
values in a linear range and write at one time (i.e.
excelSheet.Range("A1:A50").Value = myValuesArray) and it sped up execution to
run the whole report in about 5 minutes. Unfortunately, the folks seeing
this report would likely have a raging fit if they saw it like this (go
figure). They want this report in their specific format (gag...), which
means that I would have to use a non-linear range of columns per row to write
out the values. If anyone knows a solution, please let me know.

 

[John Nurick]

Please see this example code:

Public Function helloworld()
Dim hello(3) As Variant
hello(0) = 24
hello(1) = 33
hello(2) = 1
Me.Range("A1,B4,C5").Name = "YAY"
Me.Range("YAY") = hello
End Function

This won't compile in Access or Excel. But you seem to have an Excel
programming question: best ask it in an Excel programming newsgroup.

 

[Jesper F]

They want this report in their specific format (gag...), which

means that I would have to use a non-linear range of columns per row to
write
out the values. If anyone knows a solution, please let me know.

You can send a 2 dimensional array to the excel sheet and write the whole
thing at once.
What do you mean by a non-linear range? I'm not sure I understand completely
what you're trying to do.


Jesper Fjølner

 

[Guest]

You're right, I thought that I had posted this in the Excel Programming base
and then realized I had posted it in Access. The question is not purely
Excel related though, it is related to the interroperability of both programs.

 

[Guest]

I tried the 2-dimensional array trick that I had seen on some other site,
doing something like this:

Public Function helloworld()
Dim hello(3,0) As Variant
hello(0,0) = 24
hello(1,0) = 33
hello(2,0) = 1
Me.Range("A1,B4,C5").Name = "YAY"
Me.Range("YAY") = hello
end function

But that seemed to get the same results. ):

 

[Guest]

Oh, also, by non-linear I mean that normally this kind of setup would work:

If you wrote an array of 5 Variants to the range "A1:A5" from Access, A1
will end up being the first element of the array, A2 the second, and so on.
That would be a linear range. A1,B4,C5 though would be non-linear since I
only want those 3 cells written to by a 3 element array.

 

[Jesper F]

If you wrote an array of 5 Variants to the range "A1:A5" from Access, A1

will end up being the first element of the array, A2 the second, and so
on.
That would be a linear range. A1,B4,C5 though would be non-linear since I
only want those 3 cells written to by a 3 element array.

I'm affraid I don't know of a way to speed that up.
Maybe someone in the Excel group can help.