LibreOffice BASIC Easy GetCell

Improving on GetCell from an earlier post I have added a Column2Index function to allow you to pass the names of the column and row rather then the index.

If the column is a number then it will use the values as index values, otherwise it will convert both the column and the row.

These two calls are the same
GetCell("Sheet1", "D", 3) <- using the names of the columns and rows
GetCell("Sheet1", 3, 2) <- using the index of the columns and rows


For cell that contain numbers, ether fixed or formula generated you can simply read from them or write to them like so.

If GetCell("Sheet1", "D", 3).Value > 100 Then
   GetCell("Sheet1", "D", 3).Value = 0
End If

For cells that contain text you use GetCell("Sheet1", "D", 3).String the same way you would any normal string variable.

Function Column2Index(ColNameX As String)
 ReturnInt = 0
 ColNameU = Ucase(ColNameX)
 While ColNameU <> ""
  LastChar = Left(ColNameU, 1)
  ColNameU = Right(ColNameU, Len(ColNameU) - 1)
  ReturnInt = ReturnInt * 26
  ReturnInt = ReturnInt + Asc(LastChar) - 64
 Wend
 Column2Index = ReturnInt - 1
End Function

' Now accepts column and row as they are named or their index value
' These two calls are the same
' GetCell("Sheet1", "D", 3) <- using the names of the columns and rows
' GetCell("Sheet1", 3, 2) <- using the index of the columns and rows


Function GetCell(SheetName As String, Column, Row) As com.sun.star.table.XCell
 ColumnNumber = 0
 RowNumber = 0
 If ISNUMERIC(Column) Then  
  ColumnNumber = Column
  RowNumber = Row
 Else
  ColumnNumber = Column2Index(Column)
  RowNumber = Row - 1 
 End If
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 TheCell = FindSheet.GetCellByPosition(ColumnNumber,RowNumber)
 GetCell = TheCell
End Function