Square Root in VBA

[Lucy Pearl]

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? The MMULT
component requires that.

David.

[Gary''s Student]

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
--
Gary''s Student


"Lucy Pearl" wrote:

> 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?
>
>
>
>

[Lucy Pearl]

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" wrote:

>
> Have you remembered to enter the function with Ctr+Shift+Enter? The
> MMULT
> component requires that.
>
> David.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=483954
>
>

[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

[Lucy Pearl]

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" wrote:

>
> 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
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=483954
>
>

[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, and therefore its deployment in your Function Test should generate an error. And then again, Function RegionR() should carry an argument as in Function RegionR(num_regions). Ditto Function CovarMatrix() --->Function CovarMatrix()num_regions.

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

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

David.