Build this IF Function Parser to Simplify Debugging Excel Formulas : Page 3

Exposing the .Net Assembly to COM Clients
To expose this assembly for use by unmanaged COM Clients, you must declare a .NET Interface and define a GUID attribute:

   [Guid("43A8EB80-A566-4985-BEDE-58312478029D")]
   public interface IParser
   {
      void parse(string source);
   }

The .NET assembly implements the IParser interface:

   [Guid("22FAF7FA-8FCA-4eca-8E28-11DBC07475F4")]
   public class FormulaAnalyser : IParser

Building the application creates a type library called FormulaParser.tlb. Any COM client can now use the type library to access public exposed functionality in the .NET assembly.

Connecting to a .NET Assembly from Excel
To use this tool in Excel, you must be able to run macros, so you should click the Tools menu and set your Macros|Security setting to medium. You'll find an Excel add-in file in the downloadable code. An add-in file has an .xla extension and usually contains code only.

First, open up the add-in file called FormulaFormatter.xla. To see or edit the code for the add-in, press Alt + F11. Click the Tools menu and select References. If you see a reference to FormulaParser.tlb, unselect it. Browse to the location where you copied the type library FormulaParser.tlb and select that file. Before leaving the code window, make sure you save the add-in by clicking the File menu and selecting "Save FormulaFormatter.xla" (alternatively, press Ctrl + S).

The main purpose of this .xla add-in is to be able to call the Formula Formatter when a user right-clicks in a cell containing an IF function. A bit of VBA code sets up the shortcut menu for the Excel application:

   Private Sub App_SheetBeforeRightClick( _
      ByVal Sh As Object, ByVal Target As Range, _
         Cancel As Boolean)
   
      Dim bRet As Boolean
      If Target.HasFormula Then
         If Target.HasArray Then
            Debug.Print "Array formula"
         End If
         If InStr(1, Target.formula, "if(", _
            vbTextCompare) > 0 Then
            Application.CommandBars("Cell").Controls _
               (FORMULA_MENU_NAME).Enabled = True
         Else
            Application.CommandBars("Cell").Controls _
               (FORMULA_MENU_NAME).Enabled = False
         End If
      Else
       Application.CommandBars("Cell").Controls _
          (FORMULA_MENU_NAME).Enabled = False
      End If
   End Sub

Previous Page

Next Page

---

• Download The Code for this Article

---