18. VBA Tips - Write Your Own Excel Function

Nick's picture

Writing your own Excel function is remarkably easy as this tip will show you.

  • You can write a function in VBA and use it on a worksheet
  • The only thing you cannot do is to manipulate the workbook in any way
  • Apart from that, user defined functions are simple to write and use
  • So, if you think that Excel doesn't have the right function for you, write your own

Here's a screen shot of our user defined function in Excel:


Here's the code used:



  1. In a new VBA code module, type: "Function myFunction" then hit ENTER
  2. VBA will then populate the entry and exit points of the function
  3. To get a return from a function, you set myFunction to equal something
    • In this case: "Hello"
  4. In an Excel cell, type: =myFunction()
    • This will return "Hello" to the cell
  5. A function can also take arguments and use those arguments inside
    • Here's an example of that:



Training Video on how to Write Your Own Excel Function in Excel:

write-your-own-function.xls34.5 KB