Access current cell from macro in Calc (solved)

[Pansmanser]

I want to call a macro from a cell in a spreadsheet, and I want to access the particular cell to use its contents in the macro. It seems an obvious thing to want to do, but I can’t seem to find out how. Can someone tell me how to address ‘current cell’ or even ‘current sheet?’

[Lupp]

sub test
theSelection=ThisComponent.CurrentSelection
if NOT theSelection.SupportsService("com.sun.star.sheet.SheetCell")  then exit sub
theCell=theSelection
msgbox "Selected: "+theCell.AbsoluteName
end sub

You will find a lot of very valuable information in the texts by Andrew Pitonyak.

[Pansmanser]

Many thanks, Lupp. I think that is perfect. Andrew Pitonyak’s OOME is my Bible for basicmacro, but I missed this, although it now pops up straight away.

[lonk]

Dear @Lupp,

Toggle the text in the current cell.

sub test
theSelection=ThisComponent.CurrentSelection
if NOT theSelection.SupportsService("com.sun.star.sheet.SheetCell")  then :  exit sub : End If
theCell=theSelection
'msgbox "Selected: "+theCell.AbsoluteName
If UCase(theCell.String) = "" Then			: theCell.String = "Debit"	 : Exit Sub : End If
If UCase(theCell.String) = "DEBIT" Then	: theCell.String = "Credit" : Exit Sub : End If
If UCase(theCell.String) = "CREDIT" Then	: theCell.String = "Debit"	 : Exit Sub : End If
end sub

That’s also OK.

[JohnSUN]

What means “current cell”? Cell with calculated formula at now? Or active cell (outlined bold border)?

REM Function to determine the active cell
REM Parameters:
REM iSheet - the index of the sheet to which the active cell is searched.
REM 	Isn't required. By default - the active sheet
REM oDoc - workbook, for which the active cell is searched.
REM 	Isn't required. Default - the current document
REM Returns a reference to the active cell (or Null if it isn't possible to determine)
REM Author: Vladislav 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 iSheet As Long, Optional oDoc As Variant) As Object
Dim arrayOfString ()	' An array of text strings to parse
Dim lRow&, lColumn&		' The coordinates of the desired cell
Dim tmpString$			' Time line
Dim oCurrentController	' controller of the analyzed document
Dim oSheets As Variant	' All the pages of the current book
Dim oSheet As Variant	' Active (test) page of the book
REM Check input parameters and set the default values ​​:
	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
	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) )
	lRow = CLng (arrayOfString ( 1) )
	Set ActiveCell = oSheet.getCellByPosition (lColumn, lRow)
End Function

[Pansmanser]

Thank you very much, JohnSUN. More complicated than I required, but useful code.