Square Root in VBA

[Guest]

I cannot make the sqr work in VBA:

Function test1() As Variant
test1 =
Application.WorksheetFunction.MMult(Application.WorksheetFunction.MMult(RegionR, CovarMatrix), Application.WorksheetFunction.Transpose(RegionR))
End Function

RegionR is Variant with 6 objects
CovarMatrix is Variant with 6 x 6 objects

Anyone know a shortcut?

 

[davidm]

Have you remembered to enter the function with Ctr+Shift+Enter? Th
MMULT
component requires that.

David

 

[Guest]

While not commenting on your code

Sub rooot()
Cells(2, 2).Value = Sqr(Cells(1, 1).Value)
End Sub

shows how to get the square root to work in VBA

 

[Guest]

Yes I have done that. Does not help though.

The VBA help states that sqr() must be used with a double. Can a product of
variants be made into Double?

 

[davidm]

Lucy,

It appears to me that your problem is more about finding the standard
deviation via the variance. If that is the case, read on.

Depending on what you are doing, it is possible to derive the standard
deviation from the variance using a matrix and row construct with MMULT
and the TRANSPOSE function.

But to get your FUNCTION to work, first re-write it thus:

Function test1( RegionR, CovarMatrix) As Variant
test1 =
Application.MMult(Application.MMult(RegionR, CovarMatrix),
Application.Transpose(RegionR))
End Function

where, *RegionR * is a Row range and *CovarMatrix* is a Square matrix
range. Then, supposing RegionR="A1:A3" and CovarMatrix = "E1:J6", the
following *entered as an ARRAY* will return the variance (the *square
root* of which yields the standard deviation)


=Test1(A1:A3,E1:J6) Control+Shift+Enter


As hinted by earlier posts, the SQR function will simply convert the
variance to standard deviation.


HTH,

David

 

[Guest]

David:
as I have already defined RegionR and CovarMatrix (see code below) as
functions elsewhere in VBA I need the test1() to: sqr ( regionr * covarmatrix
* transpose(regionr) )

Function test() As Variant
test1 =
Application.MMult(Application.MMult(RegionR, CovarMatrix),
Application.Transpose(RegionR))
End Function

I now need the SQR of test1...

Function RegionR() As Variant
RegionR = Sheets("Inputs").Range(Cells(12, 3), Cells(12, 2 +
num_regions)).Value
End Function

Function CovarMatrix() As Variant
n = num_regions
Dim x()
ReDim x(n, n) As Variant
Dim i As Integer
Dim j As Integer
For i = 1 To n
For j = 1 To n
If j = i Then
x(i, j) = RegionVar(j)
Else
x(i, j) = RegionStd(1, i) * RegionStd(1, j) * CorrMatrix(i, j)
End If
Next j
Next i
CovarMatrix = x
End Function

 

[davidm]

If all is well with your Function, this should work:

Function test() As Variant
test1 = *SQR*(Application.MMult(Application.MMult(RegionR
CovarMatrix), _
Application.Transpose(RegionR)))
End Function

But, I have serious reservations about the entire layered-structure of
the Function, among other things.

Firstly, RegionR = Sheets("Inputs").Range(Cells(12, 3), Cells(12, 2
num_regions)).Value defines a Variant Array, not a value, an
therefore its deployment in your Function Test should generate a
error. And then again, Function RegionR() should carry an argument a
in Function *RegionR(num_regions)*. Ditto Function CovarMatrix(
--->Function CovarMatrix()num_regions.

My gravest concern is with Function CovarMatrix(). None of the batter
of Array holders RegionVar(), RegionStd(), RegionStd() and CorrMatri
is defined or dimensioned leaving it questionable as to how they ca
supply the inputs for the covariance matrix.

If you can clarify the situation, that should help the cause.

David