Newbie problem: storing in an array

Hello, I'm new to VBA. I've some questions regarding to array.

Here's the code linked with excel spreadsheet:

Function Function1(DailyClose, EFBillsYield)

TradingDays = Application.WorksheetFunction.Count(DailyClose)

For i_cnt = 1 To TradingDays - 1
DailyReturn = Application.WorksheetFunction.Ln(DailyClose(i_cnt) / DailyClose(i_cnt + 1))
Next i_cnt

AnnualReturn = Application.WorksheetFunction.Average(DailyReturn) * TradingDays
AnnualVolatility = Application.WorksheetFunction.StDev(DailyReturn) * Sqr(TradingDays)
RiskFreeRate = Application.WorksheetFunction.Ln(1 + EFBillsYield)

Function1 = (AnnualReturn - RiskFreeRate) / AnnualVolatility

End Function

When I enter the parameters DailyClose (which is an array of numeric), and EFBillsYield, which is a numeric also, the function will calculate the DailyReturn (in array) for each array element in DailyClose.

The problem is, I don't know how to catch the DailyReturn for each DailyClose element. The above code only loops and calculates DailyReturn one by one, but not storing it as an array.

How should it be modified so that the DailyReturn can be numeric array also, so that it can perform average and SD functions below the loop

Thanks,
Roy

Answer this question

Newbie problem: storing in an array

Mega_x
Hi

try the below:

TradingDays = Application.WorksheetFunction.Count(DailyClose)

Dim DailyReturn(TradingDays-1) as double

For i_cnt = 1 To TradingDays - 1
DailyReturn(i_cnt) = Application.WorksheetFunction.Ln(DailyClose(i_cnt) / DailyClose(i_cnt + 1))
Next i_cnt
Newbie problem: storing in an array

Answer this question