Excel 2007 VSTO plugin exception when setting Font.Color

Question

Hello all,

I am working on an Excel 2007 VSTO plugin that is throwing COM exceptions on the client but not when debugging on my development machine.

What the plugin does is capture Excel's Startup event, define a specialized style, then add an event handler to the SheetChange event. Anytime a value is changed in the sheet, the cell is set to the new style. All of this is to provide users a way to see the cells they've changed. Code is as follows:

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            this.BeforeSave += new Microsoft.Office.Interop.Excel.WorkbookEvents_BeforeSaveEventHandler(ThisWorkbook_BeforeSave);

            this.SheetChange += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetChangeEventHandler(ThisWorkbook_SheetChange);

            cfStyle = Globals.ThisWorkbook.Styles.Add("CFStyle", missing);
            cfStyle.Font.Color = Excel.XlRgbColor.rgbOrange;
            cfStyle.Font.Bold = true;
            cfStyle.Interior.Color = Excel.XlRgbColor.rgbLightGray;
            cfStyle.Interior.TintAndShade = 0.8;

            cfStyle.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            cfStyle.Borders.Weight = Excel.XlBorderWeight.xlThin;
            cfStyle.Borders.Color = Excel.XlRgbColor.rgbDarkSlateGray;
            cfStyle.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            cfStyle.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
        }

When this runs in dev, it runs perfectly. However when it's run on a client machine, I get this exception detail once the VSTO plugin loads. The interesting part is it seems to fail on the first COM interaction, which happens to be setting a Style.Font.Color property.

Here are the exception details:

System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

Server stack trace:

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at Microsoft.Office.Interop.Excel.Font.set_Color(Object )

at TriQuint.DemandPlanning.Workbook.ThisWorkbook.ThisWorkbook_Startup(Object sender, EventArgs e)

at Microsoft.Office.Tools.Excel.Workbook.OnStartup()

at TriQuint.DemandPlanning.Workbook.ThisWorkbook.FinishInitialization()

at Microsoft.VisualStudio.Tools.Office.EntryPointComponentBase.Microsoft.VisualStudio.Tools.Applications.Runtime.IEntryPoint.FinishInitialization()

at Microsoft.VisualStudio.Tools.Applications.AddInAdapter.ExecutePhase(ExecutionPhases executionPhases)

at Microsoft.VisualStudio.Tools.Applications.AddInAdapter.CompleteInitialization()

at Microsoft.VisualStudio.Tools.Office.Internal.OfficeAddInAdapterBase.ExecuteEntryPointsHelper()

Has anyone ever seen anything like this? I've done quite a few validations, such as ensuring the proper versions of .NET, VSTO Interop, Excel 2007, etc etc.

Thanks in advance for any advice! Jim

Answer 1

You get a 56-color palette in Excel to play with. The color palette will be different from one desktop to another, especially if you aren't using a custom template to base your worksheets from (templates can be distributed from computer to computer, thus allowing the 56 colors to be customized on one machine, stored to the template, and shared btw machines). Instead of setting the Font.Color property, set the Font.ColorIndex to a number within this range. That should eliminate the exception being thrown. However, this will probably uncover the next issue, which is that the color palette btw the client machines and your desktop are different. To resolve that, you'll need to override the default color palette to match what is on your desktop.

Answer 2

To potentially save others from the pain of many lost hours, I thought I would post my solution to this. It's so ridiculously simple that it's making me re-think my life as a developer. Ok, not really, but still...

So to re-state the desired functionality: the goal is to change a cell's style (background, font, borders, etc) whenever a user edits a cell.

Here is the code that does the trick:

void ThisWorkbook_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
        {
            foreach (Excel.Range range in Target.Cells)
            {
                Excel.Range cellRange = range.Cells[1, 1] as Excel.Range;

                cellRange.Borders.ColorIndex = 10;
                cellRange.Interior.ColorIndex = 43;
                cellRange.Font.Bold = true;
            }
        }

ThisWorkbook_SheetChange is an event handler of the Workbook.SheetChange event. Simply set the style properties that exist on the Range object. DO NOT set the style properties on the Range.Style object. If you do, this will change the default style in Excel, and cause all of your cells that use that style to change as well.

I imagine writing it this way will also work, but I have not tested this out:

void ThisWorkbook_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
            {
                Target.Cells.Borders.ColorIndex = 10;
                Target.Cells.Interior.ColorIndex = 43;
                Target.Cells.Font.Bold = true;
            }

Thanks to code4life for your post about ColorIndex. Your info helped quite a bit.