Issue

This Content is from Stack Overflow. Question asked by Rabbiiiiit

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.

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). Could anyone help me edit this code?

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

Many thanks!

Solution

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.

An object (which is presumed to be an excel range)
An Array, to allow Range(xxx).value to be used as a parameter
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.

This Question was asked in StackOverflow by Rabbiiiiit and Answered by freeflow It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.