User Defined Functions (UDF)

MS Excel allows us to create our own UDFs (user-defined functions). Using the help of VBA (Visual Basic Application), we can make formulas only once and use them any number of times in our Excel Sheets and workbooks. These UDFs can be applied just like any other built-in functions, which are already available in MS-Excel.

In this Post, we are going to see how to create a simple UDF.

Visual Basic Editor:

To create the UDF, we need to call up the Visual Basic Editor.

1. In MS-Excel 2003, It is located in the Tools > Macros Menu.
2. In MS-Excel 2007, to locate it, we have to follow the following steps.

• Step 1: Enable the Developer Tab.
• Click the Microsoft Office Button, and then click Excel Options.



Picture A: Excel Options

• In the categories pane, click Popular and select Show Developer tab in the ribbon.



Picture B: Developer Ribbon

• Click OK to close the Option dialog box.
• Step 2: Open Visual Basic Editor
• Click the Developer item on the main ribbon and then click Visual Basic item in the code panel.

Shortcut to open VBE

VBE will open with three windows inside the VBE environment. They are,

1. The Project Window (1),
2. The Code Window (2), and
3. The Properties Window (3).

Picture C: Visual Basic Editor

We can resize the windows by left-clicking where the resize icons, holding and moving sideways or up and down.

User Defined Function

To create a UDF, we need to insert a module.

Click Insert > Module.

The VBE creates a New Module (Module1) inside the project (See Project window).

Picture D: Insert Module

Note: If we record any macro, the macro will be stored inside the module only.

Every custom function (UDF) begins with the key word ‘Function’ followed by the name of the function and parentheses. It ends with the key word ‘End Function’.

VBE will automatically insert this End Function key word, once we hit the enter key after typing the function, function name and the parentheses.

Now we are going to create a UDF to compute 194C Contract TDS.

When we make single payment to the Contractor, if the contract payment is Rs.30000 or more and the type of the contract is Individual/HUF, 1% on the Contract Payment will be deducted as TDS (if the contractor is other than individual/HUF, then 2% on the contract payment will be deducted as TDS). If the single payment is less then Rs. 30000, no TDS will be deducted on the contract payment.

Type the code inside the code windows as given in the below picture.

Picture E: Code Window

Explanation:

1. ContractTDS is the name of the function
2. Amount & Contractor are variables
3. First step, we need to identify the type of the contractor. If the user enters the type of the contractor as ‘others’, our function will pick the TDS Rate as 2%. Otherwise, it will pick 1% as rate of TDS.
4. Second step, we need to identify whether the single payment amount is less than 30000 or not. If the user enters amount as 30000 or more, system will apply TDS rate on the Payment amount. Otherwise, it will consider ContractTDS as Nil.
5. Finally, the function will return the result.

How to insert UDF in Excel Worksheet:

1. Close the VBE window. Go to the excel and select any worksheet.
2. Select B1 and type ‘others’
3. Select B2 and type ‘35000’
4. Enter our formula as given in the below picture.



Picture F: Apply UDF in the Worksheet

5. Function will return ‘700’ as TDS. (i.e. 2% on 35000, because amount is not less than 30000 and the contractor type is others)
6. Change your inputs and see the result.

Multi-Level Numbering - Excel

In this Tutorial, I am going to explain you how to insert multi-level numbering using Formulas in Excel. Click here to download the exercise spreadsheet.

Details available in the Excel sheet (KM_Ex3_Multi-level_Numbering.xls) are -

Column A	Chapter Numbers	24 Chapters
Column B	Titles	247 Titles
Column C	Count	247 Titles

Before we start, just estimate how much time it will take to number the 247 titles along with Chapter numbers like 1-1, 1-2….27-1, 27-2….27-n. I guarantee that we can number them within 15 seconds.

Is that possible?

Let us try it out..

Step 1:

1. Insert a column between A & B;
2. Enter the cell value as ‘1’ in Cell B3. i.e. we are starting with Chapter 1.
3. Enter the following formula in Cell B4

           =IF(ISBLANK(A4),B3,B3+1)

Picture A: If fuction

Formula Explanation:

1. ISBLANK(A4): This formula checks whether any value/formula exists or not in Cell A4. If any value is there, the function returns the message "FALSE" (i.e. the cell has some value in it). If no value/formula exists, it returns “TRUE”.
2. IF(ISBLANK(A4),B3,B3+1):
• Formula Structure: =(logical_test,value_if_true,value_if_false)
• Condition (logical_test): We need to check whether A4 is blank or not. We are using ISBLANK(A4) as a logical test. We will get either TRUE or FALSE as a result.
• True Value (Value_if_true): If the ISBLANK(A4) returns TRUE, we are going to retain the same chapter number. Hence we put B3 as Value_if_true.
• False Value (Value_if_false): If the ISBLANK(A4) returns FALSE, next chapter is started. we need to add 1 with the previous chapter number. Hence we need to put B3+1 as Value_if_false.

Step 2:

1. Double click the Fill Handle
Fill Handle: The small black square in the lower-right corner of the selection. When you point to the Fill Handle, the pointer changes to a black cross



Picture B: Fill Handle

2. All the remaining 245 rows will be filled with corresponding Chapter Numbers as shown in below picture.



Picture C: Chapter Numbers

3. Chapter number for all the 24 chapters are ready now.

Step 3:

Now we are going to insert title numbers.

1. Insert a new column between B & C
2. Enter the cell value as ‘1’ in Cell C3. i.e. we are starting with Title number 1.
3. Enter the following formula in Cell C4

          =IF(ISBLANK(A4),C3+1,1)

Picture D: IF Function

Formula Explanation:

1. ISBLANK(A4): This formula checks whether any value/formula exists or not in Cell A4. If any value is there, the function returns the message "FALSE" (i.e. the cell has some value in it). If no value/formula exists, it returns “TRUE”.
2. IF(ISBLANK(A4),C3+1,1):
• Condition (logical_test): We need to check whether A4 is blank or not. We will get either TRUE or FALSE as a result.
• True Value (Value_if_true): If the ISBLANK(A4) returns TRUE, we are still in the same chapter and need to add 1 with the previous title number to get Number for next title. Hence, we need to put C3+1 as Value_if_true. 
• False Value (Value_if_false): If the ISBLANK(A4) returns FALSE, Next Chapter is started. We need to put ‘1’ as first title number for that chapter. Hence, put 1 as Value_if_false.
3.  Double click the Fill Handle  
4. All the remaining 245 rows will be filled with corresponding Title Numbers. Check C17, it is started with 1 as a title number for next chapter (Chapter 2).

Picture E: Title Numbers

Step 4:

1. Insert a new column between C & D
2. Select D3, click insert function icon

3. Insert Function dialog will pop up
• Select Text from the Select a category drop down box.
• Select CONCATENATE from the Select Function list box
• Press OK

4. Enter the field with the values as given in the below image and press OK

5. Double click the Fill Handle
6. Column D represents the multi-level numbering for the titles.
7. Now select Column B,C & D.
8. Press Ctrl+C.
9. Right click on the Cell B1, Select Paste Special.
10. Paste Special Dialog will appear. Now select Value under Paste group and Click OK

11. Remove Column B & C

Result will be as given in the below image

Try yourself. Sure, It wont take more than 15 Secs.