PDA

View Full Version : Problems with Evaluate method in VBA: Variance of the product of two ranges



fboehlandt
10-01-2008, 05:29 AM
Hi everyone,I would like to calculate the variance of a product array of two ranges. The Range().Formulaarray is not an option, since I just want to save the result as a variable to be used in follow-up calculations. I have come up with the code below that contains the evaluate method. Unfortunately, it produces non-sensical results (a very high number) and I haven't been able to figure out what this is calculating infact:
Sub Testfile() Dim wb As Workbook Dim testsheet_roi, testsheet_aum, result As Worksheet Dim rng_roi, rng_aum As Range Dim Variance As Double Set wb = ThisWorkbook Set roi = wb.Worksheets("testsheet_roi") Set aum = wb.Worksheets("testsheet_aum")'Here I set the two relevant ranges: Set rng_roi = roi.Range("B2:K2") Set rng_aum = aum.Range("B2:K2")'I would like to calculate the variance of the product array of the two ranges: Variance = Evaluate("Var(" & rng_roi.Address & "*" & rng_aum.Address & ")") MsgBox (Variance)End SubCan anyone help please? Thanx

Bob Phillips
10-01-2008, 06:17 AM
You are adding the worksheet qualifier



Sub Testfile()
Dim wb As Workbook
Dim testsheet_roi, testsheet_aum, result As Worksheet
Dim rng_roi, rng_aum As Range
Dim Variance As Double

Set wb = ThisWorkbook
Set testsheet_roi = wb.Worksheets("testsheet_roi")
Set testsheet_aum = wb.Worksheets("testsheet_aum") 'Here I set the two relevant ranges:
Set rng_roi = testsheet_roi.Range("B2:K2")
Set rng_aum = testsheet_aum.Range("B2:K2") 'I would like to calculate the variance of the product array of the two ranges:
Variance = Evaluate("Var(" & rng_roi.Address(, , , True) & "*" & rng_aum.Address(, , , True) & ")")
MsgBox Variance
End Sub