VSTA

Why Excel DNA?

16 August 2010 by Ross McLean

So if you read the first post hopefully you’ll be convinced that using dot Net is a viable option for an Excel developer. The next logical question you face is what method should you use to deploy your dot Net code. Let’s start by looking at what options are available:

A Managed dll called from VBA
It’s easy enough to write a plain old dll, mark it COM callable and call it into Excel from VBA. In this way you can use of all of the dot Net stuff and don’t have to do to much in the way of interfaces or set up. There are different methods that can be used, Marshall Object is probably the best if you want to work with the Excel object model directly – see here for more details.

Managed COM addins
This is a like for like managed* equivalent of a classic COM addin written in VB6. It uses IDTExtensibility2 as the interface into Excel. It can do all the things that an unmanaged COM adding can do. You should note that COM addins need to be isolated, more details can be found in Dennis superb tutorial, here.

VTSO addins
This is the way Microsoft would like us to go. It’s a managed com addin, which has been specifically design to work with a single Office application type. A lot of the plumbing and set up stuff that you have to do with a Managed COM addin is done for you in VSTO. This comes at a price of making the addin not wholly compatible across different Office versions. (Although at the time of writing, VTSO in Visual Studio 2010 and dot Net 4 should change this.) Microsoft has been working hard to make VSTO much slicker in the last few years, and these days it’s a pretty good option. It won’t do UDF (easily) though, and you will need the professional version of Visual studio, or better.
You can find a fuller discussion on the differences between VSTO addin and Managed COM addin in PED2.

Addin Express
Addin Express is a company, they make a few different products to wrap up managed COM addins, and VSTO type addins and have an excellent reputation for making the developers life easier. Prices typically start from around £220 for a life time licence.

Other dot Net wrappers.
This includes Excel DNA and other products like XLW, Excel4Net, Managed XLL and the like**. I’ve grouped these together here, but there could be a separate entry of each one just as easily. Excel DNA, XLW and Excel4Net are free, Managed XLL is quite expensive, £1000 plus, I believe.

OK, so there the main options, but which one? Well to be honest they all have there pros and cons, and any of them would be a good way to start using dot Net code with Excel, but here are some things to consider.

Cost.
Most of these options are expensive. Managed Com addins can be written in SharpDevlop which is a free IDE, but shimming*** them is hard, if you use Visual studio you get the shim for free. VSTO also needs Visual Studio, Addin express cost some cash as does Managed XLL. XLW, Net4Excel, and Excel DNA are free, and as a result they might not be quite as fully featured as the paid for options.

Deployment.
VSTO and managed COM addin used to be extremely hard to deploy, VSTO is now much better. Certainly the environment and the control you have on that environment plays a part here. If you know all your target PC’s are set up in a particular way, with a particular Office version, the headaches reduce somewhat. Excel DNA is simple to deploy, it’s a XLL. Calling a managed COM dll isn’t to hard either. I can’t talk too much about all the others, so let assume they’ve all got good deployment stories.

Development.
As far as I know Excel DNA is the only one that will allow you to write code into a text file, and then compile that code at run time – this means you don’t even need an IDE to write a Excel DNA addin – how cool is that? So if you can’t get anything installed on your PC, you’re not beaten yet! (Although you will need the dot Net framework v2 or higher). Since all of these solutions can make use of some sort of IDE, then you get all the benefits of that. Addin Express, VSTO and Managed XLL also come with many other tools and wizards that make development much simpler and quicker.

Support.
All of the options are fairly well supported. Excel DNA has an active forum on Google groups, VSTO forums are common. There is some support on the internet for XLW, mostly from a quant community, and Addin Express and managed XLL have good support web pages, and offer extended support directly from the ISV.

Open source.
If you consider this to be important then you could factor it in. I’m not sure I would count it against something – it not being open – but it is really handy to be able to dig into the source code if you hit a problem, or need to expand/hack something.

Longevity.
The last thing you want to do is learn something new, then it dies 2 years down the line. Open source in theory is good from this perspective, as other developers can pick up the project if the original ones stop working on the code base. This doesn’t mean they will! Likewise if you buy into a “big” company then they will support the product for X years… but what does that actually mean? I think we could get ourselves in to knots on this one. The good news is that what ever you use you will be using the dot Net framework, so these skills are transferable.

