Hi All,
Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called “User Defined Functions” (UDF).
UDF’s typically are placed in addins. As long as the addin is installed, the UDF’s work as expected. You get into trouble when the location of the addin changes, for example because you have distributed the Addin to your co-workers and they have installed it to their local user addin folder (which is different for each user!).
As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula:
=’C:dataFixLink2UDF.xla’!UDFDemo(A1,A2).
There are a couple of ways to avoid/resolve this situation:
Use fixed location
Of course the simplest way to avoid the problem is by fixing the location of
your addin. Tell all your users where the addin should be installed (or even
better: create a setup tool that doesn’t allow it to be installed elsewhere).
Your #Name! errors will not resurface.
Don’t use an addin
Well, not exactly so, you could still have an addin. But instead of keeping your
UDF code inside the addin, you create a facility that copies the UDF routine
into each workbook that uses it.
This is a neat solution, but it requires that your user has the security option
“Trust Access to Visual Basic Project” set.
John
Walkenbach’s Power Utility Pack uses this trick.
Redirect the UDF’s to the new location
This is the technique I’ll describe extensively in this article. The addin
checks each workbook the user opens whether this new workbook contains a link to
the addin. If so, it ensures the link points to the proper location.
Enjoy!
Regards,
Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
The trouble is Excel tries to use relative paths wherever possible. I recall having problems with users creating workbooks (that relied on my addin) from their C: drive, and trying to publish them on the network drive.
I also dabbled with VBA References to the XLA, which worked ok, but had similar problems with path locations.
In the end, I settled on the COM automation add-in (a feature of Excel 2002 onward), because it did not track path locations.
The only down side (and this is a minor issue, really!) is that you get funky looking dotted function names prior to the first successful calculations eg. =MyAddIn.MyFunction() before, and =MyFunction() after.
This was an issue for me because I had a custom right-click context menu for breaking the formula into tokens for a formula builder screen. It was never an issue for the users.
Automation Add-Ins are great.
Ideally, I would have used XLL, but I don’t have the programming experience with C++ to make it as rock solid as I required.
I’d love to write Excel UDFs in Visual Studio C#, but (I believe) only pay solutions exist so far, eg managedxll.
Rob
You can write automation add-ins with C# but the performance is rubbish. But there is an open source project called ExcelDNA that allows you to write C# UDFs, it passes them through the xll interface so they perform well.
I recently downloaded ExcelDNA, but that’s as far as I got. Development appears stalled, and looks experimental… not really something I want to throw at my customers.
To be honest, I just want Excel to support .NET libraries as add-ins out of the box. Oh, and I want a .NET flavoured RefEdit control.
I wonder why automation add-ins with C# perform rubbish? I would have expected VB6 compiled to perform worse. I need to look into that, I think :)
Cheers for the tip, I didn’t click that VS.NET can do COM.
If you wanted a fully-supported .Net solution you could try Jens Thiel managed XLL. I have not used it myself but i hear good reports (It also bypasses the marshalling problem in favour of the C API).
Technology timing comparison for one particular test UDF:
VBA 1.5
VB6 1.4
c# 9.1
c++ 0.88
As you can see the C# performance is truly appalling: this is supposed to be down to the marshalling layer.
In many cases compiled VB6 does not run any faster than VBA, probably because most of the time is either spent in data transfer between Excel and VB or in the VB runtime.
Even the most calculation-intensive UDF I have only showed a small (10-15%) improvement with a compiled VB6 automation addin.
(the VBA timings all bypass the VBE refresh slowdown bug)
Charles, What’s the “VBE refresh slowdown bug?” I googled, including with your business name, but nothing leapt out at me.
The bug is that each execution of a UDF changes the VBE titlebar to say “Running” and then switches back, unless the calculation is initiated from VBA. The resultant VBE screen flicker and windows message flows can slow down recalc significantly.
The problem is easily bypassed by using on-key to trap F9 etc, except in Automatic mode. The bug is present in all versions of Excel from Excel 97 through Excel 2007. Microsoft are aware of and can reproduce the bug.
The info is on the UDFs page on my website:
http://www.decisionModels.com/calcsecretsj.htm
Automatic and Function key Calculation slower than VBA calculation
UDFs calculate significantly slower when the calculation is started automatically by Excel or by pressing F9 than when the calculation is started by a vba calculation statement like Application.calculate.
The slowdown is significantly larger if the VBE is open and not minimised.
The slowdown is an overhead for each UDF that is recalculated, so its roughly proportional to the number of UDFs.
These timings are for 16000 very simple UDFs, using Excel 2002 on an AMD 1200MHZ with Windows XP:
Autocalc with VBE open and maximised
91 seconds
Autocalc with VBE open and minimised
38 seconds
Autocalc with VBE closed
2 seconds
Application.Calculatefull with VBE open and maximised
0.302 seconds
Application.Calculatefull with VBE closed
0.293 seconds
So if you are using a lot of UDFs it really pays to be in manual calculation mode and have a calculate button that uses VBA to initiate an Excel calculation (Application.Calculate or Application.Calculatefull).
Rob
I’m with you on Excel .net support, and the VSTO guys are making good progress so I hope the story improves in O14. For UDFs don’t discount XLM though it can be tons faster than VBA, especially for simple stuff (loops suck big time).
By my estimate the effort to learn C and the Excel C API = the effort to learn .net and the deployment challenges.