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

Creating Extension - 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 forum is not for asking questions about writing your own macros.

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

Postby onidarbe » Thu Sep 25, 2008 4:25 pm

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

Code: Select all   Expand viewCollapse view
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
User avatar
onidarbe
 
Posts: 84
Joined: Thu Nov 29, 2007 8:03 pm
Location: Belgium, Mechelen

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

Postby ooappler » Fri Jun 26, 2009 7:30 am

Great great code, I just need funtion for changing column of the ActiveCell, Thanks. :lol:
OOo 3.0.X on Mac OSx Leopard
ooappler
 
Posts: 1
Joined: Fri Jun 26, 2009 5:48 am

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

Postby RichieRH » Sun Feb 08, 2015 5:58 am

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
User avatar
RichieRH
 
Posts: 34
Joined: Sun Oct 19, 2014 6:16 pm

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

Postby FJCC » Sun Feb 08, 2015 6:37 am

If you create a macro module with the code provided and a sub like this
Code: Select all   Expand viewCollapse view
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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6168
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby RichieRH » Fri Oct 23, 2015 12:56 pm

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
User avatar
RichieRH
 
Posts: 34
Joined: Sun Oct 19, 2014 6:16 pm

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

Postby FJCC » Sat Oct 24, 2015 3:51 am

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 140 times
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6168
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby RichieRH » Sun Oct 25, 2015 5:02 am

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
User avatar
RichieRH
 
Posts: 34
Joined: Sun Oct 19, 2014 6:16 pm


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest