Strange #VALUE! error

Question

Please have a look at this error:

Here are two functions:

Public Function CompareExtra(r1 As Range, r2 As Range) As Long
   Dim r As Integer, v As Variant, v2 As Variant
   Dim rr1() As String
   Dim rr As Range
   rr1 = Split(r1, ",")
   For r = LBound(rr1) To UBound(rr1)
      v = --Trim(rr1(r))
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then CompareExtra = CompareExtra + 1
         Next rr
      End If
   Next r
End Function


Function getLeast(rng As Range, leastXValues As Long, maxValue As Long, displacement As Long, Optional Sorted As Boolean = True) As String
  Dim d As Object, AL As Object
  Dim i As Long, j As Long
  Dim a As Variant

  Set d = CreateObject("Scripting.Dictionary")
  Set AL = CreateObject("System.Collections.ArrayList")
  a = rng.Value
  i = UBound(a, 1)
  j = UBound(a, 2)
  Do
    If a(i, j) <> 0 Then d(a(i, j)) = a(i, j)
    j = j - 1
    If j = 0 Then
      j = UBound(a, 2)
      i = i - 1
    End If
  Loop Until i = 0
  For i = 1 To maxValue
    d(i) = i
  Next i
  a = d.Items()
  For i = UBound(a) - displacement To UBound(a) - displacement - leastXValues + 1 Step -1
    AL.Add a(i)
  Next i
  If Sorted Then AL.Sort
  getLeast = Join(AL.ToArray, ", ")
End Function

I can't get why if I take the result of getLeast() from a separate cell - everything works, but if I add it to CompareExtra() directly, it throws an error..

Because `getLeast` function returns String whereas `CompareExtra` function accepts range arguments. When you set the value of a cell with `getLeast` function, it is setting the default `Value` property. And using this cell in `CompareExtra` function doesn't create any problem because Cells are Range ojbects as well. — izce, Feb 08 '16 at 07:34
Thanks very much for clear explanation. Could you please suggest how to fix it, if that's okey — ArthurV, Feb 08 '16 at 07:41
Public Function CompareExtra(r1 As String, r2 As Range) should work? — ArthurV, Feb 08 '16 at 07:44
Try to overload `CompareExtra` function or return the Range object that represents the Cell containing the least value from `getLeast`, if possible. — izce, Feb 08 '16 at 07:46
I'm not proficient that much yet to be able to do it myself.. — ArthurV, Feb 08 '16 at 07:58

score 1 · Accepted Answer · answered Feb 08 '16 at 09:10

1

Simple solution is to use variants:

Public Function CompareExtra(r1 As Variant, r2 As Variant) As Long
Function getLeast(rng As Range, leastXValues As Long, maxValue As Long, displacement As Long, Optional Sorted As Boolean = True) As Variant

answered Feb 08 '16 at 09:10

Charles Williams

23,121
5
38
38

Strange #VALUE! error

1 Answers1