How do I slice a vector in Excel VBA?

Question

I want to take the average value of only a few items in an array in Microsoft Excel-VBA. For Example, take the average, for every pair of numbers in the vector: 45 21 63 12 19. The major problem here is, that I want to be able to make the quantity of numbers dynamic so that it can also always take the average of every 3 numbers.

I tried using the Index function, or the Offset function, but both did not work.

Dim arrMarks(1 To 5) As Long

arrMarks(1) = 45
arrMarks(2) = 21
arrMarks(3) = 63
arrMarks(4) = 12
arrMarks(5) = 19

length = UBound(arrMarks, 1) - LBound(arrMarks, 1) + 1

' take the average of m numbers

m = 3
m_length = length - (m-1)

Dim arravg(1 To m_length)

For i = 1 To m_length
    If (i + m) <= length Then
        arravg(i) = Application.WorksheetFunction.Average(arrMarks.Offset(m, 0))
    End If
Next i

This example just returns an error.

Desired outcome:

Values of arravg being:

Average(45, 21, 63) 
Average(21, 63, 12) 
Average(63, 12, 19)

I hope to make the code in a way that m can be filled with any number (as long as m < length) and make it work. Do you have any idea?

Your issue is not clear to me. **Edit** your question to show sample inputs **AND** desired output. — Ron Rosenfeld, Jun 11 '19 at 11:10
Posted a solution using the advanced possiblities of the `Application.Index` function. Side note: It isn't possible to apply the `.Offset` method to an array as you did in OP :-; — T.M., Jun 11 '19 at 11:29
FYI Recommended reading regarding [Some pecularities of the Application.Index function](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153#51714153) — T.M., Jun 11 '19 at 11:52

score 1 · Accepted Answer · answered Jun 11 '19 at 11:26

Get averages in consecutive groups of array elements

Allows flexible group sizes

Example Call

The only Change made to your post is that I use a 2-dim initial array arrMarks with the possible Advantage that you can assign data easily from a given range (row).

Sub ExampleCall()
ReDim arrMarks(1 To 1, 1 To 5)
arrMarks(1, 1) = 45
arrMarks(1, 2) = 21
arrMarks(1, 3) = 63
arrMarks(1, 4) = 12
arrMarks(1, 5) = 19

Dim arrAvg
arrAvg = getAverages(arrMarks, 3)           ' calculate averages in groups of 3 elements
Debug.Print "~> " & UBound(arrAvg) - LBound(arrAvg) + 1 & " averages: " & Join(arrAvg, "|")

End Sub

Results in …

1) current group: 45, 21, 63               43 
2) current group: 21, 63, 12               32 
3) current group: 63, 12, 19               31,3333333333333 
~> 3 averages: 43|32|31,3333333333333

Main function getAverages()

Function getAverages(arrData, Optional groupSize As Long = 2) As Variant()
' count array elements
  Dim lg As Long
  lg = UBound(arrData, 2) - LBound(arrData, 2) + 1

' take the average of m numbers
  Dim m_lg As Long
  m_lg = lg - (groupSize - 1)

  ReDim arrAvg(1 To m_lg)
  Dim temp, i As Long
  For i = 1 To m_lg
      If (i + groupSize) <= lg + 1 Then
          temp = Application.Index(arrData, Array(1), getElements(i, groupSize))
          arrAvg(i) = Application.WorksheetFunction.Average(temp)
          Debug.Print i & ") current group: " & Join(temp, ", "), arrAvg(i)
      End If
  Next i
' return averages
  getAverages = arrAvg
End Function

Helper function holding the current element numbers

Function getElements(ByVal start As Long, Optional ByVal groupSize As Long = 2)
ReDim temp(0 To groupSize - 1)
Dim i&
For i = 0 To groupSize - 1
    temp(i) = start + i
Next i
getElements = temp
End Function

How do I slice a vector in Excel VBA?

1 Answers1