Databases Reference
In-Depth Information
column, a one is added to the result (“
1”). This is because the cell with “A” is always one column
left of the true starting position of the data matrix. Looking at the looping structure, it is apparent
that 8 row loops are nested inside 12 column loops, and that the beginnings of the loops are offset
by the startcol and startrow locations. Because the
WellPos
variable is only dimensioned 8
+
12
(WellPos(8,12)),
it is necessary to remove the offset when referencing the
WellPos
variable
in the looping structure. The offset is added to represent the true position of the data on the
Worksheet so that it may be referenced by the Cells method.
Once the data have been read into the array, it can be extracted at any time rather easily. The following
subroutine will pop up a message box displaying the value of any set of indexes contained within the array.
×
Sub DisplayArrayVal(ByVal row As Integer, ByVal col As Integer)
'Display the contents in the WelPos Array for row & col passed
MsgBox "Well Value in row " & Chr(64 + row) & " and col " &
Str(col) & _
" is: " & Str(WellPos(row, col)), vbOKOnly + vbInformation,
"Retrieved Value"
End Sub
This subroutine should only be run
after
the
ReadIntoArray
subroutine has been executed.
Otherwise, there will be no values written into the array to extract. The subroutine will display a
message box telling the value at a particular row and column within the array (Figure 1.9). Notice
that, on the Worksheet, the Cells are limited to two decimal places. However, when the value in
the cell is extracted to a variable, its full precision (in this case Single precision) is maintained.
That is why the message box displays values with more than two decimal places.
The second and more intuitive form of copying is copying data from one Worksheet to
another. Users do this all the time manually by selecting a section of the Worksheet by hand
with the mouse and copying and pasting it. This is fine for an occasional task, but when a series
of Worksheets must be formatted in an identical manner, it gets tiresome very quickly to keep
copying and pasting the data by hand. Although it is more work up front, it is much more efficient
to construct a macro that will locate the data (using landmarks) and automatically copy the data
to its proper location. This is especially true if formatting Worksheets in such a manner is part
of an ongoing process or task that will be carried out for months if not years. Copying a section
of a Worksheet from one Worksheet to another is not a complicated process, the following
subroutine accomplishes this task quite nicely.
FIGURE 1.9
Displaying the value of a particular element of the Array.
