sheetCellRanges.getCells() doesn’t return all the cells skipping the cells with no content.
Is there a way to access all cells on sheetCellRanges, other than(pseudo code):
For range in ranges
For c in range.columns
For r in range.rows
access cell(c,r)
next r
next c
next range
Thanks,
Mike
There are usually more effective methods for solving specific problems.
In 25 years, I never needed any cell-by-cell access, and I’ve never seen any practical use case for method getCells(). Yes, if I ever had a use case for cell-by-cell access, I would do it as outlined by your code.
Is there a way to combine the outputs of queryEmptyCells ( ) and queryContentCells ( ) , that might do the trick.
At the moment I can’t think of seeing a rangeAdd function to create a single range from two.
What more effective methods did you have in mind?
Thanks,
Mike
That technique is slow, it takes 2 1/2 seconds to update the market value figures for the stocks in the spreadsheet. If ranges.getCells() didn’t skip c. 4 stocks the time required for that task would be just over 1 second. There are several columns that have to be updated that way and the times add up.
Be well,
Mike
In efficient code, the number of calls to the properties and methods of Calc objects should be minimized.
For a rectangular range of cells, we do this:
arr=range.getDataArray()
' Change the contents of the array arr
range.setDataArray(arr)
“A single range from two” can only work in rare cases. You may mean a kind of “closure” in the sens of “samllest range containing both” or an intersection. However, you can create a SheetCellRanges (plural!) object and add range by range or a multiple of ranges in one go based on their RangeAddress / RangeAddresses.
See LibreOffice: SheetCellRanges Service Reference
Thanks, Lupp but I’m already dealing with sheetCellRanges and hadn’t yet realized that ranges.getdata() returns yet another ranges object rather than single range.
Mike
When I use ranges.getData() I’ve figured out how to see the cell values.
Are the formulas in there too and I just haven’t learned to see them.
Thanks,
Mike
Use getFormulaArray instead. It returns arrays of strings.
"" empty cell
"ABCD" string
"3.14" number
"=SUM(A1:B5)" a formula expression
"'=SUM(A1:B5)" formula expression as string
"'3.14" numeric string
Yes I’ve already found that.
For Each oRng in mvColStocks
Forms = oRng.GetFormulaArray()
'msgBox( "Forms(0) = " & Forms(0) ) ’ BASIC runtime error. Object variable not set.
msgBox( "Forms(0)(0) = " & Forms(0)(0) ) ’ shows formula
msgBox( "Forms(1)(0) = " & Forms(1)(0) ) 'shows formula
'msgBox( "Forms(0)(1) = " & Forms(0)(1) ) ’ Index out of defined range
Next
The necesary addressing of the array has me puzzled, can you point me to an explanation?
Mike
Never mind I found it: Forms(row)(column)
cheers
It’s an array of row arrays like this:
(
(a1,b1,c1),
(a2,b2,c2),
(a3,b3,c3)
)
I walk through it like this:
for each rowArray in formulaArray()
for each s in rowArray()
do_stuff_with s
next
nextInitally I was trying to set the formula in all the cells in a column SheetCellRanges though I never stated that explicitly.
The problem with ranges.getCells() is that it skips empty cells, in the case I’m dealing with it returns exactly as ranges.queryContentCells(21).getCells().
When it turned out that adding a search of ranges.queryEmptyCells() and ranges.queryEmptyCells().getCells() returns nothing, perhaps as in my case the empty cells are a mix of small range and single cells.
The second search of empty cells added 50% to the search time so though it’s not as visually elegant I broke down and put zeros in those empty cells so that ranges.getCells() returns the full list.
Initially I’ve been using a rangeWalk because it hit every cell but after timing several approaches this worked fastest:
oEnum = mvColStocks.createEnumeration()
Do While oEnum.hasMoreElements() 'While there are more
oRng = oEnum.nextElement() 'Get the next component
rangeFillFormulasDown( oRng, RCformula )
Loop
Where mvColStocks is a sheetCellRanges in a column of stock market values.
I know some of you don’t like R1C1 style formulas but they are what I’m used to and dealing with formulas that change daily, it’s just easier for me using such and offsets from lastColumn.
I suspect that filling appropriate columns with namedFormulas so that I’d only have to update the formula each day rather than filling the whole column of formulas daily would be another time saver, but that’s a problem for another day.
Y’all take care,
Mike
I’ll start with thanks to Villeroy & Sokol92 for their input which greatly speeded up things.
Tha above routine averaged 729 ms over 4 runs, the below averaged 289 ms over 4 runs compared to the original rangeWalk routine that took > 2 seconds that’s huge.
I was burning midnight oil when I finally got all the kinks out of this latest or would have posted yesterday.
Dim oSrcForms()
oEnum = mvColStocks.createEnumeration()
Do While oEnum.hasMoreElements() 'While there are more
oRng = oEnum.nextElement() 'Get the next component
rStart = oRng.rangeAddress.startRow + 1
oSrcForms = oRng.getFormulaArray()
For r = LBound(oSrcForms) To UBound(oSrcForms)
For c = LBound(oSrcForms(0)) To UBound(oSrcForms(0))
oSrcForms(r)(c) = "=" & sSharesCol & rStart + r & "*" & _
sTodaysCol & rStart + r
Next c
Next r
oRng.setFormulaArray(oSrcForms)
Loop
oRng.setFormulaArray() required A1 notation so sSharesCol & sTodaysCol are A1 style letters of column numbers.
Sorry I haven’t learned how to put spaces at the front of lines to make the code easier to read.
Be well,
Mike
sokol92 showed me how to format code.
Thanks sokol92.
You may not look like a Blessing but certainly appear to be one to struggling folks like me.
Mike
If you select the following code and press Quote (or q), you will see how the code is formatted.
Formatting rules are here.
Dim oSrcForms()
oEnum = mvColStocks.createEnumeration()
Do While oEnum.hasMoreElements() 'While there are more
oRng = oEnum.nextElement() 'Get the next component
rStart = oRng.rangeAddress.startRow + 1
oSrcForms = oRng.getFormulaArray()
For r = LBound(oSrcForms) To UBound(oSrcForms)
For c = LBound(oSrcForms(0)) To UBound(oSrcForms(0))
oSrcForms(r)(c) = “=” & sSharesCol & rStart + r & “*” & _
sTodaysCol & rStart + r
Next c
Next r
oRng.setFormulaArray(oSrcForms)
Loop