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. |
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. |