[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.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 30747
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Calc, Basic] Introspective cell functions

Post by Villeroy »

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

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK
Contact:

Re: [Calc, Basic]Introspective cell functions

Post by TerryE »

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.
kevinsurbina
Posts: 2
Joined: Tue Oct 27, 2009 6:51 pm

Re: [Calc, Basic]Introspective cell functions

Post by kevinsurbina »

For everyone reading this post ( http://user.services.openoffice.org/en/ ... =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
User avatar
Villeroy
Volunteer
Posts: 30747
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc, Basic]Introspective cell functions

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
syngergy
Posts: 2
Joined: Wed Apr 03, 2013 9:53 pm

Re: [Calc, Basic] Introspective cell functions

Post by syngergy »

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
Post Reply