Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
VlookupS-Alan Beban | Excel Worksheet Functions | |||
Vlookups array function by Alan Beban | Excel Worksheet Functions | |||
Vlookups array function by Alan Beban | Excel Worksheet Functions | |||
Problem with Alan Beban's ResizeArray | Excel Programming |