Software: More Excel Then You Could Shake a Stick At!

I have done more work with Microsoft Excel in the last month then I had EVER done in my entire career. Once I figured out the model, it’s been much easier to write code. But man! I’ve been switching back and forth between .NET and COM, C# and VBA; and it all looks a little different between them. In VBA a method might take one parameter but in C# it might take 14. What! Yea. If you want to call Application.Run() from VBA only the first parameter (the function name) is required. The other 30 parameters are optional. Calling Application.Run() from C# however requires that all 31 parameters be defined. Yep. Fortunately you can just fill in the last 30 values with Type.Missing but it is really weird and makes the code hard to read. If you were going to call Run() often without parameters, your code would look much cleaner if you put the Application.Run() call into another subroutine.

For example:

private void RunFunc(string funcName)
{
   mExcelApp.Run(funcName,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value);
   return;
}


Here is a VBA snippet that checks to see if a worksheet exists in the workbook. It just seems like there should be a better way to tell me that a requested worksheet doesn’t exist in the workbook than to use a GoTo. (Yes, I know it's standard for VBA, it's just not clean.)

On Error GoTo CreateWorksheet
Set inputSheet = Application.ActiveWorkbook.Worksheets("mySheet")

CreateWorksheet:
Dim iSheetCnt As Integer
iSheetCnt = Application.ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Worksheets(iSheetCnt)
Set inputSheet = Application.ActiveWorkbook.ActiveSheet
inputSheet.Name = "mySheet"

Here is the same code in C#:

// See if the DigipedeInput worksheet exists
try
{
   inputSheet = (Excel.Worksheet)

      mWorkerWorkbook.Worksheets.get_Item("mySheet");
}
catch (System.Exception /* ex */)
{
   // This gets thrown if the sheet doesn't exist which is fine.
   // Create the worksheet
   int iSheetCnt;
   iSheetCnt = mExcelApp.ActiveWorkbook.Worksheets.Count;

   Excel.Worksheet lastSheet;
   lastSheet = (Excel.Worksheet)
   mExcelApp.ActiveWorkbook.Worksheets.get_Item(iSheetCnt);
   inputSheet = (Excel.Worksheet)
      mExcelApp.ActiveWorkbook.Worksheets.Add(Type.Missing,
      Type.Missing, Type.Missing, Type.Missing);
   inputSheet.Name = "mySheet";
}

Notice that in VBA I have to use a GoTo statement because if a worksheet is not found an error is returned? Notice in the C# that I have to catch an exception? I REALLY don’t like either of these two techniques. I’m going to sleep on it but I suspect that I’ll end up writing a little function to check for the existence of the worksheet. It will probably go something like this:

int iSheetCnt = mExcelApp.ActiveWorkbook.Worksheets.Count;
string strSearchName = “mySheet”;
bool bFound = false;
for (int iCounter = 0; iCounter < iSheetCnt; iCounter++) {
   if (strSearchName.Compare(
      mExcelApp.ActiveWorkbook.Worksheets(iCounter).Name) == 0) {
      bFound =true;
      break;
   }
}

But basically I’ll look for the worksheet and return the appropriate information.

This is actually turning out to be a really fun project. So even though figuring out the nuances between Excel's different manifestations has been a challenge I think the end results are going to be really cool.