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

Keyboard macros or custom scripts

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

Postby trvalentine » Thu May 22, 2008 11:05 pm

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.
Last edited by Hagar Delest on Wed Jun 25, 2008 5:04 pm, edited 1 time in total.
Reason: tagged the thread as Solved.
Ver 2,4 / Windoze (XP, Vista); Linux (Ubuntu) // wannabe macro programmer / Calc (mostly) & Writer
trvalentine
 
Posts: 9
Joined: Wed May 14, 2008 6:34 pm

Re: Set variable to value of =COLUMN()

Postby JohnV » Fri May 23, 2008 12:14 am

Code: Select all   Expand viewCollapse view
Sub GetValueOfCell
oSheets = ThisComponent.getSheets
oSheet = oSheets.getByIndex(0)'Sheet1
oCell = oSheet.getCellRangeByName("C4")
MsgBox oCell.getValue
End Sub
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Set variable to value of =COLUMN()

Postby Villeroy » Fri May 23, 2008 12:46 am

Whatever the cell's formula is:
Code: Select all   Expand viewCollapse view
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".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27112
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Set variable to value of =COLUMN()

Postby trvalentine » Fri May 23, 2008 3:23 am

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.
Ver 2,4 / Windoze (XP, Vista); Linux (Ubuntu) // wannabe macro programmer / Calc (mostly) & Writer
trvalentine
 
Posts: 9
Joined: Wed May 14, 2008 6:34 pm

Re: Set variable to value of =COLUMN()

Postby Villeroy » Fri May 23, 2008 11:39 am

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   Expand viewCollapse view
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   Expand viewCollapse view
'-------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   Expand viewCollapse view
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)

Last edited by Villeroy on Thu Jun 05, 2008 10:33 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27112
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Set variable to value of =COLUMN()

Postby trvalentine » Fri May 23, 2008 8:28 pm

Villeroy wrote: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   Expand viewCollapse view
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   Expand viewCollapse view
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.
Ver 2,4 / Windoze (XP, Vista); Linux (Ubuntu) // wannabe macro programmer / Calc (mostly) & Writer
trvalentine
 
Posts: 9
Joined: Wed May 14, 2008 6:34 pm

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

Postby Zeko » Thu Jul 24, 2008 6:03 am

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
OOo 2.4.X on MS Windows Vista
Zeko
 
Posts: 14
Joined: Mon Jul 14, 2008 5:23 pm

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

Postby Villeroy » Thu Jul 24, 2008 8:02 am

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   Expand viewCollapse view
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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27112
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests