How to create and use AccessibleSpreadsheet and AccessibleCell interfaces

[sokol92]

The AccessibleSpreadsheet and AccessibleCell interfaces give a variety of information about layout of cells of active sheet of Calc document on the screen.
There was no mention of these interfaces on the forum before this topic.

[sokol92]

To work with these interfaces, it is proposed to use a set of functions below.

Usage example.

'Shows the screen coordinates (in pixels) of the upper-left corner of the selected cell 
Sub ShowScreenCoordinates
  Dim oDoc, oCell, oAccCell
  oDoc=ThisComponent
  oCell = oDoc.CurrentSelection().getCellByPosition(0,0)     ' first cell in selection
  oAccCell=GetAccessibleCell(oDoc, oCell)                    ' AccessibleCell
  With  oAccCell.LocationOnScreen
    MsgBox "Screen coordinates of selected cell (pixels): X=" & .X & " Y=" & .Y
  End With   
End Sub

Tools.

' Tools for AccessibleSpreadsheet and AccessibleCell interfaces
' By @sokol92 

' Returns object (supports com.sun.star.AccessibleSpreadsheet interface) corresponding to active sheet of Calc document.
' - oDoc  Spreadsheet document object.
Function GetAccessibleSpreadsheet(ByVal oDoc) As Object
  Dim oAccDoc
  GetAccessibleSpreadsheet=Nothing
  oAccDoc=FindAccessibleRole(oDoc.CurrentController.ComponentWindow.AccessibleContext, 84)                                      ' 84 DOCUMENT_SPREADSHEET
  If Not (oAccDoc Is Nothing) Then GetAccessibleSpreadsheet=FindAccessibleRole(oAccDoc.AccessibleContext, 58).AccessibleContext ' 58 TABLE 
End Function

' Returns object (supports com.sun.star.AccessibleCell interface) corresponding to cell of active sheet.
' - oDoc  Spreadsheet document object.
' - oCell Cell of active sheet.
Function GetAccessibleCell(ByVal oDoc, ByVal oCell) As Object
  Dim oAccSpreadsheet
  GetAccessibleCell=Nothing
  If oCell.SpreadSheet.Name<>oDoc.CurrentController.ActiveSheet.Name Then Exit Function
  oAccSpreadsheet=GetAccessibleSpreadsheet(oDoc)
  If Not (oAccSpreadsheet Is Nothing) Then GetAccessibleCell=oAccSpreadsheet.getAccessibleCellAt(oCell.CellAddress.Row, oCell.CellAddress.Column).AccessibleContext
End Function

' Hierarchically search AccessibleContext for role value.
' - oAccContext Object (supports AccessibleContext service).
' - role        role value.
'
' If the object has more than 500 child objects, then the child objects are not processed.
Function FindAccessibleRole(ByVal oAccContext, ByVal role As Long) As Object
  Dim oAccChild, oResult, i As Long, j As Long, n As Long
  FindAccessibleRole=Nothing
  n=oAccContext.AccessibleChildCount
  If n=0 Or n>500 Then Exit function
  For i=0 To oAccContext.AccessibleChildCount-1
    oAccChild=oAccContext.getAccessibleChild(i).AccessibleContext
    If oAccChild.AccessibleRole=role Then
      FindAccessibleRole=oAccChild
      Exit Function
    End If
    ' recursion
    oResult=FindAccessibleRole(oAccChild, role)
    If Not (oResult Is Nothing) Then
      FindAccessibleRole=oResult
      Exit Function
    End If
  Next i
End Function

' Hierarchically search AccessibleContext up to root object.
' - oAccContext Object (supports AccessibleContext service).
Function FindAccessibleRoot(ByVal oAccContext) As Object
  Dim oAccParent, i As Long
  oAccParent=oAccContext.AccessibleParent
  Do While True
    FindAccessibleRoot=oAccParent
    i=i+1
    oAccParent=oAccParent.AccessibleContext.AccessibleParent
    If oAccParent Is Nothing Or i>100 Then Exit Do
  Loop  
End Function