Problem with a function that calls itself

[vsoler]

Hi,

I created a function that emulates the Excel function LOOKUP but that
does not require the lookup table to be sorted.

It works fine, except when the first parameter is a call to itself,
that is, when nesting the function.

For example:

In M27:M31 if I input the array function:
={vslookup(C6:C10;H6:H8;J6:J8)} 'it works fine

In N27:N31 if I input the array function:
={vslookup(M27:M31;W26:W27;X26:X27)} 'it works fine (the first
argument is the result of the previous function)

However, if I nest the 2 formulas in O27:O31::
={vslookup(vslookup(C6:C10;H6:H8;J6:J8);W26:W27;X26:X27)} ' I get
#VALUE!

What is the problem with nesting?


Function VSLookup(a, b, c)
' a, b y c son rangos verticales
' el número de filas de b y c es el mismo
Dim i, j, nrows
ReDim T(a.Rows.Count, 1)
For i = 1 To a.Rows.Count
For j = 1 To b.Rows.Count
If a(i, 1) = b(j, 1) Then
T(i, 1) = c(j, 1)
GoTo aqui
End If
Next j
T(b.Rows.Count, 1) = CVErr(xlErrNA)

aqui:
Next i
VSLookup = T
End Function

 

[Dave Peterson]

I think you have a couple of problems in your nested function.

#1. In your first nest, you're passing 3 ranges (a, b, c are all range
variables). The second time through, that a variable is not a range--it's an
array (a single column vector).

#2. You're comparing the value of a cell against that xlerrna and that won't
work.

I don't quite understand what your function is doing, but I think it would be
easiest to process the data as elements of an array--instead of cells in a
range.

I think I'd pass a variant and check to see if it's a range. If it's a range,
then convert it to an array.

I didn't do that here (I only did the first parm).

#3. I don't like the Goto. There are ways around it. So I changed it.

This may get you closer:

Option Explicit
Function VSLookup(a As Variant, b As Range, c As Range) As Variant

Dim i As Long
Dim j As Long
Dim FoundAMatch As Boolean
Dim RowsInB As Long

If TypeName(a) = "Range" Then
'make it a single column array
a = Application.Transpose(a.Value)
End If

RowsInB = b.Rows.Count

ReDim T(LBound(a) To UBound(a))

For i = LBound(a) To UBound(a)
FoundAMatch = False
For j = 1 To RowsInB
If a(i) = b(j, 1) Then
T(i) = c(j, 1)
FoundAMatch = True
Exit For
End If
Next j
If FoundAMatch = True Then
'do nothing
Else
T(RowsInB) = "xlErrNA"
End If
Next i
VSLookup = T

End Function

 

[vsoler]

I think you have a couple of problems in your nested function.

#1.  In your first nest, you're passing 3 ranges (a, b, c are all range
variables).  The second time through, that a variable is not a range--it's an
array (a single column vector).

#2.  You're comparing the value of a cell against that xlerrna and thatwon't
work.

I don't quite understand what your function is doing, but I think it would be
easiest to process the data as elements of an array--instead of cells in a
range.  

I think I'd pass a variant and check to see if it's a range.  If it's arange,
then convert it to an array.

I didn't do that here (I only did the first parm).

#3.  I don't like the Goto.  There are ways around it.  So I changed it.

This may get you closer:

Option Explicit
Function VSLookup(a As Variant, b As Range, c As Range) As Variant

    Dim i As Long
    Dim j As Long
    Dim FoundAMatch As Boolean
    Dim RowsInB As Long

    If TypeName(a) = "Range" Then
        'make it a single column array
        a = Application.Transpose(a.Value)
    End If

    RowsInB = b.Rows.Count

    ReDim T(LBound(a) To UBound(a))

    For i = LBound(a) To UBound(a)
        FoundAMatch = False
        For j = 1 To RowsInB
            If a(i) = b(j, 1) Then
                T(i) = c(j, 1)
                FoundAMatch = True
                Exit For
            End If
        Next j
        If FoundAMatch = True Then
            'do nothing
        Else
            T(RowsInB) = "xlErrNA"
        End If
    Next i
    VSLookup = T

End Function

Thank you Dave, I'm studying your code that seems to me of great help.

#1 - Yes, the origin of my problem was the confusion between a Range
and an Array. The Function has to be able to handle either one, and
that's what your code does.

#2 - I'm not comparing two values, as you say. I'm just assigning an
#N/A value to an element of my T array, because a value of the
original "a" Range was not found in the "b" lookup table.

#3 - I accept your comment on the GoTo.

What I am going to do now is to test your code. However, there is one
small piece of it that I do not understand. If my original "a" Range
is already a vertical column, why do I have to transpose it? I would
like that it remains a vertical array. Perhaps you can explain me
that.

Thank you for your help Dave.

Vicente Soler

 

[Dave Peterson]

Create a new procedure:

Dim myRng as Range
dim myVal1 as variant
dim myVal2 as variant
Set myrng = activesheet.range("A1:A4")
myval1 = myrng.value
myval2 = application.transpose(myrng.value)
Stop

Set a watch for myVal1 and myVal2.
run the code

When the procedure stops, you can look at each of the watched variables.

You'll see that myVal1 is a 4 row by 1 column (2 dimensional) array.
But myval2 is a single dimension array/vector.

It would by like defining two vars:

dim Var1(1 to 4, 1 to 1) as variant
and
dim Var2(1 to 4) as variant