I’m relatively new to Libre BASIC, so this might be a simple question. I am constructing a formula that will be inserted into a cell using BASIC where the formula itself references other cells.
Given that cell indicies in BASIC functions like getCellByPosition are numeric (0-base), how do I convert these numerically based indicies to alphabetic indicies which are required in formulas?
sCellName = object.getCellByPosition().AbsoluteName
sCellRangeName = object.getCellRangeByPosition().AbsoluteName
AbsoluteName property is presented in Calc A1 formula syntax (grammar).
But if you are working with row and column indices, then you may find it more convenient to put together the formula using the Excel R1C1 syntax. Then don’t forget to add 1 to the indices. Using the SetFormulaR1C1() function, you can assign an R1C1 formula to a cell, but it will be displayed correctly, depending on the selected formula syntax option.
Excel R1C1: R means row, C means column
C2 - absolute reference to column B
RC[-1] refers to the column to the left of the formula cell in the current row (R without index).
sFormulaR1C1 = "=RC2+RC[-1]" 'where 2 is one of your indices increased by 1
Call SetFormulaR1C1(sFormulaR1C1, oCell)
Sub SetFormulaR1C1(sFormulaR1C1$, oCell As Object)
Dim oParser As Object
oParser = ThisComponent.CreateInstance("com.sun.star.sheet.FormulaParser")
oParser.FormulaConvention = com.sun.star.sheet.AddressConvention.XL_R1C1
oCell.Tokens = oParser.parseFormula(sFormulaR1C1, oCell.CellAddress)
End Sub
Yes, yes, thanks to @Lupp and the one who helped him with this technique (@librebel)…
Updated
There is no $ in the Excel R1C1 address syntax. R4C2 is an absolute reference to a cell in row 4 column 2, same as $B$4 in A1 syntax. R4C[2] is a reference to absolute row 4 and relative 2 columns to the right of the formula cell, in A1 syntax same as C$4 if entered in column A, or D$4 if entered in column B.
Understand. But what if I use copy/paste in the spreadsheet at a later point in time. Will the cell reference remain, like it does when the $ is used, or will it be recalculated relative to the paste location?
No, $ stands for absolute reference style (MS Excel term) or absolute formula syntax (LO Calc term) and is used in Calc A1 and Excel A1 styles or syntaxes. Cell (range) AbsoluteName property uses only Calc A1 absolute (not relative) syntax. But the most interesting thing is that you do not need all this to solve your problem. Do you want to work with column and row indicies? Wonderful! The Excel R1C1 syntax works exactly like this, and LO Calc supports it in full. The procedure Set FormulaR1C1 uses the required syntax parser.
The use of the R1C1 syntax does not depend on the specified syntax in the LO Calc options. This option only affects the display of the formula on the sheet.
No problem.
NOTE: The terms “reference style” and “formula syntax” are used interchangeably here. Another term is “notation”.
Excel R1C1 reference style
Learn the ADDRESS function for clarity.
This is not required, although it is possible.
For example, an array formula is assigned to a cell range using Calc A1:
oRange = oNewSheet.getCellRangeByPosition(3, START_ROW + 1, 3, nEndRow)
oRange.ArrayFormula = "=YEAR(E" & START_ROW + 2 & ":E" & nEndRow + 1 & ")"
Updated:
Here the reference is made absolute not by the $ sign, but by numeric indicies without square brackets [ ].
For very rare cases (in fact in pursuit of a little math problem) I once made a related function:
Function colNameForColNumber(ByVal pColNum As Long, ByVal pMode As Long)
REM v0.1.0 2019-12-13 Wolfgang Jäger
REM revision (not yet)
REM The columnNames will consist of plain Latin letters. Their values as digits
REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
REM There is no zero in this system.
REM pMode True (or<>0) tells that the column names are made for TextTable. Otherwise for Spreadsheet.
digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
If pMode=1 Then
numSysBase = 52
Else
numSysBase = 26
End If
nameStr = ""
decumulator = pColNum
Do While decumulator>0
dValue = (decumulator MOD numSysBase)
If dValue=0 Then dValue = numSysBase
decumulator = Int((decumulator - dValue)/numSysBase)
nameStr = Mid(digits, dValue, 1) & nameStr
Loop
colNameForColNumber = nameStr
End Function
It’s funny, but a fact, that TextTable columns (for unreasonably wide tables) are named in a base-52 system.
Also Pitonyak in 6.6. Human readable address of cell:
Listing 6.15: Cell address in a readable form using CellAddressConversion.
oActiveCell = ThisComponent.getCurrentSelection()
oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = oActiveCell.getCellAddress
Print oConv.UserInterfaceRepresentation
Print oConv.PersistentRepresentation
Yes, I continue to use these services, even though they are depricated. However, there is one bug…
s = oConv.UserInterfaceRepresentation 'deprecated
' BUG: If it's not the 1st sheet, then the sheet name is not skipped.
s = Mid(s, InStr(s, .) + 1) 'omits the name just in case(Slightly off topic?) There’s also
Function colNumberForColName(ByVal pColName As String, ByVal pMode As Long)
REM v0.1.0 2019-12-13 Wolfgang Jäger
REM revision (not yet)
REM The columns names MUST consist of plain Latin letters. Their values as digits
REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
REM There is no zero in this system.
REM pMode>0 tells that the column names are from TextTable. Otherwise from Spreadsheet.
REM pMode <=0 interprtets the names for spreadsheets, pMode <0 reads them case-insensitive.
colNumberForColName = ""
digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Select Case pMode
Case 1
numSysBase = 52
Case <=0
numSysBase = 26
REM To make sure an error is thrown if pColName contains an illegal character:
digits = Left(digits, numSysBase)
REM For use in Spreadsheet:
If pMode<0 Then pColName = Ucase(pColName)
End Select
lenCN = Len(pColName)
accumulator = 0
For j = 1 To lenCN
d = Mid(pColName, j, 1)
charValue = InStr(1, digits, d, 0)
If charValue=0 Then Exit Function
accumulator = accumulator * numSysBase + charValue
Next j
colNumberForColName = accumulator
End Function@eeigor, thanks. Lot’s to get out of this on R1C1, and it does help clarify the why’s behind the colors-and-flowers examples which I’ve gone over carefully. (solution link).