Syntax Summary

Good Coding

Tips Writing Good Reliable Computer Code

Always Use Commenting and 'Good' Variable names

Good code will read like plain English in that the variables names will do what they actually say.

In general more global variables which are used less often will have longer more descriptive names. In short snippets of the code as we cover in this course this is less of an issue but if you are writing thousands of lines of code - say for example a tax application then variables such as the personal allowance of the higher rate of tax should have descriptive names which make it unambiguous what they refer to. For our purposes the name of a function for which the roots are bring found of the derivative of the function should have names which make it clear what they refer to. Names such as 'solve_function' and 'diff_solve_function' would be good examples.

For variables which are much more local in scope such as the index in a For Next loop then single letter variable names are adequate especially if they are not actually used within the loop. An example of this would be using 'i' as the index when setting the initial values of an array to zero.

Within an object structure such as Cnum it is acceptable to use a and b as the real and imaginary parts as your descriptive variable will be the name of the complex number

You should use comments to:

Make sure your code will produce as accurate results as possible

When you are setting an epsilon to decide when to terminate a loop you should try out different values to see how small a number you can use that the precision of the computer can cope with. This creates risk that also need to be managed - see later.

Always use application.pi() rather than hard coded 3.141593, although you can set a variable to this value so you do not have to keep recalculating it in the code.

Double precision numbers can hold very large values and very small values but they struggle when you take the difference between two very similar large numbers and this can be a source of inaccuracy. Try and consider this when structuring code so you avoid this potential source of risk.

Sometimes it may be helpful to have a double termination criterion in a loop such as in Newton - Rhapson, you could have both the function value is sufficiently small and the change in x values is sufficiently small and keep looping until both criterion are met.

Always seek to manage risk within your code

Good code does not crash whether the user has entered sensible values or not. Your code should never be left on a situation where it is executing instructions for no reason simply because you have not controlled the program flow.

If the user enters parameters which do not make sense then the code should identify this and return an error code. If the case of an excel function you can use Result = CVErr(xlErrValue) to return an error value. In the case of a function which is used within other VBA code it is good practice to allow the function to pass back error codes to the calling code by using ByVal parameter passing. Use functions like IFERROR within Excel to pick up and handle error returns from VBA. Arrays within functions should use ReDim to allow them to take an a variable size. If not then the code should explicitly check that array bounds will not be breached and return an error if they will be.

Always use potion explicit as this will pick up a very large number of typo level errors which are otherwise difficult to pick up. It also prevents VBA from incorrectly guessing what type of variable you would like to actually use

No loops should ever run the risk of repeating forever. Always ensure that 'do while' and 'do until' loops have some way of stopping should the termination criterion never be reached. This scenario should then be managed in the code and appropriate errors returned to the visual basic. A simple count variable can be used to ensure that you never get an infinite loop.

Err on the side of using more brackets than less in formulas. Expression like x/y*z should definitely be avoided and should be written as (x/y)*z or x/(y*z). Even x^2/2 can cause problems and should be written (x^2) / 2.

You should generally name your cells within excel that are being used to pass values to VBA as then you can move the cells around and VBA will still pick up the right values. This applies when excel is reading values from excel within the code and not when parameters are being passed as function arguments.


Always indent your code within 'if then else end if structures' and loops as this helps someone else reading the code to see what it does.

You excel spreadsheet should clearly show what each input variable does with input values labelled and tables set out in a clear structure. Using bold type for table headers and for answers is also helpful.

You can also use different colours for input and output values and set the input parts of the spreadsheet in a different area to the output parts.