Cloning using Serialization

I found some great C# code the other day which allows deep copying of objects, rather than shallow copies which object.Clone() does. Saved heaps of time mucking around with the ICloneable interface, and basically rewriting heaps of code.

You need to decorate the class(es) that you want to clone with the [Serializable] attribute. If you are trying to clone a object of a type which has members of some other class, those classes need to be serializable too.

public static object Clone(object obj)
{
MemoryStream ms = new MemoryStream();
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, obj);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
return bf.Deserialize(ms);
}

Working with Excel Shapes from C#

In VBA, you can select shapes and control them usingSelection.ShapeRange. Selection morphs to different types depending on what property is called. Go here for more information.

In C#, the Selection(range) object does not have a ShapeRange property. However the DrawingObjects object does have aShapeRange property. So if you want to copy a ShapeRange from one sheet to another, try the following.

Say you have this VBA code to port to C#:

Set aShape = srcSheet.DrawingObjects.ShapeRange.Group
x = aShape.Left
y = aShape.Top
aShape.Copy
dstSheet.Paste
Selection.Left = x
Selection.Top = y
Selection.ShapeRange.Ungroup
aShape.Ungroup

This converts to:

aShape = (Excel.Shape)drawObj.ShapeRange.Group();
x = aShape.Left;
y = aShape.Top;
aShape.Copy();
dstSheet.Paste(Missing, Missing);
drawObj = (Excel.DrawingObjects)dstSheet.DrawingObjects(Missing);
Excel.ShapeRange shapeRng = (Excel.ShapeRange)drawObj.ShapeRange;
shapeRng.Left = (float)x;
shapeRng.Top = (float)y;
shapeRng.Ungroup();
aShape.Ungroup();

This solution assumes that there was nothing on the destination sheet to begin with. (the line dstSheet.DrawingObjects refers to all the Drawing objects on the sheet)

Using RibbonX UI with Office 2007 from C# COM Addin

Hi all,

Yes, I am here still. I have been trying to use the RibbonX control in Office 2007 from a C# COM Addin. There is only one other blog that I have seen that has touched on the subject so far http://pschmid.net , so I look forward to seeing what he has to say in the coming weeks.

Any other blogs out there covering this subject?

Garbage Collection of CommandBarButtons

I am building an Excel Addin in C#. It starts with creation of a single
commandbutton on the Excel toolbar (call it B1). When clicked this opens
a form with a few Pictureboxes. These pictureboxes can either be clicked
to start some routine or dragged onto the Excel toolbar to become a
commandbutton (call them B2). The commandbutton B2 has a clickhandler
event and has OnAction as . When I click the
pictureboxes on the Form, the form is made invisible. The pictureboxes
on the form fire ok, but after a series of events, the commandbutton
click events do not fire anymore.

e.g
1. open Excel, click a commandbutton previously dragged onto the Excel
toolbar (B2) - fires ok
2. click commandbutton on toolbar (B1) and click a pictureBox on Form -
fires ok
3. click (B2) - does not fire
4. click (B1) and click a pictureBox - fires ok

I should note that each time the commandbutton or picbox is clicked the
excel object is obtained from your method given in
http://blogs.officezealot.com/whitechapel/archive/2005/04/10.aspx

Andrew`Whitechapel: Second, the behavior you describe for the event handlers sounds exactly
like the classic delegate scoping issue. The object that holds the event
sink (delegate) must be declared at class scope, not at function scope.
If you declare it at function scope it will go out of scope at the end
of the function and become available for garbage collection. It will
then actually be garbage collected at some indeterminate point in the
future.

So, my advice is to use the Excel.Application object given to you up
front, and cache this at class scope. Then also cache the
CommandBarButton object at class scope.

Creating Excel Addin with C# Extensibility

Well it has been a long time since my last.
I have been using C# over the last few months to automate an Excel Addin and have run into every gotcha known to man!
I will be compiling my experiences and hopefully they will be helpful to new Excel/Office Addin programmers.