In Zebra BI we are positioning our custom WPF forms on top of the Excel window in order to offer a richer and more contextual UI. Calculating the exact position for the forms is quite complex and painful, due to things like:

• detecting the Excel window state and left edge position
• calculating from points to pixels ¹
• coping with the Windows DPI
• taking Ribbon / Formula Bar / Excel version into consideration
• serious lack of events on many of Excel’s objects
• …

The last nail in the coffin were the Panes which also need to be considered when a user uses the Freeze Panes option, which basically splits the worksheet into 2 or 4 Panes. At first it seemed like a simple adjustment until I started digging into the object model and ultimately found out that:

I am starting a category called “excel Excel” which will contain useful tips and tricks for Excel. It will be mostly directed towards Excel developers and power users.

How do I [blank] in Excel?

StackOverflow is full of questions like:

“How do I change the color of a Chart Series in Excel in VBA / C#?”

While doing it manually is easy, the problem occurs when you wish to start talking to Excel from the code. This is not surprising, considering the number of different objects present in the Excel Object Model. One can spend an insane amount of time trying to figure out the exact five steps to reach the desired effect. It is easy to get lost in all the objects, and their methods and properties. Which is why I love the macro recording option.

Source code needs to be under version control, period. When it comes to deciding which system to use, Git is one of the options. There are many heated debates which one is the best, but let’s not go into that. All of them do their job, so it mostly comes down to personal preference. I like Git.

^{Image source}