[Solved] Set variable to value of =COLUMN()

[trvalentine]

I'm a newbie at OOo Basic, haven't programmed in ages. I can define a variable and set its value within a module, but how can I grab the value of a cell containing the formula =COLUMN() and set the variable to that value minus 1?

TIA.

[JohnV]

Code: Select all

Sub GetValueOfCell
oSheets = ThisComponent.getSheets
oSheet = oSheets.getByIndex(0)'Sheet1
oCell = oSheet.getCellRangeByName("C4")
MsgBox oCell.getValue
End Sub

[Villeroy]

Whatever the cell's formula is:

Code: Select all

oSheet = thisComponent.Sheets.getByIndex(0)
oCell = oSheet.getCellByPosition(3,3) 'or getByName("D4")
adr = oCell.getCellAddress()
print adr.Sheet, adr.Column, adr.Row
REM a cell has also a RangeAddress with StartRow=EndRow, StartColumn=EndColumn
REM same with a range
oRange = oSheet.getCellRangeByPosition(3,3,4,4) 'or getByName("D4:E5")
adr = oRange.getRangeAddress()
print adr.Sheet,  adr.StartColumn, adr.StartRow, adr.EndColumn, adr.EndRow

Search this forum for "XRay".

[trvalentine]

Thank you Villeroy and JohnV for your very quick response. I see now that I assumed too much and explained too little.

What I want to do is look at the value of =COLUMN() either in the active cell when the macro begins or in a cell one or two rows higher than the active cell (but definitely in the same column), subtract one from that number so I can get back to the same (starting) column after I've done a GoToStartOfRow to capture some information by using GoRight, i.e. perform GoRight a variable amount of times, the variable being the value of =COLUMN() minus 1.

Can this even be done?

Thanks.

[Villeroy]

So you may rephrase the subject line of your initial post. I'd suggest something like "[Basic] How to get the active sheet cell".
The short answer is: You can't. -- Pardon?
They did not (forgot to?) implement an API function for this most frequently needed task.
getActiveCell, variant 1.

Code: Select all

Function getActiveCell(oView)
oDummy = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
REM store what is currently selected
oTmp = oView.getSelection()
REM selection of an empty ranges collection falls back to the active cell
oView.select(oDummy)
REM but what we get in return is a cell rather than empty collection
oAC = oView.getSelection()
REM restore the previous selection
oView.select(oTmp)
getActiveCell = oAC
End Function

Edit 2008-06-05: fixed two errors in the above code.

Don't you laugh!

And then a spreadsheet view provides a string named "ViewData" (getViewData(), restoreViewData(str)) which describes the position of the active cell (among other things).
getActiveCell, variant 2

Code: Select all

'-------usefull helper-function, returning focussed cell 
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
'		 ;sh;							;lSheet +3
Row<=8191:'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
Row>8191:'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
	as1()  = Split(oView.ViewData, ";")
	lSheet = CLng(as1(1))
	sDum = as1(lSheet +3)
	as1() = Split(sDum, "/")
	on error goto errSlash
		lCol = CLng(as1(0))
		lRow = CLng(as1(1))
	on error goto 0
	getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
	if NOT(bErr) then
		bErr = True
		as1() = Split(sDum, "+")
		resume
	endif
End Function

For those who prefer a mature programming language, getActiveCell in Python:

Code: Select all

def getActiveCell(oView):
    '''Desparately missing in API. We extract from view data.'''
    sData = oView.getViewData()
    oSheet = oView.getActiveSheet()
    as1  = sData.split(";")
    lSheet = long(as1[1])
    sDum = as1[lSheet +3]
    delim = '/' in sDum and '/' or '+'
    as2 = sDum.split(delim)
    lCol = (as2[0])
    lRow = (as2[1])
    return oSheet.getCellByPosition(lCol,lRow)

[trvalentine]

Don't you laugh!

Not fair! As soon as I read it, I started (but wasn't drinking coffee at the keyboard). That's like asking someone to stand in a corner and not think of XYZ for an hour.

There was a lot of good stuff which led me to some other stuff. Eventually, I came up with this near the beginning:

Code: Select all

oSelectedCells = ThisComponent.CurrentSelection 
oActiveCell = oSelectedCells.CellAddress 
nColumn = oActiveCell.Column 

That got me the value I needed. Then, knowing I was coming from Column A, I used:

Code: Select all

dim fincycle2(1) as new com.sun.star.beans.PropertyValue
fincycle2(0).Name = "By"
fincycle2(0).Value = nColumn
fincycle2(1).Name = "Sel"
fincycle2(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, fincycle2())

That got me over the hump. Thanks so much.

[Zeko]

Here is a Function I wrote that will return the value of the Currently Active cell in a spreadsheet.
I have noted how the cell could be updated if desired. I hope this helps.

Function GetCellVariant as Variant

Dim oDoc as object
Dim oSheet as object
Dim oCell as object
dim oThisCell as object
Dim oCurrentCell as object
dim nCol, nRow as integer

oDoc = StarDesktop.CurrentComponent
oSheet = oDoc.Sheets(0)

oCurrentCell = ThisComponent.CurrentSelection
oThisCell = oSelectedCell.CellAddress
nCol = oThisCell.Column
nRow = oThisCell.Row

oCell = oSheet.getcellbyposition(nCol,nRow)
Select case oCell.type
case 0 'empty cell
case 1 'numeric value in cell
GetCellVariant = oCell.value
'oCell.value = somevalue 'will update the cell with a new value
case 2 'string value in cell
GetCellVariant = oCell.string
'oCell.strint = newstring 'will update the cell with a new string
end Select
end Function 'GetCellVariant

[Villeroy]

Zeko,
Your macro fails with any selection which is not a single cell (a range of cells, multiple ranges of cells, a shape).

Code: Select all

REM gets the active cell's value from ThisComponent's current controller (active window)
Function getMyActiveValue()
  getMyActiveValue = getValueOfActiveCell(ThisComponent.getActiveController())
End Function
REM ---------------------------
REM pass a document's view from where you need the value
REM returns Basic String, Double, NULL or Empty for text, number, error or blank cell
REM calls getActiveCell, getCellValue
Function getValueOfActiveCell(oView) As Variant
  oCell = getActiveCell(oView)
  getValueOfActiveCell = getCellValue()
End Function
REM ---------------------------
Function getCellValue(oCell as com.sun.star.sheet.Cell) as variant
dim lContentType&,lResultType&
lContentType = oCell.getType
lResultType = oCell.FormulaResultType
If oCell.getError <> 0 then
   'return Null which gives err #VALUE when passed back to a sheet-cell
   getCellValue = Null
else
   with com.sun.star.table.CellContentType
   select case lContentType
      case is = .FORMULA
      If lResultType = com.sun.star.sheet.FormulaResult.VALUE then
         getCellValue = oCell.getValue()
      else lResultType = com.sun.star.sheet.FormulaResult.STRING
         getCellValue = oCell.getString()
      'no way_ http://www.openoffice.org/issues/show_bug.cgi?id=58749
      'elseIf lResultType = com.sun.star.sheet.ERROR then
      '   getCellValue = oCell.getError
      end if
   case is = .VALUE
      getCellValue = oCell.getValue()
   case is = .TEXT
      getCellValue = oCell.getString()
   case is = .EMPTY
   'default variant empty
   end select
   end with
end if
end function

Use one of my two functions from above for missing getActiveCell(oView).