3

I have been trying to speed up a macro by using XLLs, however, it seems is a lot faster with the UDF than with the XLL.

Some data with code profiling demonstrates it

XLL Time for the sub Proc:module 1 iteration 11.64831 seconds

UDF Time for the sub Proc:module 1 iteration 4.25986 seconds

It happens on two UDFs that I have converted, factor is abour 2x or 3x slower. For instance, the XLL function for this is:

[ExcelFunction(Description="Joins cell values", Category="Example1")]
public static object RangeJoin(object[,] cells)
{
List<string> list = new List<string>();
foreach (object o in cells){      
if ( !(o is ExcelEmpty) )
list.Add(o.ToString()); }
return string.Join(" ", list.ToArray());

}

The UDF Function is 

Function RangeJoin(Rng As Range) As String
Dim vArr As Variant
Dim v As Variant
vArr = Rng

RangeJoin = vbNullString

For Each v In vArr
  RangeJoin = RangeJoin & " " & v
 Next v

End Function

Both were tested for Range(A1:A701) with data and blanks between cells, both work as expected, just XLL is slower.

Sgdva
  • 2,800
  • 3
  • 17
  • 28
  • You're doing more work in your C# version. BTW, how does C++ come into play here? – Ed S. Mar 10 '15 at 05:58
  • The so called "XLL" looks like some custom variant of JavaScript (or EcmaScript, if you will), or perhaps C#. The so called "UDF" looks like VBA, VIsual Basic for Applications. There's no C++ at all here. Why is this tagged C++, and, assuming that it's Microsoft that uses the silly names XLL and UDF, why t.f. do they do that? – Cheers and hth. - Alf Mar 10 '15 at 06:18
  • 1
    You *could* be ahead with code like this, if only it would give the processor a harder workaround. You are just manipulating strings, that has a constant cost that doesn't depend on the language or runtime. What kills you here is the required data conversion, going from a Range to an object[,] is quite expensive. – Hans Passant Mar 10 '15 at 09:05
  • My bad, corrected the tag C++ to C#. UDF (User Defined Function) is in VBA, but, the code is slow. I have read that by turning a UDF into a XLL library would be faster, but this isnot the case. – Sgdva Mar 10 '15 at 13:54
  • @HansPassant Yeah, I thought that object was heavier than Range, but then, is there no possible way to turn that function into a XLL? I can't see another variable in Visual Studio that could simulate Range for excel. – Sgdva Mar 10 '15 at 13:58
  • XLLs were invented to write code in a native language. You didn't. Even if you do write it in C++, you are still fairly unlikely to be ahead significantly. And oops, workaround = workout. – Hans Passant Mar 10 '15 at 14:03
  • @HansPassant I'm using excel DNA in visual studio to turn it into the XLL, I assume that, by your answer, function needs to don't rely on ranges defined by excel or it will be slower, so XLLs just work for custom calculations and not this kind of functions? – Sgdva Mar 10 '15 at 15:33

1 Answers1

2

  1. VBA accesses cell contents directly. It uses its native types and 99% of the time a well coded UDF will be faster than an external library doing the same thing.

  2. In your example you are doing doing a lot more in the C# version.

  3. If you really want to improve the performance and take advantage of C# ability to do stuff without looping* you should consider passing a Range object to your external library pretending like you're actually passing a 1D array. Receive that in C# and you're good to go for all the cool stuff like LINQ.

Community
  • 1
  • 1
  • Sadly I need 15 rep to vote it up, but, yeah, I guess I should stick with the slowness of this UDF, range are always variable, so, I guess I'll stick with the UDF, thanks! – Sgdva Mar 10 '15 at 16:44
  • @Sgdva np. you can always accept the answer if you are happy with it by clicking the green checkmark next to the score. Btw. To improve the current VBA code maybe rewrite your question a bit and post it on [CodeReview](http://codereview.stackexchange.com). There are a lot people there who can help you optimize your code a lot :) One suggestion would be to first filter the range and exclude blanks and then load that to an array. –  Mar 10 '15 at 17:01
  • Agreed on the filter, but, sadly, the formula relies on the results of another formula which long is variable according to user input in a main process (this is not the immediatly after one). – Sgdva Mar 10 '15 at 17:13