COM

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 • 7 Comments »

64 bit VBA

11 December 2009 by Ross McLean

In my last post, VBA Version 7  Daniel, author of Daniel’s XL Toolbox, commented that he had not come across anything untoward with the “new!” version of VBA, and reported, so far, all is well with his addin – good news! Then JP from Code For Excel & Outlook, pointed to a  VBA Compatibility tool (tool, info) from MS – thanks JP, I think this is called team work! ;-)

Anyway, I believe the major update might be support of 64 bit types in VBA?  I’m not running a 64 bit OS to test, but I found this:

Excel 2010 can load much larger workbooks. Excel 2010 made updates to use 64-bit memory addressing in order to move beyond the 2-GB addressable memory boundary that limits 32-bit applications.

The down side of this is:

Visual Basic for Applications (VBA)   VBA code that uses the Declare statement to access the Windows application programming interface (API) or other DLL entry points will see differences between 32-bit and 64-bit versions. The Declare statement must be updated with the PtrSafe attribute after inputs and outputs to the API have been reviewed and updated. Declare statements will not work in 64-bit VBA without the PtrSafe attribute. New data types are added to 64-bit Office 2010 VBA: LongLong and LongPtr. For more information about VBA, see the “64-bit VBA Overview” and “Declare Statement” articles in the Microsoft Visual Basic for Applications online Help in Office applications.

So if your using API calls in you VBA, and you hit a 64 bit versions, you could be about to hit some issue. Doesn’t sound like the fix will be to hard though, but we’ll wait and see eh!

Bonus thought: Does this mean the C API has been updated too?

Tags: ,
Categories: COM, MS, News!, VBA • 5 Comments »

.Net 4.0 imporvments, Good for Office Devlopers!

24 November 2009 by Ross McLean

I’ve bogged before about version 4.0 of .Net and what it could mean for Office devs. Here’s one other improvement I didn’t know about:

Improvements to Interop Marshaling in V4: IL Stubs Everywhere

The article sums it up thus:

· Faster interop marshaling: the more complex the signature the greater the speed-up

· x86 and x64 behavior matches: we’ve updated the x64 marshaling to behave exactly as x86 always has and mostly without impact to x64

· Better debugging: when something goes wrong in marshaling we now give you the ability, and specialized tools, to find the problem

There’s actual another important effect of the IL Stubs, and that is that the PIA is version independent. In fact the CRL will create it dynamical at run time. So now you can target all version of Office from one 4.0 addin – cool.

I’ll wait a see what the performance increase in like from 3.5 – that sounds like a post in its self!

Tags: , , ,
Categories: COM, VB.Net • 3 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 »

Working with Colours in Excel – M.I.E Colour Manager

21 February 2009 by Ross McLean

Here is the BETA version of my Colour Manager, and tool for creating colour pallets in Excel:

ColouPalletManager.PNG

It’s a VB6 com addin, the only thing you really need to know is that you load a picture on the left and then click that picture to get the colour at that point. You can then click a pallet square to place it where that square is. Click the send to workbook button to send it to the active workbook.

However I have also made a video to show how to use it. Bosh!

Here is the file!

UPDATED FILE HERE:
MIE Colour Manager Beta 0.3.0.zip

Enjoy, feedback more than welcome.

Tags: , ,
Categories: Addins, Charting, COM, Videos • 24 Comments »

« Older Entries