XL DNA

Excel Worksheet Navigator Addin

When working with large workbooks, clicking through the tabs at the bottom of the workbook is a massive chore. I’ve seen a few different addins that offer solutions to this problem, toolbars, ribbon drop downs, userforms, there all fine, but what I really wanted was just to get rid of the tabs and have them over on the left hand side of the screen. I don’t know why Excel doesn’t not allow you to do this anyway, but it doesn’t. Clearly this was a job for a Custom Task Pane.

 

I quickly developed an add in that does just this, you can download it for free here:

Downloads

 Alternative download link: MIE_WorkSheet_Navigator_V1,

If you’re not sure how to install this addin into Excel, unzip the file and then follow the instructions here.

 

If you only work with large workbooks occasionally, you don’t have to install the addin.  Once you have unziped the file, you can just click it like any other Excel file, and that will open the tool for that Excel “session”.

Once the addin is loaded it will add an icon to the Home Ribbon, which looks like this:  MIENavCPTRibBut

Click that and the Custom task Pane will open,  (click the cross in the right hand top corner of the Custom Task Pan to close it).  This picture, – click to get full size, shows you how to use it – it’s dead simple!

Please note: This addin required dot Net 4, and Excel 2007 plus, chances are you’ll be fine, if not leave a comment.

Worksheet_Navigator

Worksheet Navigator: How To. Click for full size

 

For the developers….

Custom Task Panes are not available to VBA developers, so that meant, VB6, VSTO, C++, or DNA. Guess which I picked!!!

It took me a while to workout how to get the code working from a complied .dll, but I got there in the end – I was doing something stupid. Once again XL DNA is simple, clean, and powerful, brilliant!

Using a Excel DNA Function in VBA

If you have written an Excel DNA XLL, its not unthinkable that you’ll wan to call that function and use it in VBA at some point. Well its easy enough to do with Application Run, just wrap the DNA function like this:

Function foo(Arg1, Arg2)
foo = Application.Run("addthem", Arg1, Arg2)
End Function

Although I’ve said Excel DNA it’s in fact true for any XLL function.

There is another way to do this which is very similar but uses ExecuteExcel4Macro, but you have to pass every thing as string, the application run method, converts to and from COM types for you – nice!

Adding Intellisense to a DNA solution.

A few people have asked how to add intellisense to a DNA solution. Obviously for someone like me who knows the object model so well and has perfect spelling etc.* its not needed, but for lesser mortals here’s how its done!

Setup up a DNA project in the normal way, then download the PIA of your choice.

Wooo, hang on what the hells a PIA?!?!.

A PIA, or Primary Interop Assembly, is a dot Net assembly that lets you use  COM object “stuff” in/from managed code. They are generally considered a pain in the posterior, hey maybe that’s really what PIA stands for…Pain In Ar…! Anyway, see here for a bit more detail.

I’ve saved you some time, by collecting all the available ones here. But you might already have them on your PC, if you do they will be in C:\WINDOWS\assembly\, called something like  “Microsoft.Office.Interop.Excel.XX.dll” where XX is the office version number…

Oh, more pain… now the thing is there’s a PIA for each version of Office/Excel. Each PIA supports the OM for a specific version, so using the 2007 one and deploying the solution against a 2003 version of Excel might cause a problem. DNA does not need the PIA as it uses the C API to talk to Excel, but to get intellisense we need to use a PIA. Oh, and theres no PIA for Excel 2000, but you can roll you own (add a reference to the Excel.exe, and the IDE will make a “IA” , the “Primary” is dropped as its not from the vendor, it gets messy here, lets move on – quickly!)

OK, so, setup up a DNA project in the normal way, download the above .Zip file and extract to your hard disk. Go to the “Project” menu, “Add reference”, then “.Net assembly Browser” then go and find the PIA folder you just extracted, and pick the right version for your needs. Here I’m targeting Office XP!

Notice that a reference is added to the project for the PIA, and can be seen in the project viewer.


Now, how to get it to work. Well the exact way is up to you, but if your using a module to hold a global reference to the Excel application, like we suggested before, then in that module change to code to something like this:


