[Calc, Basic] Introspective cell functions

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, Basic] Introspective cell functions

Postby Villeroy » Mon Feb 18, 2008 9:53 pm

About
Some frequently requested functions to extract from cells informations other than values. Best used to fix badly designed spreadsheets where information is hidden beyond the level of cell values. Use the functions to extract the info, fix your calculation model with some conditional formatting and replace formulas with values (paste special). CELL_URL is particulary useful with address lists where urls are hidden in text-hyperlinks.
These functions share the same problems with similar Basic functions. Contrary to Excel-VBA, user defined functions do not get cell objects as arguments. StarBasic functions get (arrays of) values only. After opening a saved document or after changing anything related, these function need a hard recalculation (Ctrl+Shift+F9). Usually they can not update automatically because the arguments don't change.

Install
Copy all the code to a module in container "My Macros", library "Standard". Other libraries won't work with cell functions.
 Edit: 2011-08-05: Turn off the completely useless option Tool>Options>Calc>Calculate: "Auto detect labels". It puts quotes around user-defined names when they are not detected for some reason. Then you get the #NAME error together with an unwittingly modified formula. 


Usage
These functions reference single cells by 3 function arguments:
1. Sheet as index from 1 to sheet count or sheet name
2. Row index starting at 1
3. Column index starting at 1

