VBA array input array output problem

**financerelated** · 11-29-2017, 03:25 PM

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).

All tips are more than welcome!
Thanks in advance!

Job

**MrShorty** · 11-29-2017, 04:07 PM

As I explain here (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I do when writing a UDF is to add a stop statement or a breakpoint so that I can enter debug mode. So the first thing I did with your file was to add a stop statement to each of the minvar() UDFs, then enter each into a cell on the spreadsheet.

Stepping through each function, it looks like the same error in both. In each function, you are attempting to divide an array (X or the result of the matrix multiplication) by a scalar (C) using the division operator. Unfortunately, it does not appear that the VBA division operator knows how to divide an array/matrix by a scalar.

The first solution that I see is to loop through each element of the array X (so the minvar approach will be better than minvar2) and divide by C one element at a time. Are you familiar with VBA loops (For..Next in particular? https://msdn.microsoft.com/en-us/VBA...next-statement )?

VBA array input array output problem

LinkBack

Thread Tools

Rate This Thread

Display

VBA array input array output problem

Re: VBA array input array output problem

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] finding an output from 5 variables in an array using exterior input user variables

Array function only displays first value of array as output

[SOLVED] Excel 2003 Array as input - how to place output in a second array?

[SOLVED] Returning an array of lookup values from an input array, rather than single value

Replace hard coded array in formula with link to input array

Array Range Depends on Value in Each Cell-input an array

Input/output values into/from an array

Problem with Time Output from Array.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions