UDF (User Defile Function) in EXCEL

About User Defined Functions

Excel provides the user with a large collection of functions inbuilt, more than enough to satisfy the average user. Many more functions can be added by installing the various add-ins that are available on internet.

Most of the calculations can be easily done with the inbuilt functions provided by Microsoft,  but it isn't long before you find yourself wishing that there was a function that did a particular job, and you can't find anything suitable in the list. You need a UDF.

A UDF (User Defined Function) is simply a function that you create yourself with VBA. UDFs are often called "Custom Functions". A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in.

UDFs can be accessed by code modules too. Often UDFs are created by developers to work solely within the code of a VBA procedure and the user is never aware of their existence.

Like any function, the UDF can be as simple or as complex as you want. Let's start with an easy one...

A function that will calculate Simple interest.

Open a new workbook and then open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or ALT+F11).

You will need a module in which to write your function so choose Insert > Module. Into the empty module type: Function SimpleInterest and press ENTER.
The Visual Basic Editor completes the line for you and adds an End Function line as if you were creating a subroutine.
You will find it as follow:

Function SimpleInterest()

End Function

 For calculation of simple interest we need 3 values from user i.e. Principal Amount, Rate of Interest, and Number of period. So, we will add 3 parameter to the function, and calculation of the Simple Interest.

Function SimpleInterest(pAmt As Double, rate As Integer, period as Integer)

 Dim si as Double

 si = (pAMT * rate * period)/ 100                  ' (PRN/100)

 SimpleInterest = si                               ' return value 
End Function

Now we can test our function right away. Switch to the Excel window and enter figures for principal, rate of interest and period in separate cells. In a fourth cell enter your function as if it were one of the built-in ones. In this example cell A2 contains the Principal Amount (1000), cell B2 has Rate of Interest (12%) and cell C2 has period(24). In D2 I typed =SimpleInterest(A2,B2,C2) and the new function calculated the simple interest  (28.8)...









A function that will give Name of Weekday

There is a function in Excel that return me the number of Week, but I want name of the week not a number!!! So, let's create function that will show us name of week(like "Monday").
We can do this by doing manipulation the inbuilt function. But I don't it to do every time so I decided to create a Custom user define function, that will full fill my requirement.


So, Let's Start...

Function WeekDayName(InputDate As Date)
    Dim wDay As Integer    Dim wDayName As String
 

    wDay = Weekday(InputDate, vbSunday)         ' vbSunday is set as defaule start date

    Select Case wDay
        Case 1
            WeekDayName = "Sunday"
        Case 2
            WeekDayName = "Monday"
        Case 3
            WeekDayName = "Tuesday"
        Case 4
            WeekDayName = "Wednesday"
        Case 5
            WeekDayName = "Thursday"
        Case 6
            WeekDayName = "Friday"
        Case 7
            WeekDayName = "Saturday"
    End Select
End Function

I've called my function "WeekDayName" and it takes a single argument, which I call "InputDate" which (of course) has to be a date. Here's how it works...

• The first line of the function declares a variable that I have called "WeekDayNumber" which will be an Integer (i.e. a whole number).
• The next line of the function assigns a value to that variable using Excel's WEEKDAY function. The value will be a number between 1 and 7. Although the default is 1=Sunday, I've included it anyway for clarity.
• Finally a Case Statement examines the value of the variable and returns the appropriate piece of text. 

Were do we find our custom function in Excel?

You can also find the functions listed in the Function Wizard (sometimes called the Paste Function tool). Use the wizard to insert a function in the normal way (Insert > Function).

Scroll down the list of function categories to find User Defined and select it to see a list of available UDFs...

Extract Flash Game from Excel with VBA

Open new Excel file and insert new ActiveX Button control past following code in it's Click function.

