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 reverse order in each format. The example subroutines below show reading and writing using the Cells(Row,Column) format and the Range(“Column,Row”) format. For example Cells(1,2) is equivalent to Range(“B1”).
The example programs below read a number from a Worksheet, and write that number to a Worksheet. Before you run the programs, be sure the value in Worksheet Cell C5 is 21. (We used C5 in a previous example for built-in functions.) Sub ReadWriteCells() will read in the value 21 from C5 and write out the value to D5. Sub ReadWriteRange() will read in the value 21 from C5, multiply 21 by 2, and write out the value to E5.
Sub ReadWriteCells() 'Important: Cells(Row,Column)
Dim Var As Long
Var = Cells(5,3) 'Reads the value of C5
Cells(5,4) = Var 'Writes the value of Var to D5
MsgBox “Cell D5 = 21”
End Sub
Sub ReadWriteRange() 'Important: Range("Column,Row")
Dim v As Long
v = Range("C5") 'Reads the value of C5
v = v * 2
Range("E5") = v 'Writes the value of v to E5
MsgBox "Cell E5 = 42"
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 View -> Macros -> View Macros to get the Macros dialog box (See section 11.1). Select ReadListWriteList() from the list and press Run. The list of seven integers will be copied to Column H.
Sub ReadListWriteList() 'Important: Cells(Row,Column)
Dim var(20) As Long 'Declare array with 21 values.
Dim i As Long
For i = 1 To 20 ‘Loop i 20 times
var(i) = Cells(i+1,7) 'Read list from worksheet into array
If Cells(i+1, 7) = 999 Then Exit For 'End of list when marker 999 reached
Next i
For i = 1 To 20 ‘Loop i 20 times
Cells(i+1, 8) = var(i) 'Write list to worksheet from the array
If var(i) = 999 Then Exit For 'End the list of data with a marker 999
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-Questions
