Creating a VBA Function

In the previous post, we saw the example of a macro or a subroutine which calculated the hypotenuse of a right angled triangle. While subroutines and macros are quite useful, in that particular example, it would be more useful to have a custom function built which can be called, as and when required.

Most MS Excel users are already aware of some built in functions that Excel offers: SUM, AVERAGE, MAX, MIN etc. And while it is possible to get away without writing custom functions by adding complex calculations, it is often easier to write a custom function to minimize errors and increase speed and efficiency.

If we were to re-write that same Hypotenuse macro as a function, we would use the following lines of code in a VBA editor module:

Function Hypotenuse(Side1, Side2)
    Hypotenuse = (Side1 ^ 2 + Side2 ^ 2) ^ 0.5
End Function

To use this function in Excel, we would do the following:

which would result in:

Writing a function is quite versatile as it gives us the option, as in this example, calculate the hypotenuse of right angled triangles quite quickly and easily.

Please note that there are some differences between a subroutine and a function. A function must take in arguments in most cases: in this case, Side1 and Side2. There are examples of functions that don’t take in arguments: for instance, the built in RAND() function in Excel does not take in any arguments and generates a random number between 0 and 1. However, such examples are rare.

Practice Examples

Try playing around with building your own custom functions. Here are a few ideas to practice:

1. A FtoC function which converts temperature in deg F to deg C.
2. A FinalVelocity function that calculates the velocity of an object right before impact with the ground when dropped from a certain height
3. An EquivalentRadius function that assumes a given area to be a circle and calculates the radius.

Feel free to include your code in the comments below.