Browse DevX
Sign up for e-mail newsletters from DevX


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

If you're tired of trying to decipher long Excel IF formulas displayed in a single line of code, try this convenient Formula Formatter add-in that displays such formulas neatly indented in a popup window.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date