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.
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.
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.
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. Doing default settings is interesting.
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: Range to filter (optional – can use current range instead). Filter location (optional – if you are using an add-in then you can place in there, otherwise can put in temporary sheet). Filter action (copy to new range or filter in place (default)). Get unique items from a column (will need to use different method than AdvancedFilter).
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. This is how the AdvancedFilter method works: Sub AdvancedFilterClassExample() Dim iIndex As Integer Dim rResult As Range Dim clsSearch As Search 'clsSearch.
Distributing Add-ins via Installer
Recently I needed to create an installer for my add-ins using Inno Setup installer. Inno Setup is a free ware professional installer. It lets you use Pascal to create special code to help you install the add ins. Luckily, over at Dutch Gemini’s website he has helped with plenty of the code to get there. Very well done, I might add. It looks like he uses a single add-in file for Excel 2003 and below and Excel 2007 and above (for ribbon purposes).