PDA

View Full Version : Changes in Excel 2007



Djblois
12-03-2007, 02:01 PM
My company has just switched everybody over to Excel 2007 and my add-in works but it seems to be running slower. Has anyone noticed code running slower in 2007?

1)any suggestions to speed up code in 2007, that either couldn't be done in 2003 or was slower in 2003?


2) anybody find anything that runs slower in 2007 than in 2003 in code?

rory
12-03-2007, 02:10 PM
From everything I've read there seems to be a general feeling that a lot of things are a bit slower and that code to do with charts can run much slower in 2007. I don't know of any generic ways to speed things up, other than I assume compiled dlls or xlls will still be much faster than plain VBA.

Djblois
12-03-2007, 02:12 PM
what do you mean by compiled dlls or xlls? I know a dll is a Dynamic link Libraby but how do I use it and how do I compile it? Also, what is an XLL and how do I use and compile it?

Bob Phillips
12-03-2007, 02:27 PM
You can create ActiveX DLLs in VB6 or C++ that are compiled and therefore fast, and can be referenced from within VBA.

An XLL is windows DLL that Excel recognises and opens directly.

The DLL is faster by virtue of the fact taht it is compiled. However, as it is going to need a VBA wrapper to access it, it will still pass through the COM layer, through the C API to get anything done.

The XLL is also faster by virtue of the fact that it is compiled, but unlike the DLL, Excel recognises it and therefore can call its functions directly from the worksheet, thereby not needing to go through the COM layer, but directl to the C API, making them very fast.

VB6 DLLs are relatively easy to write, but a C++ version is magnitudes harder, and XLLs are C or C++, so are again much harder than VBA.

rory
12-03-2007, 02:33 PM
To create an xll you will obviously need a C programming tool (bare minimum a text editor and a compiler); for a dll you need VB - Visual Studio or a version of Office Developer (Office XP was the last version that had a Developer Edition). You can also go down the Visual Studio Tools for Office (VSTO) route with CodeBehind, but that's much more complicated.

Djblois
12-03-2007, 02:42 PM
I have the free version of Visual Studio. Can I use that to create it or do I need VSTO? Also, is it much quicker??? Finally, does anyone know any books or references they can recomment that I may use to help me with this?

rory
12-03-2007, 02:48 PM
Do you mean Visual Studio Express? If so, you should be able to create a dll with that using the Office Primary Interop Assemblies. I can't say how much (if any) of a speed increase you would get since that would require a COM wrapper and you will lose some speed in the interface. Also it will depend what your add-in does. If you had a copy of VB6, it's much closer to VBA.
Have you saved all your workbooks in 2007 format or are they being used in compatibility mode?

Bob Phillips
12-03-2007, 02:50 PM
Before you go the VS route, think hard. Managed code is an entirely new concept, and will need a lot of work, a lot of commitment, and a lot of financial investment. And quite honestly the product is still anything but mature, deployment is a nightmare. I understand that VS2008 is a big improvement, but I haven't tried it yet myself.

You will probably get more mileage in the short/medium term by looking at your design, and identifying the bottlenecks and attacking these.

Djblois
12-03-2007, 02:52 PM
Thanks, I am always looking at the bottle necks. Let me look at them again.