XL DNA

Using a Excel DNA Function in VBA

16 November 2010 by Ross McLean

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!

Tags: ,
Categories: VBA, XL DNA • 1 Comment »

Adding Intellisense to a DNA solution.

28 October 2010 by Ross McLean

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!

Tags: ,
Categories: Downloads, XL DNA • 8 Comments »

Debugging a DNA solution

15 October 2010 by Ross McLean

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

Tags: ,
Categories: XL DNA • 5 Comments »

Excel DNA and the Excel Object Model

30 September 2010 by Ross McLean

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

Tags: ,
Categories: XL DNA • 16 Comments »

Writing an Excel DNA function Using an IDE

22 September 2010 by Ross McLean

In this post we are going to cover how to use an IDE (Integrated Development Environment) to write a DLL and use that DLL with Excel DNA.  There are a few reasons we would pick this approach over just writing the code into a text/xml file.

  • You get to use an IDE, and all the benefits that come with that…
  • Complex code is easier to manage
  • Debugging is much easier
  • The DLL “protects” your IP (a bit)
  • Working with userforms etc. becomes possible/much simpler

There’s other stuff as well but lets not labour the point.

OK, first which IDE to use? As far as I know all Microsoft IDE which support .Net 2 or above will work with the latest version of DNA. So that’s 2005 through to 2010, including any and all express versions. The great thing about DNA is its not compiling some oddball super specialized DLL which needs a lot of equally obscure registry settings and support files to run, its a bog standard managed DLL. But remember you will need the correct dot Net framework running on the target PC.

In this example I’m going to be using SharpDevelop [get it here]. It’s a free IDE for the dot Net framework (supporting, C#, F#, IronPython, and Boo).  In many ways it’s not as polished the Express editions, but in some ways it’s better. The “Pro” editions of VS are in my opinion the finest development environments available, but they do cost a lot of money. The other monumentally good thing about SharpDevelop is that it installs and opens much quicker that any version of Visual Studio. Installing VS takes an age, opening VS 2008 (VB) express for the first time on my PC takes 30 seconds for SharpDevelop(3.2) its 4 seconds! Regardless of what you are using, the principals are the same, it might just be that the various bits are in slightly different places, just look around; you’ll find what you need in the end!

Ok, Step one (after installing the IDE, and downloading the DNA package*) is to create a new Class project. Open SharpDevelop, click  File>New>>Solution:

In the dialog that pops up find the VB node, and select that (tip, you don’t need to drop into any of the child nodes), pick the “Class Library” project. Notice at the top right of the dialog there is a dropdown for picking the target framework version. Unless you have a compelling reason not too, pick “.Net Framework 2.0” – this should have the widest coverage. Finally fill out the details at the bottom and create the project.


In case you have not used an IDE before it worth us talking about what you have just done. In the world of Excel we normally create one file and that holds all our code. This is a little different, but don’t worry it’s not rocket sugary. What the IDE has just done is write to disk a few files that it will use to hold and manage the project, if you add new files, and when you build the project these file will be placed in this project folder. To start with there are 3 files and 3 sub folders. The folders are (briefly!); “Bin”, with another sub folder, this is where the output DLL will normally be written to, “Obj” with further sub folders; this is where the IDE puts the files it uses for building your project, and “Properties”, guess what goes in there! The 3 files are “XYZ”.sln, this is the solution file – this opens the project and all the other support things for this solution (for example if you had more than one project in a solution), “ZYX.vbproj”, the VB project you just created, this will also open the project, and the solution, but not any other projects in the solution, and “NewClass.vb”, this is the actual code file, like a module in VBA.

Ok, back in the IDE, you should have something that looks like this:


The first thing we need to do is add a reference to the Excel DNA integration assembly. To do this go to the Project menu, then “Add Reference”…

In the dialog that is displayed, go to the “.Net Assembly Browser” tab, and click Browse, then find your DNA folder, open the “Distribution” sub folder and select the file called “ExcelDna.Integration.dll”:

Click “Open” and the file will be added to the list of References, then click OK


Next we need to add some code to the class. At the top of the class file remove the comments and add “Imports ExcelDna.Integration”, while we’re here let’s rename the class to “Public Class MyFirstDNAAddin”.  Next, in the project explore to the left of the screen, right click on the class file, pick rename, and change the name of the class file from “NewClass.vb” to something like “MyAddin.vb”

We are now ready to add some functional code. Lets Write a “Hello world” function. Add this code under the Class declaration:

Public shared Function HelloWorld (byVal sString As String) As String
Return "Hello World, from a DLL..." & sString
End Function

Next compile the project, hit F8, or Build>>Build Solution, or click one of the green down arrow buttons, shown in the red box below:


Cool, all being well, you should have just built a DLL. Notice at the bottom of the IDE there’s an “Output” window, which tells you how long the project took to compile, if there were any errors these should also be displayed at the bottom of the IDE.

Next go to your project Folder, and open the Bin folder and then the Debug folder, the DLL will be in here with two other files, the DNA integration DLL and a temp file, copy the DLL you just created – “MyFirstIDEAddin.dll”, or whatever you called it,  in to a new folder,… somewhere new…


Next copy the “ExcelDNA.xll” from the DNA package (its in the distribution folder) into the same folder where your DLL now is. Then create a new text file and add the following code in it:

<DnaLibrary>
<ExternalLibrary Path=”MyFirstIDEAddIn.dll” />
</DnaLibrary>

Close the text file, saving the changes, and rename the text file to something like “HelloIDE.DNA”, finally change the name of the XLL to “HelloIDE.xll”, and double click it.

Excel should open, enable macros if needed, and open anew workbook. If you now go to the function wizard, your function should be in there, and you can use it as you would any other function.

Remember you need to distribute the three files for this to work on another PC, that’s the DNA file, the DLL file and the XLL. If you only want to send out 1 file you can use the packager, we will cover this in another post.

* download Excel DNA from codeplex, unzip the files, and copy them to some suitable location, “C:\ExcelDNA\\ExcelDna-0.27”, for example would be a good place.

Tags: ,
Categories: XL DNA • 37 Comments »

« Older Entries