Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
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


by Mathias 20. October 2009 10:46

When working with Excel workbooks with C#, I often need to retrieve the entire contents of a particular worksheet, so that I can process the data within C# code. By “the entire contents”, I mean the content of every cell between cell A1 and the last cell of the sheet, that is, the cell such that there is no cell on its right or below it that contains anything.

To do this, I use the following code, where excelWorksheet is a Worksheet (duh):

Excel.Range firstCell = excelWorksheet.get_Range("A1", Type.Missing);
Excel.Range lastCell = excelWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

object[,] cellValues;
object[,] cellFormulas;

Excel.Range worksheetCells = excelWorksheet.get_Range(firstCell, lastCell);
cellValues = worksheetCells.Value2 as object[,];
cellFormulas = worksheetCells.Formula as object[,];

The 2 resulting arrays of objects, cellValues and cellFormulas, contain the values and formulas, or null if the cell has no content.

However, while I was working on Akin recently, I realized 2 interesting things I had never noted before. First, the resulting array is 1-based, even though “C# arrays are zero indexed; that is, the array indexes start at zero”. Then, this code will fail if your spreadsheet contains only one value, in cell A1.More...

by Mathias 15. October 2009 18:03

I recently finished reading the Art of Unit Testing, by Roy Osherove (Manning); here are a few thoughts on the book.

Who to give it too

This is an excellent book for the C# developer with solid foundations in object-oriented design, who has already some exposure to writing unit tests. If you have worked on a decent-scale project, and found yourself thinking “hmmm, I am sure there is a smarter way to write these tests”, you should definitely get that book. Note that while it will be extremely useful to the test-driven development practitioner, this is NOT a book on TDD.More...

by Mathias 12. October 2009 10:54

I just found a solution to an issue which has been bothering me for a while. The reference article by Microsoft which describes how to deploy a Visual Studio 2005 Tools for Office solution using Windows Installer (a life-saver) doesn’t say anything about how to grant trust to multiple assemblies. This is a problem if you want to use satellite dlls in your add-in.

I figured out a workaround a while back, but I wasn’t convinced this was a good solution. Today, I came across this thread, where the second post (by Lex007) describes a simple way to do that, by modifying the SetSecurity project. Instead of passing only one dll, the tweak allows to pass a comma-separated list of dlls. I just tried it out, and it works like a charm.

by Mathias 10. October 2009 07:36

Much ado on the interwebs has followed Joel’s recent post on the now-infamous Duct Tape Programmer. You have to give it to Joel, he has a talent for writing pieces which get lots of people really worked up – what in some circles is called trolling. Out of curiosity, I looked up Google Trends, and unless some other piece of hot news related to duct tape surfaced on Sept 23rd, you can see with your naked eye the amount of buzz this one single post managed to generate. Impressive.




Comment RSS