Page 229 - IT 402 v2.0 class 10
P. 229
Arguments Passed as Values
When the arguments are passed by values, then the compiler copies the values of arguments in the function.
The parameter in the called function is initialised with the value of the passed argument.
As long as the parameter has not been declared as constant, the value of the parameter can be changed, but
the changes are only performed within the scope of the called function only they have no effect on the value
of the argument in the calling function.
Arguments passed to a macro from Calc are always values. It is not possible to know what cells, if any, are
used. For example, =PositiveSum(A3) passes the value of cell A3 and PositiveSum has no way of knowing that
cell A3 was used. If we know which cells are referenced rather than the values in the cells, pass the range as a
string, parse the string and obtain the values in the referenced cells.
Macros Act Like Built-In Functions
lthough alc finds and calls macros as normal functions they do not really behave as built-in functions. For
example, macros do not appear in the function lists. It is possible to write functions that behave as regular
functions by writing an Add-In.
Accessing Cell Directly
OpenO ce internal ob ects can be accessed directly to manipulate a alc document. For example the macro
in the following coding adds the values in cell A2 from every sheet in the current document.
‘ThisComponent’ is set by StarBasic when the macro starts to reference the current document. A Calc
document contains sheets ‘ThisComponent.getSheets()’. Use ‘getCellByPosition(col, row)’ to return a cell at a
specific row and column.
To Add cell A2 in every sheet:
Function SumCellsAllSheets()
Dim TheSum As Double
Dim i As integer
Dim oSheets
Dim oSheet
Dim oCell
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount()-1
oSheet = oSheets.getByIndex(i)
oCell = oSheet.getCellByPosition(0, 1) ‘ GetCell A2
TheSum = TheSum + oCell.getValue()
Next
SumCellsAllSheets = TheSum
End Function
A cell object supports the methods getValue(), getString() and getFormula() to get the numerical value, the
string value or the formula used in a cell. Use the corresponding set functions to set appropriate values. Use
‘Sheet.getCellRangeByName(“A2”)’ to return a range of cells by name. If a single cell is referenced, then a cell
object is returned. If a cell range is given, then an entire range of cells is returned. When a macro is called a
Calc function, the macro cannot modify any value in the sheet from which the macro was called.
More About Spreadsheet 227

