VSTO & .NET & Excel

November 14, 2006

The Transition Case – COM Add-in Part II

Filed under: .NET & Excel — Dennis M Wallentin @ 2:50 pm

Developing the DataTool COM Add-in with classic VB

Previously post on the subject:
The Transition Case – VBA Part I

Here in the second post on the subject we will take a closer look on how to create an unmanaged COM  Add-in with classic VB.

Except for the ‘transition discussion’ the downloadable file gives an input on how easy it is to ‘port’ a XLA project to a COM project. The major difference is that we need to change the strategy on how we implement the Hot Keys and also which Hot Keys to use.

Hot Keys in unmanaged COM Add-ins

In Excel it’s quite easy to add Hot Keys via the OnKey method of the Application object. When developing unmanaged COM Add-ins I usually don’t use Hot Keys due to the heavy use of Commandbar(s) and Menu option(s). However, a customer to me asked for some week ago if it was possible or not to add Hot Keys to unmanaged COM Add-ins. I remembered  that I read something that it was not doable.  But before giving a reply to the customer I checked it with my good friend Mike Rosenblum who pointed me to some posts he made on different forums last year on the subject. Unfortunately no solution was given so I decided to consult Dan Appleman’s Visual Basic Programmer’s Guide to the Win32 API where I found an API to start with:

‘Determine the state of the specified virtual key at the time
‘the function is called.
Public Declare Function GetAsyncKeyState Lib “user32.dll” _
(ByVal vKey As LongAs Integer

In the next stage I added a hidden form with a timer object to the project which didn’t work out well due to slow response. Next I added a reference to the CCRP’s High Speed Timer which gave a satisfied solution for the time frame I wanted.

The tests with Excel 2003 went well but I noticed some other issues in that:

  • When running more instances then one of Excel 2003 the Hot Keys executed the same instruction in all instances.
  • When running both Excel 2002 and 2003 the Hot Keys also executed the same instructions in all instances.

The solution to get the active instance of Excel ‘isolated’ so that the Hot Keys are only executed in that instance is to use subclassing. Subclassing with earlier versions of Excel like 97  2000 and 2002 is not doable due to slow response. It’s beyond the scope of this blogpost to dive deeper into subclassing and Excel. Anyway,  based on my tests it seems to work with Excel 2003 and 2007. I didn’t consider to create my own subclassing solutions as I have previously used msghook.dll with good result. Therefore I just added a reference to the msghook.dll in the project.

The following procedures are part of the Connection class and only the relevant code for the Hot Keys are showed:

 Option Explicit
‘The classes for Timer and SubClassing.
Private WithEvents Timer1 As ccrpTimer
Private WithEvents XLHook As MsgHook

‘Variable that hold the value if a running instance of Excel is
‘Activated (=True) or Deactivated (=False)
Private bFlagActivate As Boolean

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
            ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
            ByVal AddInInst As Object, custom() As Variant)

‘Create a new instance of MsgHook.
Set XLHook = New MsgHook
‘Subclassing
XLHook.StartSubclass CONST_XLWIND

‘Create a new instance of CCRP Timern.
Set Timer1 = New ccrpTimer

‘Manipulate the two most important properties.
With Timer1
    .Enabled = True
    .Interval = 0
End With

End Sub

Private Sub AddinInstance_OnDisconnection( _
        ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
        custom() As Variant)

‘Stop the subclassing and release the variable.
XLHook.StopSubclass
Set XLHook = Nothing
‘Stop the timer and release the variable.
Timer1.Enabled = False
Set Timer1 = Nothing

End Sub

Private Sub Timer1_Timer(ByVal Milliseconds As Long)
If bFlagActivate Then
    ‘If the user use CTRL+ALT+C —– Chart report
    If GetAsyncKeyState(vbKeyC) Then
        If GetAsyncKeyState(vbKeyControl) Then
        If GetAsyncKeyState(vbKeyMenu) Then Create_Chart_Report
        End If
    End If
    
   ‘If the user use CTRL+ALT+R —– Data report
    If GetAsyncKeyState(vbKeyR) Then
        If GetAsyncKeyState(vbKeyControl) Then
        If GetAsyncKeyState(vbKeyMenu) Then Create_Data_Report
        End If
    End If
    
    ‘If the user use CTRL+ALT+P —– Pivot table report
    If GetAsyncKeyState(vbKeyP) Then
        If GetAsyncKeyState(vbKeyControl) Then
        If GetAsyncKeyState(vbKeyMenu) Then Create_Pivot_Report
        End If
    End If
End If
End Sub

Private Sub XLHook_BeforeMessage(uMsg As Long, wParam As Long, _
            lParam As Long, retValue As Long, Cancel As Boolean)
‘Check to see if the message from VMI is equal the message we are
‘looking for.
If uMsg = WM_ACTIVATEAPP Then
    ‘ The running instance of Excel is being activated.
    If wParam Then
        ‘Set the flag to True.
        bFlagActivate = True
    Else
        ‘The running instance of Excel is being deactivated.
        ‘Set the flag to False.
        bFlagActivate = False
    End If
