VBA – Custom Excel Formulas

Posted on August 4, 2013 by

In this article I will show how to make custom MS Formulas. This is quite useful, if you want to use a formula, which is not available in the predefined Excel formulas. The inspiration for this article came, after I was looking in the way the methods in C# work and I realized that I have already seen this somewhere… Yup, I have seen it in Excel.

The first formula, which I have created takes one number, squares it and its value. For example, if we take 5, the formula multiplies it (5*5), adds 5 to the result and returns 30.

This is the code of the formula:

Function MutltiplyAndSum(a) As Integer
Dim b As Integer
b = a + a * a
MutltiplyAndSum = b
End Function

Function MutltiplyAndSum(a) As Integer

Dim b As Integer

b = a + a * a

MutltiplyAndSum = b

End Function

This is how it looks like, in excel:

The second Formula needs two numbers as input, and returns one number, which is the result of the sum of the two squares. The code is here:

Function Multiply2(a As Integer, b As Integer) As Integer
c = a * a + b * b
Multiply2 = c
End Function

Function Multiply2(a As Integer, b As Integer) As Integer

c = a * a + b * b

Multiply2 = c

End Function

The trick with the custom formula is to define what you are giving to it (in my first case I am giving one integer) and what it should return. In the first case I want the integer “b” as a result, in the second case I want “c”. It is quite easy, once you get used to it and it can be really useful! Enjoy it!

If you want to see the file and to use the two formulas, you may download it from here.