VBA Excel: Put double type values, cell ranges, and separated cells into the same parameter of a VBA function

Rabbiiiiit 0
Viewed 37

I used VBA to write a homemade function to calculate the length of a vector (Euclidean distance), and currently it works for any range selected for function parameter.

Function VectorLength(Numbers1 As Range) As Double

     Dim i As Range
     Dim val As Double
     For Each i In Numbers1
         val = val + i * i
     Next

     VectorLength = Math.Sqr(val)

End Function

Here, I want to generalize it to work with separate cells (non-connected cells, e.g. A3:A5, A7, A9) or double type values (e.g. 3.02, 1E-5, A3:A5).

But, for example, when I try to change to separate cells where I need to set the number of input parameters as variant, I used ParamArray. However, the code below comes to an error when running.

Function VectorLength2(ParamArray Numbers2() As Variant) As Double

    Dim j As Long
    Dim k As Variant
    For j = LBound(Numbers2) To UBound(Numbers2)
        For Each k In Numbers2(j)
            val = val + k * k
        Next k
    Next j

    VectorLength = Math.Sqr(val)

End Function

Well, my main aim is to imitate excel pre-defined SUM or AVERAGE function, with the input parameters being any of the double type values, separate cells or range.

Many thanks!

vba excel
1 Answers

The code below will point you towards a solution. You basic problem is that you wish to have multiple different type allowed in your paramarray. VBA can suport this through the Variant type BUT you have the responsibility of determining what happens for each different type.

The code below is written to differentiate three different type groups.

  1. An object (which is presumed to be an excel range)
  2. An Array, to allow Range(xxx).value to be used as a parameter
  3. Anything that is a single value that can be evaluated as a number
Option Explicit

Function VectorLength(ParamArray ipNumbers() As Variant) As Double
   
    Dim myResult As Double
    Dim myVal As Variant
    
    Dim myItem As Variant
    For Each myItem In ipNumbers
    
        Select Case True
        
            ' It is assumed that any object passed to the method is an excel.range
            Case VBA.IsObject(myItem)
            
                For Each myVal In myItem
                    myResult = myResult + CDbl(myVal) * CDbl(myVal)
                Next
                
                
             Case VBA.IsArray(myItem)
             
                ' the code is the same as for ISObjewct but
                ' a separate case statement is used in case
                ' you want to do something different with an array item
                
                For Each myVal In myItem
                    myResult = myResult + CDbl(myVal) * CDbl(myVal)
                Next
                
                
            Case VBA.IsNumeric(myItem)
            
                myResult = myResult + CDbl(myItem) * CDbl(myItem)
                
                
            Case Else
            
                Err.Raise 17, "VectorLenth", "A non numeric item was found: '" & CStr(myItem) & ")"
            
        
        End Select
        
    Next

    VectorLength = vba.math.sqr(myResult)

End Function

The above code compiles without error and has no notable Rubberduck code inspections, but otherwise has not been tested.

freeflow0
Related