
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
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.
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.
WRITING THE PROGRAM
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]
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.
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.
EXPLANATION
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]
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.
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.
EXPLANATION
=RemoveNumbers(G8) and the answer appears without the numbers
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.