Prototyping Numerical Algorithms in Windows - Excel as a Viable Alternative to Matlab

Excel spreadsheets and VBA are good tools for prototyping numerical algorithms. There's just a few things you need to know to get the most out of the experience, a few tricks and a few traps. Once you have the prototype, you can implement the results in a more industrial-strength framework (I like C++/C#).

Writing a Custom Worksheet Function in MS Excel (Alt-F11, Alt-Q)

Excel is a remarkable product that can be customised. Starting with the basics, everyone should know how to write a custom worksheet function in Excel. Example: Write a worksheet function that returns 2*PI.

Alt-F11 (make sure the cell is not in edit mode)
Function TwoPi() As Double
TwoPi = 2 * Excel.WorksheetFunction.Pi
End Function
Alt-Q

Excel worksheet functions only recalculate when their inputs change. If you change the definition of the function, and the arguments remain the same, the function will not recalculate with shift-F9. I discovered this bug when I tried to change a function definition dynamically at run-time, using a set of cells to specify coefficients. Changed the def, pressed shift-F9 and ... nothing happened.

Macro Security (Digital Signed Macros)

If your macro security level is set to high you will only be able to run signed macros from trusted sources. Alt-TMS will allow you to change your settings. Alt-TD in the VBA editor will help you set digital signatures for your macros.

Option Explicit

When developing algos in VB Option Explicit is your friend. Avoid creating variable names implicitly, which can lead to difficulty debugging your algorithm.

Function Overloading in Excel VBA Macros

This is not allowed. Any attempt to do so will result in "Ambiguous Name detected". I think this is ok for the simplicity of the language. You can get round this by putting version numbers on your overloaded functions.

Name Lookup Bugs in Excel 2002

Create custom worksheet function. Call it F. Rename it to F1. Rename it to F. #NAME error. Rename it back to F1. It works. May need to do a few iterations before you see the bug.
There are other name lookup errors that can occur which are not bugs. For example, editing a function in a module in worksheet A and testing it out in worksheet B will not work. Each worksheet is effectively a separate namespace.

Min/Max in Excel VBA

Amazingly VBA doesn't have a min/max function!!! So use Excel's instead (example: Application.Max(5,2)).

Random Number Generation

In Matlab you can use the function rand(10) to choose a number between 1 and 10. To do this in Excel you can use the function RANDBETWEEN(1, 10) but this relies on Analysis ToolPak being a valid add-in in your Excel. If you don't have access to Analysis ToolPak you can write the following worksheet function:

Function Random() as Double
Randomize: Random = Excel.WorksheetFunction.Ceiling( Rnd * 10, 1)
End Function

Custom Worksheet Functions in C#

Msdn Blog Post on Excel C#.