Excel Macro-Enabled Workbooks in 2007, Worksheet Functions and Error 51

What's the game with xlms files (Excel macro-enabled workbooks)? Why do I need to save to a "macro-enabled" file to use macros?

The purported justification is added security.

Microsoft are willing to complicate programmability (the key strength of the OLE architecture) under the pretext of security.

A short-term solution is to save as Excel 1997-2003 workbook.

Ok, so why are we so bothered about this.

Let's say we want to create a new Excel worksheet function. We can create this in a module. Suppose we make a function that computes sales commission which returns 5% of whatever number the user puts in. Easy enough. If you didn't define a variable, you'll get an error "Variable not defined" (assuming Option Explicit is set, almost always a good idea).

Suppose you write a worksheet function and a message box appears: "That function is not defined". What does it mean? Usually, it means you have created a function using a reserved keyword in VBA. For example, you can't define a worksheet function called step, because step is a VBA reserved keyword e.g. For I = 1 To 10 Step 2.

Taking Square Roots

To compute a square root in VBA use the SQR function. This is the same as SQRT in an Excel worksheet. Oftentimes, you'll find the raw VBA method is somewhat different to the worksheet function.

Taking Logarithms

In VBA, log means natural logarithm. In a worksheet, log means log to base 10. Alternatively you can use log(number, base).

Other things "not defined"

We've seen the error "Function not defined" now suppose you open your workbook one day and it says "User-defined type not defined". Problem is - you can't find the location where the code is breaking. Even shoving a breakpoint in where you think the problem is doesn't help.
Why? Because it's a "Compile Error" not a run-time error! First step, identify all type definitions in your VBA modules (they start with the prelude "Public Type" or "Private Type"). Then see all usages. Next check WHERE the Type is defined. Are there functions defined before it? Try commenting out those functions.

You do the steps above and try to rerun your program (I avoid the dimunitive term macro). Now, the Type error vanishes, but the next time you use the type you see the message "Compile Error: Internal Error". Out of curiosity, click Help and you will see a description of the error: you will see it is "Internal Error (Error 51)". Sheepishly, the docs ask the developer to "make sure (the) error wasn't generated by the Error statement or Raise method". The explanation and solution is given as: "An internal malfunction has occurred in Visual Basic .. contact Microsoft Product Support Services to report the conditions under which this error appeared". This is an error in the Visual Basic symbol table caused by the order in which you define things. Visual Basic requires you to define objects in the order: Types, Functions and Functions of Functions. Messing up the order (e.g. by so-called "refactoring" - one of the most stupid terms in computing) or missing a definition that the compiler doesn't catch can result in Error 51.