Thread: Worksheet functions in VBA on arrays Thanks: 0 Likes: 0

1. Worksheet functions in VBA on arrays

Hi all,

I'm having a problem with using worksheet functions on arrays stored in vba. I use the following code to generate my array from results of iterations in Excel:

Code:
Sub PopArray()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Runs As Integer
Dim Cols As Integer
Dim Y As Double
Dim X As Double
Dim L1A As Variant

Cols = 11
Runs = Range("Runs").Value

ReDim L1A(Runs, Cols)

For i = 1 To Runs
Application.CalculateFull

For j = 1 To Cols
X = Range("L1_START").Offset(0, j).Value
L1A(i, j) = X

Next j

Next i

Y = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))

End Sub
I generate the two dimensional array without any issues - the problems start when I try and get the average value in the first column (last code line). I cannot seem to get the result to stay as a value.

Endgame is to generate three statistics from the array for each column: The average, the 25th Percentile and the 75th Percentile, percentiles using the LARGE function. These then to be stored as arrays and pasted into Excel.

Can anybody give me a hand finsihing off the code?

Many thanks  Reply With Quote

2. If the three columns are 11, 12, and 13.

I am not sure of your request using the "Lagre" function (see code):
'LARGE(array,k)
'the 25th Percentile using the LARGE function.
'Percentile1_25 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
'the 75th Percentile using the LARGE function.
'Percentile1_75 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)

Try this:
Code:
Option Explicit
Sub PopArray()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Runs As Integer
Dim Cols As Integer
Dim Y As Double
Dim X As Double
Dim L1A As Variant

Dim Average1 As Double
Dim Average2 As Double
Dim Average3 As Double
Dim Percentile1_25 As Double
Dim Percentile1_75 As Double
Dim Percentile2_25 As Double
Dim Percentile2_75 As Double
Dim Percentile3_25 As Double
Dim Percentile3_75 As Double

Average1 = 0
Average2 = 0
Average3 = 0
Percentile1_25 = 0
Percentile1_75 = 0
Percentile2_25 = 0
Percentile2_75 = 0
Percentile3_25 = 0
Percentile3_75 = 0

'If the three columns are 11, 12, 13
For Cols = 11 To 13 Step 1
Runs = Range("Runs").Value
ReDim L1A(Runs, Cols)
For i = 1 To Runs
Application.CalculateFull
For j = 1 To Cols
X = Range("L1_START").Offset(0, j).Value
L1A(i, j) = X
Next j
Next i
If Cols = 11 Then
Average1 = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))
'LARGE(array,k)
'the 25th Percentile using the LARGE function.
'Percentile1_25 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
'the 75th Percentile using the LARGE function.
'Percentile1_75 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
ElseIf Cols = 12 Then
Average2 = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))
'the 25th Percentile using the LARGE function.
'Percentile1_25 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
'the 75th Percentile using the LARGE function.
'Percentile2_75 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
ElseIf Cols = 13 Then
Average3 = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))
'LARGE(array,k)
'the 25th Percentile using the LARGE function.
'Percentile3_25 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
'the 75th Percentile using the LARGE function.
'Percentile3_75 = WorksheetFunction.Large(L1A(1, 1), L1A(1, Runs), ?????)
End If
Next Cols

'Your code to move the three Averages and Percentiles

End Sub

Have a great day,
Stan  Reply With Quote

3. Re: Worksheet functions in VBA on arrays

No need to loop through individual cells to move the contents into a VB array. Also, the INDEX worksheet function when used with an argument of 0 indexes an entire row/column. So,
Code:
Dim Arr As Variant
Arr = Cells(1, 1).Resize(Runs, Cols).Value
With Application.WorksheetFunction
MsgBox .Percentile(.Index(Arr, 0, 1), 0.25)
End With
Edit: Store all the results in an appropriate array and 'reverse' the above method to transfer the result into a worksheet range.

Hi all,

I'm having a problem with using worksheet functions on arrays stored in vba. I use the following code to generate my array from results of iterations in Excel:

Code:
Sub PopArray()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Runs As Integer
Dim Cols As Integer
Dim Y As Double
Dim X As Double
Dim L1A As Variant

Cols = 11
Runs = Range("Runs").Value

ReDim L1A(Runs, Cols)

For i = 1 To Runs
Application.CalculateFull

For j = 1 To Cols
X = Range("L1_START").Offset(0, j).Value
L1A(i, j) = X

Next j

Next i

Y = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))

End Sub
I generate the two dimensional array without any issues - the problems start when I try and get the average value in the first column (last code line). I cannot seem to get the result to stay as a value.

Endgame is to generate three statistics from the array for each column: The average, the 25th Percentile and the 75th Percentile, percentiles using the LARGE function. These then to be stored as arrays and pasted into Excel.

Can anybody give me a hand finsihing off the code?

Many thanks  Reply With Quote

4. Thanks guys, used a variation of your idea which worked well!  Reply With Quote

5. Re: Worksheet functions in VBA on arrays

Hi All,

I am running a percentile function on an array of data (very similar to the current situation)
My array consists of Past 5 years of data & i am looking to calculate percentile on values for each year.

Is there a way by which i cam apply something like .Percentile(Array(365 to 730), 25%) ? (i.e. this is like calculating percentile for Second Year) Originally Posted by tusharm No need to loop through individual cells to move the contents into a VB array. Also, the INDEX worksheet function when used with an argument of 0 indexes an entire row/column. So,
Code:
Dim Arr As Variant
Arr = Cells(1, 1).Resize(Runs, Cols).Value
With Application.WorksheetFunction
MsgBox .Percentile(.Index(Arr, 0, 1), 0.25)
End With
Edit: Store all the results in an appropriate array and 'reverse' the above method to transfer the result into a worksheet range.  Reply With Quote

User Tag List Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•