Retrieving a Defined Name

Under Insert | Names | Define you are able to define names which apply to certain cells or ranges. You may wish to be able to reference these from within a Macro. This example does that, by retrieving the first cell in such a defined range (that is, although defined ranges can cover many cells this function only retrieves a single cell from that range, the first cell).

This also includes some primitive error handling for the case when the name is not found, it does this via a global function.

Function GetNamedCell( nameRef as String )
	'Indicate what to do if an error occurs
	On Error Goto ErrorHandler
	
	'reference the current document
	Dim oDocument as Object
	oDocument = ThisComponent	

	'get the NamedRange wanted
	Dim oNamedRange as Object
	oNamedRange = oDocument.NamedRanges.getByName( nameRef )
	
	'refer to the first cell
	Dim oCell as Object
	oCell = oNamedRange.getReferredCells().getCellByPosition(0,0)
	
	'return the string from that cell	
	GetNamedCell = oCell.getFormula()
	
ErrorHandler:
	'check to see if any errors occured and set the output to the error
	if Err <> 0 then
		GetNamedCell = ReportErr( Err )
	end if
End Function

' returns an error string that matches the typical form found in Calc
Function ReportErr( ErrCode as Integer )
	ReportErr = Switch( _
		ErrCode = 525, "#NAME?", _
		ErrCode = 524, "#REF", _
		ErrCode = 519, "#VALUE", _
		ErrCode <> 0, "Err:" + ErrCode, _
		ErrCode = 0, "" )
End Function