Expert: Bill - 4/24/2007

Question
Hi Bill,

How are you?

I am pulling my hair out trying to debug some coding which I have written and would be eternally grateful if you can kindly put me out of my misery.

I have a workbook with 2 worksheets, Export and Log. Worksheets "Log" contains 3 columns of data, fT, fA and fB. Worksheets "Export" is for displaying the results of calculations made by VBA coding to the data in Worksheets "Log". The Sub below attempts to calculate the correlation of data columns fT and fA and columns fB and fT. It is syntaxed so that it will work irrespective of what the active sheet is.

Sub CalcStats()
Dim FirstRow As Long
Dim LastRow As Long
Dim reqSampleSize As Long
Dim RangefT As Variant
Dim RangefA As Variant
Dim RangefB As Variant
Dim CorrelfA 'As Double
Dim CorrelfB 'As Double
Dim ShtLog As Worksheet
Dim shtExport As Worksheet

Const fTCol = 3
Const fACol = 4
Const fBCol = 5

reqSampleSize = 60
FirstRow = LastRow - reqSampleSize + 1

LastRow = Application.CountA(Sheets("log").Range("a:a"))

Set ShtLog = Workbooks("CORREL2.xls").Sheets("log")
Set shtExport = Workbooks("CORREL2.xls").Sheets("export")
   
RangefT = ShtLog.Range(ShtLog.Cells(LastRow, fTCol), ShtLog.Cells(FirstRow, fTCol))
RangefA = ShtLog.Range(ShtLog.Cells(LastRow, fACol), ShtLog.Cells(FirstRow, fACol))
RangefB = ShtLog.Range(ShtLog.Cells(LastRow, fBCol), ShtLog.Cells(FirstRow, fBCol))

CorrelfA = Application.WorksheetFunction.Correl(RangefT, RangefA)
   shtExport.Range("c6").Value = CorrelfA
CorrelfB = Application.WorksheetFunction.Correl(RangefT, RangefB)
   shtExport.Range("c7").Value = CorrelfB

End Sub

The problem I have is that I keep getting run time error 1004. "application-defined or Object defined error" when the sub is ran. Debug reveals that the Rangeft( and presumably RangefA and RangefB) definitions being the culprit. Replacing the range variables with hard coded range references (e.g Range("c62:c2") instead of RangefT) would make the sub work so it seems clear that the range syntax is wrong. But try as I might I just cannot find a combination that will work.  Your advice would be much appreciated

regards
m.lock

Answer
It seems that you mean for RangefT, RangefA, RangefB to be or refer to ranges.  If this is the case then whenever a variable is to an object, like a range, a worksheet, a workbook, a chart, etc. then you must use the Set command.  So,

Dim RangefT As Range
Dim RangefA As Range
Dim RangefB As Range

Set RangefT = ShtLog.Range(ShtLog.Cells(LastRow, fTCol), ShtLog.Cells(FirstRow, fTCol))

Set RangefA = ShtLog.Range(ShtLog.Cells(LastRow, fACol), ShtLog.Cells(FirstRow, fACol))

Set RangefB = ShtLog.Range(ShtLog.Cells(LastRow, fBCol), ShtLog.Cells(FirstRow, fBCol))

You can still use

Dim RangefT As Variant
Dim RangefA As Variant
Dim RangefB As Variant

but VBA has to do a little more work to figure out that the variable will be an object, in your specific case.

And, declaring a variable as Variant is the most expensive way, in terms of memory, to have EXCEL/VBA store the variable because it has to hold/allocate enough space to accommodate the "largest" possible "thing" to pass to the variable even when the "thing" being passed may ultimately be just a string one character long.

Make sense?