Build Your Customized Function in Excel

Excel has a lot of built-in functions for you to use. However sometimes you might still need a function that fits your application best. In this section you will learn how to built your own function.

"Software innovation, like almost every other kind of innovation, requires the ability to collaborate and share ideas with other people, and to sit down and talk with customers and get their feedback and understand their needs." - Bill Gates

It's the end of the school term. Suppose you are a form teacher who wanna know the grades of each student in your class. Let's say 85-100 = Grade A, 70-84 = Grade B and 50-69 = Grade C.

First, open VBA editor and right click ThisWorkbook -> Insert -> Module to add a module to your VBA project.

Then copy the below codes to Code Pane.

Function ExcelMagicWorld(a As Double, b As Double, c As Double)

Dim GradeA, GradeB, GradeC As Integer

Select Case a

Case 85 To 100

GradeA = GradeA + 1

Case 70 To 84

GradeB = GradeB + 1

Case 50 To 69

GradeC = GradeC + 1

End Select

Select Case b

Case 85 To 100

GradeA = GradeA + 1

Case 70 To 84

GradeB = GradeB + 1

Case 50 To 69

GradeC = GradeC + 1

End Select

Select Case c

Case 85 To 100

GradeA = GradeA + 1

Case 70 To 84

GradeB = GradeB + 1

Case 50 To 69

GradeC = GradeC + 1

End Select

ExcelMagicWorld = GradeA & "A " & GradeB & "B " & GradeC & "C"

End Function

Great! Now you can use your own function. Just type the function name "ExcelMagicWorld" following the symbol "=" in the target cell. Then specify three cells which stand for the score of each subject. That's it.

In the codes, we defined a customized function. We also used Dim to declare variables. Last we used the beautiful statement Select...Case. Look forward to the future posts which will cover all these topics.