Databases Reference
In-Depth Information
subroutine created in Chapter 2 is utilized to bring this data
into the existing Excel template. Notice how the
The
ImportDataToWorkbook
function is embedded in the call
GetFileName
to allow the user to select the appropriate data file.
Call ImportDataToWorkbook(ActiveWorkbook.Name, SGDataSheet,
GetFileName
("txt",
_
"Strain Gage Resistance Measurements"), False, True, False,
False, False)
One of the goals of this exercise is to readily identify which gages are defective. One way to
do this would be to highlight the good and bad gages contained on the substrate. However, it would
be bad form to highlight the data contained on the original sheet as it will make it difficult to look
at later. To preserve the original data sheet, a copy will be created of it that will be used to display
the good and the bad strain gages on the substrate. The
subroutine created in
CopyWorksheet
Chapter 6 is utilized for this purpose.
Call CopyWorksheet(ActiveWorkbook.Name, SGDataSheet,
ActiveWorkbook.Name, "RowRpt", True, "SGDefects")
On each of the sheets in the calculation Workbook, it is desired to have a footer that displays
the Worksheet name at the bottom center of every printed Worksheet. The
UniversalFooter
subroutine created in Chapter 6 can do this quite easily.
Call UniversalFooter(CalcBook, ''”, OrigWkstName$, ''”)
Once the data has been loaded into a Worksheet, the analysis of the data can begin. The first step
is to perform some basic statistics on the lot of substrates that house the gages. A calculation is made
to determine the average and standard deviations of the resistors located in identical regions of all the
substrates within the given lot. The sheets “AVG” and “STDEV” are utilized to hold this information.
When performing calculations of this nature, two variations are possible.
One
is to simply
perform the calculations in code and write the value of the calculation into a cell. This is really an
inferior method for several reasons. First, the person looking at the spreadsheet will have just a
number, with no idea how it was calculated or what data was utilized to calculate it. Another
limitation of such an approach is that it prevents users from using the created Worksheets to perform
“what if”-types of analyses. In other words, changing a value in one area of the report will not
propagate through the entire Worksheet and change the results that depend on the altered value in
other sections of the Worksheet.
Using the
subroutine developed in Chapter 4, it is possible to utilize the Worksheets
to do the calculations by writing the appropriate formula to a particular cell. The only caveat here is
that the formulas to be written must be constructed specifically for each cell to reflect the values they
must reference. This is easily done in the
AddFormula
subroutine by means of a looping structure
that builds a string representation of the formula desired, which can then be assigned to the cell.
OriginalCalcs
Sub OriginalCalcs()
'Here we extract the original data and compute the mean and
stdev using all of the data
'Place the Average on hidden Worksheet AVG and the STDEV on
hidden W orksheet STDEV
Dim ii As Integer, msg$
Dim row As Integer, col As Integer, cellstr$, funcstr$
Sheets(SGDataSheet).Activate
TRows = TotalRows(CalcBook, SGDataSheet)
ColOrigin = FindCol(FindPos2(CalcBook, SGDataSheet, "A")) + 1
