VBA-code with array-input and array output

[heggalompen]

Hi all,I am trying to program something in VBA. The inputs are matrices which varies on the amount of stocks, which is unfortunately not the same all the time.I already managed to get VBA to calculate 4 constants (A, B, C, D) with the matrices as input, there will always be only 4 constants, but they depend on the input of course. These 4 constants are calculated with the function letters (see attachment). The next thing is that I want to calculate minimum variance portfolio weights, which could be done with the following excel formula: MMULT(inverse covariance matrix, 1-vector) / C. I tried to program this into VBA with the help of google but it is not really working. I hope the problem is a bit clear by my text and the attachted excel file including the vba code letters and my attempts for the portfolio weights (minvar and minvar2).

Link to excel/vba-file:

https://files.fm/u/6djneetk#_All tips are more than welcome!Thanks in advance,
- Thomas

 

[StephenCrump]

Welcome to the Forum!

To divide an array X by a constant C, you need to loop through each element, i.e, rather than saying: NewArray = X / C

So one way to do this:

L28:L33 = {=Minvar(C20:H25,I28:I33)}

Function MinVar(covar As Range, ones As Range) As Variant

    X = WorksheetFunction.MMult(covar, ones)
    C = WorksheetFunction.Sum(X)
    
    For i = 1 To UBound(X)
        X(i, 1) = X(i, 1) / C
    Next i
    
    MinVar = X

End Function

As an alternative to passing ones as an argument, you could also generate it in the function:

ones = Evaluate("Row(1:" & covar.Rows.Count & ")^0")

Excel 2010
	C	D	E	F	G	H	I	J	K	L
27							1-vector			Results
28	39.29057	13.46507	5.100492	6.399951	13.76665	4.687612	1			-0.02187
29	13.46507	118.7604	-2.89447	47.24421	52.46669	12.03087	1			-0.19522
30	5.100492	-2.89447	17.10598	-6.775	-16.4519	5.273497	1			0.21604
31	6.399951	47.24421	-6.775	27.41679	35.43786	3.439729	1			0.36513
32	13.76665	52.46669	-16.4519	35.43786	96.51904	-3.27943	1			0.06807
33	4.687612	12.03087	5.273497	3.439729	-3.27943	6.743096	1			0.56784
34										1.00000
Sheet1