Ask Your Question
0

how do i get current cell column & row in basic

asked 2020-07-06 18:30:41 +0200

colingwright gravatar image

current cell property?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2020-07-08 12:40:11 +0200

colingwright gravatar image

updated 2020-07-08 12:50:56 +0200

JohnSUN gravatar image

Obtain the active cell.

REM Author: Paolo Mantovani
REM email: mantovani.paolo@tin.it
Sub RetrieveTheActiveCell()
 Dim oOldSelection 'The original selection of cell ranges
 Dim oRanges 'A blank range created by the document
 Dim oActiveCell 'The current active cell
 Dim oConv 'The cell address conversion service
 Dim oDoc
 oDoc = ThisComponent
 REM store the current selection
 oOldSelection = oDoc.CurrentSelection
 REM Create an empty SheetCellRanges service and then select it.
 REM This leaves ONLY the active cell selected.
 oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
 oDoc.CurrentController.Select(oRanges)
 REM Get the active cell!
 oActiveCell = oDoc.CurrentSelection
 oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion")
 oConv.Address = oActiveCell.getCellAddress
 Print oConv.UserInterfaceRepresentation
 print oConv.PersistentRepresentation
 REM Restore the old selection, but lose the previously active cell
 oDoc.CurrentController.Select(oOldSelection)
End Sub
edit flag offensive delete link more

Comments

1

Please use this button for code

CodButton.png

JohnSUN gravatar imageJohnSUN ( 2020-07-08 12:51:49 +0200 )edit
0

answered 2020-07-06 19:07:16 +0200

JohnSUN gravatar image

This is very old solution:

REM Function for determining the active cell
REM Parameters:
REM resultType - 0 (default) return ActiveCell as object (for macro), 
REM         1 - return column number (based 0),
REM         2 - retur row number
REM         3 - absolute name of active cell as string
REM iSheet - index of the sheet for which the active cell is being searched.
REM         is optional. By default - active sheet
REM oDoc - a spreadsheet for which an active cell is searched.
REM         is optional. The default is the current document.
REM Returns a reference to the active cell or Null if it could not be determined
REM Author: Vladyslav Orlov aka JohnSUN, Ukraine, Kiev, 2012
REM by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM by Villeroy > http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=38672#p177590
Function ActiveCell(Optional resultType As Integer,  Optional iSheet As Long, Optional oDoc As Variant) As Variant
Dim arrayOfString()
Dim lRow&, lColumn&     
Dim tmpString$
Dim oCurrentController
Dim oSheets As Variant
Dim oSheet As Variant
    If IsMissing(oDoc) Then oDoc = ThisComponent
    If NOT oDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Function
    oCurrentController = oDoc.getCurrentController()
    If IsMissing(iSheet) Then
        oSheet = oCurrentController.getActiveSheet()
        iSheet = oSheet.getRangeAddress().Sheet
    Else
        If (iSheet < 0) Then Exit Function
        oSheets = ThisComponent.getSheets()
        If (iSheet >= oSheets.getCount()) Then Exit Function
        oSheet = oSheets.getByIndex(iSheet)
    EndIf
    If IsMissing(resultType) Then resultType = 0
    tmpString = oCurrentController.getViewData()
    arrayOfString() = Split(tmpString, ";")
    If UBound(arrayOfString) < (3 + iSheet) Then Exit Function
    tmpString = arrayOfString(3 + iSheet)
    If InStr(tmpString,"+") > 0 Then
        arrayOfString() = Split(tmpString, "+")
    Else
        arrayOfString() = Split(tmpString, "/")
    EndIf
    lColumn = CLng(arrayOfString(0))
    If resultType = 1 Then
        ActiveCell = lColumn
    Else
        lRow = CLng(arrayOfString(1))
        If resultType = 2 Then
            ActiveCell = lRow
        Else        
            If resultType = 3 Then 
                ActiveCell = oSheet.getCellByPosition(lColumn, lRow).AbsoluteName
            Else
                ActiveCell = oSheet.getCellByPosition(lColumn, lRow)
            EndIf 
        EndIf 
    EndIf 
End Function

Call it from cell as function:

ActiveCell

Or from macro as described

edit flag offensive delete link more

Comments

Thanks John for solution. I was hoping that there would be shorter solution such as being able to get a "row" or "column" property of the current cell. I have seen a similar solution to yours in Andrew Pitonyak's "OpenOffice.org Macros Explained". I will add the solution he gives (coded by Paolo Mantovani) as another answer.

colingwright gravatar imagecolingwright ( 2020-07-08 12:37:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-06 18:30:41 +0200

Seen: 121 times

Last updated: Jul 08