Alan Beban's vlookups...not for zero based arrays
 

Alan Beban (http://home.pacbell.net/beban/) asked for feedback on his
generously provided array functions. I see that he monitors posts here, so
in the absence of his email, here's a question/statement.

The vlookups function is implicitly assuming the array passed to it is 1
based (and so returns the wrong values for zero based arrays and can crash
in some circumstances). This may be known or not - but it's not noted in the
comments attached to the procedure or the documentation in the
ArrayFunctions workbook.

It fails on the (4) lines:

outputArrayVLookups(i, j) = lookupArray(tempArrayVLookups(i, j),
ColumnNumber)

because the counters go from 1 to p (where p is the number of occurrances of
the lookupValue in the array. For a zero based array, the wrong values are
being selected because the counters are off by 1 (and can fail if the
lookupValue is in the last row of the array.

A simple test is provided below - you need to create a 2 column range to
test it on (you can populate the range however you like - but my test case
was character 'a' in the first column and a set of numbers in the second
column - which will cause it to crash for the zero based array because an
element isn't ).

(make sure you don't have an option base statement in the module)

Sub testVlookups()

Dim av1BasedArray As Variant
av1BasedArray = ActiveCell.CurrentRegion.Value

Dim sIdentifier As String
sIdentifier = Trim$(ActiveCell.Value)

Dim avRetrievedColumn As Variant
avRetrievedColumn = VLookups(sIdentifier, av1BasedArray, 2) ''' note
column 2 for 1 based array

ActiveCell.Offset(0, 4).Resize(UBound(avRetrievedColumn), 1).Value =
avRetrievedColumn

Dim av0BasedArray() As Variant
ReDim av0BasedArray(UBound(av1BasedArray) - 1, 2)

Dim i As Long, j As Long

For i = 1 To UBound(av1BasedArray)
For j = 1 To UBound(av1BasedArray, 2)
av0BasedArray(i - 1, j - 1) = av1BasedArray(i, j)
Next j
Next i

Erase avRetrievedColumn

avRetrievedColumn = VLookups(sIdentifier, av0BasedArray, 1) ''' change
column to 1 for a zero based array

ActiveCell.Offset(0, 5).Resize(UBound(avRetrievedColumn), 1).Value =
avRetrievedColumn

End Sub


(It's straightforward to add a test for a zero based array and modify the 4
lines as required)

cheers,
Christopher

Alan Beban's vlookups...not for zero based arrays
 

Thanks for the feedback. I have emailed you my email address.

Alan Beban

Chris Short wrote:
Alan Beban (http://home.pacbell.net/beban/) asked for feedback on his
generously provided array functions. I see that he monitors posts here, so
in the absence of his email, here's a question/statement.

The vlookups function is implicitly assuming the array passed to it is 1
based (and so returns the wrong values for zero based arrays and can crash
in some circumstances). This may be known or not - but it's not noted in the
comments attached to the procedure or the documentation in the
ArrayFunctions workbook.

It fails on the (4) lines:

outputArrayVLookups(i, j) = lookupArray(tempArrayVLookups(i, j),
ColumnNumber)

because the counters go from 1 to p (where p is the number of occurrances of
the lookupValue in the array. For a zero based array, the wrong values are
being selected because the counters are off by 1 (and can fail if the
lookupValue is in the last row of the array.

A simple test is provided below - you need to create a 2 column range to
test it on (you can populate the range however you like - but my test case
was character 'a' in the first column and a set of numbers in the second
column - which will cause it to crash for the zero based array because an
element isn't ).

(make sure you don't have an option base statement in the module)

Sub testVlookups()

Dim av1BasedArray As Variant
av1BasedArray = ActiveCell.CurrentRegion.Value

Dim sIdentifier As String
sIdentifier = Trim$(ActiveCell.Value)

Dim avRetrievedColumn As Variant
avRetrievedColumn = VLookups(sIdentifier, av1BasedArray, 2) ''' note
column 2 for 1 based array

ActiveCell.Offset(0, 4).Resize(UBound(avRetrievedColumn), 1).Value =
avRetrievedColumn

Dim av0BasedArray() As Variant
ReDim av0BasedArray(UBound(av1BasedArray) - 1, 2)

Dim i As Long, j As Long

For i = 1 To UBound(av1BasedArray)
For j = 1 To UBound(av1BasedArray, 2)
av0BasedArray(i - 1, j - 1) = av1BasedArray(i, j)
Next j
Next i

Erase avRetrievedColumn

avRetrievedColumn = VLookups(sIdentifier, av0BasedArray, 1) ''' change
column to 1 for a zero based array

ActiveCell.Offset(0, 5).Resize(UBound(avRetrievedColumn), 1).Value =
avRetrievedColumn

End Sub


(It's straightforward to add a test for a zero based array and modify the 4
lines as required)

cheers,
Christopher