Architecture.
I’m not going to go into the ins and outs of how each of these solutions work, but what I will say is that Excel DNA is one of the more flexible and agile ways to implement managed code into unmanaged applications. COM Interop**** isn’t. VSTO, Managed COM addins and indeed managed dll all use interop and a COM interfaces. Managed XLL uses the same methods to interface with Excel as Excel DNA, LXW is slightly different, but its still spits out a normal XLL at the end. Net4Excel is more of a manager of dot Net code, and works is a completely different way to the other products. Addin express does a few different things depending on what type of solution you are using, sometimes COM some times via the C interface similar to Excel DNA.

There might be other things to think about too, but I’ll leave it here for now, the important thing is to try and have at least some understanding of what the options are, and the pros and cons of each. Of course your situation might require something that only one or two products can offer, so your hand might be forced, but for most of us we have some flexibility.

Here’s my summary. If you want to get started, you might like to do that for free, and also create some solutions that can be used in the wild. In my view the easiest way to do that is with Excel DNA. There are some down sides to be aware of; some of the tooling is not as good in Excel DNA as in VSTO or Addin Express for example. Some of the features are missing, or not yet 100% stable, like they might be in Manged XLL for instance, but over all, pound for pound it’s a pretty good bet!

Now, that’s only my view, it might be wrong, I often am, you’re welcome to your own views, why not share them in the comments?

Thanks
Ross

* In this context – when talking about dot Net – Managed means that the code you write is “controlled” by the dot Net frame work. In practice what this means is there’s some part of the framework responsible for making sure your code runs smoothly, it catches small bugs and handles them in a way that won’t kill your app, it cleans up memory so you don’t have to, and lot of other stuff that I don’t know about!

** JetXLL and ex hale, don’t seem to be quite there yet, but I might be wrong…

***Shimming is where a managed component is interfaced by an unmanaged one. This is needed when working with Office applications because if the managed code in your addin errors, it can cause all the other managed code to stop working (if its not shimmed). VSTO has this build in, managed COM addins don’t, but there is a free template file for VS that adds this extra layer.

****COM Interop lets managed dot Net code be called and used by COM type code; in essence it converts types between native COM types and Managed types.

Tags:
Categories: VB.Net, VSTA, VSTO, XL DNA • 8 Comments »

Excel Development beyond VBA

9 August 2010 by Ross McLean

Over the next few weeks/months, I’m aiming to do a few post about Excel DNA. Excel DNA is a technology that allows dot Net code to be used with Excel, via a C XLL proxy. I hope to show how to start using this wonderful product, how easy it is, how much fun it is, and how cool dot Net stuff is. But before we delve deep into Excel DNA, I wanted to take a step back and ask a more fundamental question.

Why the heck should anyone programming Excel bother with anything that’s not VBA, and if not VBA, then why dot Net?

These are fundamental questions, right?

Why look beyond VBA?
I love VBA, I think it’s a truly brilliant technology, from conception to implementation. If you add API’s and .DLL to pure VBA, then there’s nothing it can’t accomplish. However, at this level it can start to get a bit tricky.  Solutions become messy, difficult to maintain, or time consuming to write in the first place. Still there’s often no need to use something else, so why bother?

This is a debate you could have ad infinitum, but there are some serious reason to start thinking about technologies* beyond VBA.

1. It’ aging.
OK, it’s aging very gracefully but there’s no getting away from the fact it’s an old technology base. Maybe not a huge issue on the face of it but there are something’s to think about. No-one knows when or if MS will “kill off” VBA, it looks unlikely to happen anytime soon, and if and when it does, it wont happen over night, but it will happen at some point.
The IDE, has not been updated in 10 years, and it’s really starting to show its age. As a developer, especially as a RAD developer, you should be concerned with this. Improvements in IDE’s have made development faster, more enjoyable, and less error prone, not as much a MS would like us all to believe, but things have moved on.
Another issue with its age is that it’s static, there’s been few, and I mean few updates to VBA since Excel 2000. In 2010, it got bummed up to version 7, which offered support for 64bit computing, but apart from that, nothing has been added to the langue for 10 years! If you need to integrate with the web, or modern databases, you’ll need external libraries, if you want to parallelise tasks good luck to you. If you want to use modern UI controls, you need to pull some strings… all these things are doable, just not easily.

