Stopped working, not sure why

[ajc5382]

Below is code for my volatility calculation.

rngvol is the number of days the volatility is taken over
volcal is the number of data items
cor1 is the standard deviation over a given range
volatility 1 should be the volatilty result

'Statistical Volatility Calculation 1




rngvol = Worksheets("config").Cells(32, 2)
volcalc = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))


Do Until Worksheets("info").Cells(rngvol + i + 1, 1).Value = ""
For i = 1 To volcalc - rngvol + 1
cor1 = WorksheetFunction.StDev(INFO.Range(INFO.Cells(2 + i, 4), INFO.Cells(rngvol + i + 1, 4)).Value)
volatility1 = cor1 * Worksheets("config").Cells(38, 2).Value
Worksheets("info").Cells(rngvol + i + 1, 8).Value = volatility1

Next i
Exit Do


Loop

 

[Mohammad Basem]

Hi,

Two questions:

What do you mean by "Stopped working"?

What is the purpose of the Do...Loop?

 

[ajc5382]

Hi,

Two questions:

What do you mean by "Stopped working"?

What is the purpose of the Do...Loop?

1.I left the previous day with everything working fine, and came back the next day to find that it wasn't generating any values or calculations. There was no error, but it wasn't working correctly.

2. The do...loop stops the calculation when there is no longer data.

 

[Mohammad Basem]

Potential reasons could be Cells(rngvol + i + 1, 1) is empty, or volcalc - rngvol + 1 is zero.

Use debug to trace the values.

 

[ajc5382]

Potential reasons could be Cells(rngvol + i + 1, 1) is empty, or volcalc - rngvol + 1 is zero.

Use debug to trace the values.

I made a few minor adjustments, interestingly enough, when I step through this section of the code, each time I push F8 it calculates the value. Any thoughts?

 

[Mohammad Basem]

Which value? Can you post sample data with the code, so we can be on the same page. It is difficult to judge with the absence of the code and data.

 

[ajc5382]

I removed the do...until statement, however know im getting an error that the stdev property of the worksheet function class cannot be found. This was just working I have no idea what happened. Is there something wrong with my notation? I've gotten this error with worksheetfunction.ln() Regardless. All that this code is doing is:

1. Taking a range of data from a column with % differences, and using the worksheetfunction class to calculate the standard deviation.
2. Multiplying that by the sqrt of 365 to calculate the volatility.

 

[Mohammad Basem]

Try replacing

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]cor1 = WorksheetFunction.StDev(INFO.Range(INFO.Cells(2 + i, 4), INFO.Cells(rngvol + i + 1, 4)).Value)[/COLOR][/SIZE][/FONT]

with

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]With Worksheets("info")
    cor1 = WorksheetFunction.StDev(.Range(.Cells(2 + i, 4), .Cells(rngvol + i + 1, 4)).Value)
End With[/COLOR][/SIZE][/FONT]

 

[ajc5382]

Try replacing

[FONT=Consolas][SIZE=2][COLOR=Navy]cor1 = WorksheetFunction.StDev(INFO.Range(INFO.Cells(2 + i, 4), INFO.Cells(rngvol + i + 1, 4)).Value)[/COLOR][/SIZE][/FONT]

with

[FONT=Consolas][SIZE=2][COLOR=Navy]With Worksheets("info")
    cor1 = WorksheetFunction.StDev(.Range(.Cells(2 + i, 4), .Cells(rngvol + i + 1, 4)).Value)
End With[/COLOR][/SIZE][/FONT]

I'll try it tomorrow, thanks so much you've been a huge help. I have a question though, is it possible if the variable wasn't dimmed originally (at all) or was dimmed incorrectly, that this could be the cause of the error.

 

[Mohammad Basem]

I have a question though, is it possible if the variable wasn't dimmed originally (at all) or was dimmed incorrectly, that this could be the cause of the error.

If the variable was not declared (dimmed) it will be considered as variant and this will not affect the process. Still you get accurate results.

If the variable was incorrectly declared (e.g. declared as Long and used as string) it will generate a Type Mismatch error that stops the process.

You can just avoid this unnecessary headache and declare all variables before use. Use Option Explicit on top of the Module or code window to help locating undeclared variables.