none
Key binding removed, but VBA function called anyway RRS feed

  • Question

  • As I describe in http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/9fff83f8-0a97-46dc-824a-9ec5abb1d2d6, I create a VBA macro from my Word application-level add-in, hook it up to a key press, and have it call code in the add-in. That code in the add-in then unhooks the key press and undefines the VBA macro, and inserts the key char into the document. That all seems to work fine.

    (My application has a mode where changing the cursor position does linguistic analysis on the cursor paragraph and highlights it to show the results. If the user types to change the text, I detect that via the key press hook and switch out of the mode.)

    The problem is that if the same key is pressed again, I get a message box saying "Sub or function undefined", even though I've unhooked the key press and checked that it's unhooked. I keep getting the same message whenever I press the key, until I press a different key, and then pressing the original key starts working properly again.

    My add-in is used with Word 2007 and is currently targeting .NET 4.0 Client Profile. (I had similar symptoms targeting .NET 3.5 SP1, but maybe slightly different in details.) The key binding customization context is the document.

    Monday, July 18, 2011 11:49 PM

All replies

  • Hello,

    It is highly possible that you can make it work by releasing all COM objects created in your code. Looking at your cody, it seems calling GC.Collect + GC.WaitForPendingFinalizers twice will resolve the issue. If not, I suggest that you minimize the code and follow suggestions I posted in When to release COM objects in Office add-ins developed in .NET.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Tuesday, July 19, 2011 7:58 AM
  • Besides Andrei's suggestion I'd check the CustomizationContext when you're removing the KeyBinding. You want to make sure it's exactly the same as when you're adding it...
    Cindy Meister, VSTO/Word MVP
    Tuesday, July 19, 2011 11:11 AM
    Moderator
  • I've boiled the code down about as small as I can into a new Word 2007 add-in project, and the problem still occurs, despite Andrei and Cindy's kind suggestions.

    ThisAddIn.cs:

    using System;
    using Tools = Microsoft.Office.Tools;
    using Word = Microsoft.Office.Interop.Word;
    
    namespace TestAddIn1
    {
      public partial class ThisAddIn
      {
        VbaAccess m_access = null; // ComAddInAutomationService object
    
        #region VSTO generated code
    
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
          this.Startup += new System.EventHandler(ThisAddIn_Startup);
          this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
    
        #endregion
    
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
          foreach (Word.Window wn in this.Application.Windows) {
            TestPane apc = newAnalysisPane(wn, true);
          }
        }
    
        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }
    
        ///////////////////////////////////////////////////////////////////////
    
        public TestPane getAnalysisPane(Word.Window wn)
        {
          TestPane apc = null;
          foreach (Tools.CustomTaskPane ctp in CustomTaskPanes) {
            if (ctp.Window == wn && ctp.Control is TestPane) {
              apc = (TestPane)ctp.Control;
              break;
            }
          }
          return apc;
        }
    
        TestPane newAnalysisPane(Word.Window Wn, bool visible)
        {
          TestPane apc = new TestPane(this);
          Tools.CustomTaskPane ctp = this.CustomTaskPanes.Add(apc, "TestPane", Wn); // add user control to CTP
          ctp.Width = apc.PreferredSize.Width + 6; // kludge works, though width is not locked
          ctp.Visible = visible;
          apc.m_ctp = ctp;
          return apc;
        }
    
        // Allows calling from VBA into C#.
        // Returns an object accessible from VBA via 
        // Application.COMAddIns(""TestAddIn1"").Object.
        protected override object RequestComAddInAutomationService()
        {
          if (m_access == null)
            m_access = new VbaAccess();
    
          return m_access;
        }
      }
    }
    
    


    TestPane.cs;

    using System;
    using System.Windows.Forms;
    using Tools = Microsoft.Office.Tools;
    using Word = Microsoft.Office.Interop.Word;
    
    namespace TestAddIn1
    {
      public partial class TestPane : UserControl
      {
        static string m_vbaProcName = "VbaCancelAnalysisMode";
        ThisAddIn m_tai; // ThisAddIn
        public Tools.CustomTaskPane m_ctp; // our parent CTP
        bool m_analysisMode = false;
    
        public TestPane(ThisAddIn tai)
        {
          InitializeComponent();
    
          this.m_tai = tai;
          m_tai.Application.WindowSelectionChange +=
            new Word.ApplicationEvents4_WindowSelectionChangeEventHandler(
              Application_WindowSelectionChange);
        }
    
        ///////////////////////////////////////////////////////////////////////
    
        void analysisModeCheckBox_CheckStateChanged(object sender, EventArgs e)
        {
          Word.Document doc = Globals.ThisAddIn.Application.ActiveDocument;
          if (this.analysisModeCheckBox.Checked) {
            turnOnAnalysisMode(doc);
          } else {
            turnOffAnalysisMode(doc);
          }
        }
    
        void Application_WindowSelectionChange(Word.Selection sel)
        {
          if (sel.Document.ActiveWindow != this.m_ctp.Window) {
            return;
          }
          if (!m_analysisMode) { return; }
          updateGuiForUserSelection(sel.Document, sel);
        }
    
        ///////////////////////////////////////////////////////////////////////
    
        void turnOnAnalysisMode(Word.Document doc)
        {
          if (!hookUpVba(doc)) {
            cancelAnalysisMode();
            return;
          }
          m_analysisMode = true;
          updateGuiForUserSelection(doc, m_tai.Application.Selection);
        }
    
        void updateGuiForUserSelection(Word.Document doc, Word.Selection sel)
        {
          int iPara = 1;
          doc.Paragraphs[iPara].Range.HighlightColorIndex = Word.WdColorIndex.wdAuto;
    
          Word.Range range = sel.Range.Duplicate;
          range.End++;
          range.HighlightColorIndex = Word.WdColorIndex.wdPink;
        }
    
        void turnOffAnalysisMode(Word.Document doc)
        {
          m_analysisMode = false;
          // put this early to try to avoid "undefined sub or function"
          unhookVba(doc);
          int iPara = 1;
          doc.Paragraphs[iPara].Range.HighlightColorIndex = Word.WdColorIndex.wdAuto;
        }
    
        public void cancelAnalysisMode()
        {
          if (this.analysisModeCheckBox.Checked) {
            // Will cause turnOffAnalysisMode to be called.
            this.analysisModeCheckBox.Checked = false;
          } else {
            turnOffAnalysisMode(m_tai.Application.ActiveDocument);
          }
        }
    
        ///////////////////////////////////////////////////////////////////////
    
        public bool hookUpVba(Word.Document doc)
        {
    
          // We may have already hooked up for this document?
          if (VbaIsHookedUp(doc)) return true;
    
          // The automationObject is what's returned by the 
          // RequestComAddInAutomationService method of ThisAddIn.
          // It's a VbaAccess (defined in this project) instance 
          // with a cancelAnalysisMode() method.
          string code =
    @"Sub VbaCancelAnalysisMode()
    'MsgBox ""In EclairCancelAnalysisMode""
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns(""TestAddIn1"")
    Set automationObject = addIn.Object
    automationObject.cancelAnalysisMode
    End Sub";
          // Add "VbaCancelAnalysisMode" VBA function to doc.
          VbaUtility.addVbaCode(doc, code, m_vbaProcName);
          // Hook up keypresses to that function.
          VbaUtility.addKeyBindings(m_tai.Application, doc, m_vbaProcName);
    
          return true;
        }
    
        public void unhookVba(Word.Document doc)
        {
          // Clear key bindings before removing code, so in case of timing 
          // or other problems we don't ever have bindings to removed code.
          VbaUtility.clearAllKeyBindings(m_tai.Application, doc);
    
          VbaUtility.removeAllVbaCode(doc, m_vbaProcName);
        }
    
        public bool VbaIsHookedUp(Word.Document doc)
        {
          return VbaUtility.docHasVbaCodeProcNamed(doc, m_vbaProcName);
        }
    
        ///////////////////////////////////////////////////////////////////////
    
        // Key press causes EclairCancelAnalysisMode to be called, 
        // and then key is passed back here to insert into document.
        // Without this, keystroke would be lost.
        public void insertKey(byte[] keystate)
        {
          // Not sure whether there's a better way than this, probably is. 
          // This is guaranteed NOT to work with Unicode chars and on non-US 
          // keyboards. 
          //StringBuilder sb = new StringBuilder();
          uint keyCode = 0, unhandledKeyCode = 256; // uint is 32 bit, so won't wrap at 256
          for (; keyCode < 256; keyCode++) {
            if (keystate[keyCode] != 0) {
              // Besides the 0x80 bit, we find some 0x01 bits set.
              //sb.Append(String.Format("0x{0:x2} 0x{1:x2}\n", keyCode, keystate[keyCode]));
            }
            if ((keystate[keyCode] & 0x80) != 0) { // 0x80 indicates pressed
              // backspace, tab = 0x08-0x09
              // shift, ctrl, alt = 0x10-0x12
              // clear(?), enter = 0x0C-0x0D
              // space = 0x20
              // delete = 0x2E
              // left shift, right shift, left ctrl, right ctrl = 0xA0-0xA3
              if (0x08 == keyCode || keyCode == 0x0D || // backspace, enter
                0x20 == keyCode || keyCode == 0x2E || // space, delete
                0x30 <= keyCode && keyCode <= 0x39 || // numbers 0-9
                0x41 <= keyCode && keyCode <= 0x5A || // letters A-Z
                0xBA <= keyCode && keyCode <= 0xC0 || // punct 1 ;=,-./`
                0xDB <= keyCode && keyCode <= 0xDE)  // punct 2 [\]'
              {
                // Found a key we understand, we do want to take action on it.
                break;
              } else if (keyCode < 0x10 && keyCode > 0x12 && // NOT shift, ctrl, alt
                    keyCode < 0xA0 && keyCode > 0xA3)  // NOT left shift, right shift, left ctrl, right ctrl
              {
                // Found a key we don't understand; we don't want to 
                // take action on it (just notify).
                // I guess we're keeping the last one found if multiple,
                // and if we find a key we understand we ignore this.
                unhandledKeyCode = keyCode;
              }
            }
          }
          bool shifted = false;
          if (keyCode < 256) {
            if ((keystate[0x10] & 0x80) != 0) {
              shifted = true;
            } else {
            }
          } else if (unhandledKeyCode < 256) {
            MessageBox.Show(this, String.Format("Unhandled key {0:x2}", unhandledKeyCode),
              "TestAddIn1", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            return;
          }
    
          Keys key = (Keys)keyCode;
          KeysConverter kc = new KeysConverter();
    
          // Now xlate byte into char (or other action) and put in doc.
          Word.Selection sel = m_tai.Application.Selection;
          Word.Range range = sel.Range.Duplicate;
          //bool insertion = sel.Start == sel.End;
          if (keyCode == 0x08) { // backspace
            if (range.Text == "") {
              range.Start--;
            }
            range.Text = ""; // TODO? what if shifted?
          } else if (keyCode == 0x0D) { // enter
            // insert paragraph mark; TODO? what if shifted?
            range.Text = "\r";
          } else if (keyCode == 0x20) { // space
            // TODO? treat Shift+space differently?
            range.Text = " ";
          } else if (keyCode == 0x2E) { // delete; TODO? what if shifted?
            if (range.Text == "") {
              range.End++;
            }
            range.Text = "";
          } else if (0x30 <= keyCode && keyCode <= 0x39) { // numbers
            string s = ")!@#$%^&*("; // corresponding shifted chars
            range.Text = shifted ? s[(int)(keyCode - 0x30)].ToString()
              : kc.ConvertToString(key);
          } else if (0x41 <= keyCode && keyCode <= 0x5A) { // letters
            // letters are considered as upper by default in these APIs
            range.Text = shifted ? kc.ConvertToString(key)
              : kc.ConvertToString(key).ToLower();
          } else if (0xBA <= keyCode && keyCode <= 0xC0) { // punct 1
            string s = ":+<_>?~";
            range.Text = shifted ? s[(int)(keyCode - 0xBA)].ToString()
              : kc.ConvertToString(key);
          } else if (0xDB <= keyCode && keyCode <= 0xDE) { // punct 2
            string s = @"{|}""";
            range.Text = shifted ? s[(int)(keyCode - 0xDB)].ToString()
              : kc.ConvertToString(key);
          }
    
          // If we set sel.Text, replacement text is selected; 
          // this can change you from an insertion point to a selection.
          // OTOH, if you set the text of a copy of the selected Range,
          // the selection remains where it is, but you want it to be 
          // an insertion point after the inserted text.
          if (keyCode != 0x08 && keyCode != 0x2E) { // backspace, delete
            sel.End++; sel.Start++;
          }
        }
      }
    }
    


    VbaAccess.cs:

    using System.Runtime.InteropServices;
    using Word = Microsoft.Office.Interop.Word;
    
    // Part of mechanism for calling from VBA into C#.
    // This is the callee part.
    namespace TestAddIn1
    {
      [ComVisible(true)]
      public interface IVbaAccess
      {
        void cancelAnalysisMode();
      }
    
      [ComVisible(true)]
      [ClassInterface(ClassInterfaceType.None)]
      public class VbaAccess : IVbaAccess
      {
        [DllImport("user32.dll", CharSet = CharSet.Auto, ExactSpelling = true)]
        public static extern int GetKeyboardState(byte[] keystate);
    
        // While in analysis mode, this is hooked up to keypresses so that we 
        // can detect user typing and get out of analysis mode.
        // That means unhooking keypresses and undefining VBA stuff 
        // (so that the VBA stuff doesn't get saved in the document).
        // TODO Current problem is that when you press the same key a second time, 
        // it still tries to call the VBA function VbaCancelAnalysisMode, 
        // but that's now undefined.
        public void cancelAnalysisMode()
        {
          Word.Window wn = Globals.ThisAddIn.Application.ActiveWindow;
          if (wn != null) {
            TestPane apc = Globals.ThisAddIn.getAnalysisPane(wn);
            if (apc != null) {
              // Want to cancel analysis mode before inserting key so 
              // that, eg, chars are not inserted with highlight colors.
              // Also want to unhook key presses.
              apc.cancelAnalysisMode();
    
              // Doesn't seem to help.
              if (true) {
                System.GC.Collect();
                System.GC.WaitForPendingFinalizers();
                System.GC.Collect();
                System.GC.WaitForPendingFinalizers();
              }
    
              // Don't let the keypress go into limbo.
              byte[] keystate = new byte[256];
              if (GetKeyboardState(keystate) != 0) {
                apc.insertKey(keystate);
              }
            }
          }
        }
      }
    }
    
    


    VbaUtility.cs:

    using System;
    using Vbe = Microsoft.Vbe.Interop;
    using Word = Microsoft.Office.Interop.Word;
    
    // This file contains code related to defining, testing for, and deleting 
    // VBA macros, and for hooking up macros to keypresses (in the context of 
    // particular documents).
    namespace TestAddIn1
    {
      static class VbaUtility
      {
        // Only the chars that are entered without a shift have enums. 
        // This will probably not work well on odd or non-US keyboards.
        static Word.WdKey[] boundKeys = {
            Word.WdKey.wdKeyA,
            Word.WdKey.wdKeyB,
            Word.WdKey.wdKeyC,
            Word.WdKey.wdKeyD,
            Word.WdKey.wdKeyE,
            Word.WdKey.wdKeyF,
            Word.WdKey.wdKeyG,
            Word.WdKey.wdKeyH,
            Word.WdKey.wdKeyI,
            Word.WdKey.wdKeyJ,
            Word.WdKey.wdKeyK,
            Word.WdKey.wdKeyL,
            Word.WdKey.wdKeyM,
            Word.WdKey.wdKeyN,
            Word.WdKey.wdKeyO,
            Word.WdKey.wdKeyP,
            Word.WdKey.wdKeyQ,
            Word.WdKey.wdKeyR,
            Word.WdKey.wdKeyS,
            Word.WdKey.wdKeyT,
            Word.WdKey.wdKeyU,
            Word.WdKey.wdKeyV,
            Word.WdKey.wdKeyW,
            Word.WdKey.wdKeyX,
            Word.WdKey.wdKeyY,
            Word.WdKey.wdKeyZ,
            Word.WdKey.wdKey0,
            Word.WdKey.wdKey1,
            Word.WdKey.wdKey2,
            Word.WdKey.wdKey3,
            Word.WdKey.wdKey4,
            Word.WdKey.wdKey5,
            Word.WdKey.wdKey6,
            Word.WdKey.wdKey7,
            Word.WdKey.wdKey8,
            Word.WdKey.wdKey9,
            Word.WdKey.wdKeyBackSingleQuote,
            Word.WdKey.wdKeyBackSlash,
            Word.WdKey.wdKeyBackspace,
            Word.WdKey.wdKeyCloseSquareBrace,
            Word.WdKey.wdKeyComma,
            Word.WdKey.wdKeyDelete,
            Word.WdKey.wdKeyEquals,
            Word.WdKey.wdKeyHyphen,
            Word.WdKey.wdKeyOpenSquareBrace,
            Word.WdKey.wdKeyPeriod,
            Word.WdKey.wdKeyReturn,
            Word.WdKey.wdKeySemiColon,
            Word.WdKey.wdKeySingleQuote,
            Word.WdKey.wdKeySlash,
            Word.WdKey.wdKeySpacebar,
            Word.WdKey.wdKeyTab,
          };
    
        // Add a VBA function (defined by VBCode) to the document.
        public static void addVbaCode(Word.Document doc, string VBCode, string procName)
        {
          Vbe.VBProject Project = doc.VBProject;
          Vbe.VBComponent Module = Project.VBComponents.Add(
            Vbe.vbext_ComponentType.vbext_ct_StdModule);
          Vbe.CodeModule Code = Module.CodeModule;
          Code.AddFromString(VBCode);
        }
    
        // Remove the VBA component that we added, identified by its type 
        // and the presence of our method.
        public static void removeAllVbaCode(Word.Document doc, string procName)
        {
          Vbe.VBProject Project = doc.VBProject;
          for (int i = Project.VBComponents.Count; i > 0; i--) {
            Vbe.VBComponent Component = Project.VBComponents.Item(i);
            if (Component.Type == Vbe.vbext_ComponentType.vbext_ct_StdModule) {
              Vbe.CodeModule Code = Component.CodeModule;
              try {
                if (Code.get_ProcStartLine(procName, Vbe.vbext_ProcKind.vbext_pk_Proc) > 0) {
                  Project.VBComponents.Remove(Component);
                }
              } catch (Exception ex) {
              }
            }
          }
        }
    
        public static bool docHasVbaCodeProcNamed(Word.Document doc, string procName)
        {
          bool found = false;
          Vbe.VBProject Project = doc.VBProject;
          for (int i = Project.VBComponents.Count; i > 0; i--) {
            Vbe.VBComponent Component = Project.VBComponents.Item(i);
            if (Component.Type == Vbe.vbext_ComponentType.vbext_ct_StdModule) {
              Vbe.CodeModule Code = Component.CodeModule;
              try {
                if (Code.get_ProcStartLine(procName, Vbe.vbext_ProcKind.vbext_pk_Proc) > 0) {
                  found = true;
                  break;
                }
              } catch (Exception ex) {
              }
            }
          }
          return found;
        }
    
        ///////////////////////////////////////////////////////////////////////
    
        public static void addKeyBindings(Word.Application Application, Word.Document doc, string procName)
        {
          Application.CustomizationContext = doc;
          Word.KeyBinding kb;
          int kc;
          foreach (Word.WdKey key in boundKeys) {
            kc = Application.BuildKeyCode(key);
            kb = Application.KeyBindings.Add(
              Word.WdKeyCategory.wdKeyCategoryCommand, procName, kc);
            kc = Application.BuildKeyCode(key, Word.WdKey.wdKeyShift);
            kb = Application.KeyBindings.Add(
              Word.WdKeyCategory.wdKeyCategoryCommand, procName, kc);
          }
        }
    
        public static void clearAllKeyBindings(Word.Application Application, Word.Document doc)
        {
          Application.CustomizationContext = doc;
          Application.KeyBindings.ClearAll();
        }
      }
    }
    
    


    TestPane.cs is created as a user control, with a single checkbox named analysisModeCheckBox with a CheckStateChanged event handler.

    If you run in the debugger, type "Hello world." into the first paragraph of the document, then check the checkbox. In this mode, the character to the right of the cursor is highlighted in pink. Unchecking the box turns off the mode. We also want the mode to be turned off if the user types in the document while the mode is turned on, so we use VBA and key bindings to detect that.

    The problem is that if the same key is pressed again, I get a message box saying "Sub or function undefined", even though I've unhooked the key press and checked that it's unhooked. I keep getting the same message whenever I press the key, until I press a different key, and then pressing the original key starts working properly again.

    Tuesday, July 19, 2011 11:04 PM
  • Someone else reports the same problem with Excel: http://www.eggheadcafe.com/community/aspnet/66/10149201/excel-vba-keybinding-doesnt-turn-off-immediatly.aspx. The suggested possible solution of calling DoEvents doesn't work for me.
    Thursday, July 21, 2011 4:37 PM
  • Hello,

    When a key is pressed, the flow of calls started by the VBA procedure removes the code of the VBA procedure itself. Doesn't it resemble the baron known for escaping from a swamp by pulling himself up by his own hair? :) You need to do this after the call from VBA is completed. You can do this in a timer event. Make sure the timer event occurs in the same thread.

    Add-in Express implements another approach which you may find useful. It creates a hidden window for the add-in. Your code posts a custom windows message to it and continues/finishes. When the host application decides it can process the message, you'll get an event.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Friday, July 22, 2011 12:41 PM
  • Andrei is correct that the flow of calls started by the VBA procedure removes the code of the VBA procedure itself. However, that's not the issue, the issue is that the key binding is not removed. One might guess that there could be a relation, but in fact there's not. This is shown by the fact that if I comment out the code that removes the VBA procedure, the procedure gets called from the key press, showing that the key binding is still in place.
    Friday, August 5, 2011 5:52 PM
  • I'd still be looking at where all this keybinding may have been stored - at some prior time. For example, if you were doing some testing before you added the CustomizationContext or if you tested adding the keybinding manually and stored it in a global template. If I were you, I'd check whether the keybinding hasn't been stored in Normal.dotm


    Cindy Meister, VSTO/Word MVP
    Monday, August 8, 2011 9:32 AM
    Moderator
  • Andrei could be on to something, though; perhaps the problem is due to removing the key binding from code invoked by the key binding.

    Monday, August 8, 2011 6:25 PM