Scribd Logo
Upload

Welcome to Scribd!

  • 75 views

    USER Defined - functIOS - Excel

    Uploaded by

    okvishal20021979
    This document discusses how to create user-defined functions (UDFs) in Microsoft Excel using Visual Basic for Applications (VBA). It explains that UDFs allow users to create custom formulas that can be reused. It provides steps for accessing the Visual Basic editor, inserting a module to write code, and provides an example of a UDF that calculates tax deducted at source (TDS) based on contract payment amount and type of contractor. The UDF returns either 1% or 2% of the payment amount as TDS depending on the criteria.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    USER Defined - functIOS - Excel

    Uploaded by

    okvishal20021979
    75 views4 pages
    This document discusses how to create user-defined functions (UDFs) in Microsoft Excel using Visual Basic for Applications (VBA). It explains that UDFs allow users to create custom formulas that can be reused. It provides steps for accessing the Visual Basic editor, inserting a module to write code, and provides an example of a UDF that calculates tax deducted at source (TDS) based on contract payment amount and type of contractor. The UDF returns either 1% or 2% of the payment amount as TDS depending on the criteria.

    Original Title

    USER Defined_functIOS - Excel

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses how to create user-defined functions (UDFs) in Microsoft Excel using Visual Basic for Applications (VBA). It explains that UDFs allow users to create custom formulas that can be reused. It provides steps for accessing the Visual Basic editor, inserting a module to write code, and provides an example of a UDF that calculates tax deducted at source (TDS) based on contract payment amount and type of contractor. The UDF returns either 1% or 2% of the payment amount as TDS depending on the criteria.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    75 views4 pages

    USER Defined - functIOS - Excel

    Uploaded by

    okvishal20021979
    This document discusses how to create user-defined functions (UDFs) in Microsoft Excel using Visual Basic for Applications (VBA). It explains that UDFs allow users to create custom formulas that can be reused. It provides steps for accessing the Visual Basic editor, inserting a module to write code, and provides an example of a UDF that calculates tax deducted at source (TDS) based on contract payment amount and type of contractor. The UDF returns either 1% or 2% of the payment amount as TDS depending on the criteria.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 4

    User Defined Function (UDF) http://msofficetuts.blogspot.

    com

    MS Excel allows us to create our own UDFs (user-defined functions).


    Using the help of VBA (Visual Basic Application), we can make formulas 1
    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.
    a. Step 1: Enable the Developer Tab.
    i. Click the Microsoft Office Button, and then click Excel
    Options.

    ii. In the categories pane, click Popular and select Show


    Developer tab in the ribbon.

    http://msofficetuts.blogspot.com Kamaraj Mani N


    User Defined Function (UDF) http://msofficetuts.blogspot.com

    iii. Click OK to close the Option dialog box.


    2
    b. Step 2: Open Visual Basic Editor
    i. Click the Developer item on the main ribbon and then
    click Visual Basic item in the code panel.

    Shortcut key to open the Visual Basic Editor


    Alt+F11

    VBE will open with three windows inside the VBE environment. They are,
    1. The Project Window,
    2. The Code Window (2), and
    3. The Properties Window (3).

    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).

    http://msofficetuts.blogspot.com Kamaraj Mani N


    User Defined Function (UDF) http://msofficetuts.blogspot.com

    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.

    http://msofficetuts.blogspot.com Kamaraj Mani N


    User Defined Function (UDF) http://msofficetuts.blogspot.com

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

    Explanation
    1. ContractTDS is the name of the function
    2. Amount & Contractor are called 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.

    Verification:
    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.

    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.

    http://msofficetuts.blogspot.com Kamaraj Mani N

    You might also like