VBA - Use part of an array

[ojhawkins]

Hi All,

I have an array basically like this

Dim arr(1 to 500)

For i = 1 to 500

Is there any way I can use part of it to make a calculation?

For example I want to calculate the average on the first 250 values, so i=1 to i=250?

Any help is greatly appreciated

Cheers

 

[Rick Rothstein]

Hi All,

I have an array basically like this

Dim arr(1 to 500)

For i = 1 to 500

Is there any way I can use part of it to make a calculation?

For example I want to calculate the average on the first 250 values, so i=1 to i=250?

Any help is greatly appreciated

Cheers

First 250 values? It sounds like you have simplified your request for us. Doing that would pretty much mean any solution we give you would not be easily modifiable by you to handle what I suspect is a more complex selection of cells. Can you verify that all you really want is to average the first 250 cells and nothing more? If you do want something more, please tell us exactly what so we can give you a usable solution.

 

[ojhawkins]

Yes correct,

I am calculating stock price movement and then want to calculate both 130 day and 260 day implied Vol so instead or creating 2 arrays of 130 and 260 length

I want to store 1 array and use it to do both calculations

below is the calculation which I can get success fully but need to create 2 arrays or erase the Array for the second calculation

arr(i) = WorksheetFunction.Ln(Sheets(MetWScount).Cells(i, 7).Offset(1, 0) / Sheets(MetWScount).Cells(i, 7).Offset(2, 0))

tdv130 = WorksheetFunction.StDev(arr) * (260) ^ 0.5

 

[Firefly2012]

Howdy

You can use Index to return the elements you need:

Msgbox "Average of first 250 elements is " & Application.Average(Application.Index(arr,Evaluate("ROW(1:250)")))

MsgBox "Average of next 250 elements is " & Application.Average(Application.Index(arr,Evaluate("ROW(251:500)")))

I'm not sure this would be any faster at the end of the day than if you simply used two arrays though - Rick may be better able to answer that than I.