Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 14. November 2010 18:06

I ran into something odd in Excel in my current project; it seems a lot like a bug to me, but that might also be simply that I know less about Excel than I think I do. Anyways, I have reproduced the issue with a simpler scenario.

The problem goes along these lines: in a spreadsheet, I create headers for data, and write out a few records below through code. Everything works fine – even if I hide a column, data gets properly written to the worksheet.

When I add a Filter to the header row, everything still goes great. But when I filter out some of the values in the filter of one of the columns and hide that column, then the code just doesn’t write any values in that column.

Let’s illustrate: here is some quick and dirty VBA code that generates rows of random numbers, and writes them to a sheet, starting in row 2 (my original code was C# / VSTO, but I figured it would be smart to try out VBA as well):

Dim CurrentRow As Integer

Public Sub WriteData()
    If CurrentRow = 0 Then CurrentRow = 2
    Dim data(1 To 3) As Integer
    data(1) = Int(Rnd * 5)
    data(2) = Int(Rnd * 5)
    data(3) = Int(Rnd * 5)
    Dim sheet As Worksheet
    Set sheet = ActiveSheet
    Dim startCell As Range
    Set startCell = sheet.Cells(CurrentRow, 1)
    Dim endCell As Range
    Set endCell = sheet.Cells(CurrentRow, 3)
    Dim targetRange As Range
    Set targetRange = sheet.Range(startCell, endCell)
    targetRange.Value2 = data
    CurrentRow = CurrentRow + 1
End Sub

If I run the macro WriteData on a sheet, I’ll see something like this:

WritingData

More...

by Mathias 13. June 2010 12:30

In my last post I explored how ExcelDNA can be used to write high-performance UDFs for Excel, calling .Net code without the overhead of VSTO. Using .Net instead of VBA for intensive computations already yields a nice improvement. Still, I regretted that ExcelDNA supports .Net up to 3.5 only, which puts the Task Parallel Library off limits – and is too bad  because the TPL is just totally awesome to leverage the power of multi-cores.

As it turned out, this isn’t totally correct. Govert  Van Drimmelen (the man behind ExcelDNA) and Jon Skeet (the Chuck Norris of .Net) pointed that while the Task Parallel Library is a .Net 4.0 library, the Reactive Extensions for .Net 3.5 contains an unsupported 3.5 version of the TPL – which means that it should be possible to get parallelism to work with ExcelDNA.

This isn’t a pressing need of mine, so I thought I would leave that alone, and wait for the 4.0 version of ExcelDNA. Yeah right. Between my natural curiosity, Ross McLean’s comment (have fun at the Excel UK Dev Conference!), and the fact that I really want to know if I could get the Walkenbach test to run under 1 second, without too much of an effort, I had to check. And the good news is, yep, it works.

Last time we saw how to turn an average PC into a top-notch performer; let’s see how we can inject some parallelism to get a smoking hot calculation engine.

More...

by Mathias 7. June 2010 10:23

Some time ago, I came across ExcelDNA, an open-source library designed to integrate .Net into Excel, via a  post by the Grumpy One, who described it as an interesting way to get Excel to talk to a compiled library. Sounds right down my alley, but I still managed to let 6 months pass until I finally tried it.

This reminded me of another post, by J-Walk this time, where he uses a random walk simulation in VBA to benchmark system performance. Back then, I ran the VBA code, and also the equivalent C# in a console app, out of curiosity: 11.38 seconds, vs. 2.73 seconds. Why not try the same experiment, and see if we can get the best of both worlds and bring some of the C# power into Excel via ExcelDNA?

So I created a Class Library, with the following method, a close equivalent to the VBA benchmark code:

public class Experiment
{
  public static string RandomWalk()
  {
     var stopwatch = new Stopwatch();
     stopwatch.Start();
     var position = 0;
     var random = new Random();
     for (var run = 0; run < 100000000; run++)
     {
        if (random.Next(0, 2) == 0)
        {
           position++;
        }
        else
        {
           position--;
        }
     }
     stopwatch.Stop();
     var elapsed = (double)stopwatch.ElapsedMilliseconds / 1000d;
     return "Position: " + position.ToString() + ", Time: " + elapsed.ToString();
  }
}

More...

by Mathias 12. December 2009 11:43

A few days back, I stumbled upon this page, where Frank Rice describes how to use VBA to list all VBA macros and functions a Workbook contains. I thought that was interesting: it’s not the type of VBA code most commonly seen, and the idea of VBA code interacting with VBA code is fun. So I tweeted it, and Charts GrandMaster Jon Peltier, in his own words,  could not “leave anything alone, and made some changes to how the procedure worked”. Nice changes, if I might add.

I am not one to leave anything alone, either, and wanted to check how well that would work using C#.

Disclaimer: I have done enough checking to know that the code works in non-twisted cases, but this is far from polished. This would need some handling for exceptions before making it to anything shipped to a client you care about, for instance. My goal was to provide a solid code outline, feel free to modify to fit your needs.

The class/method below takes in a fully-qualified file name (i.e. with the full path, just what you would get from an OpenFileDialog), and searches for all the procedures (sub or function) defined in VBA.

As a bonus, I added some extra code to extract the signature of the procedure, and the header comments. The signature - what arguments it takes as input, and what it returns - is a much better summary than simply its name, and I figured that if the author bothered to add comments, it was probably extracting that, too. It also illustrates nicely some of the functionalities of the API.

More...

by Mathias 29. October 2009 10:05

Recently, a client asked me if it was possible to create an Excel scatter plot of his products, adding a label on each data point, and using different colors and symbols for different types of products. You could think of this as plotting 5 dimensions at once, instead of the usual two.

I quickly coded a VBA macro to do that, with a sample workbook to illustrate the usage. The macro is pretty rough, but was sufficient for my needs as is, so I haven’t put extra efforts in: feel free to improve upon it…

Here is a sample of the output:PowerScatterPlot

More...

Comments

Comment RSS