8

I have a piece of hardware for which I am getting 30 data points. Each of these points is recorded in a spreadsheet at several different places before the sheet is made visible, then another program takes over the excel spreadsheet. It is required all these values are written to the spreadsheet before the other program takes over. If I write each cell individually, the writes are taking approximately 50ms, which takes about 1.25 seconds to complete the data acquisition.

If I could write all the values to the spreadsheet at one time, I feel this will significantly speed up the writing of all these cells. The problem I see is that Ranges work very well for updating contiguous cells where as my data isn't contiguous. Essentially, this would be an example of what I want to write:
A1 = 1
B23 = a
F8 = 2012/12/25
D53 = 4.1235
B2 = 5

I have tried creating a range of "A1,B23,F8,D53,B2", then set the values using an array of values. I tried 3 different arrays: object[5], object[1,5], and object[5,1]. These all set the values of the specified cells in the range to the first index of the array I created in all cases.

Is there a way to update these 30 cells data without iterating through the cells one at a time?

Thanks, Tom

bunggo
  • 330
  • 3
  • 13

3 Answers3

3

If your architecture would permit, another idea is using a hidden sheet with a continuous rectangular range, set names to its parts and use these names on all other sheets.

Eugene Ryabtsev
  • 2,232
  • 1
  • 23
  • 37
0

I would define a rectangular range object that includes all the cells whose values you want to modify. Get a rectangular object[,] array from that range's value property. Write the new values to the array, and then set the range's value using the modified array.

phoog
  • 42,068
  • 6
  • 79
  • 117
  • Yeah, I wanted to try this, but the range of cells ends up being huge, and there are all sorts of charts and formulas I'm afraid might interfere with this one. I may still try it though. Thanks. – bunggo May 16 '12 at 06:08
  • 1
    @bunggo In that case, I would say that Eugene Ryabtsev's suggestion looks promising. – phoog May 16 '12 at 06:36
0

You could write the values to contiguous cells that are somewhere out of the way, say in column X, and have formulae in the target cells that refer to these updated cells. So cell A1 would have the formula "=X1", cell B23 "=X2" and so on.

oldcoder
  • 36
  • 3