2010-05-31

VBA One-liner to Concatenate Excel Cell Values

Here's a VBA one-liner to concatenate a column of Excel cell values, with a separator, into one string: Join(Application.Transpose(<column range>), ","). The column range is an Nx1 array, Transpose() returns a one-dimensional N-element array and Join() function a one-dimensional array as the first parameter.

To concatenate values from a row of cells, you have to use Transpose() twice like this: Join(Application.Transpose(Application.Transpose(<row range>)), ","). Excel's Range always returns a two-dimensional array (in this case, a row is a 1xM array) and you flatten the two-dimensional array by calling Transpose() twice. There doesn't seem to be a built-in function to flatten two-dimensional arrays.

References

2010-05-27

Avoiding #N/A in Range VLOOKUP

I was using VLOOKUP to put percentages in buckets. Here's the lookup table:

   a b
1  0 1
2 25 2
3 50 3
4 75 4

And here's the formula I added to my data sheet: =VLOOKUP(input, A1:B4, 2, TRUE). When the fourth parameter, Range_Lookup is set to TRUE, the function will match a range instead of an exact input value. If the input value was greater than 0 (e.g. 0, 25.5), VLOOKUP returns a bucket number, as expected. My input data was dirty and included negative numbers (e.g. -0.5, -1), so VLOOKUP would then return #N/A and dependant formulas would show an error.

One solution was enclose the input value in an IF function and avoid negative input values: =VLOOKUP(IF(input < 0, 0, input), A1:B4, 2, TRUE).

After reading about VLOOKUP, Range_Lookup parameter ...

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

... another way to avoid the #N/A error is to provide a dummy value smaller than all the negative values and an extra bucket:

    a b
1 -99 0
2   0 1
3  25 2
4  50 3
5  75 4

References

2010-05-23

Rudimentary C# COM Interop Client for Excel

I started writing some basic C# COM Interop clients for Excel to keep a hand in C# and be able to develop more sophisticated applications than VBA could provide. Here are some basic things I learnt and a complete working example to read data from an Excel workbook.

Configure your project

  1. Add to this project reference in the IDE: GAC (or COM) Microsoft.Office.Interop.Excel.
  2. Add to the source code:
    using System.Runtime.InteropServices;
    using Microsoft.Office.Interop.Excel;
    

Clean up COM References

After your program has finished using Excel, it should release all references to COM objects otherwise the Excel automation process remains running (check the Task Manager process list for Excel.exe). It seems a bit back-to-front to mention this first but I like to know how free unused resources before I start using them. Here is a discussion and statements you should add to your code.

Optional Parameters and Type.Missing

C# (pre-2010) doesn't support optional parameters so you have to supply an argument for all parameters in exposed methods. Since many frequently-used Excel methods require a lot of parameters, you have to tolerate verbose function calls in your C# source code.

