Worksheet functions in VBA on arrays

Radiomir

New Member
Joined
Oct 9, 2007
Messages
15
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Radiomir,

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
 
Upvote 0
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
 
Upvote 0
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)

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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top