If you read my posts much, you’ll notice I focus on C# a lot. I’m not a big VB.NET fan. One thing VB.NET has that C# doesn’t have (yet — I hear they’re adding it in C# 4) though is optional parameters.

.NET, of course, allows you to mix and match languages. I can use a 3rd party library, or an in-house library, that was created with VB.NET, even though I code in C#. Not a problem there; they play nicely together.

A problem did arise, however, when many of the methods I was calling had optional parameters. C# was making me pass an object, and I didn’t know I had a choice. I created dummy objects and overloaded methods to get what I needed. That was a mistake.

I wish I had known then about Type.Missing. I found about it when I was playing with COM and studying for a certification test. COM also has optional parameters. The example I was reading used Type.Missing, and I wanted to cry. I had spent way too much time smashing my head into my desk with the library fiasco. All I needed was this simple construct.

So I’m sharing it here, in case anyone else has overlooked this. Maybe I’ll save someone a headache. Or a splintered desk.

Here is a simple example of using Type.Missing with Excel COM interop.

Note that you must add references to your project to Excel (Excel Object Library or similar, assuming you have Excel installed on your computer) from the COM tab in order for it to work. Adding a reference to that will also add one for Microsoft.Office.Core for you. You must also set Excel to allow code to automate it.

using System;
using System.Runtime.InteropServices;
using Excel;
namespace ConsoleApplication1
{
   class Program
   {
   private static Object OptionalParamHandler = Type.Missing;
   static void Main(string[] args)
   {
      try
      {
         Application NewExcelApp = new Application();
         NewExcelApp.Visible = true;
         NewExcelApp.Workbooks.Add(Type.Missing);
         NewExcelApp.Worksheets.Add(OptionalParamHandler,
         OptionalParamHandler, OptionalParamHandler,
         OptionalParamHandler);
      }   
      catch (Exception e)
      {
         Console.WriteLine("Exception: " + e.Message + " " + e.StackTrace);
      }
      Console.ReadLine();
   }
}
}

The following are some posts and articles that I’ve recently enjoyed, in no particular order. I thought I’d share.

RESTful Web services: The basics

Relative vs. Absolute References in Formulas

Excel Basics: How to add drop down list to validate data

What the IF? – learn 6 cool things you can do with excel if() functions

10 Terrible Tech Ads

How to Beat the Plague of Limiting Beliefs

Sometimes you just want to know if a cell contains a given string of text; perhaps you want to know as part of a larger formula, or to display a warning, or to use conditional formatting. In some languages, you could check if a string contains another string with a substring or index-of function. For Excel formulas, you can use FIND.

FIND returns the number of the starting position of the first text string from the first character of the second text string. Unfortunately, it doesn’t return -1 to indicate non-existence. It returns #VALUE. You can test for that with ISERROR.

FIND is case-sensitive. You can force case-insensitive by converting both arguments to upper-case. An example is illustrated below (formulas were filled down from B2-B7 and C2-C7, respectively).

Normal formula used: =IF(ISERROR(FIND(”dog”,A2)), “not found”,”found”)
Case-insensitive: =IF(ISERROR(FIND(”DOG”,UPPER(A2))), “not found”,”found”)

See more examples of using FIND over on the Office site.

Have you ever needed to write an Excel macro that looped through rows and manipulated the data in some manner? For example, you need to delete rows where column C has the value “GREEN”. But you have blank or inconsistent rows, or just a metric ton of data, so you can’t just sort and delete them.

In order to avoid looping through 64,000 rows (which is quite slow, really), you’d want to only go to the last row that contains data. You need to know which row is truly the last row that has values in the cells.

The typical SpecialCells code I found didn’t quite work for me due to blanks and formulas. It ignores rows that have nothing but formulas in them, such as summation rows.

So I went looking for a better solution. I’ve seen this function posted a couple places online. It has helped me several times, so I thought I’d share.

Function LastCell(ws As Worksheet) As Range
Dim lastRow&, LastCol%
On Error Resume Next
With ws
lastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
Set LastCell = ws.Cells(lastRow&, LastCol%)
End Function

You’d call that function like this.

Dim lastRow As Integer
lastRow = LastCell(ActiveWorkbook.ActiveSheet).Row

You could then loop through the rows like this.

Dim rowCounter As Integer
For rowCounter = 1 To lastRow
' do whatever
Next

Happy macroing!

In older versions of Excel, you could allow macros using the Tools menu (Options, Security tab, Macro Security button).

Office 2007 is totally different from 2003 and earlier versions. Here is the link to their help document detailing how to enable macros to run without the prompt (Enable all macros).

Enable or disable macros in Office documents

Basically, they moved the security settings from the Tools / Options menu to the new Trust Center.