This type of VBA error is also usually relatively easy to fix, as you will be given details of the nature of the error, and shown the location where the code has stopped running.

For example, if your code attempts to divide by zero, you will be presented with a message box, which states "Run-time error '11': Division by zero".

You can then link all those cells which need to use the formula, to that single cell.

So look at your spreadsheet, if you have a vlookup() that gets repeated over a 1000 cells and essentially all of them return the same value, take it out and put it in a single cell.

The Auto Syntax Check option is accessed by selecting Compile Errors are recognised by the VBA compiler as being illegal and therefore, are highlighted as errors before your macro even starts to run.

Thus, the question whether Microsoft Excel is an appropriate option for developing optimization models and algorithms for complex combinatorial problems suggests itself.

More often than not people would use an If, Else Statement to test whether a condition is TRUE or FALSE.

Follow this link to see why Loops should (and usually can) be avoided.

The Auto Syntax Check option causes a message box to pop up whenever a syntax error is typed into the visual basic editor.

If this option is switched off, the visual basic editor still highlights syntax errors in red.