Often you would use the default value for most parameters and you can simply provide the value Type.Missing. If you have Visual Studio Tools for Office (VSTO), you can use a global variable called missing. In the example below, Workbooks.Open() requires 15 arguments but I only provide the file name and use the default value for the remaining parameters.

      Excel.Application app = new Excel.Application();
      Excel.Workbook wbk = app.Workbooks.Open(
        path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Getting Data From Excel - Indexers, Arrays and Accessors

You can access Worksheets, Ranges and their values in VBA with this syntax: object.property or collection(index). Using the Excel object model in C# via COM Interop requires knowing about indexers, arrays and accessors.

Items in Excel collections (e.g. Worksheets) can be accessed using indexers, e.g. Workbook.Worksheets[1]. Like their VBA counterparts, some indexers support different types of arguments. The return value is always of type object so you have to cast the return result. For example:

      Excel.Worksheet wks0 = (Excel.Worksheet)wkss["Sheet1"];
      Excel.Worksheet wks1 = (Excel.Worksheet)wkss[1];

For me, an Excel indexer is syntactic sugar for an accessor method called get_Item(). (Note: The SharpDevelop IDE doesn't list this accessor in the list of object methods but VS C# 2008 Express does):

      Excel.Worksheet wks2 = (Excel.Worksheet)wkss.get_Item("Sheet1");
      Excel.Worksheet wks3 = (Excel.Worksheet)wkss.get_Item(1);

Another thing to note is that the indexers return a type object so you have to cast the return result.

Once you have a reference to a Worksheet, you can refer to individual cells using its Cells array (and remember to cast the return result):

      Excel.Range cell0 = (Excel.Range)wks0.Cells[1,1];
      Excel.Range cell1 = (Excel.Range)wks0.Cells.get_Item(1,1);

You can also get a single cell using the Worksheet's Range property. Unlike the Cells property, Range is a parameterised property, so you have to use the Worksheet method get_Range(object Cell1, object Cell2):

      Excel.Range rng0 = wks0.get_Range("A1", Type.Missing);

A common way to refer to a set of cells is to provide the top left and bottom right cell to the Worksheet's get_Range() accessor:

      Excel.Range rng1 = wks0.get_Range("A1", "A6");

If you try to get a Range using the function or array syntax, e.g. Excel.Range rng1 = wks0.Range("A1", "A6"); or Excel.Range rng0 = wks0.Range["A1","A6"];, you get a compile-time error: Property, indexer, or event 'Range' is not supported by the language; try directly calling accessor method 'Excel._Worksheet.get_Range(object, object)', which I take it to mean that Range is exposed as a property but can only be used as a method since C# (pre-2010?) doesn't support parameterised properties.

After getting a reference to a Range, you can get its data using either the get_Value() accessor or Value2 property (like Range, the Value property is a parameterised property). Finally, since a Range object can refer to a single cell or a set of cells, you have to know whether you are retrieving a single object or an object array from a Range.

      string s0 = (string)rng0.get_Value(Type.Missing);
      string s1 = (string)rng0.Value2;
      object[,] arr0 = (object[,])rng1.get_Value(Type.Missing);
      object[,] arr1 = (object[,])rng1.Value2;

Complete Example

Putting all that information together, here is the complete code for a rudimentary C# COM Interop client to read data from cells A1:A6 in an Excel workbook. Note that some statements are redundant and are there to illustrate different ways of getting the same result.

The program's output is:

$A$1:$A$6
1,1 Text:Text
2,1 1:1
3,1 5.678:5.678
4,1 0.5:0.5
5,1 1/06/2010 12:00:00 AM:40330
6,1 21.5078:21.507812345
Check Excel.exe in Task Manager . . .

The program outputs both a cell's Value and Value2 property. As you can see, cell A5's Value is a date while its Value2 property is a number, and cell A6's Value is a currency amount to four decimal points while its Value2 property is a double. Just before it exits, all COM references to Excel should be cleaned up and the program prompts the user to check the Task Manager, where there should not be any Excel.exe processes in the process list.

//Add to project reference:
//GAC, Microsoft.Office.Interop.Excel
using System;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace ExcelAutomation1 {
  class Program {
    public static void Main(string[] args) {
      string path = @"C:\Users\hung\Documents\Programming\C#\ExcelAutomation1\ExcelAutomation1\Test.xls";
      Excel.Application app = new Excel.Application();
      Excel.Workbook wbk = app.Workbooks.Open(
        path, false, true, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      Excel.Sheets wkss = wbk.Worksheets;
      Excel.Worksheet wks0 = (Excel.Worksheet)wkss["Sheet1"];
      Excel.Worksheet wks1 = (Excel.Worksheet)wkss[1];
      Excel.Worksheet wks2 = (Excel.Worksheet)wkss.get_Item("Sheet1");
      Excel.Worksheet wks3 = (Excel.Worksheet)wkss.get_Item(1);
      Excel.Range cell0 = (Excel.Range)wks0.Cells[1,1];
      Excel.Range cell1 = (Excel.Range)wks0.Cells.get_Item(1,1);
      Excel.Range rng0 = wks0.get_Range("A1", Type.Missing);
      //Excel.Range rng1 = wks0.Range("A1", "A6"); //Not supported
      //Excel.Range rng1 = wks0.Range["A1","A6"]; //Not supported
      Excel.Range rng1 = wks0.get_Range("A1", "A6");
      Console.WriteLine(rng1.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
      string s0 = (string)rng0.get_Value(Type.Missing);
      string s1 = (string)rng0.Value2;
      object[,] arr0 = (object[,])rng1.get_Value(Type.Missing);
      object[,] arr1 = (object[,])rng1.Value2;
      int cntRow = arr0.GetLength(0);
      int cntColumn = arr0.GetLength(1);
      for (int r = 1; r <= cntRow; ++r) {
        for (int c = 1; c <= cntColumn; ++c) {
          Console.WriteLine(r.ToString() + "," + c.ToString() + " " + arr0[r,c] + ":" + arr1[r,c]);
        }
      }
      rng0 = rng1 = null;
      cell0 = cell1 = null;
      wks0 = wks1 = wks2 = wks3 = null;
      wkss = null;
      
      GC.Collect();
      GC.WaitForPendingFinalizers();
      GC.Collect();
      GC.WaitForPendingFinalizers();

      wbk.Close(Type.Missing, Type.Missing, Type.Missing);
      Marshal.FinalReleaseComObject(wbk);
      app.Quit();
      Marshal.FinalReleaseComObject(app);
      Console.Write("Check Excel.exe in Task Manager . . . ");
      Console.ReadKey(true);
    }
  }
}

References

2010-05-19

Excel Inplace (or embedded) without ActiveWorkbook

An Excel workbook in a SAP InPlace view (or embedded in within a window) stopped working, displaying a run-time 1004 error: Method 'Sheets' of object '_Global' failed. The problem was caused by a UserForm dialog in that workbook which the user can open to enter a value. The code in the dialog then filtered the data in all the sheets based on the user's input. The code failed when it tried to call the Sheets() method.

The Sheets() is a short-cut for Application.ActiveWorkbook.Sheets(), so when a workbook is embedded in a view and loses focus (such as when the dialog was opened), Application.ActiveWorkbook doesn't have a valid value so the Sheets() method fails. When the workbook is opened in an Excel window, there is always an active workbook (and a valid ActiveWorkbook value).

The VBA code was part of the workbook, not in a separate add-in file, so a solution was to use ThisWorkbook.Sheets(). Another solution could be to use Application.Workbooks(1).Activate.

There is also an Application.IsInPlace property which returns TRUE if Excel is embedded within another window.

2010-05-20: Hm ... I could have also used ThisWorkbook.Activate to ensure that there was always an active workbook.

References

2010-05-17

Start External Program to Debug a Class in Visual C# 2008 Express

While playing with an automation server for MS-Excel, I wanted to debug my class method by starting Excel in Visual C# Express and using the formula in a worksheet. After the usual couple of minutes of hunting through the project property sheets, it dawned on me that you can't define the start program in the Visual C# Express IDE.

So I reopened my project in SharpDevelop (another .Net IDE), defined Start External Program in my project and continued playing with my automation server.

Checking the project files, I found that SharpDevelop added or modified the StartAction and StartProgram properties to the project's .csproj file:

  <PropertyGroup Condition=" '$(Configuration)' == 'Debug' ">
    <StartAction>Program</StartAction>
    <StartProgram>D:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE</StartProgram>
  </PropertyGroup>

Then I reopened the solution in Visual C# Express and found that I could start Excel to debug the class, too! It seems that Visual C# Express debugger and MSBuild.exe tool does support the feature but the IDE doesn't allow you to edit the Start Action parameter.

References

2010-05-13

Prevent VBScript in Custom Outlook Form From Executing

My custom Outlook form added some canned text in the message object in the Item_Open() function. Annoyingly, when I open the form in design mode, the Item_Open() is called, cluttering up the message object. It turns out that you have to hold down the Shift key when opening a form to prevent VBScript in the form from executing.

Holding down the Shift key while starting MS-Access also prevents start-up options from executing, so it is a somewhat consistent feature in MS-Office.

References

2010-05-11

Adding Text To Message Body in Outlook Custom Form

I had a simple request to add some canned text to the message body of a Outlook custom form when the form was first opened, so I did this in VBScript:

Sub Item_Open()
  ...
  Item.HTMLBody = "Blah blah blah"
  ...
End Sub

Yet when I created a new item, the message body was empty! The same problem occurs with Item.Body. After the usual bit of trial-and-error, I discovered that the text only appears if you use HTMLBody or Body on the RHS of a statement:

  Dim strTest
  strTest = Item.HTMLBody
  Item.HTMLBody = "Blah blah blah"
More concisely:
  Item.HTMLBody = "Blah blah blah" & Item.HTMLBody

The text also appears if I display a message box after I add text to the message box.

It seems like the HTMLBody or Body field is not instantiated or refreshed unless it is used.

References

2010-05-01

Outlook To: Field Unexpectedly Shrinks

While developing a custom Outlook form, I found that the To: field would unexpectedly shrink to a few pixels tall and pull the remaining controls in the form upwards. The problem occurred when I added Item.Recipients.Add "test" in my VBScript code to populate the To: field with a dummy e-mail address then clicked in the To: field. (I was using a dummy address to avoid accidentally sending test messages to unsuspecting recipients.) It seems as if putting the cursor into that field while Outlook is resolving the non-existent address (i.e. before it marks that address with a wavy red underline) causes the problem. The problem never happens if you populate the To: field manually with a dummy address, probably because Outlook is resolving the address as you type.

In my case, I found that typing Ctrl+K (Check Names) in the To: field restores the height of the field.

The solution during testing is to use a real address (such as your own).