Page 173 - IT_V1.0_Class10
P. 173
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)
TheSum = TheSum + oCell.getValue()
Next
SumCellsAllSheets = TheSum
MsgBox ″The sum is″ & 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.
SORTING COLUMNS USING MACRO
We can create a macro that performs the sorting operation i.e. arrangement of data in ascending or descending
order.
In this illustration, we’re sorting data stored in different cells in ascending order by using a macro. Follow the
given steps to do so: 2
Step 1: Select the cell in which data is stored.
Step 2: Click on the Tools menu.
Step 3: Select Macros option. A sub-menu
appears on the screen.
Step 4: Select the Record Macro option. 1
The Record Macro dialog box opens
with Stop Recording button.
3
Step 5: Click on the Sort Ascending option
4
present on the Standard toolbar.
Step 6: Sorted data will appear on the
screen. Click on Stop Recording
option. The BASIC Macros dialog box appears on the screen.
Step 7: Click on New Module button to create a module. The New Module dialog box appears on the
screen.
Using Macros in Spreadsheet 171

