Adressing part of an array

[HanniMcPfanni]

Hi

I have defined an array in VBA MyArray(1 To 500). Now I would like to apply a Worksheet function to a part of it; let's say I would like to calculate the standard deviation of element 50 to 100. How do I adress the range ?

X = Application.WorksheetFunction.StDev( ? )

Thanks

 

[MickG]

Hi, On the basis of Range(A1:A10") holding the values 1 to 10, and the following code returning the Sum of "A2 to A5".i.e (ans=14)
I imagine you could apply this for your StDev.

Dim ray
ray = Range("A1:A10")
MsgBox Application.sum(Application.Index(ray, Array(2, 3, 4, 5)))

Mick

 

[HanniMcPfanni]

Thanks.

But the problem is that I don't have the values of my array on the spreadsheet, so I cannot adress them with A1:A10; I need to directly adress MyArray.

 

[MickG]

Hi, The array "Ray" holds the values "A1:A10".
The msgbox return the sum of the Items 2 to 5 within the array "Ray", not from the sheet !!.
Try This

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B)           [/B][/COLOR][COLOR=royalblue][B]Col(C)              [/B][/COLOR]
1.      1345    36.8544434227408  =STDEV(A2,A3,A5,A6) 
2.      1301    27.4639157198405  =STDEV(A1:A10)      
3.      1368                                          
4.      1322                                          
5.      1310                                          
6.      1370                                          
7.      1318                                          
8.      1350                                          
9.      1303                                          
10.     1299

Private Sub CommandButton2_Click()
Dim ray
ray = Range("A1:A10")
'StDev for specific values within array "Ray"
MsgBox Application.StDev(Application.Index(ray, Array(2, 3, 5, 6)))
'StDev for complete array "Ray"
MsgBox Application.StDev(ray)

Regards Mick

 

[HanniMcPfanni]

OK. Then I have to reformulate my question:

How can I set ray without using spreadsheet adresses ?

E.g. Set ray = MyArray(2 to 5) ?

I can use

X= Application.WorksheetFunction.StDevP(MyArray(2), MyArray(5))

but then VBA only takes two values instead of everything inbetween

 

[MickG]

How do you propose to fill the array within the code ???
Mick

 

[HanniMcPfanni]

First of all: thanks for you persistency

This is what I want to do:

I have a spreadsheet with 10 000 values (you are right on this one)
I fill them in an array
I calculate the standard deviation for every rolling 250, that is

for i=1 to 9 750
stdev(i to i+250)
next i

If there is a way to do that with spreadsheet references, I am happy to learn about it; it just seemed complicated to me.

Thnaks again

 

[MickG]

Try this with your data in column "A"
See code for Details:-

[COLOR=navy]Sub[/COLOR] MG12Apr17
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] C [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
'[COLOR=green][B]Steps through Column A to row 10000 in steps of 250[/B][/COLOR]
[COLOR=navy]For[/COLOR] n = 1 To 10000 [COLOR=navy]Step[/COLOR] 250
 '[COLOR=green][B]Set the Range variable "Rng" to Each set of 250 cells[/B][/COLOR]
    [COLOR=navy]Set[/COLOR] Rng = Range("A" & n).Resize(250)
     C = C + 1
     '[COLOR=green][B]Places the results in Column "B"[/B][/COLOR]
        Range("B" & C) = Application.StDev(Rng)
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[HanniMcPfanni]

I'll check that out. Thanks a lot.