VBA Functions

Session 107-1 Page 1

Category Array Array Array

Function Array Filter Join

Array Array Array Boolean Boolean Boolean Boolean Boolean

LBound Split UBound IsArray IsDate IsEmpty IsError IsMissing

Boolean Boolean

IsNull IsNumeric

Boolean

IsObject

Conversion Conversion Conversion Conversion Conversion Conversion Conversion Conversion Conversion Conversion Conversion

Cbool Cbyte Ccur Cdate CDbl Cdec Cint CLng CSng CStr Cvar

VBA Functions

Purpose Returns a variant that contains an array Returns a subset of a string array, filtered Returns a string created by joining substrings contained in an array Returns the lower bound of an array Returns an array consisting of a number of substrings Returns the upper bound of an array 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 Returns TRUE if an expression contains a null value Returns TRUE if an expression can be evaluated as a number Returns TRUE if an expresion reference an OLE automation object Converts an expression to Boolean data type Converts an expression to byte data type Converts an expression to currency data type Converts an expression to date data type Converts an expression to double data type Converts an expression to decimal data type Converts an expression to integer data type Converts an expression to long data type Converts an expression to single data type Converts an expression to string data type Converts an expression to variant data type

Developed for use with the Beginning Word Macros Master Class. ? Sharon Parq Associates, Inc. All rights reserved. Further information can be found at .

Session 107-1 Page 2

Category Conversion Conversion Conversion Conversion Date/Time Date/Time Date/Time

Function CVDate Hex Oct StrConv Date DateAdd DateDiff

Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Date/Time Environment

DatePart DateSerial DateValue Day Hour Minute Month MonthName Now Second Time Timer TimeSerial TimeValue Weekday WeekdayName Year InputBox

Environment MsgBox

Environment QBColor Environment RGB

Purpose Converts an expression to a Date data type Converts from decimal to hexadecimal Converts from decimal to octal Returns a string variant converted as specified Returns the current system date Returns a date with a specific date interval added to it Returns the difference between two dates as a time interval Returns an integer containing a specific part of a date Returns a date for a specified year, month, and day Converts a string to date Returns the day of the month of a date Returns the hour of a time Returns the minute of a time Returns the month of a date Returns a string indicating the specified month Returns the current system date and time Returns the seconds in a time value Returns the current system time Returns the number of seconds since midnight Returns the time for a specified hour, minute, and second Converts a string to a time serial number Returns a number representing a day of the week Returns a string indicating the specified weekday Returns the year of a date Displays a box to prompt a user for input and returns the value entered Displays a modal message box and returns the ID of the button clicked Returns a red/green/blue (RGB) color code Returns a number representing an RGB color value

Developed for use with the Beginning Word Macros Master Class. ? Sharon Parq Associates, Inc. All rights reserved. Further information can be found at .

Session 107-1 Page 3

Category Environment Errors Errors

Function VarType CVErr Error

File I/O File I/O File I/O

EOF FileLen FreeFile

File I/O File I/O File I/O File I/O File I/O File I/O File System File System File System File System

Input Loc LOF Seek Spc Tab ChDir ChDrive CurDir Dir

File System File System File System File System File System Financial Financial Financial

FileAttr FileDateTime GetAttr MkDir SetAttr DDB FV Ipmt

Financial

IRR

Financial

MIRR

Purpose Returns a value indicating the subtype of a variable Returns a user-defined error type Returns the error message that corresponds to an error number Returns TRUE if the end of a text file is reached Returns the number of bytes in a file Returns the next file number available for use by the Open statement Returns characters from a sequential text file Returns the current read or write position of a text file Returns the number of bytes in an open text file Returns the current position in a text file Positions output when printing to a file Positions output in an output stream Change the default directory (folder) Change the default disk drive Returns the current path Returns the name of a file or directory that matches a pattern Returns the file mode for a text file Returns the date and time when a file was last modified Returns a code representing a file attribute Create a new directory Set file attributes Returns the depreciation of an asset Returns the future value of an annuity Returns the interest payment for a given period of an annuity Returns the internal rate of return for a series of cash flows Returns the modified internal rate of return for a series of periodic cash flows

Developed for use with the Beginning Word Macros Master Class. ? Sharon Parq Associates, Inc. All rights reserved. Further information can be found at .

Session 107-1 Page 4

Category Financial Financial Financial Financial Financial Financial Financial

Function Nper NPV Pmt PPmt PV Rate SLN

Financial

SYD

Lookup/Ref Lookup/Ref Lookup/Ref

Choose IIf Switch

Numeric

Abs

Numeric

Atn

Numeric

Cos

Numeric

Exp

Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric Numeric

Fix Int Log Randomize Rnd Round Sgn Sin Sqr Tan Val

Purpose Returns the number of periods for an annuity Returns the net present value of an investment Returns a payment amount for an annuity Returns the principal payment amount for an annuity Returns the present value of an annuity Returns the interest rate per period for an annuity Returns the straight-line depreciation for an asset for a period Returns the sum-of-years' digits depreciation of an asset for a period Returns a value from a list of arguments Evaluates an expression and returns one of two parts Returns a value associated with the first TRUE expression in a list Returns the absolute value of a number Returns the arctangent of a number Returns the cosine of a number Returns the base of the natural logarithms (e) raised to a power Returns the integer portion of a number Returns the integer portion of a number Returns the natural logarithm of a number Seed the random number generator Returns a random number between 0 and 1 Rounds a number to a specific number of decimal places Returns an integer that indicates the sign of a number Returns the sine of a number Returns the square root of a number Returns the tangent of a number Returns the numbers contained in a string

Developed for use with the Beginning Word Macros Master Class. ? Sharon Parq Associates, Inc. All rights reserved. Further information can be found at .

Session 107-1 Page 5

Category Op Sys

Op Sys Op Sys

Op Sys Op Sys

Op Sys

Op Sys Op Sys

Op Sys String String String String String String String String String

String String

String String String String

Function CallByName

CreateObject DoEvents

Environ GetAll

GetAllSettings

GetObject GetSetting

Shell Asc Chr Format FormatCurrency FormatDateTime FormatNumber FormatPercent InStr InStrRev

Lcase Left

Len Ltrim Mid MidB

Purpose Executes a method, or sets or returns a property of an object Creates an OLE Automation object Yields execution so the operating system can process other events Returns an operating environment string Returns a list of key settings and their values from an application's entry in the Windows registry Returns a list of settings and values from the Windows Registry Retrieves an OLE Automation object from a file Returns a key setting value from an application's entry in the Windows registry Runs an executable program Returns the ASCII value of the first character of a string Returns a character based on an ANSI character code Returns formatted string based on a value and a pattern Returns a number as a string, formatted as currency Returns an expression formatted as a date or time Returns a number as a formatted string Returns a number as a string, formatted as a percentage Returns the position of a string within another string Returns the position of a string within another string, beginning at the back of the string Returns a string converted to lowercase Returns a specified number of characters from the left of a string Returns the length of a string, in characters Returns a copy of a string with no leading spaces Returns a specified number of characters from a string Returns a specified number of bytes from a specified position in a string

Developed for use with the Beginning Word Macros Master Class. ? Sharon Parq Associates, Inc. All rights reserved. Further information can be found at .

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download