Microsoft Excel has over 200 built-in functions that perform a range of
calculations including simple math, financial calculations,
statistics and text manipulation. Even with all the built-in
functions, there are times when the function you need is not
available. So, what do you do? Answer, create your own!
Excel
allows you to create custom functions that operate
similar to Excel's built-in functions. And get this, it's
easy!
The
following example converts a date value to the day of the
week. For instance, it will return "Sunday" if the
date is a Sunday.
Let's
get started...
- Open
Microsoft Excel and open or create a workbook.
- Open
the Microsoft Visual Basic Editor ("VBE") screen by
pressing Alt + F11 or clicking the VBE icon on the Visual Basic
toolbar.
- Add
a "Module" to the VBAProject by selecting Insert
Module from the VBE menu. You should see a module
added in the Project window.
- In
the module window, enter the following code:
|
Function DOW(dDate
as Date)
nDOW = Weekday(dDate)
Select Case nDOW
Case 1
DOW = "Sunday"
Case 2
DOW = "Monday"
Case 3
DOW = "Tuesday"
Case 4
DOW = "Wednesday"
Case 5
DOW = "Thursday"
Case 6
DOW = "Friday"
Case 7
DOW = "Saturday"
End Select
End Function
(Hint: you can hightlight
this code and copy it into the module instead of typing it.)
|
|
- Save
the spreadsheet by selecting File Save from the
menu or clicking on the disk icon on the toolbar.
- Return
to the spreadsheet, select a cell and enter the following:
|
=DOW(today()) |
|
|
|
|
|
The cell should
display the name of the day, for instance Monday. |
|
Custom
functions are often useful when:
- Performing
complex calculations
- Using
a series of "IF" statements
- Accessing
information in external databases or other spreadsheets
- Entering
long formulas in a cell is too cumbersome
Contact
us today for additional information or assistance creating custom
functions or Excel Add-ins for your organization.
|