CELL_STYLE for instance:
=CELL_STYLE(1;1;1) equivalent to absolute <$1st_sheet>.$A$1
=CELL_STYLE("mySheet";1;1) equivalent to $mySheet.$A$1
=CELL_STYLE(SHEET();1;1) equivalent to $A$1. SHEET() returnes this sheet's index
=CELL_STYLE(SHEET()+1;1;1) equivalent to <next_Sheet>$A$1. SHEET()+1 returnes next sheet's index
=CELL_STYLE(SHEET();ROW(A1);COLUMN(A1) equivalent to relative A1.
=CELL_STYLE(SHEET();1;COLUMN(A1) equivalent to mixed A$1.
=CELL_STYLE(SHEET();ROW(A1);6) equivalent to mixed $F1.
=CELL_STYLE(SHEET();ROW();6) --> This row, column F.
=CELL_STYLE(SHEET();ROW();COLUMN()) --> examines this cell itself

Note: function CELL_STYLE can take an optional 4th boolean argument. If true, the localized name of a built-in style gets returned.

All errors are #VALUE (Basic Null), booleans are returned as 1 or 0.

See function comments for more details.
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
REM ################### RETURNING STRING #################################################
Function CELL_NOTE(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns annotation text
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_NOTE = v.Annotation.getText.getString
   else
      CELL_NOTE = v
   endif
End Function
Function CELL_URL(vSheet,lRowIndex&,iColIndex%,optional n%)
'calls: getSheetCell
REM returns URL of Nth text-hyperlink from a cell, default N=1)
Dim v
   If isMissing(n) then n= 1
   If n < 1 then
      CELL_URL = Null
      exit function
   endif
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      if v.Textfields.Count >= n  then
         CELL_URL = v.getTextfields.getByIndex(n -1).URL
      else
         Cell_URL = Null
      endif
   else
      CELL_URL = v
   endif
End Function
Function CELL_FORMULA(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM return unlocalized (English) formula
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_FORMULA = v.getFormula()
   else
      CELL_FORMULA = v
   endif
End Function
Function CELL_STYLE(vSheet,lRowIndex&,iColIndex%,optional bLocalized)
'calls: getSheetCell
REM return name of cell-style, optionally localized
Dim v,s$,bLocal as Boolean
   if not isMissing(bLocalized) then bLocal=cBool(bLocalized)
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      if bLocal then
         s = thisComponent.StyleFamilies("CellStyles").getByName(v.CellStyle).DisplayName
      else
         s = v.CellStyle
      endif
      CELL_STYLE = s
   else
      CELL_STYLE = v
   endif
End Function
Function CELL_LINE(vSheet,lRowIndex&,iColIndex%,optional n)
'calls: getSheetCell
REM Split by line breaks, missing or zero line number returns whole string.
REM =CELL_LINE(SHEET(),1,1,2) -> second line of A1 in this sheet
Dim v,s$,a(),i%
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      s = v.getString
      if not isMissing(n) then i = cInt(n)
      if i > 0 then
         a() = Split(s,chr(10))
         If (i <= uBound(a())+1)then
            CELL_LINE = a(i -1)
         else
            CELL_LINE = NULL
         endif
      else
         CELL_LINE = s
      endif
   else
      CELL_LINE = v
   endif
end Function

REM ################### RETURNING NUMBER #################################################
Function CELL_ISHORIZONTALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_ISHORIZONTALPAGEBREAK = Abs(cINT(v.Rows.getByIndex(0).IsStartOfNewPage))
   else
      CELL_ISHORIZONTALPAGEBREAK = v
   endif
End Function
Function CELL_ISVERTICALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_ISVERTICALPAGEBREAK = Abs(cINT(v.Columns.getByIndex(0).IsStartOfNewPage))
   else
      CELL_ISVERTICALPAGEBREAK = v
   endif
End Function
Function CELL_CHARCOLOR(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns color code as number
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_CHARCOLOR = v.CharColor
   else
      CELL_CHARCOLOR = v
   endif
End Function
Function CELL_BACKCOLOR(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns color code as number
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_BACKCOLOR = v.CellBackColor
   else
      CELL_BACKCOLOR = v
   endif
End Function
Function CELL_VISIBLE(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns visibility state as number 0|1
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_VISIBLE = Abs(v.Rows.isVisible)
   else
      CELL_VISIBLE = v
   endif
End Function
Function CELL_LOCKED(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns locked state as number 0|1
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_LOCKED = Abs(v.CellProtection.isLocked)
   else
      CELL_LOCKED = v
   endif
End Function
Function CELL_NumberFormat(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM returns the number format index
Dim v
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      CELL_NumberFormat = v.NumberFormat
   else
      CELL_NumberFormat = v
   endif
End Function
Function CELL_NumberFormatType(vSheet,lRowIndex&,iColIndex%)
'calls: getSheetCell
REM return a numeric com.sun.star.util.NumberFormat which describes a format category
Dim v,lNF&
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      lNF = v.NumberFormat
      CELL_NumberFormatType = ThisComponent.getNumberFormats.getByKey(lNF).Type
   else
      CELL_NumberFormatType = v
   endif
End Function

'################### HELPERS FOR ABOVE CELL FUNCTIONS #########################################
Function getSheet(byVal vSheet)
REM Helper for sheet functions. Get cell from sheet's name or position; cell's row-position; cell's col-position
on error goto exitErr
   select case varType(vSheet)
   case is = 8
      if thisComponent.sheets.hasbyName(vSheet) then
         getSheet = thisComponent.sheets.getByName(vSheet)
      else
         getSheet = NULL
      endif
   case 2 to 5
      vSheet = cInt(vSheet)
      'Wow! Calc has sheets with no name at index < 0,
      ' so NOT isNull(oSheet), if vSheet <= lbound(sheets) = CRASH!
      'http://www.openoffice.org/issues/show_bug.cgi?id=58796
      if(vSheet <= thisComponent.getSheets.getCount)AND(vSheet > 0) then
         getSheet = thisComponent.sheets.getByIndex(vSheet -1)
      else
         getSheet = NULL
      endif
   end select
exit function
exitErr:
getSheet = NULL
End Function

Function getSheetCell(byVal vSheet,byVal lRowIndex&,byVal iColIndex%)
dim oSheet
'   print vartype(vsheet)
   oSheet = getSheet(vSheet)
   if varType(oSheet) <>9 then
      getSheetCell = NULL
   elseif (lRowIndex > oSheet.rows.count)OR(lRowIndex < 1) then
      getSheetCell = NULL
   elseif (iColIndex > oSheet.columns.count)OR(iColIndex < 1) then
      getSheetCell = NULL
   else
      getSheetCell = oSheet.getCellByPosition(iColIndex -1,lRowIndex -1)
   endif
End Function

 Edit: 2008-06-26: Added CELL_ISHORIZONTALPAGEBREAK and CELL_ISVERTICALPAGEBREAK 

 Edit: 2009-03-18: Fixed some typos and an error in the description. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17262
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Basic]Introspective cell functions

Postby TerryE » Tue Feb 19, 2008 8:33 am

Placeholder for Change Control and to remove topic from "View unanswered posts" list.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: [Calc, Basic]Introspective cell functions

Postby kevinsurbina » Tue Oct 27, 2009 7:06 pm

For everyone reading this post ( http://user.services.openoffice.org/en/forum/viewtopic.php?f=21&t=2762 but me, the answer to my question will be obvious. It's true, I'm a total noob, still, I need to know.

The post addresses hyperlink extraction and placement into adjacent cell.

I got the code, but after checking my OO 3.1 program files, have no clue where to put the code. Here's what was written:
Install
Copy all the code to a module in container "My Macros", library "Standard".
Where are "My Macros" found?
OpenOffice 3.1 on Windows XP
kevinsurbina
 
Posts: 2
Joined: Tue Oct 27, 2009 6:51 pm

Re: [Calc, Basic]Introspective cell functions

Postby Villeroy » Tue Oct 27, 2009 7:23 pm

Tools>Macros>Organize>Basic...
[Tutorial] How to install a code snippet
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17262
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Basic] Introspective cell functions

Postby syngergy » Wed Apr 03, 2013 11:18 pm

When I run the code (I tried copying the whole thing and running the CELL_URL macro, and just copying the CELL_URL function code) I get a "argument not optional" error around "v = getSheetCell(vSheet,lRowIndex&,iColIndex%)"
OOo 3.4.1
Windows 7
syngergy
 
Posts: 2
Joined: Wed Apr 03, 2013 9:53 pm


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest