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


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

     VectorLength = Math.Sqr(val)

End Function

Many thanks!


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)
             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)
            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

    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.

people found this article helpful. What about you?