Hi Team
I'm trying to write an efficient search function with the following definitions:
I have written two versions, one for strings and one for numbers. The one for numbers is below.
Can anyone increase the efficiency of this code?
Function FndAryNmbr(dFindme As Double, vaArray As Variant, lArrayCol As Long) As Long
'searches for an element in an array column. returns the elementnumber
'returns the element number if found. returns 0 if not
'the array column being searched must be sorted
Dim llsplit As Long
Dim llSearchElement As Long
llSearchElement = WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2, 0)
llsplit = 1
Do While dFindme <> vaArray(llSearchElement, lArrayCol)
llsplit = llsplit + 1
If dFindme > vaArray(llSearchElement, lArrayCol) Then
llSearchElement = llSearchElement + WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ llsplit, 0)
Else
llSearchElement = llSearchElement - WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ llsplit, 0)
End If
If (llSearchElement > UBound(vaArray, 1)) Then
llSearchElement = UBound(vaArray, 1)
End If
If (llSearchElement < 1) Then
llSearchElement = 1
End If
If (WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ llsplit, 0) = 1) And _
(WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ (llsplit - 1), 0) = 1) Then
FndAryNmbr = 0
Exit Function
End If
Loop
FndAryNmbr = llSearchElement
End Function
I'm trying to write an efficient search function with the following definitions:
- Searches one SORTED column (lArrayCol)
- looks for one element (dFindme)
- in a 2 dimensional array(vaArray)
- Returns 0 if element not found
- returns element number in column if found
I have written two versions, one for strings and one for numbers. The one for numbers is below.
Can anyone increase the efficiency of this code?
Function FndAryNmbr(dFindme As Double, vaArray As Variant, lArrayCol As Long) As Long
'searches for an element in an array column. returns the elementnumber
'returns the element number if found. returns 0 if not
'the array column being searched must be sorted
Dim llsplit As Long
Dim llSearchElement As Long
llSearchElement = WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2, 0)
llsplit = 1
Do While dFindme <> vaArray(llSearchElement, lArrayCol)
llsplit = llsplit + 1
If dFindme > vaArray(llSearchElement, lArrayCol) Then
llSearchElement = llSearchElement + WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ llsplit, 0)
Else
llSearchElement = llSearchElement - WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ llsplit, 0)
End If
If (llSearchElement > UBound(vaArray, 1)) Then
llSearchElement = UBound(vaArray, 1)
End If
If (llSearchElement < 1) Then
llSearchElement = 1
End If
If (WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ llsplit, 0) = 1) And _
(WorksheetFunction.RoundUp(UBound(vaArray, 1) / 2 ^ (llsplit - 1), 0) = 1) Then
FndAryNmbr = 0
Exit Function
End If
Loop
FndAryNmbr = llSearchElement
End Function