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 1
- 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:
ActivePane’s Index property doesn’t work correctly
According to the Excel Object Model specification the ActivePane property:
Returns a Pane object that represents the active pane in the window.
and you can:
Use the Index property to obtain the index of the active pane.
Here is a simple code that is used to display the index of the current ActivePane:
1 2 3
Let’s see it in action.
As you see when there are 2 panes it always returns 2 and when we have 4 panes it always returns 4. I’m guessing it is just returning the number of the panes :)
I needed to determine in which of the Panes is the current selection so I started looking at the Panes members. The only promising one was the
VisibleRange. I didn’t want to use the Excel function
INTERSECT since is quite costly performance wise. I figured out that just comparing the
Left property of the
VisibleRange with the
Left property of the current selection should be enough (the same for the
Top property). So I came up with this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
This works just fine with
Ranges and pretty much every Excel object that has
Top properties. You get the correct index of the
However, what you also get, are some triggered events as soon as you access the VisibleRange property. I have no clue why this happens. Even a simple assigment like
var range = pane.VisibleRange; causes the active chart to get deactivated, which again caused quite a few additional problems that are beyond the scope of this post.
PointsToScreenPixelsmethod is bugged as well.↩