Imports Microsoft.Office.Interop.Excel
  Public Module Globals
      Public zXL As Application
  End Module

Then back in you class module you can use intellisense, ace.

Before you build the final version, make sure you remove the reference to the PIA, you can just delete it from the project viewer, and then delete the dll from the debug/release directory (i.e….\\Intellisense Example\bin\Debug\)

And there you go!

One other thing to note is that it works a little bit better in Visual Studio, SharpDevelop also exposes events, which you can’t use in every content, obviously, but its no big deal!

* vbg – on both counts!

Debugging a DNA solution

Debugging a DNA solution, like so many things in programming, is super easy once you know how. Debugging code as it executes is an extremely useful thing to do, and using SharpDevelop it’s really rather easy.

Open up a DNA project, compile the project and set up the files need for the XLL to work, as show in previous posts. At this point you should have an XLL that will work in Excel. Now to add the ability to debug the DLL, simply go to the project options (Project>Project Options) and click the debug tab. Once there add a reference to the Excel.exe path on your PC, and then the path to the XLL you want to debug in the boxes as shown below. Note, that the path to the XLL MUST be in quotes, as shown. I find it best to put .DNA file and the .XLL in the bin\debuf folder of the project, that way each time you rebuild the project the most current version will be debugged:

You will need to add a brake point in the code, so that you can step through it at run time, this is done by clicking on the margin of the code as in the VBA IDE, once the code hits that brake point you can step through the code with F11 (not F8), and to run to the next brake point is F6 (not F5). Errors are handled in the same way as VBA. Notice at the bottom of this screen are tabs for the debugging windows, watch window, locals, call stack etc. If you don’t see them you can get them from the View>>Debug menu.

To get the solution to run, click the run button (the green arrowhead) or hit F5.

One thing that not perfect is that while the code is running, Excel won’t display correctly.

So there you go simple! Note that the steps for debugging is MS Visual Studio are the same, you just need to find the debug section, and the function keys are the same as VBA

Excel DNA and the Excel Object Model

In this post we are going to take a look at how we can work with the Excel Object Model (OM) via a DNA addin.

First create a new DNA solution in SharpDevlope, and import the Excel DNA Integration DLL, as we did before, etc, etc, etc. (See last post)

To get to the object model we only need 1 line of code!

Add this inside a new class, build it and add the DNA and XLL files as needed (see the last post for a how to, if you need to) This function will return the username, from the Excel object model, see easy!

Public Shared Function UserName (ByVal sString As String) As String
Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application
Return XLApp.Username
End Function

Lets try some other things!  Try this code:

Public Shared Sub WNC
Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application
Dim x As String
x = inputbox ("what do you want to call the active worksheet?", "EXCEL DNA TEST")
XLAPP.activesheet.name = x
End Sub

This changes the sheet name of the active workbook, the input box is from dot net not Excel. Here’s a range example:

Public Shared Sub Range
Dim XlApp As Object = ExcelDna.Integration.ExcelDnaUtil.Application
XLApp.range("a1:a10").select
msgbox(XLApp.selection.cells.count)
End Sub

Cool, so there you go that’s how to get at the object model.

One other thing you might like to think about is how to put this to use in a project. What I do is add a standard module, and inside that declare a variable like this:

Public Module Globals
Friend XL as Object
End Module

Then use the auto open event to set a refeance to this variable, from which it can be used through the project.  Like this :

Imports ExcelDna.Integration

Class StartUp

Implements IExcelAddIn

Public Sub  Start() Implements IExcelAddIn.AutoOpen
XL = ExcelDna.Integration.ExcelDnaUtil.Application
End sub

Public Sub  Close() Implements IExcelAddIn.AutoClose
'Fires when addin is removed from the addins list
'but not when excel closes - this is to
'avoid issues caused by the Excel option to cancel
' out of the close     'after the event has fired.
msgbox ("Bye bye, from DNA")
End sub

End Class

Anyway there’s something for you to be thinking about for the next week! Enjoy

Ross