2. Up skill yourself.
Lets face it, you’re comfortable with VBA, you know it inside out, and its hard to learn a new language, you can keep on using that API trick to help resize form controls, and that bit of code you have to pull data from a web service works ok. That’s fine, I agree, if it an’t broke, don’t fix it. But, don’t you want to try something new? Don’t you want to increase your skill set, make your CV look better? Wouldn’t you like to get some of the benefits other programmers are getting from using the classes Microsoft are dishing out? You can’t know every thing, let MS pick up the slack, and you can reap the rewards.

3. All the cool kids are doing it…
This is not as trivial as it sounds. How many things have you invented in VBA? Most of the code and ideas/approaches I use has been written better by someone else, someone who’s better at coding than me. New ideas and code isn’t going to come in VBA format. OK, you say, I have everything I need already, but how do you know what you need until you’ve seen the possible. Dot net will open up new opportunities for your code, new, dare I say it, better, ways to solve problems. And all the smart programmers out there writing dot Net code will be offering up their ideas for you to utilise too, why not give yourself the option to use it?

There are other reasons to move to technologies outside of VBA, there are many arguments and counter arguments to be had, but hopefully the above will get your mind ticking over… so the next part of the question…

Why dot Net?
This is a simple one to answer. It’s just easier to use an MS product than anything else. Think about it, we are talking about coding for a MS application, on a MS platform , why not use a MS tool. It’s not like you have a lot of choice anyway, you’ve got, VB6, which you in fact you don’t have because it’s very dead, you have, Delphi, which is fine, but with the best will in the world it’s never going to be able to match MS for office development… maybe Java – there are a few API scatted around the internet, but nothing to get too excited about- , or C, which is again is fine, but it’s hard work. Nope there’s no real argument to be had, if you move outside of VBA, it has  to be dot Net
Also, with the possible exception of Java, the dot Net framework is so much more powerful that anything else around, you would have to have a really, really, really, really compelling reason not to want to use it, and as we have just suggested, nothing else has that compelling reason.

So there you go, these are the reasons I think we need to be thinking about technologies outside of VBA, but wait, there more, I ask some Excel developers already using dot Net to list there top 5 reasons, here they are:

Mathias Brandewinder – Clear Lines consulting.

  1. VBA has stopped evolving, and as such is becoming dated.
  2. Development tools.
  3. Testability.
  4. VBA is limited to Office,
  5. Version control.

And 2 bonus reasons, thanks Mathias!

  1. Code re-use
  2. Using .Net within Office wasn’t the most pleasant experience, but VSTO and projects like Excel DNA make it increasingly easy.

N.B – I’ve edit Mathias original notes which where much longer, so please don’t comment saying… “yeah but you can do all that in VBA”, we know, but its nearly always easier to do it in .Net – Ross.

XL Dennis – VSTO &.NET & Excel

  1. Protection of the intellectual property
  2. The Security paradigm that .NET is built on
  3. The user friendly IDE allow us to be more productive
  4. Offer modern technologies and controls to create attractive UI for the end users
  5. Interaction with other programs and services

Simon Murphy – Codematic

  1. Powerful IDE
  2. Powerful languages
  3. Rich body of dev knowledge
  4. It is finally use-able for excel devs
  5. Allow separation of your code from users code.

Thanks Chaps…Looking at these there are lot of points that match my thoughts, which is pleasing (I wrote the copy before I got any replies from the other guys), and there are a few that I missed. Versioning, separation of code, “its finally use-able”, what’s your take? Do you think you would dip your toe, if only some Excel blogger would get his act together and give some practical advice? Let me know either way!

Ross

* I use the word technology, and not languages here because we are talking about a different way to programme Excel, its complexly different. The languages (VB in particular) are probably the things that are most similar between .Net and VBA.  Don’t worry though the learning cure not that steep!

** at least I am, sorry mac people, maybe coco/mono might work?!

Tags:
Categories: COM, VB.Net, VSTA, VSTO, XL DNA • 5 Comments »

The MIE Podcasts – Govert van Drimmelen

5 July 2010 by Ross McLean

In this podcast we caught up with Excel DNA author Govert van Drimmelen. Govert is a Mathematics lecturer at the University of Johannesburg, in South Africa. Excel DNA has had some attention from the Excel blog sphere in the past few months, Simon, Mike, and Mathias have all posted about it. I’ve been using it for a while now, and it’s brilliant. Here we talked about how Excel DNA came about, the basics of how it works, some of the new features and what’s planned for the further.

Govert van Drimmelen, famous for his Tom Selleck impression

