Passing an Array or Range through a function in VBA

Question

So I want to make a basic function that takes an average of values that I highlight in Excel. I am well aware there is already a built-in function in Excel for this but I am trying to make one as practice.

My problem is I am not sure how to pass a range and then call on specific elements in the Range.

Below is the pseudo code I've been playing around with. I understand it may be horribly written. I am a beginner and I just want to get some practice.

Function averagetest(range As Range) '<------(Is this how I pass a Range into a function?)
      Dim N as Integer
      Dim i as Integer
      Dim average as Double
      average = 0 
      N = LengthofRange '<--------- (Is there a way to get the length of the 
      range like UBound or LBound for an array?)
      Do Until i = LengthofRange
          average = average + Range(i, i+1) '<--------(Is this how you call a 
          specific element in the range? I'm just adding every element in the 
          Range)
          i = i + 1
      Loop
average = average/N

End Function

Mathieu Guindon · Accepted Answer · 2017-05-04 18:39:35Z

You can't assume a Range is going to be contiguous, nor can you assume a Range is going to be horizontal, nor vertical.

A Range is a collection of objects, so you iterate it with a For Each loop for optimal performance.

Assuming the function is meant to be used as a UDF worksheet function, and therefore is defined in a standard module (.bas):

Public Function AverageTest(ByVal target As Range) As Variant

    Dim total As Double
    Dim count As Double

    Dim cell As Range
    For Each cell In target
        If IsNumeric(cell.Value) Then
            total = total + cell.Value
            count = count + 1
        'Else
        '    AverageTest = CVErr(xlErrValue)
        '    Exit Function
        End If
    Next

    If count = 0 Then
        AverageTest = CVErr(xlErrDiv0)
    Else
        AverageTest = total / count
    End If

End Function

Note:

Parameter is passed ByVal, and isn't named after an existing type (Range); we don't need a reference to the range pointer, a copy of it is good enough.
Function is explicitly Public, and has an explicit return type (Variant).
Function returns a Variant, so as to return a Double result in the "happy path", or an appropriate Error value (#Div/0!) when applicable.
Function is only counting numeric cells, which means it works even if the target range contains error values. The commented-out code would bail out and return a #VALUE! error if a non-numeric value is encountered.

How you "pass the range" is the caller's problem. There are many ways you can do this - from an Excel formula:

=AverageTest(A1:A10)
=AverageTest(A1:B12,F4:L4)

You can also use it in VBA code:

foo = Module1.AverageTest(ActiveSheet.Range("A1:D10"))

David Zemens · Accepted Answer · 2017-05-04 18:34:51Z

Do not use range as a variable.

Then you can use rows.Count or Columns.Count to get the extent

Function averagetest(rng As Range)
      Dim N as Integer
      Dim i as Integer
      Dim average as Double
      average = 0 
      N = rng.rows.count 
      For  i = 1 to N 'use For loop
          average = average + rng.cells(i,1)'Cells will work here
      Next i
      averagetest= average/N

End Function

Or you can do this -- there's not really any need to iterate over the count of cells, when you can just iterate over Each cell in the rng.Cells collection. I would also change the variable name from average (which is misleading) to something a bit more descriptive, like total:

Option Explicit
Function averagetest(rng As Range)
    Dim cl As Range
    Dim total As Double

    For Each cl In rng.Cells
        total = total + cl.Value
    Next
    averagetest = total / rng.Cells.Count

End Function

As a bonus, this latter method would work on a 2-dimensional range as well.

Note that this will treat empty cells as 0-values (the AVERAGE worksheet function ignores empty cells, so your results may vary) and it will raise an error if there are non-numeric values in the range.

Collectives™ on Stack Overflow

Passing an Array or Range through a function in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related