AdvancedFilter: Parsing the Search String
One of the most difficult parts of creating an advanced filter class is figuring out how to parse the search data and turn it into a 2D array, which Excel can understand. I figured my user interface will be a string. So I started there. In my original implementation of the search algorithm it wasn’t very robust, so this time around I figured I would do it “right.”
Well, right is a relative term. The “right way” is using a recursive descent parser, which seems overly complicated to me. But a simpler approach that I found is a shunting yard algorithm. This will suit my purposes well.
Algorithm Resources
Public Function FindIndexSorted(ByRef oaArray(,) As Object, ByVal IsEqual As Func(Of Object, Integer), ByVal iSearchIndex As Integer) As Integer
Dim iResult As Integer = 0, iTest As Integer, iNext As Integer
Dim iUpperSearch As Integer = oaArray.GetUpperBound(0)
Dim iLowerSearch As Integer = 0
Dim iPrevious As Integer = -1
Try
If IsEqual(oaArray(0, iSearchIndex)) < 0 Then 'If value is the less than the first index then skip Return -1 ElseIf IsEqual(oaArray(iUpperSearch, iSearchIndex)) > 0 Then 'If it is greater than the last index then skip
Return -(iUpperSearch + 1)
Else
iResult = -1
End If
'Find start indexes
Do While iResult = -1
iNext = (iUpperSearch + iLowerSearch) \ 2 'Get new search location
iTest = IsEqual(oaArray(iNext, iSearchIndex))
If iTest > 0 Then 'Get new lower search location
iLowerSearch = iNext
ElseIf iTest < 0 Then 'Get new upper search location iUpperSearch = iNext Else 'If equal find first instance of item iResult = iNext - 1 If iResult > -1 Then
Do While IsEqual(oaArray(iResult, iSearchIndex)) = 0
iResult -= 1
If iResult = -1 Then Exit Do
Loop
End If
iResult += 1
End If
If iPrevious = iNext Then 'Get first item
If IsEqual(oaArray(iLowerSearch, iSearchIndex)) < 0 Then Return -(iLowerSearch - 1)
iTest = IsEqual(oaArray(iUpperSearch, iSearchIndex))
If iTest < 0 Then
Return -(iUpperSearch - 1)
ElseIf iTest = 0 Then
Return iUpperSearch
End If
Else
iPrevious = iNext
End If
Loop
Catch ex As InvalidCastException
iResult = -1
End Try
Return iResult
End Function
'https://en.wikipedia.org/wiki/Binary_search_algorithm#Deferred_detection_of_equality
Public Function BinarySearch(ByRef oaArray(,) As Object, ByVal IsEqual As Func(Of Object, Integer) _
, ByVal iSearchColumnIndex As Integer) As Integer
Dim iMin = 0, iMax = oaArray.GetUpperBound(0)
'// continually narrow search until just one element remains
Do While iMin < iMax
Dim iMid = CInt(Math.Floor((iMin + iMax) / 2))
'// code must guarantee the interval is reduced at each iteration
Debug.Assert(iMid < iMax)
'// note: 0 0 Then
iMin += 1
End If
End If
Return -iMin
End If
End Function
Time Card Alternatives
In addiction to the Excel Time Card you can use these:
The time card is my pilot program to learn how to do the personal finance add-in better. I chose not to charge for the time card simply because there are already many time tracking things out there, like SlimTimer (if I needed one for on the go I would probably use this one, nice and clean and easy to use), ClickTime, RescueTime (this one is an interesting concept where it tracts your time for you), and TimeCard.com.
Using Pivot Tables as a Staging Resource
When I originally created the Excel time card dashboard I didn’t want to back my data with any pivot tables. I soon found though that it slowed the calculations so much that I needed to use pivot tables just to make it bearable to work with the dashboard. The reason I didn’t want to use pivot tables was because they are prone to do wacky and unexpected things.
Generic Types
Last time we went over Overloading function in .NET. Today we’ll take a look at generics. I had been wondering how to do this for some time, seeing that I could do it with Microsoft’s built in code. I have to say, it is pretty nice and really makes coding much easier and cleaner.
Overloading in VB.NET
In previous posts I showed how you can use Extensions and Lambda Expressions to make some pretty versatile functions in .NET. Today I’ll show a method that really helps for the readability and organization of your code.
In the past we used optional parameters to make a function do multiple things. We even needed to make new functions that did pretty much the same thing in order to make it better organized. Well, now we can avoid using those by overloading the functions. I still use optional parameters and even new functions but this has been greatly reduced by the ability to overload.
Excel Time Card Completed
The Excel time card I have been working on is completed. Feel free to have a look and tell me what you think! I still need to add more documentation, which I will role out in the future let me know if you have any questions or found any bugs!
Got to the download page to get it!
A Class Made from a Shaped Recordset
Here’s some fun code that I worked on a while back. This disconnected record set is what you call a shaped record set, created on the fly. It is a pretty complex class all wrapped in one nice little bundle. Unfortunately it goes terribly slow. Creating a class with the dictionary object in the Scripting Runtime Object Library should be the fastest, if you’re looking for speed.
Hugelkultur Beds
When it comes to gardening, I have a black thumb. But every year I try as I might to garden again. Last year I tried mixing some wood and branches into my square boxes inspired from the hugelkultur bed design. This seemed to have a positive effect on my garden and helped it retain water better.
So this year, I’m at it again, but on a bigger scale.
8 feet by 4 feet wide by 3 feet deep pit for wood.
Lambda Expressions
Another thing that I like about programming in .NET beyond LINQ and Extensions is lambda expressions. (It should be noted that LINQ, Extensions, and lambda expressions are all related). Lambda expressions are “are callable entities that are defined within a function, you can return a lambda expression from a function and you can pass lambda expressions to other functions.” Lambda expressions come with the System.Core library, so no need to add a reference nor Imports at the top of your class.