Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-10: VBA for Excel Functions
(Levels: Advanced)

Three topics in this step:
- using Excel functions within macros,
- creating new Excel functions with VBA for Excel and,
- using VBA functions within macros.

In alphabetical order here are the 15 VBA words that you need to learn to work efficiently with functions:

Date, Day, Hour, LCase, Minute, Month, MonthName, Now, Round, Second, Time, Trim, UCase, WorksheetFunction, Year

Existing Excel Functions

There are hundreds of functions available in VBA. Most of the functions that you find in Excel are available through macros in this form:
Range ("C1").Value= Application.WorksheetFunction.Sum(Range("A1:A32"))
this sentence sums the values of cell A1 to A32 and stores the total in cell C1.

Using Excel functions through VBA reduces substantially the calculation time and creates spreadsheet without formulas that you can more easily send to others.

New Excel Functions

You can create new functions in Excel. For example the function created by the code below will simply multiply the value of a cell by 2.

Function fctDouble(varInput)
    fctDouble = varInput * 2
End Function

Once this code is in a module in your workbook you access the new function the same way that you access the other functions in Excel by clicking on the icon function on the tool bar or from the menu bar "Insert/Functions" . In the dialog box select the "User Defined" category and select you new function ("fctDouble" in this example) and follow the instructions.

VBA Functions

Here are some VBA functions that you will use  within my Excel macros:

LCase, UCase
The IF statements, the SELECT CASE  and DO WHILE are all case sensitive. When you test a string of characters and you don't know if the user will enter upper case or lower case letters, use the LCase or UCase functions within your test and write the string in proper case:

If LCase(Selection.Value)= "toto" then...
or
Select Case LCase(Selection.Value)
or
Do While LCase(Selection.Value)< > "toto"

If UCase(Selection.Value)= "TOTO" then...
or
Select Case UCase(Selection.Value)
or
DO While UCase(Selection.Value)< > "TOTO"

Month, Day, Year

If you have a date in cell A1 like January, 3 2007
Range("A2)".Value = Month(Range("A1").Value the value entered in A2 will be 1
Range("A2)".Value = Day(Range("A1").Value the value entered in A2 will be 3
Range("A2)".Value = Year(Range("A1").Value the value entered in A2 will be 2007

Month and MonthName

As you have seen above:
Month(A1) will return "3" if there is "3/12/2007"   in A1
But:
MonthName(Range("A1").Value) will return "January" if there is "1" in A1
MonthName(Range("A1").Value,True) will return "Feb" if there is "2"   in A1
MonthName(Month(Range("A1").Value)) will return "March" if there is "3/12/2007"   in A1

NOW()

NOW()is an Excel function but also a VBA function. With the following code the Excel formula NOW() is inserted in cell A1. The cell "A1" will show the date of the day and this date will change every time the workbook is opened:
Range("A1").Formula = "=Now()"

With the following code, the cell "A1" will carry the date when the procedure is executed and will keep this value until you execute the procedure again. It won't change every time you open the workbook.
Range("A1").Value = Now()

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple