Jon's Programming Blog

Linq: An Introduction

Another thing that I like about programming in VB.NET beyond Extensions is LINQ. LINQ is similar to SQL in structure - which is “a declarative programming paradigm that expresses the logic of a computation without describing its control flow.” So LINQ takes declarative paradigm and applies it to object oriented programming.

Extensions in VB.NET

Learning how to use VB.NET can be quite the leap from VBA, but once you start learning VB.NET and have much more control it’s difficult to want to go back to VBA.

One of the things that I like about VB.NET is methods. Extensions allow you to extend data type methods. So, let’s say you have a String data type (Strings in VB.NET are like a class in and of themselves) and you want it to have the method of appending “]” at the end. So you would have an extension method like so:

Why UDF Array Functions are Faster

Over at FastExcel the answer was given why UDF arrays go faster to the question I had on why UDF array formulas are faster than regular formulas. I had forgotten about this post that he had written.

Here’s the answer that he gives. Check out his blog post to see more of the details of why this is.

Calculation Efficiency

Chandoo had some posts on speeding up Excel worksheets, one of the posts focuses on formulas and another he let the general readers make their suggestions. I made the suggestion that people use array formulas. But most of the other suggestions said not to use array formulas since they slow down your spreadsheet. Needless to say, I was a bit confused, I have seen quite the performance boost from using array formulas.

Updates

AdvancedFilter Class

Just so everyone knows. I’m going to postpone the AdvancedFilter series until I start working on it in .NET, that way I’ll remember more about what I was doing before. I’ll start working on that when I finish up the Time Card application and the Chart Manipulator.

Time Card

I probably won’t be finishing up the beta version of the time card until early April. I’m currently debugging the UDF function “SumDate” which is pretty central to the time card. For some reason it is outputting the correct results for all but one result in an array of results. I’ll probably get that figured out today.

The Problems Of Copy/Pasting Code

Copying and pasting is great for coding, but sometimes it can be pretty silly if you make a mistake in the code. Here’s a triple copy/paste fail (the values should be set to true):

Public Sub SheetActivate_Dashboard(ByVal sh As Object)

    Try
        gXLApp.EnableEvents = False
        OpenTimeStamp()
    Finally
        gXLApp.EnableEvents = False
    End Try

End Sub

Public Sub SheetDeActivate_Dashboard(ByVal sh As Object)

    Try
        gXLApp.EnableEvents = False
        If Not gfTimeStamp Is Nothing Then
            gfTimeStamp.Visible = False
            gfTimeStamp.Close()
            gfTimeStamp = Nothing
        End If
    Finally
        gXLApp.EnableEvents = False
    End Try

End Sub

Public Sub DoubleClick_Dashboard()

    Try
        gXLApp.EnableEvents = False
        OpenTimeStamp()
    Finally
        gXLApp.EnableEvents = False
    End Try

End Sub

AdvancedFilter Class Part 3

In order to get the nice drop down menu we’ll need a second class I’ll call “SearchTerms.” This class is also necessary to store each individual search term entered into the class. Included in this class will be:

' SearchTerms
' Description: This class contains the search information.
'
' Authors: Jon Nyman, www.spreadsheetbudget.com
'
' Change Overview
' Date Comment
' --------------------------------------------------------------
' 03/02/2012 Initial version
'
Option Explicit
'---------------------------------------------------------------
'Class Variable Declarations
'---------------------------------------------------------------
Private msSearchTerm As String
Private msHeader As String
Private meHeaderOperator As HeaderOperator
Private meMatchType As MatchType

'---------------------------------------------------------------
'Class Property Procedures
'---------------------------------------------------------------

Property Get SearchTerm() As String
    SearchTerm = msSearchTerm
End Property Property 

Let SearchTerm(ByVal sItem As String)
    msSearchTerm = sItem
End Property

Property Get Header() As String
    Header = msHeader
End Property 

Property Let Header(ByVal sHeader As String)
    msHeader = sHeader
End Property

Property Get Header_Operator() As HeaderOperator
    Header_Operator = meHeaderOperator
End Property

Property Let Header_Operator(ByVal eHeaderOperator As HeaderOperator)
    meHeaderOperator = eHeaderOperator
End Property

Property Get Match_Type() As MatchType
    Match_Type = meMatchType
End Property

Property Let Match_Type(ByVal eMatchType As MatchType)
    meMatchType = eMatchType
End Property

Creating a Settings Class in VBA

In .NET they have a nice settings class you can create using ApplicationSettingsBase base class. In VBA it takes a bit more work to get it working, but can be fairly elegant if done correctly.

Some criteria that is needed for a settings class in vba are:

To have a fast look up I use the dictionary object  which can look up values much faster than the collection object.

AdvancedFilter Class Part 2

The first thing we’ll want to do is determine what macro options we would like in the class.

The options I currently have are:

  1. Range to filter (optional – can use current range instead).
  2. Filter location (optional – if you are using an add-in then you can place in there, otherwise can put in temporary sheet).
  3. Filter action (copy to new range or filter in place (default)).
  4. Get unique items from a column (will need to use different method than AdvancedFilter).
  5. Copy unique to range.
  6. Return filtered data in variant array.
  7. Return filtered data as range.

So let’s get started. First we’ll need the properties in the class, I’ll call “Search.” Notice how I’ve left many of the properties as write only, since I’m not anticipating needing to retrieve that data later.

Wrapping a Class Around the AdvancedFilter Method

Classes are a tool in object-oriented programming that make programming much simpler. One class that I have developed in VBA is a class that makes using the AdvancedFilter method extremely easy and useful. In fact, I like it so much that I stopped using the regular find method. In the coming weeks I will demonstrate how this was done.

← 7 →