Good Morning,

I am currently using solver in my vba code to solve for optimal weights on a portfolio of assets. All of the calculations are currently being performed on an excel worksheet. I am looking to take the calculation off of the worksheet and just handle it within arrays in VBA. I am wondering how this can be done as it seems that arrays in VBA don't automatically recalculate when a value changes in one of the arrays, which will happen as Solver begins iterating through the different portfolio weights. Is it possible to have an array that will recalculate?

Just for some background on what I have going on in the worksheet that I would like to move to VBA Arrays. I have a vector of mean returns, a variance/covariance matrix, a vector of portfolio weights. The formula for mean return is:

{=MMULT(TRANSPOSE(Vector of weights),Vector of mean returns)}

and for portfolio variance:

{=MMULT(MMULT(TRANSPOSE(Vector of Weights),Covariance Matrix), Vector of Weights)}

Solver sets variance to Min by changing the vector of portfolio weights.