Compile Error in VBA Code

Hi,

I am trying to teach myself how to use VBA and, to that end, I wish to write some code to calculate the standard deviation of a range of data to be selected by the user.

Unfortunately I have stumbled at the first hurdle, receiving a Compile Error when trying to execute the code. Can someone please tell me what is wrong:

Please Login or Register  to view this content.

Thanks!

Hi Andrew,

The Set keyword assigns an object reference to a variable or property. Volatility is declared as a literal (a double) so you will get a compile error. Also, it's not great to give your variables and procedures the same name - it gets confusing!

Handling Application.InputBox in this situation is a little bit tricky: if the user cancels then the assignment will fail and the only way to counter this is with error handling.

Please Login or Register  to view this content.

Hi Andrew

You need to dim Volatility as Range, not Double as it is the range holding your data, not the resulting value

Please Login or Register  to view this content.

It will work fine without dimming Answer, and just having Msgbox display the result of StDev but I am guessing you want to use the result somewhere else

Please Login or Register  to view this content.