Ask Your Question

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

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:
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("")
 REM Get the active cell!
 oActiveCell = oDoc.CurrentSelection
 oConv = oDoc.createInstance("")
 oConv.Address = oActiveCell.getCellAddress
 Print oConv.UserInterfaceRepresentation
 print oConv.PersistentRepresentation
 REM Restore the old selection, but lose the previously active cell
End Sub
edit flag offensive delete link more



Please use this button for code


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

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 Villeroy >
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("") Then Exit Function
    oCurrentController = oDoc.getCurrentController()
    If IsMissing(iSheet) Then
        oSheet = oCurrentController.getActiveSheet()
        iSheet = oSheet.getRangeAddress().Sheet
        If (iSheet < 0) Then Exit Function
        oSheets = ThisComponent.getSheets()
        If (iSheet >= oSheets.getCount()) Then Exit Function
        oSheet = oSheets.getByIndex(iSheet)
    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, "+")
        arrayOfString() = Split(tmpString, "/")
    lColumn = CLng(arrayOfString(0))
    If resultType = 1 Then
        ActiveCell = lColumn
        lRow = CLng(arrayOfString(1))
        If resultType = 2 Then
            ActiveCell = lRow
            If resultType = 3 Then 
                ActiveCell = oSheet.getCellByPosition(lColumn, lRow).AbsoluteName
                ActiveCell = oSheet.getCellByPosition(lColumn, lRow)
End Function

Call it from cell as function:


Or from macro as described

edit flag offensive delete link more


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 " 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


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

Seen: 121 times

Last updated: Jul 08