If you don’t know what Excel DNA is, well then listen to the podcast, but in short it’s a free and easy way to get .Net code into Excel.

I’m sure you will all join with me in thanking Govert, not only for making time for the podcast, but also for all the hard work he’s done  in getting Excel DNA out of the door – well done Govert!!!

And remember if you’d like to see more of Excel DNA, I’m giving a talk about it at the UK Excel Dev Con, in a weeks time!!!

Podcast: Play in new window | Download

Tags: , ,
Categories: C/C++, Podcasts, UDF, VB.Net, VBA, VSTA, VSTO • No Comments »

A Devlopers Tool of Choice?

3 August 2009 by Ross McLean

When we talk about which language to use we often, if not always get talking about the technical advantages of a given tool, XLL’s are faster than VBA, VBA is easier to deploy than VSTO, VSTO is more secure than VBA, and so on and so on. Simon has some information here and I have some .Net stuff here. But one aspect that’s over looked is what each offers the developer.

Let’s consider an example. I’m writing a adding for the users in my department, it needs to contain some UDF’s, apply a custom formatting option to a selected range, help import a strangely formatted XML file, and link to a database and pull back some data. What are you going to write that in?

Well from a technical point of view you might like to used an XLL for the UDF’s, a Com adding for the formatting and database stuff and make use of some .Net classes for the XML processing. Nice!

For 90 percent of us, we will solve every problem using VBA, simply because that’s the only tool we have. But what if?

Here’s what I think different technologies offer the developer, not the technological advantage, it the things that make the developer’s live more enjoyable!

VBA: For simple stuff it’s quickest. As things get more complex and or the difficulty of what you are trying to do gets harder VBA becomes more of an effort (think of userform stuff, VBA, vs .Net?!). If you need something that needs constant changes, or something that you expect to grow in size over a period of time it might be worth doing it VBA.

VB6 (Com addins): If the UI you need is a bit more complex, than it might be time to switch to VB6, the more powerful forms and use of active X components means that you can have more choice when designing them. Also from a deployment POV, you have to worry less about the thing being installed on the PC, as you can compile them into the DLL

As the size and complexity of the project increases, switching to VB6 can be a help. Functionality can be complied in to different parts, external libraries can be used and packaged easily, source code can be shared with other developers – although this is rare for Excel projects.

Another good reason for a developer to pick VB6 com adding over VBA ones is that, although generally VB6 code can be changed with little effort to run in VBA, there are times when VBA does not quite have the same feature set, and dropping some code straight in to a VB6 com addin is just easier than messing around trying to convert it to VBA.

VSTO: Well its IDE isn’t 100 years old, thats a big plus! But the most compelling reason why an Excel dev would want to use VSTO, would be to make use of the framework. Forms are incredibly powerful in .Net (form namespace), but so too is all the functionality that the framework provided, like the threading name space, or the services name space. When we start to try and do really grand stuff, .Net is brilliant.

C/C++ [XLL]: Purely from an easy of development POV, I can only really think of 2 things XLL would over. One would be a situation where you need to deploy is a “hostile” environment, so the extra work in the coding would be repaid in the effort saved deploying. The other would be if there is a C library with some complex functions in it, a bit like with the VB/VBA issue, it might just be easier to keep the C code and wrap it up in a XLL.

VSTA: What’s VSTA?!?!

So there you go that’s my take on it, disagree?

Tags: , , ,
Categories: C/C++, COM, VB.Net, VSTA, VSTO • 4 Comments »

Professional Excel Development Second Edition

8 June 2009 by Ross McLean

Well my copy arrived this morning, and what a beauty it is! I’ve obviously only had a cursory glance (stupidly busy with work – UAT any one?) but I have to say that it’s really impressive.

pedsd1

There’s loads of updated content from the first edition and it’s an extra 200 pages long. There’s  an interesting chapter about interfaces that looks really good, the .Net stuff looks excellent and there’s lots of Excel 2007 info in there, including a chapter about cross version applications. Not to mention all the samples and code on the CD!

If you develop in Excel, do yourself a favour and get  a copy, go on, you deserve it! Looking on Amazon, the second edition appears to be cheaper than the first edition, which is fun! You could always try an win a copy over at Dicks!

Oh, and dont forget the PED  support forum!

Tags: , , , ,
Categories: VB.Net, VSTA, VSTO • 2 Comments »

« Older Entries