Excel VBA Programming For Dummies
Book image
Explore Book Buy On Amazon

A function performs a calculation and returns a single value. The SUM function adds the sum of a range of values. The same holds true for functions used in your VBA expressions: Each function does its thing and returns a single value.

The functions you use in VBA can come from three sources:

  • Built-in functions provided by VBA
  • Worksheet functions provided by Excel
  • Custom functions that you (or someone else) write, using VBA

VBA provides numerous built-in functions. Some of these functions take arguments and some do not.

VBA function examples

Here are a few examples of using VBA functions in code. Note the use of the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box.

Displaying the system date

The first example uses VBA's Date function to display the current system date in a message box:

Sub ShowDate()
MsgBox Date
End Sub

Notice that the Date function doesn't use an argument. Unlike worksheet functions, a VBA function with no argument doesn't require an empty set of parentheses. In fact, if you provide an empty set of parentheses, the VBE will remove them.

To get the system date and time, use the Now function instead of the Date function. Or to get only the time, use the Time function.

Finding a string length

The following procedure uses the VBA Len function, which returns the length of a string. The Len function takes one argument: the string. When you execute this procedure, the message box displays 11 because the argument has 11 characters.

Sub GetLength()
Dim MyString As String
Dim StringLength As Integer
MyString = "Hello World"
StringLength = Len(MyString)
MsgBox StringLength
End Sub

Excel also has a function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.

Displaying the integer part of a number

The following procedure uses the Fix function, which returns the integer portion of a value — the value without any decimal digits:

Sub GetIntegerPart()
Dim MyValue As Double
Dim IntValue As Integer
MyValue = 123.456
IntValue = Fix(MyValue)
MsgBox IntValue
End Sub

In this case, the message box displays 123.

VBA has a similar function called Int. The difference between Int and Fix is how each deals with negative numbers.

  • Int returns the first negative integer that's less than or equal to the argument.
  • Fix returns the first negative integer that's greater than or equal to the argument.

Determining a file size

The following Sub procedure displays the size, in bytes, of the Excel executable file. It finds this value by using the FileLen function.

Sub GetFileSize()
Dim TheFile As String
TheFile = "c:MSOFFICEEXCELEXCEL.EXE"
MsgBox FileLen(TheFile)
End Sub

Notice that this routine hard codes the filename (that is, it explicitly states the path). Generally, this isn't a good idea. The file might not be on the C drive, or the Excel folder may have a different name. The following statement shows a better approach:

TheFile = Application.Path & "EXCEL.EXE"

Path is a property of the Application object. It simply returns the name of the folder in which the application (that is, Excel) is installed (without a trailing backslash).

Identifying the type of a selected object

The following procedure uses the TypeName function, which returns the type of the selected object (as a string):

Sub ShowSelectionType()
Dim SelType As String
SelType = TypeName(Selection)
MsgBox SelType
End Sub

This could be a Range, a ChartObject, a TextBox, or any other type of object that can be selected.

The TypeName function is very versatile. You can also use this function to determine the data type of a variable.

VBA functions that do more than return a value

A few VBA functions go above and beyond the call of duty. Rather than simply return a value, these functions have some useful side effects. Table 1 lists them.

Table 1: Functions with Useful Side Benefits

Function

What It Does

MsgBox

Displays a handy dialog box containing a message and buttons. The function returns a code that identifies which button the user clicks.

InputBox

Displays a simple dialog box that asks the user for some input. The function returns whatever the user enters into the dialog box.

Shell

Executes another program. The function returns the task ID (a unique identifier) of the other program (or an error if the function can't start the other program).

Discovering VBA functions

How do you find out which functions VBA provides? Good question. The best source is the Excel Visual Basic Help system. Table 2 contains a partial list of functions (minus some of the more specialized or obscure functions).

For complete details on a particular function, type the function name into a VBA module, move the cursor anywhere in the text, and press F1.

Table 2: VBA's Most Useful Built-in Functions

Function

What It Does

Abs

Returns a number's absolute value

Array

Returns a variant containing an array

Asc

Converts the first character of a string to its ASCII value

Atn

Returns the arctangent of a number

Choose

Returns a value from a list of items

Chr

Converts an ANSI value to a string

Cos

Returns a number's cosine

CurDir

Returns the current path

Date

Returns the current system date

DateAdd

Returns a date to which a specified time interval has been

added — for example, one month from a particular date

DateDiff

Returns an integer showing the number of specified time intervals between two dates, for example the number of months between now and your birthday

DatePart

Returns an integer containing the specified part of a given

date — for example, a date's day of the year

DateSerial

Converts a date to a serial number

DateValue

Converts a string to a date

Day

Returns the day of the month from a date value

Dir

Returns the name of a file or directory that matches a pattern

Erl

Returns the line number that caused an error

Err

Returns the error number of an error condition

Error

Returns the error message that corresponds to an error number

Exp

Returns the base of the natural logarithm (e) raised to a power

FileLen

Returns the number of bytes in a file

Fix

Returns a number's integer portion

Format

Displays an expression in a particular format

GetSetting

Returns a value from the Windows registry

Hex

Converts from decimal to hexadecimal

Hour

Returns the hours portion of a time

InputBox

Displays a box to prompt a user for input

InStr

Returns the position of a string within another string

Int

Returns the integer portion of a number

IPmt

Returns the interest payment for an annuity or loan

IsArray

Returns True if a variable is an array

IsDate

Returns True if an expression is a date

IsEmpty

Returns True if a variable has not been initialized

IsError

Returns True if an expression is an error value

IsMissing

Returns True if an optional argument was not passed to a procedure

IsNull

Returns True if an expression contains no valid data

IsNumeric

Returns True if an expression can be evaluated as a number

IsObject

Returns True if an expression references an OLE Automation object

LBound

Returns the smallest subscript for a dimension of an array

LCase

Returns a string converted to lowercase

Left

Returns a specified number of characters from the left of a string

Len

Returns the number of characters in a string

Log

Returns the natural logarithm of a number to base e

LTrim

Returns a copy of a string, with any leading spaces removed

Mid

Returns a specified number of characters from a string

Minute

Returns the minutes portion of a time value

Month

Returns the month from a date value

MsgBox

Displays a message box and (optionally) returns a value

Now

Returns the current system date and time

RGB

Returns a numeric RGB value representing a color

Right

Returns a specified number of characters from the right of a string

Rnd

Returns a random number between 0 and 1

RTrim

Returns a copy of a string, with any trailing spaces removed

Second

Returns the seconds portion of a time value

Sgn

Returns an integer that indicates a number's sign

Shell

Runs an executable program

Sin

Returns a number's sine

Space

Returns a string with a specified number of spaces

Sqr

Returns a number's square root

Str

Returns a string representation of a number

StrComp

Returns a value indicating the result of a string comparison

String

Returns a repeating character or string

Tan

Returns a number's tangent

Time

Returns the current system time

Timer

Returns the number of seconds since midnight

TimeSerial

Returns the time for a specified hour, minute, and second

TimeValue

Converts a string to a time serial number

Trim

Returns a string without leading or trailing spaces

TypeName

Returns a string that describes a variable's data type

UBound

Returns the largest available subscript for an array's dimension

UCase

Converts a string to uppercase

Val

Returns the numbers contained in a string

VarType

Returns a value indicating a variable's subtype

Weekday

Returns a number representing a day of the week

Year

Returns the year from a date value

About This Article

This article can be found in the category: