When creating a function of your own in Excel often it is more practical to borrow formulas rather than code all of this behavior yourself. In this post, we will learn how to create functions that borrow other functions already available in Excel. Specifically, we are going to create a function that calculates the range of a dataset through using the difference between the max and min functions of excel.

In the piture below we want to find the range of this data.

To get the answer we need to go into the VBA editor. This is available by clicking on the developer tab and clicking on Visual basic. When you do this you will see the following.

Once inside visual basic click on insert->module to add a new module. Inside this module is where we will place our code.

The Code

The code is rather simply and is shown below

At the top we type the name function to indicate to Excel what we are making. Next to this, we define the name of the function and inside the parentheses we indicate what the arguments are. after this we indicate the data type. In this situation the name of the function is “spread” and it takes the argument “spr” and the data type is “Double.”

In the next link we explain the behavior of the function. We use the command application.WorksheetFunction,Max() to call the max function whic will find the largest value in the spr data object. We repeat this process with the min function after the minus sign. Lastly, we end the function.

The Results

We can now test the function. In the first picture we call the function and in the second we show the results.

Now for the results

The dataset is small enough that you can check this manually. The number 12 is the largest while the number 1 is the smallest. The difference between 12 and 1 is 11.

Conclusion

With a few lines of code we can quickly borrow functions in Excel to create our own functions. Doing this can save a lot of time especially when you begin to create much more complex functions