Private Sub CommandButton1_Click()
    Dim tmpFileName As String, FileNumber As Integer 
    Dim myFileId As Long 
    Dim myArr() As Byte 
    Dim i As Long 
    Dim MyFileLen As Long, myIndex As Long 
    Dim swfFileLen As Long 
    Dim swfArr() As Byte 
    tmpFileName = Application.GetOpenFilename("office File(*.doc;*.xls),*.doc;*.xls", , "Select Excel / Word File") 
    If tmpFileName = "False" Then Exit Sub 
    myFileId = FreeFile 
    Open tmpFileName For Binary As #myFileId 
    MyFileLen = LOF(myFileId) 
    ReDim myArr(MyFileLen - 1) 
    Get myFileId, , myArr() 
    Close myFileId 
    Application.ScreenUpdating = False 
    i = 0 
    Do While i < MyFileLen 
        If myArr(i) = &H46 Then 
            If myArr(i + 1) = &H57 And myArr(i + 2) = &H53 Then 
                swfFileLen = CLng(&H1000000) * myArr(i + 7) + CLng(&H10000) * myArr(i + 6) + _ 
                CLng(&H100) * myArr(i + 5) + myArr(i + 4) 
                ReDim swfArr(swfFileLen - 1) 
                For myIndex = 0 To swfFileLen - 1 
                    swfArr(myIndex) = myArr(i + myIndex) 
                Next myIndex 
                Exit Do 
            Else 
                i = i + 3 
            End If 
        Else 
            i = i + 1 
        End If 
    Loop 
    myFileId = FreeFile 
    tmpFileName = Left(tmpFileName, Len(tmpFileName) - 4) & ".swf" 
    Open tmpFileName For Binary As #myFileId 
    Put #myFileId, , swfArr 
    Close myFileId 
    MsgBox "SaveAs " & tmpFileName 
End Sub 

Calculate with Microsoft Word

Hey!!!! Most of us are know that in Microsoft there is only an Excel is the application that can calculate. But this is not True. We can also perform basic calculations using Microsoft Word tables.

It can do basic calculation and updated the values, and also do calculation with bookmarks...




Let's see how we can do this.....

I am assuming that you are aware with excel spread sheet and Cells funda....

So, not wasting any time and start this session..

Create Table with 5 rows and 3 columns. as shown in following figure...

Figure 1






Here Column A = Sr.No, Column B= Subject Name and Column C = Marks Scored

Now, we want to calculate the total of Mark scored in Column C row number 5... yes, same as we do in excel.

