Generating a large number of formatted cells with MACRO (BASIC)

[VlooiKat]

Hi all,

I need to generate a large number of backcolored cells from a numerical array in the sheet. I have tried with conditional formatting, but i have too many conditions, and have given up in it for now. The cells are numbered from 1 to n (fairly large, say order 1000) and have to be generated in the sheet in systematic way. However, all of them need to have individual non correlating background colors. For simplicity’s sake lets say i want to do this:

Sub TEST1
Dim SHEET As Object
SHEET = ThisComponent.Sheets(0)

Dim MATRIX(49,49) As Integer
dim h As Integer
dim i As Integer

  For h = 0 to 49
    For i = 0 to 49
      MATRIX(i,h) = i + h*50
    next i
  next h

Dim CELL As Object
  For h = 0 to 49
    For i = 0 to 49
      CELL = SHEET.GetCellByPosition(h,i)
      CELL.Value = MATRIX(i,h)
      CELL.CellBackColor = RGB(i, h, 0)
    next i
  next h
End Sub

The RGB(i,h,0) is just an illustration of the fact that each cell needs to have its background color individually assigned. In my case the values of the colors for each cell are defined in another array.

Generating individual cells like this in a loop is too slow. There must be a way to do this faster. As far as i know, SetDataArray only sets numerical values?

Thanks in advance

[JohnSUN]

Welcome @VlooiKat! Yes, you are right - setDataArray() work with values only. Use the controller methods .getTransferable() and .insertTransferable() to copy formatting. Read more in chapter 5.23.6. An alternative to the clipboard – transferable content

[VlooiKat]

Thanks JohnSUN! Excuse me for not expressing myself clearly, where I wrote “copying” I should have written “generating”, as the cells have yet to be produced in the sheet from numerical data.

[Lupp]

If it actually is about a complete copy of a source range (contents, formulas, attruibutes and all), the copyRange method is the appropriate means. As compared to the controller methods working with selections, it is independent of the CurrentSelection.
Example code (with fix ranges for simplicity):

Sub copyRangeDemo()
REM This assumes source and target are in the same sheet,
REM but any sheet can also use the method with CellAddress
REM and/or RangeAddress pointing to different sheets.
doc = ThisComponent
sheet = doc.Sheets(0)
REM Only fix coded for the demo:
trg = sheet.getCellRangeByPosition(0, 0, 49, 49)
srcRA = trg.RangeAddress
With srcRA
  REM .Sheet = .... valid index if needed
  .StartColumn = .StartColumn + 51
  .EndColumn = .EndColumn + 51
End With
REM Only fix coded for the demo:
trgCA = trg.getCellByPosition(0, 0).CellAddress
actingSheet = doc.Sheets(1)
REM Different sheet chosen only for demo!
actingSheet.copyRange(trgCA, srcRA)
End Sub

See also: ask276385demoCopyRangeMethod.ods

[Lupp]

BTW: You cannot copy between different documents this way.
This is also made clear by the fact that the targetCellAddress and the sourceRangeAddress don’t contain information about the document.

[VlooiKat]

Thanks a lot Lupp for the effort! Yet I excuse myself for not expressing myself properly: I actually didn’t mean “copying” but “generating” and have edited the post. The cells do not exist yet, but have to be generated from data which I have in numerical arrays.

[Lupp]

Even to set an already calculated DataArray for a range of 2500 cells may eat a few seconds. If individual attribute values need to be set per cell, you will need much more time to create them as objects, and to shape them as wanted individually. On my /(well, rather old) machine this would be nearly 10 s for your example.
It might really be usefull to tell us what you finally want to achieve. There may be ways to a more efficient solutione (or sheet design) you idn’t yet think of.
Be aware of the fact that the poor efficiency is only to a minimal degree due to “silly old Basic”. It’s the individual creation and handling of cells that spoils performance in this case.

[VlooiKat]

I want to make a visual overview for a studytool. The numbers/cells represent pages of a book you want to master, en the color could give all sorts of information. For example discrete levels of mastering, but I also want a color scale for each cell/page based on time passed since last revision for that page, and that is where I run into problems with conditional formatting. Also, the total number of pages differs per book, so that has to be dynamic too. Hence why I also want it simple: just colored numbers, stacked in an as compact way as possible. Of course there will be tables/arrays where it draws its information from, but they could be located out of view/on another sheet. So basically looking for some ideas here…

[Lupp]

Coloring / attributing individual cells based on sufficiently simple conditions is efficiently done by the core code. This even works for the All Cells modes not just overlaying a cell style. The API you need to use for custom programming, however, doesn’t support this well as far as I can see.

Anyway: I would doubt if a student actually can extract all th information given by content+format, if it comes to the complexity you seem to aim at.
I would need (mostly) one information at a time.

If I understood your intentions basically right, I’m afraid there will not come a helpful idea to me.
Sorry.

[VlooiKat]

Yes, I forgot to mention: I envisioned the different information modes even subdivided in menus. So you could select one mode where it shows you the level of mastering per page (maybe just 3 colors/levels). A second mode that gives for all the pages the time passed as a color scale, a third mode that shows important pages that you have flagged yourself… etc… This can all be done by conditional formatting like you said, but not the color scales as far as I can see now. And it would have to be rendered/calculated fairly quickly. Some complexity indeed… But, thanks for the help anyway! I will continue to think and improve myself.