Ctrl shift enter for array

I am trying to create a macro that returns the estimated std dev for a portfolio per cell. However, since the number of shares, their insidiousness, and also their weights will change mostly constantly and with different portfolios that I want in VBA (my ultimate goal is to make my reports as light as possible). In the meantime, there is no need to worry about it.

I don't seem to understand ctrl+ shift+ enter, or the sqr part.

Could you take a look at my code help me?

Sub PorteføljeRisiko()
Dim RapportBok As Workbook
Dim RapportArk As Worksheet
Set RapportBok = Workbooks("Rapport kunder")
Set RapportArk = RapportBok.Sheets(1)

Dim Region1 As Long
Dim RegionA As Long
Dim Matrise As Range

      

(since the number of shares does not change, I find out how many rows actually include the value (i.e.% weighting in each share))

Region1 = Application.WorksheetFunction.CountIf(RapportArk.Range("AC7:AC18"), "<>" & "")
RegionA = Region1 - 1 

      

(to get the matrix for the kovar between stocks, since there are no stocks with a puff and selling stocks. The matrix starts in cells (3.55)

SisteKolonne = RapportArk.Cells(RegionA + 3, RegionA + 55)

Set Matrise = RapportArk.Range(Cells(3, 55), Cells(3 + RegionA, 55 + RegionA))

Set Region = RapportArk.Range("AC7:AC" & Region1 + 6)

      

(I want the result in Range ("AG21"))

RapportArk.Range("AG21").FormulaArray = MMult(Application.MMult(Region, Matrise), Application.Transpose(Region))

      

(Everything works fine, except that it returns #VALUE !, as I cannot get neither the ctrl+ shift+ enternor the SQR part in the macro)

End Sub

      

+3


source to share


1 answer


First of all, what you are calculating here in VBA is not formula

, but value

. Also, I was not able to verify all of your calculations, but I could see the problem here:

Application.MMult(Region, Matrise)

The dimensions do not follow the rules for matrix multiplication. From code analysis Region

, this is a column vector with size Region1

and Matrise

is a matrix Region1 x Region1

.

Remember that matrix multiplication is not commutative. Most likely, you need to switch the order of these operands:

Application.MMult(Matrise, Region)

      



This will give you a column vector of size Region1

. But then you want to have a dot product with a vector Region

, and the row vector must appear here again, so you must apply Transpose

to the first operand, not the second. So the correct statement should be:

RapportArk.Range("AG21").value = _
    Application.MMult(Application.Transpose(Application.MMult(Matrise, Region)), Region)

      

Since the last operation is dot-product

two vectors, you can perhaps simplify it with SumProduct

:

RapportArk.Range("AG21").value = _
    Application.SumProduct(Application.MMult(Matrise, Region), Region)

      

+6


source







All Articles