VBA – How to Find TaxiCab Numbers in Excel – Quick Search in Array

Quick Search for value in Array. In VBA this is sometimes considered problem, as far as people tend to loop through the whole array for the search. There is a better way to do it, with System.Collections.ArrayList, which is not exactly an array, but it can be searched through a built-in function. 🙂 Thus, to show the “quick search for a value in array”, the code below solves a problem for finding TaxiCab numbers.

TaxiCab numbers are numbers expressible as the sum of two [positive] cubes in two different ways. The smallest one is 1729, which is expressible through:

1^3 + 12^3 
9^3 + 10^3

Thus, I was thinking that there should be a fast way to calculate the first few numbers with System.Collections.ArrayList as far as I have written about a helper to sort arrays and range sorter. So, as far as it can be obviously solved with a N^2 complexity algorithm it is quite easy to be done:

Public Sub TaxiCabNumber()
    
    Dim a           As Long
    Dim b           As Long
    Dim lastNumber  As Long
    Dim cnt         As Long
    
    lastNumber = 200
    
    Dim arrList     As Object
    Set arrList = CreateObject("System.Collections.ArrayList")

    For a = 1 To lastNumber
        For b = a + 1 To lastNumber            
            Dim current As String
            current = a ^ 3 + b ^ 3            
            'Debug.Assert (a <> 1 Or b <> 12) And (a <> 9 Or b <> 10)            
            If arrList.contains(current) Then
                Debug.Print current
            Else
                arrList.Add (current)
            End If            
            cnt = cnt + 1
        Next b
    Next a    
End Sub

As you see, on the code above the System.Collections.ArrayList uses ArrayList.Contains(Element) just as nicely as it would be using it in C# or any other “fancy” language.