Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Oct 2007
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default 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

  4. #4
    New Member
    Join Date
    Oct 2007
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys, used a variation of your idea which worked well!

  5. #5
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

    Quote Originally Posted by tusharm View Post
    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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