[Calc] Get ActiveCell (with changing column or row)

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
onidarbe
Posts: 84
Joined: Thu Nov 29, 2007 8:03 pm
Location: Belgium, Mechelen

[Calc] Get ActiveCell (with changing column or row)

Post by onidarbe »

Get the ActiveCell with or whitout changing to an other row or column (offset)
column=columnname
row+1=number

Code: Select all

function fActiveCell(optional vChangeColOrRow) as object 
   oActiveSheet=ThisComponent.CurrentController.ActiveSheet
   vViewData=ThisComponent.CurrentController.viewData
   vViewData=join(split(vViewData,";"),"/") 'replace ; with /
   vViewData=join(split(vViewData,":"),"/") 'replace : with /
   vViewData=join(split(vViewData,"+"),"/") 'replace + with /
   vViewData=split(vViewData,"/") 'split the string
   iCol=val(vViewData(6))
   iRow=val(vViewData(7))
   if not isError(vChangeColOrRow) then 'change row or column
      if isNumeric(vChangeColOrRow) then 'change row
         iRow=int(val(vChangeColOrRow))
      else 'change column
         for iCol=0 to 999
            if oActiveSheet.GetCellByPosition(iCol,0).String=vChangeColOrRow then exit for
            if oActiveSheet.GetCellByPosition(iCol,0).String="" then  'Column-label not found
               msgbox "Column-name " & chr(34) & vChangeColOrRow & chr(34) & " is not found !!!"
               exit function
            endif
         next
      endif
   endif
   fActiveCell=oActiveSheet.GetCellByPosition(iCol,iRow)
end function
OOo 3.1.X on Ms Windows XP
ooappler
Posts: 1
Joined: Fri Jun 26, 2009 5:48 am

Re: [Calc] Get ActiveCell (with changing column or row)

Post by ooappler »

Great great code, I just need funtion for changing column of the ActiveCell, Thanks. :lol:
OOo 3.0.X on Mac OSx Leopard
User avatar
RichieRH
Posts: 36
Joined: Sun Oct 19, 2014 6:16 pm

Re: [Calc] Get ActiveCell (with changing column or row)

Post by RichieRH »

What is this used for? I cant use it, showing error. Or maybe could you please give an example in calc so I can download it.
Debian 8 Jessie
Linux Mint 17.3 XFCE
Country : Indonesia
Learning is a need
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Calc] Get ActiveCell (with changing column or row)

Post by FJCC »

If you create a macro module with the code provided and a sub like this

Code: Select all

Sub Main2
ActiveCell = fActiveCell()
print ActiveCell.AbsoluteName
End Sub
the sub will print the name of the active cell. The function fills the strange lack in the api of a way to get the active cell. Instead of just printing the cell name, you would usually want to act on the cell in some way.
I think the code for changing the column of the active cell is not correct. Changing the row seems to work. Call the function with a number as an argument and the cell returned by the function will be the one in the given row of which ever column contains the active cell.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RichieRH
Posts: 36
Joined: Sun Oct 19, 2014 6:16 pm

Re: [Calc] Get ActiveCell (with changing column or row)

Post by RichieRH »

Please give me the example , calc file, so I could understand what is the code used for........
Debian 8 Jessie
Linux Mint 17.3 XFCE
Country : Indonesia
Learning is a need
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Calc] Get ActiveCell (with changing column or row)

Post by FJCC »

I can't give you a useful example of the function but here is an implementation of it. Enter in K2 a number between 1 and 20 or one of the column headers (Alpha, Beta, etc.). Click a cell in the range A2:H21. Click the button and the macro will print either:
1. The cell value that is in the column of the selected cell but has the row index in K2 or
2. The cell value that is in the row of the selected cell but has the column header in K2.
Attachments
OffsetActiveCell.ods
(16.33 KiB) Downloaded 499 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RichieRH
Posts: 36
Joined: Sun Oct 19, 2014 6:16 pm

Re: [Calc] Get ActiveCell (with changing column or row)

Post by RichieRH »

Hai...FJCC,

I had download your file, and I had it with some errors.Did I make wrong steps?
I figure it out and may suggest you, that the problem is caused by vChangeColOrRow didn't have object variable yet..............but I can't fix it cause I don't really understand about this programming code. I t s because of my lack of skill in English Language..
Debian 8 Jessie
Linux Mint 17.3 XFCE
Country : Indonesia
Learning is a need
Post Reply