Many of the document-level methods and properties affect the entire document-for example, the ability to save and print a document. Other methods and properties exist purely as a convenience and the information is also available at the sheet level. For example, the Calc document acts as a draw-page supplier to access all of the draw pages, even though they are available individually from the sheet that contains them.
The official definition of a named range is a named formula expression. My first thought was, "What's a named formula expression?" Typically, a named range represents a cell range, but it may also refer to external data. Naming ranges allows you to give meaningful names to things that you reference. Named ranges may therefore be used as an address in a formula. The NamedRangeFlag constants define how a named range may be used (see Table 47 ).
Numeric | Value | Description |
---|---|---|
1 | com.sun.star.sheet.NamedRangeFlag.FILTER_CRITERIA | The range contains filter criteria. |
2 | com.sun.star.sheet.NamedRangeFlag.PRINT_AREA | The range can be used as a print range. |
4 | Com.sun.star.sheet.NamedRangeFlag.COLUMN_HEADER | The range can be used as column headers for printing. |
8 | com.sun.star.sheet.NamedRangeFlag.ROW_HEADER | The range can be used as row headers for printing. |
Each named range service supports the methods in Table 48 .
Method | Description |
---|---|
getReferredCells() | Get the CellRange referenced by named range. |
getContent() | The named range content is a string and can be a reference to a cell, cell range, or formula expression. |
setContent(String) | Set the content of the named range. |
getReferencePosition() | Get the CellAddress used as a base for relative references in the content. |
setReferencePosition(CellAddress) | Set the reference position. |
getType() | Get the type as a NamedRangeFlag constant (see Table 47). |
setType(NamedRangeFlag) | Set the type of the named range. |
The document's NamedRanges property contains the collection of named ranges in the document. You can extract each individual named range by using named and indexed access.
Use the method addNewByName(name, content, CellAddress, NamedRangeFlag) to create and add a new named range. The name and the content are both of type String. The CellAddress specifies the base address for relative cell references. The NamedRangeFlag can contain a combination of flags but it is typically zero.
Use the method addNewFromTitles(CellRangeAddress, Border) to create named cell ranges from titles in a cell range. The Border value is taken from Table 49 and it specifies where the titles are located in the cell range.
Value | Description |
---|---|
com.sun.star.sheet.Border.TOP | Select the top border. |
com.sun.star.sheet.Border.BOTTOM | Select the bottom border. |
com.sun.star.sheet.Border.RIGHT | Select the right border. |
com.sun.star.sheet.Border.LEFT | Select the left border. |
Use the method outputList(CellAddress) to write a list of the named ranges to a sheet. The first column contains the name of each named range, and the second column contains the content. Finally, use the removeByName(name) method to remove a named range.
Calc documents and individual spreadsheets support the XProtectable interface. Use the methods protect(password) and unprotect (password) to activate or disable protection. The password is passed in as a String. The isProtected() method returns True if protection is currently active.
By default, a Calc document automatically recalculates formulas when the cells to which they refer are modified. At times, it is useful to disable automatic recalculation. The methods in Table 50 allow you to control recalculation in the entire document.
Method | Description |
---|---|
calculate() | Recalculate all cells with changed content. |
calculateAll() | Recalculate all cells. |
isAutomaticCalculationEnabled() | True if automatic calculation is enabled. |
enableAutomaticCalculation(Boolean) | Enable or disable automatic calculation. |
"Goal Seek" attempts to solve equations with one unknown variable. In other words, after defining a formula with multiple fixed values and one variable value, Goal Seek tries to find an acceptable value for the unknown variable.
Consider a very simple example. If you jump off a cliff, gravity will accelerate you toward the ground at 32 feet per second each second. In other words, in one second you will be traveling at 32 feet per second and in two seconds you will be traveling at 64 feet per second. The equation is given as "velocity = acceleration * time". I have a constant value for the acceleration due to gravity, and I want to know how long before I am traveling at 100 feet per second. Admittedly, this is a trivial example, but it is easy to understand.
Use the document method seekGoal(CellAddress, CellAddress, String) to perform a Goal Seek operation. The first cell address identifies the cell that contains the formula to solve. The second cell address identifies the cell that contains the variable that will change. Place the best guess that you can make into this cell. The final string contains the value that you want to obtain from the formula. The macro in Listing 38 sets the formula and then performs a Goal Seek operation.
Sub GoalSeekExample Dim oSheet 'oSheet will contain the fourth sheet. Dim oGCell 'B24, gravity value of 32 Dim oTCell 'C24, time value Dim oRCell 'Resulting equation "=B24*C24" Dim oGoal 'Returned goal object oSheet = ThisComponent.Sheets(3) oGCell = oSheet.getCellByPosition(1, 23) oGCell.setValue(32) oTCell = oSheet.getCellByPosition(2, 23) oTCell.setValue(1) oRCell = oSheet.getCellByPosition(0, 23) oRCell.setFormula("=B24 * C24") oGoal=ThisComponent.seekGoal(oRCell.CellAddress, oTCell.CellAddress, "100") MsgBox "Result = " & oGoal.Result & CHR$(10) &_ "The result changed by " & oGoal.Divergence &_ " in the last iteration", 0, "Goal Seek" End Sub
The seekGoal() method returns a structure containing two floating-point Double values. The Result property identifies the proposed solution. The Divergence property identifies the difference between the last guessed result and the current result. If the divergence is small, the result is probably reasonably accurate. If, however, the divergence is large, the result is probably inaccurate. I performed a test where there was no solution. The divergence value was roughly 1.0E308.
Do not let the simplicity of the provided example fool you. I used the seekGoal() method to provide a solution to a problem that had no closed form solution-I had to use a numerical algorithm to solve the problem.