List of Excel Functions - Code To Use List

I saw a few people were downloading my list of Excel functions that I created. I thought it might be useful show the code that I use to get that data to a manageable format. Although the code is written in VB.NET it should be easily converted to VBA. [wpdm_file id=3] I use a simple structure (type in VBA) for the data. Private Structure ExcelFunctionInfo Dim Name As String Dim Category As String Dim ExcelVersion As Integer Dim Description As String Dim Syntax As String Dim URL As String End Structure I then just loop through the data in the string that was gotten from the text file. [Read More]

Sheet Exists

I was looking at Siddharth Rout’s new blog (Adding/Deleting Sheets to the Excel File) and realized that I haven’t posted my version of sheet exists. Mine probably does too much per Code Complete standards, but it is nice not to have to think of multiple. If I were to refactor this code I would take out the code for the charts and probably just make it work only for checking if the sheet exists and adding a new sheet if it doesn’t. [Read More]

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. A generic type is a single programming element that adapts to perform the same functionality for a variety of data types. [Read More]

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. [Read More]

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. Pretty crazy looking code, but pretty elegant once you get used to looking at code like that, unfortunately it’s not very using for creating classes on the fly, or even instead of regular classes (It would make making classes a synch! [Read More]

Lambda Expressions

An Introduction

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. [Read More]

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. What’s so great about this? Well, it does slow the program down, but it speeds up the the programming process, puts the information in a form that is more descriptive (although that is no excuse not to thoroughly comment your code), and makes programming easier. [Read More]

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. [Read More]

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. [Read More]

AdvancedFilter Class Part 3

Creating the Sub Class

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:

  1. Search Term
  2. Header
  3. Header Operator
  4. Match Type

' 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