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 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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.