So far we have input data with input boxes, and output data with message boxes. Eventually we want to link all programs to the Worksheet. That means reading and writing from the Worksheet. Cells have a number, number format. Range has a letter, number format. We will most often program with the cells format.
Important: Notice that the Row and Column are in natural order in each format. This is different from Excel where the Row and Column are in reverse order for (number, number) format. The example subroutines below show reading and writing using the oCell(Column,Row) format. Notice also that column and row start at 0, not 1. For example oCell(1,2) is equivalent to oCell(“B3”).
The example programs below write a number from a Worksheet, and read a number from a Worksheet. Before you run the programs, be sure the value in Worksheet Cell C6 is 21. Sub WriteCell() will write the value 7 to A4. Sub ReadCell() will read in the value 21 from C6.
I have used the letter o in front of Cell and Sheet to remind you they are objects.
Sub WriteCell() REM Cells(Column,Row)
oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = oSheet.GetCellByPosition(0,3) REM A4
oCell.Value = 7
MsgBox "Number 7 will appear in cell A4."
REM OR
oSheet.GetCellByPosition(0,0).Value = 9 REM A1
MsgBox "Number 9 will appear in cell A1."
End Sub
Sub ReadCell()
oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = oSheet.GetCellByPosition(2,5) REM C6
MsgBox "oCell C6 = " + oCell.Value
REM OR
v = oSheet.GetCellByPosition(2,5).Value REM C6
MsgBox "v = " + v
End Sub
If you get the following BASIC runtime error box, click on the worksheet and click back to the macro panel. It will make the ActiveSheet active, the macro can see it.
Sub ReadWrite()
Dim v As Long
oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = oSheet.GetCellByPosition(2,4) REM C5
v = oCell.value
REM OR v = oSheet.GetCellByPosition(2,4).value
v = v * 2
oCell = oSheet.GetCellByPosition(3,4) REM D5
oCell.Value = v
REM OR oSheet.GetCellByPosition(3,4).Value = v
MsgBox "42 will appear in cell D5. "
End Sub
Note: We use the Cells number,number format because it is easier to read in a list of numbers and write out a list of numbers when using arrays. See the ReadListWriteList() subroutine below.
Often we will read or write a list of values in a worksheet. Copy or type the program below into your code window. Next, in the worksheet, starting with G2 enter seven integer numbers into Column G. Make the last integer 999. This subroutine reads a list of values in Column G, and stops reading when it reaches 999. The values are then written to Column H. To run the subroutine from the Worksheet, choose Tools→Macros→Run Macro→Macro Selector. On Macro Selector navigate My Macros→Standard→Module1→Macro Name. Select macro ReadListWriteList() and click Run. The list of seven integers will be copied to Column H.
Sub ReadListWriteList()
REM Read list.
Dim var(20) As Long
Dim i As Long
oSheet = ThisComponent.CurrentController.ActiveSheet
For i = 0 To 20
var(i) =oSheet.GetCellByPosition(6,i+1).Value
If var(i) = 999 Then Exit For
Next i
MsgBox "i= " + i + " var(i)= " + var(i)
REM Write List
For i = 0 To 20
oSheet.GetCellByPosition(7,i+1).Value = var(i) REM Write
If var(i) = 999 Then Exit For
Next i
MsgBox “Have read and written a list of numbers.”
End Sub
Table of Contents
Ch1-Introduction
Ch2-Loops
Ch3-If Statements
Ch4-Functions
Ch5-Subs & Functions
Ch6-Read & Write
Ch7-Operators
Ch8-Built-in Functions
Ch9-Built-in Examples
Ch10-Debugging
Ch11-Running Subs
Ch12-Sample Programs
Ch13-WS Formulas
Ch14-WS Functions
Ch15-Calc Help Page
