Databases Reference
In-Depth Information
Function SheetExists(wkbook$, sheetname$, Optional addsheetname
As Boolean = False) As Boolean
'This function will determine if sheetname$ exists in Workbook$
'If the sheet exists returns TRUE, Returns False if sheet
added or Not Present
Dim i As Integer, Count As Integer, DataBook$
Call ActivateWorkbook(wkbook$)
'rotate through all the Worksheets by getting the count of
Worksheets
For i = 1 To Worksheets.Count
ActiveWorkbook.Worksheets(i).Activate
If Worksheets(i).Name = sheetname$ Then
'Sheet Exists
SheetExists = True
Exit Function
End If
Next i
'If we get this far sheetname$ is NOT in wkbook$
SheetExists = False
If addsheetname = False Then
Exit Function
Else
'Add and Name Sheet at the same time!
Sheets.Add.Name = sheetname$
SheetExists = False
Exit Function
End If
End Function
With a new sheet created to hold the fitted data set, the regression can begin. This is done with
the following call to the “ATPVBAEN.XLA!Regress” macro.
Application.Run "ATPVBAEN.XLA!Regress",
ActiveSheet.Range(YRange), _
ActiveSheet.Range(XRange), False, Labels, , "FitResults",
False, _
False, False, True, , False
With the linear regression complete, the fitted line parameters COD (r
2
), Y-Intercept, and
slope can be extracted from the results using the
FindPos
,
FindRow
, and
Find Col
functions.
The COD and Y-Intercept can be determined rather easily by means of the following code
snippets:
COD =
Workbooks(ActiveWorkbook.Name).Worksheets("FitResults").Cells
(FindRow(FindPos(ActiveWorkbook.Name, "FitResults", "R
Square", False, True)), FindCol(FindPos(ActiveWorkbook.Name,
"FitResults", "R Square", False, True)) + 1)
