CUSTOM FUNCTION IN EXCEL [THROUGH VBA]

INTRODUCTION

We have created our first program HELLO WORLD using the sub procedures or a simple set of statements.

The statement in the sub procedures are executed in a line, unless there is some control which switches the control out of the line, which we’ll learn later in the course.

How many times it happens that we want to do something specific in EXCEL but we are limited with the availability of the BUILT-IN functions and we say that it can’t be done. So, for such a situation, we have got the power of VBA where we can create our own custom function.

The custom function can be as complex as we want and we can make it do many many things which are not possible otherwise.

So , in this article we are going to create a custom excel function with the use of VBA[ Visual basic for Applications] for Excel.

Article repeats some of the steps for the friends who haven’t gone through the previous post.

FIRST STEP

OPENING THE EXCEL

  • Double Click the Excel icon and start the application.
  • The Excel will ask you about the kind of worksheet we want to open.
  • Choose BLANK WORKBOOK and click ok.
  • Now we are inside the excel with a blank workbook opened before us.
EXCEL OPENING WINDOW

CHECKING THE STATUS OF DEVELOPER TAB

  • After opening the Excel
  • Check if DEVELOPER TAB is visible or not. If not refer to the previous post regarding the same process.
  • After the developer tab is visible, go to developer tab.
  • Click Visual Basic.
  • Now we have reached the visual basic window.
  • In VBE, add a module to the project folder as shown in the following post.[Adding a module]
  • We’ll write down the function program in the module.
VBA WELCOME SCREEN

In the welcome window of Excel , you can see that I have already saved the file with a name. That is all your preference as I just love to repeatedly do CTRL+S to keep the data saved to avoid any problem. you can also save now or later by pressing CTRL+S or going to FILE MENU and then save.The save dialog box will open for the first time asking you for location and name of the file and its type ofcourse.

WRITING THE PROGRAM

OUT OF THE FOUR TYPES DISCUSSED, WE’LL USE THE SUB PROCEDURE FIRST.Just to remind of the format of function Function in the VBA is written in the following formatFunction name_of_function(arguments to be passed)…..End Function

PRE PLANNING THE FUNCTION STRUCTURE

Before we create any function, it is very important to make a plan of the function. There are a few things which should be taken care of:

  • If any argument is passed or not.
  • Number of arguments to be passed into the function
  • If the returning value will be single or an array
  • The way, calculation will be done

etc.

So here, for the sake of understand we will create a small function where we will multiply the four numbers passed into the function.

Name of the function will be CustomMul (custom multiplication)

Four arguments will be passed into the function i.e. four numbers

Returning value will be one, the multiplication of all the numbers.

So let us see the code of the function first.

WRITING THE CODE FOR CUSTOM FUNCTION

CODE

‘ MY FIRST CUSTOM FUNCTION FOR EXCEL BY GYANKOSH.NET

Function CustomMul(a, b, c, d)
                   CustomMul = a * b * c * d
End Function

After typing the code, save the file by clicking save button in the toolbar.

PICTURE [HOW TO ENTER CODE]

VBA: CUSTOM FUNCTION CODE IN VBE

EXPLANATION

The function is shown above.

The function takes four inputs as a,b,c,d

After the values are passed into the function, the calculation is done as

CustomMul=a*b*c*d

and put the value in the function , which will be returned when we use the function. * We will also take care of data types but later. MsgBox(“Any Text”) This method displays the text entered in a small window which you must have seen in many applications.

USING THE CUSTOM CODE FUNCTION

After saving the function, now the function will be listed in our function library .

We can use this function in the workbook anywhere. Let us try to use the function.

VBA: USING CUSTOM FUNCTION

EXPLANATION

There are four numbers present in four different column and we need the multiplication result for all the four numbers.

So we put the function in the cell as

=customMul(G6,H6,I6,J6)

where the cells G6, H6, I6, J6 contain the four numbers to be multiplied.

The ans appear as 215832 which is correct.

The numbers can be passed directly also.  

CONGRATULATIONS!!! FIRST FUNCTION

WRITING THE CODE FOR CUSTOM FUNCTION-2

OBJECTIVE OF THE FUNCTION

TO CREATE A FUNCTION WHICH REMOVES NUMBERS FROM THE TEXT

PRE PLANNING OF THE FUNCTION

Let us plan the way we would try to solve this problem.

1. The input is going to be a TEXT which we need to operate upon , which means that we need to pass an argument as TEXT.

2.let us name the function as RemoveNumbers.

3. After the passing of Text, we need to find out which component is digit and which one is letter. For this we’ll compare the digits/letters one by one with an array which contains the digits 0 to 9.

4. We’ll make a temporary string, where we will add the letters and discard the numbers.

5. We’ll get our result.

CODE

Function RemoveNumbers(Txt)                             'Name of the function and passing a parameter
    temp = ""                                                          'A temporary String
    For i = 1 To Len(Txt)                                          'Loop for checking every digit/letter of the passed string.
             If Not Mid(Txt, i, 1) Like "[0,1,2,3,4,5,6,7,8,9]" Then
                temp = temp & Mid(Txt, i, 1)
              End If
            Next i
            RemoveNumbers = temp                               ' The value passed to the function for returning it.
 End Function

PICTURE [HOW TO ENTER CODE]

VBA: CODE EXAMPLE 2:REMOVE NUMBERS FROM TEXT IN VBA

LEN(STRING) This function returns the length of the given string as a number. Suppose we put LEN(“HELLO”), it’ll return 5 as answer.

EXPLANATION

The code of the function is shown above. We can copy the code and paste it in the VBE window. Let us try to understand the code. Although a few comments are given already.

We started by declaring the name and passing one argument into the function.Which will be a string(Text).

After this, we stated a temporary string named TEMP.

A FOR loop is used which will start from i=1 upto the length of the text. [FUNCTION LEN(TEXT) gives the length of the string/text)

Next, we check if the particular digit/letter is NOT matching any number from the array i.e. if its a text then, we add that letter to the string TEMP using a concatenate operator (&).

End the IF statement.

After the loop is complete the string temp will be having all the letter and no numbers.

Pass the value to the function name so that it can return it.

End the function.
‘ ( Text followed by an apostrophe) Comments can be put anywhere inside the code starting with an apostrophe. Commenting is very necessary for the code understanding.

USING THE FUNCTION

After saving the function, now the function will be listed in our function library .

We can use this function in the workbook anywhere. Let us try to use the function.

VBA: REMOVING NUMBERS FROM TEXT :USING THE FUNCTION

EXPLANATION

A sample text is there in cell G8 which is a random blend of letters and numbers.

‘The text is =asdf465sdf456sdf564f

We put the function as
=RemoveNumbers(G8)

and the answer appears without the numbers asdfsdfsdff which is correct. 

Now we can use this function anywhere in the workbook without even bothering about the code just like the EXCEL BUILT IN functions.