For calculation within the table you have to press Ctrl + F9 key. You will see "{ }" with brown color highlight (As shown in figure 2). We have to add our formula in this brackets. Write "=Sum(Above)" without ( " ), and then Press Shift + F9, you will see result in (Column C, Row 5) as shown in figure 3

Figure 2




Figure 3






"Above" is the keyword for the function Sum(). It identify that the above the formula all rows has some value, same you can use "Left". Function you can use in Word are... ABS, AND, AVERAGE, COUNT, DEFINED, FALSE, IF, INT, MAX, MIN, MOD, NOT, OR, PRODUCT, ROUND, SIGN, SUM, TRUE.

A few skills for a smart presentation

A few skills for a smart presentation

Becoming a competent, rather than just confident, speaker requires a lot of practice. But here are a few things you can consider to start sharpening your presentation skills:

-    10-20-30 rule – This is a rule that states that a PowerPoint slide should have no more than 10 slides, should take no longer than 20 minutes and have no text less than 30 point font.

-    Be entertaining – Speeches should be entertaining and informative. People expect some appeal to there emotions. Simply reciting dry facts without any passion or humor will make people less likely to pay attention.

-    Slow down – Nervous and inexperienced speakers tend to talk way to fast. Consciously slow your speech down and add pauses for emphasis.

-    Eye contact – Match eye contact with everyone in the room.  You shouldn’t focus all your attention on the decision maker, since  others in the room may hold persuasive sway over the decision maker

-    15 word summary – Can you summarize your idea in fifteen words? If not, rewrite it and try again. Speaking is an inefficient medium for communicating information, so know what the important fifteen words are so they can be repeated.

-    Don’t read – If you don’t know your speech without cues, it doesn’t just make you more distracting. It shows you don’t really understand your message, a huge blow to any confidence the audience has in you.

-    Stories – If your presentation is going to be a longer one, explain your points through short stories, quips and anecdotes. Great speakers know how to use a story to create an emotional connection between ideas for the audience.

-    Make yourself heard - Nothing is worse than a speaker you can’t hear. Even in the high-tech world of microphones and amplifiers, you need to be heard.

-    Practice – Practice your speaking skills regularly in front of an audience. Not only is it a fun time, but it will make you more competent and confident when you need to approach the podium.

-    Do apologize if you’re wrong – One caveat to the above rule is that you should apologize if you are late or shown to be incorrect.

-    Put yourself in the audience - When writing a speech, see it from the audiences perspective. What might they not understand? What might seem boring? Use “What’s In It For Me” to guide you.

-    Have fun - With a little practice you can inject your passion for a subject into your presentations. Enthusiasm is contagious.

Basic Function available in Excel....

=IF() Function

How the IF() function works:
Syntex : "=IF(Condition,TRUE,FALSE)"

The Excel IF() checks to see if a certain condition is true or false. If the condition is satisfied then it returns true, else retun false.

The IF() we are using in this tutorial will asks if the value in column A is greater than the value in column B. If the condition get true then it will show value as "A is larger" in column D. If it is not, the IF function will place the statement "B is larger" in column D.
Add some value to Cell A1 and Cell B1 and write following formula in Cell D1.

=IF(A1 > B1,"A is larger","B is larger")

Note: There are two text statements "A is larger" and "B is larger" are in quotations. In order to add text to an Excel IF Function, it must be in quotation marks.

How to write Multiple IF function ?

From above example, if we want to check for 3 different value, we have to use multiple if function... Let's C how can we do it.
example with value : 
Cell A1 =5

Place formula at Cell B1
=IF(A1=5,"Five",IF(A1=10,"Ten","Unknown Value"))

In above examples we check for the value in Cell A1, If it contains value 5 then in Cell B1 you will get "Five", in case if you now change value in Cell A1 to 10 then In Cell B1 it will automatically reflect as "Ten" and other this two value will show "Unknown Value".
As I told you IF check for the Condition and work accordingly, when we have wrote "=IF(A1=5," is the condition check point TRUE="Five",  FALSE = IF(A1=10,"Ten","Unknown Value"). when we have written again IF condition in the IF that means it again going to check for condition and will return to parent function either TRUE or FALSE.

About macros in Excel

If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions (function: A pre-written formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) that are stored in a Microsoft Visual Basic module (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.) and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps.

This topic provides an overview on recording and running macros in Excel. 

Recording macros

When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands. If you make a mistake when you record the macro, corrections you make are also recorded. Visual Basic (Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) stores each macro in a new module attached to a workbook.

Making a macro easy to run

You can run a macro by choosing it from a list in the Macro dialog box. To make a macro run whenever you click a particular button or press a particular key combination, you can assign the macro to a toolbar button, a keyboard shortcut (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.), or a graphic object on a worksheet.

Managing your macros

After you record a macro, you can view the macro code with the Visual Basic Editor (Microsoft Visual Basic Editor: An environment in which you can edit macros that you've recorded and write new macros and Visual Basic for Applications programs.) to correct errors or change what the macro does. For example, if you wanted the text-wrapping macro to also make the text bold, you could record another macro to make a cell bold and then copy the instructions from that macro to the text-wrapping macro.

The Visual Basic Editor is a program designed to make writing and editing macro code easy for beginners, and provides plenty of online Help. You don't have to learn how to program or use the Visual Basic language to make simple changes to your macros. With the Visual Basic Editor, you can edit macros, copy macros from one module to another, copy macros between different workbooks, rename the modules that store the macros, or rename the macros.

Macro security

Excel provides safeguards that help protect against viruses (virus: A computer program or macro that "infects" computer files by inserting copies of itself into those files. When the infected file is loaded into memory, the virus can infect other files. Viruses often have harmful side effects.) that can be transmitted by macros. If you share macros with others, you can certify them with a digital signature (digital signature: An electronic, encryption-based, secure stamp of authentication on a macro or document. This signature confirms that the macro or document originated from the signer and has not been altered.) so that other users can verify that they are from a trustworthy source. Whenever you open a workbook that contains macros, you can verify their source before you enable them.