Invoking Excel Functions in VBA Instructions

If a VBA function that's equivalent to one you use in Excel is not available, you can use Excel's worksheet functions directly in your VBA code. Just precede the function with a reference to the WorksheetFunction object. For example, VBA does not have a function to convert radians to degrees. Because Excel has a worksheet function for this procedure, you can use a VBA instruction such as the following:

Deg = Application.WorksheetFunction.Degrees(3.14)

The WorksheetFunction object was introduced in Excel 97. For compatibility with earlier versions of Excel, you can omit the reference to the WorksheetFunction object and write an instruction such as the following:

Deg = Application.Degrees(3.14)

There are no new VBA functions in Excel 2002 or Excel 2003.

Table B-2 SUMMARY OF VBA FUNCTIONS

Function

Action

Abs

Returns the absolute value of a number

Array

Returns a variant containing an array

Asc

Converts the first character of string to its ASCII value

Atn

Returns the arctangent of a number

CallByName*

Executes a method, or sets or returns a property of an object

CBool

Converts an expression to a Boolean data type

CByte

Converts an expression to a byte data type

CCur

Converts an expression to a currency data type

CDate

Converts an expression to a date data type

CDbl

Converts an expression to a double data type

CDec

Converts an expression to a decimal data type

Choose

Selects and returns a value from a list of arguments

Chr

Converts a character code to a string

CInt

Converts an expression to an integer data type

CLng

Converts an expression to a long data type

Cos

Returns the cosine of a number

CreateObject

Creates an Object Linking and Embedding (OLE) Automation

object

CSng

Converts an expression to a single data type

CStr

Converts an expression to a string data type

CurDir

Returns the current path

CVar

Converts an expression to a variant data type

Function

CVDate

CVErr

Date

DateAdd

DateDiff

DatePart

DateSerial

DateValue

DoEvents

Environ

Error

FileAttr

FileDateTime

FileLen

Filter

Format

FormatCurrency*

Action

Converts an expression to a date data type (for compatibility, not recommended)

Returns a user-defined error value that corresponds to an error number

Returns the current system date

Adds a time interval to a date

Returns the time interval between two dates

Returns a specified part of a date

Converts a date to a serial number

Converts a string to a date

Returns the day of the month of a date

Returns the depreciation of an asset

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

Yields execution so the operating system can process other events

Returns an operating environment string

Returns True if the end of a text file has been reached

Returns the error message that corresponds to an error number

Returns the error message that corresponds to an error number

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

Returns the file mode for a text file

Returns the date and time when a file was last modified

Returns the number of bytes in a file

Returns a subset of a string array, filtered

Returns the integer portion of a number

Displays an expression in a particular format

Returns an expression formatted with the system currency symbol

Table B-2 (Continued) Function

FormatDateTime* FormatNumber* FormatPercent* FreeFile

GetAllSettings GetAttr GetObject GetSetting

Hour

Input

InputBox

InStr

InStrRev*

IPmt

IsArray

IsDate

IsEmpty

IsError

IsMissing

Action

Returns an expression formatted as a date or time

Returns an expression formatted as a number

Returns an expression formatted as a percentage

Returns the next available file number when working with text files

Returns the future value of an annuity

Returns a list of settings and values from the Windows Registry

Returns a code representing a file attribute

Retrieves an OLE Automation object from a file

Returns a specific setting from the application's entry in the Windows Registry

Converts from decimal to hexadecimal Returns the hour of a time

Evaluates an expression and returns one of two parts

Returns characters from a sequential text file

Displays a box to prompt a user for input

Returns the position of a string within another string

Returns the position of a string within another string, from the end of the string

Returns the integer portion of a number

Returns the interest payment for a given period of an annuity

Returns the internal rate of return for a series of cash flows

Returns True if a variable is an array

Returns True if a variable is a date

Returns True if a variable has not been initialized

Returns True if an expression is an error value

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

Function Action

IsNull Returns True if an expression contains a Null value

IsNumeric Returns True if an expression can be evaluated as a number

IsObject Returns True if an expression references an OLE Automation object

Join* Combines strings contained in an array

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

Loc Returns the current read or write position of a text file

LOF Returns the number of bytes in an open text file

Log Returns the natural logarithm of a number

LTrim Returns a copy of a string with no leading spaces

Mid Returns a specified number of characters from a string

Minute Returns the minute of a time

MIRR Returns the modified internal rate of return for a series of periodic cash flows

Month Returns the month of a date

MonthName Returns the month, as a string

MsgBox Displays a modal message box

Now Returns the current system date and time

NPer Returns the number of periods for an annuity

NPV Returns the net present value of an investment

Oct Converts from decimal to octal

Partition Returns a string representing a range in which a value falls

Pmt Returns a payment amount for an annuity

Ppmt Returns the principal payment amount for an annuity

Table B-2 (Continued)

Function

QBColor Rate

Replace*

RGB Right

Round

RTrim

Second

Seek

Shell

Space

Split*

Sqr Str

StrComp StrConv String StrReverse*

Action

Returns the present value of an annuity

Returns a red/green/blue (RGB) color code

Returns the interest rate per period for an annuity

Returns a string in which a substring is replaced with another string

Returns a number representing an RGB color value

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

Returns a random number between 0 and 1

Returns a rounded number

Returns a copy of a string with no trailing spaces

Returns the seconds portion of a specified time

Returns the current position in a text file

Returns an integer that indicates the sign of a number

Runs an executable program

Returns the sine of a number

Returns the straight-line depreciation for an asset for a period

Returns a string with a specified number of spaces

Positions output when printing to a file

Returns a one-dimensional array containing a number of substrings

Returns the square root of a number

Returns a string representation of a number

Returns a value indicating the result of a string comparison

Returns a converted string

Returns a repeating character or string

Returns a string, reversed

Function Action

Switch Evaluates a list of Boolean expressions and returns a value associated with the first True expression

SYD Returns the sum-of-years' digits depreciation of an asset for a period

Tab Positions output when printing to a file

Tan Returns the tangent of a number

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 spaces and/or trailing spaces

TypeName Returns a string that describes the data type of a variable

UBound Returns the largest available subscript for a dimension of an array

UCase Converts a string to uppercase

Val Returns the number formed from any initial numeric characters of a string

VarType Returns a value indicating the subtype of a variable

Weekday Returns a number indicating a day of the week

WeekdayName* Returns a string indicating a day of the week

Weekday Returns a number representing a day of the week

Year Returns the year of a date

*Not available in Excel 97 and earlier editions

Appendix C

Sell Your Annuity

Sell Your Annuity

Do you have annuity you dont want? Discover When is it Time to Sell Your Annuity? What can I do? Where can I get the money I need? I have an annuity, but I dont know that I can sell it. Is there a good time to sell my annuity? I already have a home improvement loan, but it was used before the roof needed replacing.

Get My Free Ebook


Post a comment