PDA

View Full Version : Please help - writing simple formula in VBA



peterm
02-24-2009, 05:27 PM
Hi all, this is my first post. I appreciate any help in advance.

In the attached spreadsheet there is a data set that is highlighted in yellow. This is a covariance formula (correlation * standard dev asset1 * standard dev asset 2). I am trying to automate this in VBA so when i click the button at the top, the covariance matrix is filled in using that formula automatically.

I was trying to write something using offsets, but kept getting data type mismatch errors.

I am hoping someone might be able to point me in the right direction as to how I might go about doing this!

Many thanks!!!!
Pete

Kenneth Hobs
02-24-2009, 06:09 PM
Welcome to the forum!

It is unclear what is what. The covariance is filled where? Correlation, sd1 and sd2 are where?

peterm
02-24-2009, 06:19 PM
Mr Hobs, thanks for responding

The correlation matrix is b30:k39

The covariance matrix is b43:k52
The standard deviations are b27:k27

So for the covariance of large cap value to mid cap growth, the formula is correlation of mid cap growth to large cap value * standard dev of large cap value * standard dev of mid cap growth)

So what i am looking to do is make cells b43:k52 calculate that formula once the correlation matrix is populated (this is done by Private Sub correlmatrix1 in the vba code)

does that make sense? i'm trying to be as clear as i can...maybe...

Thanks!

Sagy
02-26-2009, 10:54 AM
Since the covariance matrix (B43:K52) is already using formulas the recalculations of all these cells will happen automatically if Workbook calculation is set to Automatic. You could also add
Application.CalculateFull
at the end of your macro to force full recalculation even if the user set the Workbook calculation to something other than Automatic.