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
   168   169   170   171   172   173   174   175   176   177   178