End If
End Sub

The following picture shows the references for the COM Add-in case:

References

The project for the COM Add-in case can be downloaded here.

All comments are most welcome.

Edit note:
I’m consider to not create a shimmed managed COM Add-in for the case but before taking any decision please let me know what You think. The reason for my consideration is my published 9 articles about managed COM Add-ins.

Kind regards,
Dennis

Advertisements

6 Comments »

  1. Hey Dennis,

    Very nice solution… Subclassing Excel is definately not easy. I’m not too familiar with the msghook.dll nor the the CCRP timer, but you have put together a very nice, intuitive solution. 🙂

    It is strange how much variability there is between Excel verisons, however. I wonder why that is. Just one of those complexities I guess.

    I would not bother with a Managed COM solution here. It would be essentially the same, other than omitting ‘Set’ keyword it really should just work. Ok, maybe the word “should” is the key word there, LOL, but it really should be pretty much the same.

    Of course, back to the “front loader” discussion, the COM or Managed COM Add-in could open an XLA that traps the .OnKey() event and then passes such calls to a method within the .NET project that handles the OnKey() calls. More of a hassle than your solution, to be sure, but it would work in any Excel version.

    Good Stuff Dennis,
    Mike

    Comment by Mike Rosenblum — November 14, 2006 @ 8:13 pm

  2. Mike,
    Thanks for Your kind comment 🙂
    I fully agree about managed COM Add-ins. It will be more challenging and interesting to port it to a VSTO 2005 SE Add-in 🙂
    One important aspect that we should not forget is deployment including also distribution of PIA.
    Nowadays I’m happy that solutions like this one works at least with one Excel version. In the future I strongly believe that we need to accept that we no longer can work with a number of versions like 2000 to 2014.
    The ‘Front Loader’ approach is one of the aspect I would like to discuss more about in some future blogposts. I’ve done some tests and I believe that the approach could / should qualify to be part of ‘best practice’ to get a more ‘workable’ flexibility with COM Add-ins & Excel.
    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 14, 2006 @ 9:26 pm

  3. Hi chaps,

    “I would not bother with a Managed COM solution here.”

    Yep me too, your other articles are excellent enough – stop showing off! 🙂

    “n the future I strongly believe that we need to accept that we no longer can work with a number of versions like 2000 to 2014.”

    I agree with that very much too, although if you listen to the video of Eric Carter on “the visual side”, he talks about compatibility with multi versions in VSTO as being very important, but yeah maybe a range, like 97 – xp, 03 – 09, is going to be the future.

    I was playing around with #dev at the weekend with one of my managed com addins – I built it in VS2005 from the template and ported then opened it in #Dev, it worked ok (although i did not build it, just debug). One thing that slipped into my mind was would it be possible to build a shim from VBA? – I just don’t know enough about if you could get hold of the CLR and create a new app domain. Food for thought though.

    Good stuff by the way Dennis. I’ve subclassed userforms with good results, but never the excel app it-self.

    Comment by ross — November 14, 2006 @ 11:09 pm

  4. Ross,

    Thanks for Your kind feedback 🙂

    The shimming procedure should be considered as a ‘temporarily workaround’ until we have ported us to the VSTO platform for Excel developing.

    In VSTO it already exist a VSTOLoader that load the assemblies into their own AppDomains. VBA is part of the ‘unmanaged world’ and as such it will never require the start procedure that CLR represent.

    I try to keep myself updated on all new stuff but I must admit I don’t know what #Dev is.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 15, 2006 @ 1:31 pm

  5. http://www.icsharpcode.net/OpenSource/SD I think #dev is widely used?
    “In VSTO it already exist a VSTOLoader that load the assemblies into their own AppDomains. VBA is part of the ‘unmanaged world’ and as such it will never require the start procedure that CLR represent.”
    Sorry I didn’t make my point clearly, I was suggesting that for projects in #dev, you might be able to load the Managed Com into it’s own app domain, using VBA, because #dev does not have a C as a language option… although why can we load a managed com addin into it’s own app domain using managed code? – I’ve got some reading to go an do!
    Cheers
    Ross

    Comment by ross — November 19, 2006 @ 10:00 pm

  6. Ross,

    The whole idea (or at least one of the corner stones) with .NET is that the CLR load applications in their own AppDomain. In that way the ‘DLL Hell’ does not exist in a pure ‘.NET enviroment’. The exception is when using managed COM Add-ins in an unmanaged enviroment (Excel et al) which the ‘shimming’ is a solution for.

    Thanks for the link and it’s one of the things that I’ve read about but never take the time to explore.

    On the other hand I spent some time with Mono:
    http://www.mono-project.com/Main_Page

    Thanks Ross for keeping me up to speed 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 19, 2006 @ 11:10 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: