Although Excel has 350+ functions built-in, you can use VBA to build your own custom functions.
A couple of months ago, I delivered a 1-hour live online training session called VBA 101. It was a gentle introduction to VBA in Excel – what it is, how it can be used and a few examples. If you were unable to attend, there’s a recording available here:
https://theexceltrainer.co.uk/training-session-recording-excel-vba-101-lpi-version/
Part of the webinar covered how to create user-defined functions. You can now download the file that I used in the user-defined functions section of the webinar.
You can download it from here.
It contains 10 examples:
- Calculating a number of gallons based on a cell containing a number of litres
- Calculating a number of litres based on a cell containing a number of gallons
- Calculating the amount of carpet needed to carpet a room, given the length and width of the room
- Calculating the amount of carpet needed to carpet a square room
- Calculating the amount of grass needed in a garden, given the size of the garden
- Calculating the number of miles per gallon for a hire car, given the start mileage, end mileage and fuel tank capacity
- Calculating a sales bonus based on the revenue generated
- Combining the contents of 2 cells, ensuring a space is added between the 2 values/strings (often forgotten when using concatentate or ampersand)
- Converting a celsius temperature to farenheit
- Converting a farenheit temperature to celsius
So what’s the point of creating simple functions like these? Well lets take the temperature conversion example. Most Excel users are not mathematically-minded and even if they are, most Excel users don’t want to think about the underlying calculations in a spreadsheet.
So to be able to say to someone, just type =FToC and include the cell containing the farenheit temperature, that will speed up the creation of the spreadsheet, not to mention reduce the risk of errors due to incorrect formulas.
To view the VBA code in the downloaded file, simply press ALT+F11 and double click on the module called Functions.