Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing to a range of cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing to a range of cells
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) "BlockNinja" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing to a range of cells
Nope same results unfortunately. ): Only wrote 24's out to those cells.
"Bob Phillips" wrote: 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) "BlockNinja" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing to a range of cells
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) "Bob Phillips" wrote in message ... 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) "BlockNinja" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing to a range of cells
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" wrote: 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) "Bob Phillips" wrote in message ... 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) "BlockNinja" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing to a range of cells
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) "BlockNinja" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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) "BlockNinja" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help writing Excel macro when range changes | Excel Worksheet Functions | |||
Writing a range to an array... | Excel Programming | |||
writing range contents to arrays; best way? | Excel Programming | |||
Writing Range to Array | Excel Programming |