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.

 

[Bob Phillips]

Is this any good

Public Function helloworld()
Dim hello
Dim rng As Range
Set rng = Range("YAY")
ReDim hello(rng.Rows.Count, rng.Columns.Count)
hello(0, 0) = 24
hello(3, 1) = 33
hello(4, 2) = 1
rng = hello
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Nope same results unfortunately. ): Only wrote 24's out to those cells.

 

[Bob Phillips]

No, if you define YAY from A1 to at least C5 it works, writing 24, 33 and 1

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

I got your code to work but unfortunately it also writes blanks to the cells
in between, which in my case wouldn't work b/c I would have already
copy/pasted formulas into those blank cells from a single row that takes care
of the formulas and formatting. ):

I did manage to come to another solution though in the grander scheme of
things, and just wrote a pure Excel macro that my Access prog invokes after
transferring out the queries via TransferSpreadsheet. Then Excel takes care
of transposing the fields to the right places, the whole process is now
optimized from about 25-30 minutes to about 5 now, which is good enough to at
least get to lunch at a more reasonable time every day now.

 

[Bob Phillips]

If it's okay to lose formulae but keep the values, try

Public Function helloworld()
Dim hello
Dim rng As Range
Set rng = Range("YAY")
hello = Range("YAY")
hello(1, 1) = 24
hello(4, 2) = 33
hello(5, 3) = 1
rng = hello
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)