# 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.

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.

``` 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.```