Page 1 of 1

### [Calc, Basic] Introspective cell functions Posted: Mon Feb 18, 2008 9:53 pm
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: getSheetCellREM returns annotation textDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_NOTE = v.Annotation.getText.getString   else      CELL_NOTE = v   endifEnd FunctionFunction CELL_URL(vSheet,lRowIndex&,iColIndex%,optional n%)'calls: getSheetCellREM 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   endifEnd FunctionFunction CELL_FORMULA(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM return unlocalized (English) formulaDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_FORMULA = v.getFormula()   else      CELL_FORMULA = v   endifEnd FunctionFunction CELL_STYLE(vSheet,lRowIndex&,iColIndex%,optional bLocalized)'calls: getSheetCellREM return name of cell-style, optionally localizedDim 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   endifEnd FunctionFunction CELL_LINE(vSheet,lRowIndex&,iColIndex%,optional n)'calls: getSheetCellREM 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 sheetDim 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   endifend FunctionREM ################### RETURNING NUMBER #################################################Function CELL_ISHORIZONTALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_ISHORIZONTALPAGEBREAK = Abs(cINT(v.Rows.getByIndex(0).IsStartOfNewPage))   else      CELL_ISHORIZONTALPAGEBREAK = v   endifEnd FunctionFunction CELL_ISVERTICALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_ISVERTICALPAGEBREAK = Abs(cINT(v.Columns.getByIndex(0).IsStartOfNewPage))   else      CELL_ISVERTICALPAGEBREAK = v   endifEnd FunctionFunction CELL_CHARCOLOR(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM returns color code as numberDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_CHARCOLOR = v.CharColor   else      CELL_CHARCOLOR = v   endifEnd FunctionFunction CELL_BACKCOLOR(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM returns color code as numberDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_BACKCOLOR = v.CellBackColor   else      CELL_BACKCOLOR = v   endifEnd FunctionFunction CELL_VISIBLE(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM returns visibility state as number 0|1Dim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_VISIBLE = Abs(v.Rows.isVisible)   else      CELL_VISIBLE = v   endifEnd FunctionFunction CELL_LOCKED(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM returns locked state as number 0|1Dim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_LOCKED = Abs(v.CellProtection.isLocked)   else      CELL_LOCKED = v   endifEnd FunctionFunction CELL_NumberFormat(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM returns the number format indexDim v   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)   if vartype(v) = 9 then      CELL_NumberFormat = v.NumberFormat   else      CELL_NumberFormat = v   endifEnd FunctionFunction CELL_NumberFormatType(vSheet,lRowIndex&,iColIndex%)'calls: getSheetCellREM return a numeric com.sun.star.util.NumberFormat which describes a format categoryDim 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   endifEnd 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-positionon 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 selectexit functionexitErr:getSheet = NULLEnd FunctionFunction 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)   endifEnd Function`

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

 Edit: 2009-03-18: Fixed some typos and an error in the description.

### Re: [Calc, Basic]Introspective cell functions Posted: Tue Feb 19, 2008 8:33 am
Placeholder for Change Control and to remove topic from "View unanswered posts" list.

### Re: [Calc, Basic]Introspective cell functions Posted: 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?

### Re: [Calc, Basic]Introspective cell functions Posted: Tue Oct 27, 2009 7:23 pm
Tools>Macros>Organize>Basic...
[Tutorial] How to install a code snippet

### Re: [Calc, Basic] Introspective cell functions Posted: 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%)"