Value Error when using UBound for ParamArray

Question

I am trying to write a function that calculates the minimum distance of one zip code to the other. The function should take in the longitude and latitude of one zip code, and then a 2-D array with all longitude and latitude info of zip codes. Here is the function I wrote:

Public Function PassArray(Longitude As Double, Latitude As Double, ParamArray varValues() As Variant) As Double
    Dim arr() As Variant
    Dim x As Long
    For x = 1 To UBound(varValues(0), 1)
        ReDim Preserve arr(x)
        arr(UBound(arr)) = Sqr((Longitude - varValues(0)(x, 1)) ^ 2 + (Latitude - varValues(0)(x, 2)) ^ 2)
    Next x
    PassArray = WorksheetFunction.Min(arr)

I got #Value! error when I tried to use this function. I checked every step and it seems that the UBound(varValues(0), 1) is causing the problem. When I try UBound(varValues) it returns 0, which I guess it is the first dimension upper bound of the parameter array?

I cannot get why UBound(varValues(0), 1) would not work. I thought it should return my longitude & latitude array's last row number.

Also keep in mind that `ParamArray` is zero indexed I believe. Your `for` loop starts at element 1, so it will always miss the first element in the array. If only a single element is passed, it will not enter into the `for` loop. — basodre, Jan 03 '19 at 20:50
Drop the `ParamArray` - it's not doing what you think it does, and is only confusing things in this instance: if you're always just passing a single 2D array as the 3rd parameter, then there's only ever going to be a single element in `varValues`, at index `0`, holding your 2D array: `ParamArray` is basically nesting your 2D array into a single variant array, and that isn't needed. Removing `ParamArray` will simplify everything (i.e. `UBound(varValues, 1)` instead of having `varValues(0)` all over the place). Also consider intaking a `Range` to make it even simpler; *then* refactor to array. — Mathieu Guindon, Jan 03 '19 at 21:11

score 4 · Accepted Answer · answered Jan 03 '19 at 21:41

Consider @Mathieu Guindon's comment and go along those lines:

Option Explicit

'ASSUMPTION: coordinatesArray is a 2D array with rows in dimension 1 and columns in dimension 2.
Public Function PassArray(longitude As Double, latitude As Double, coordinatesArray As Variant) As Double
    Dim rowLowerBound As Long
    Dim rowUpperBound As Long
    Dim x As Long

    'We're looking at coordinatesArray's first dimension (rows).
    'Let's consider both the lower and upper bounds, so as to adapt to the
    'configuration of coordinatesArray.
    rowLowerBound = LBound(coordinatesArray, 1)
    rowUpperBound = UBound(coordinatesArray, 1)

    'Dim arr upfront; this will be way faster than redimming within the loop.
    ReDim arr(rowLowerBound To rowUpperBound) As Double

    For x = rowLowerBound To rowUpperBound
       'Your calculations go here.
       'You can access coordinatesArray elements like so:
       'coordinatesArray(x, 1) for row x, column 1, and
       'coordinatesArray(x, 2) for row x, column 2.
       arr(x) = Sqr((longitude - coordinatesArray(x, 1)) ^ 2 + (latitude - coordinatesArray(x, 2)) ^ 2)
    Next x

    'Note that Application.WorksheetFunction.Min doesn't seem to care
    'whether arr is zero, one or n-based.
    PassArray = Application.WorksheetFunction.Min(arr)
End Function

Note that I can't vouch for your distance calculation; might work for cartesian coordinates, but for not for longitude/latitude.

Value Error when using UBound for ParamArray

1 Answers1