| In this Appendix Although I discussed quite a few VBA functions in this book, my coverage was by no means exhaustive. VBA boasts more than 160 built-in functions that cover data conversion, dates and times, math, strings, and much more. This appendix presents a categorical list of each VBA function and the arguments it uses (required arguments are shown in bold type ). You can get full explanations and examples for all the functions in the Functions section of the VBA Help file. Table B.1. Conversion Functions | Function | What It Returns | | CBool( expression ) | An expression converted to a Boolean value. | | CByte( expression ) | An expression converted to a Byte value. | | CCur( expression ) | An expression converted to a Currency value. | | CDate( expression ) | An expression converted to a Date value. | | CDbl( expression ) | An expression converted to a Double value. | | CDec( expression ) | An expression converted to a Decimal value. | | CInt( expression ) | An expression converted to an Integer value. | | CLng( expression ) | An expression converted to a Long value. | | CSng( expression ) | An expression converted to a Single value. | | CStr( expression ) | An expression converted to a String value. | | CVar( expression ) | An expression converted to a Variant value. | | CVDate( expression ) | An expression converted to a Date value. (Provided for backward compatibility. Use CDate instead.) | | CVErr( errornumber ) | A Variant of subtype Error that contains errornumber . | Table B.2. Date and Time Functions | Function | What It Returns | | Date | The current system date as a Variant . | | Date$() | The current system date as a String . | | DateAdd( interval, number, date ) | A Date value derived by adding number time intervals (months, quarters , and so on) to date . | | DateDiff( interval, date1, date2,... ) | The number of time intervals between date1 and date2 . | | DatePart( interval, date,... ) | The interval given by date . | | DateSerial( year, month, day ) | A Date value for the specified year , month , and day . | | DateValue( date ) | A Date value for the date string. | | Day( date ) | The day of the month given by date . | | Hour ( time ) | The hour component of time . | | Minute( time ) | The minute component of time . | | Month( date ) | The month component of date . | | MonthName( month , abbreviate ) | The name of the month associated with the specified month number. | | Now | The current system date and time. | | Second( time ) | The second component of time . | | Time | The current system time as a Variant . | | Time$ | The current system time as a String . | | Timer | The number of seconds since midnight. | | TimeSerial( hour, minute, second ) | A Date value for the specified hour , minute , and second . | | TimeValue( time ) | A Date value for the time string. | | Weekday( date ) | The day of the week, as a number, given by date . | | WeekdayName( weekday , abbreviate ) | The name of the weekday associated with the specified weekday number. | | Year( date ) | The year component of date . | Table B.3. Error Functions | Function | What It Returns | | Error( errornumber ) | The error message, as a Variant , that corresponds to the errornumber . | | Error$( errornumber ) | The error message, as a String , that corresponds to the errornumber . | Table B.4. File and Directory Functions | Function | What It Returns | | CurDir( drive ) | The current directory as a Variant . | | CurDir$( drive ) | The current directory as a String . | | Dir( pathname , attributes ) | The name, as a Variant , of the file or directory (folder) specified by pathname and satisfying the optional attributes (for example, vbHidden ). Returns Null if the file or directory doesn't exist. | | Dir$( pathname , attributes ) | The name, as a String , of the file or directory (folder) specified by pathname and satisfying the optional attributes (for example, vbHidden ). Returns Null if the file or directory doesn't exist. | | EOF( filenumber ) | True if the end of file specified by filenumber has been reached; False otherwise . | | FileAttr( filenumber, returnType ) | The file mode (if returnType is 1) or the file handle (if returnType is 2) of the file given by filenumber . | | FileDateTime( pathname ) | The Date that the file given by pathname was created or last modified. | | FileLen( pathname ) | The length, in bytes, of the file given by pathname . | | FreeFile( rangenumber ) | The next available file number available to the Open statement. | | GetAttr( pathname ) | An integer representing the attributes of the file given by pathname . | | Loc( filenumber ) | The current read/write position in an open I/O file. | | LOF( filenumber ) | The size , in bytes, of an open I/O file. | | Seek( filenumber ) | The current read/write position, as a Variant , in an open I/O file. | | Shell( pathname, windowstyle ) | The task ID of the executed program given by pathname . | Table B.5. Financial Functions | Function | What It Returns | | DDB( cost, salvage, life, period , factor ) | The depreciation of an asset over a specified period using the double-declining balance method. | | FV( rate, nper, pmt , pv, type ) | The future value of an investment or loan. | | IPmt( rate, per, nper, pv , fv, type ) | The interest payment for a specified period of a loan. | | IRR( values , guess ) | The internal rate of return for a series of cash flows. | | MIRR( values, finance_rate, reinvest_rate ) | The modified internal rate of return for a series of periodic cash flows. | | NPer( rate, pmt, pv , fv, type ) | The number of periods for an investment or loan. | | NPV( rate, value1 , value2 ...) | The net present value of an investment based on a series of cash flows and a discount rate. | | Pmt( rate, nper, pv , fv, type ) | The periodic payment for a loan or investment. | | PPmt( rate, per, nper, pv , fv, type ) | The principal payment for a specified period of a loan. | | PV( rate, nper, pmt , fv, type ) | The present value of an investment. | | Rate( nper, pmt, pv , fv, type, guess ) | The periodic interest rate for a loan or investment. | | SLN( cost, salvage, life ) | The straight-line depreciation of an asset over one period. | | SYD( cost, salvage, life, period ) | The sum-of- years digits depreciation of an asset over a specified period. | Table B.6. Math Functions | Function | What It Returns | | Abs( number ) | The absolute value of number . | | Atn( number ) | The arctangent of number . | | Cos( number ) | The cosine of number . | | Exp( number ) | e (the base of the natural logarithm) raised to the power of number . | | Fix( number ) | The integer portion of number . If number is negative, Fix returns the first negative integer greater than or equal to number . | | Hex( number ) | The hexadecimal value, as a Variant , of number . | | Hex$( number ) | The hexadecimal value, as a String , of number . | | Int( number ) | The integer portion of number . If number is negative, Int returns the first negative integer less than or equal to number . | | Log( number ) | The natural logarithm of number . | | Oct( number ) | The octal value, as a Variant , of number . | | Oct$( number ) | The octal value, as a String , of number . | | Rnd( number ) | A random number. | | Round( expression , numberdecimalplaces ) | The numeric expression rounded to a specified number of decimal places. | | Sgn( number ) | The sign of number . | | Sin( number ) | The sine of number . | | Sqr( number ) | The square root of number . | | Tan( number ) | The tangent of number . | Table B.7. Miscellaneous Functions | Function | What It Returns | | Array( arglist ) | A Variant array containing the values in arglist . | | CallByName( object , procname , etc. ) | The value of the procname property of the specified object . Also can run the object's procname method. | | Choose( index, choice1 , etc. ) | A value from a list of choices. | | CreateObject( class ) | An Automation object of type class . | | DoEvents | Yields execution to the operating system so that it can process pending events from other applications (such as keystrokes and mouse clicks). | | Environ( envstring number ) | A String value that represents the operating system environment variable given by envstring or number . | | Format( expression, format ) | The expression , as a Variant , according to the string format . | | Format$( expression, format ) | The expression , as a String , according to the string format . | | FormatCurrency( Expression , etc. ) | Expression formatted as a currency value. | | FormatDateTime( Date , NamedFormat ) | Date formatted as a date or time value. | | FormatNumber( Expression , etc. ) | Expression formatted as a numeric value. | | FormatPercent( Expression , etc. ) | Expression formatted as a percentage value. | | GetAllSettings( appname , section ) | All the settings in the specified section of the Registry. | | GetObject( pathname, class ) | The Automation object given by pathname and class . | | GetSetting( appname , etc. ) | A setting from the Registry. | | IIf( expr, truepart, falsepart ) | The truepart value if expr is True; returns falsepart otherwise. | | Input( number, #filenumber ) | number characters , as a Variant , from the I/O file given by filenumber . | | Input$( number, #filenumber ) | number characters, as a String , from the I/O file given by filenumber . | | InputB( number, #filenumber ) | number bytes, as a Variant , from the I/O file given by filenumber . | | InputB$( number, #filenumber ) | number bytes, as a String , from the I/O file given by filenumber . | | InputBox( prompt, etc. ) | Prompts the user for information. | | IsArray( varname ) | True if varname is an array. | | IsDate( expression ) | True if expression can be converted into a date. | | IsEmpty( expression ) | True if expression is empty. | | IsError( expression ) | True if expression is an error. | | IsMissing( argname ) | True if the argument specified by argname was not passed to the procedure. | | IsNull( expression ) | True if expression is the null string (""). | | IsNumeric( expression ) | True if expression is a number. | | IsObject( expression ) | True if expression is an object. | | LBound( arrayname, dimension ) | The lowest possible subscript for the array given by arrayname . | | MsgBox( prompt, etc. ) | The button a user selects from the MsgBox dialog box. | | Partition( number, start, stop,... ) | A String that indicates where number occurs within a series of ranges. | | QBColor( color ) | The RGB color code that corresponds to color (a number between 1 and 15). | | RGB( red, green, blue ) | The color that corresponds to the red , green , and blue components . | | Switch( expr1, value1 , etc. ) | Evaluates the expressions ( expr1 and so on) and returns the associated value ( value1 and so on) for the first expression that evaluates to True. | | Tab( n ) | Positions output for the Print # statement or the Print method. | | TypeName( varname ) | A string that indicates the data type of the varname variable. | | UBound( arrayname, dimension ) | The highest possible subscript for the array given by arrayname . | | VarType( varname ) | A constant that indicates the data type of the varname variable. | Table B.8. String Functions | Function | What It Returns | | Asc( string ) | The ANSI character code of the first letter in string . | | AscB( string ) | The byte corresponding to the first letter in string . | | AscW( string ) | The Unicode character code of the first letter in string . | | Chr( charcode ) | The character, as a Variant , that corresponds to the ANSI code given by charcode . | | Chr$( charcode ) | The character, as a String , that corresponds to the ANSI code given by charcode . | | ChrB( charcode ) | The byte that corresponds to the ANSI code given by charcode . | | ChrW( charcode ) | The Unicode character that corresponds to the ANSI code given by charcode . | | Filter( sourcearray,match , etc. ) | Given an array of strings ( sourcearray ), returns a subset of strings (that is, another array) that match a criterion ( match ). | | InStr( start, string1, string2 ) | The character position of the first occurrence of string2 in string1 , beginning at start . | | InStrB( start, string1, string2 ) | The byte position of the first occurrence of string2 in string1 , starting at start . | | InStrRev( stringcheck,stringmatch , start ) | The character position (working from the end of the string) of the first occurrence of stringmatch in stringcheck , beginning at start . | | Join( sourcearray , delimiter ) | A string consisting of the concatenated values in a string array ( sourcearray ), separated by delimiter . | | LCase( string ) | string converted to lowercase as a Variant . | | LCase$( string ) | string converted to lowercase as a String . | | Left( string, length ) | The leftmost length characters from string as a Variant . | | Left$( string, length ) | The leftmost length characters from string as a String . | | LeftB( string ) | The leftmost length bytes from string as a Variant . | | LeftB$( string ) | The leftmost length bytes from string as a String . | | Len( string ) | The number of characters in string . | | LenB( string ) | The number of bytes in string . | | LTrim( string ) | A string, as a Variant , without the leading spaces in string . | | LTrim$( string ) | A string, as a String , without the leading spaces in string . | | Mid( string, start, length ) | length characters, as a Variant , from string beginning at start . | | Mid$( string, start, length ) | length characters, as a String , from string beginning at start . | | MidB( string, start, length ) | length bytes, as a Variant , from string beginning at start . | | MidB$( string, start, length ) | length bytes, as a String , from string beginning at start . | | Replace( expression, find, replace ) | A string in which one or more instances of a specified substring ( find ) in an expression have been replaced by another substring ( replace ). | | Right( string, length ) | The rightmost length characters from string as a Variant . | | Right$( string, length ) | The rightmost length characters from string as a String . | | RightB( string, length ) | The rightmost length bytes from string as a Variant . | | RightB$( string, length ) | The rightmost length bytes from string as a String . | | RTrim( string ) | A string, as a Variant , without the trailing spaces in string . | | RTrim$( string ) | A string, as a String , without the trailing spaces in string . | | Space( number ) | A string, as a Variant , with number spaces. | | Space$( number ) | A string, as a String , with number spaces. | | Split( expression , delimiter ) | An array consisting of substrings from a string expression in which each substring is separated by a delimiter . | | Str( number ) | The string representation, as a Variant , of number . | | Str$( number ) | The string representation, as a String , of number . | | StrComp( string1, string2, compare ) | A value indicating the result of comparing string1 and string2 . | | StrReverse( expression ) | A string consisting of the characters from a string expression in reverse order. | | String( number, character ) | character , as a Variant , repeated number times. | | String$( number, character ) | character , as a String , repeated number times. | | Trim( string ) | A string, as a Variant , without the leading and trailing spaces in string . | | Trim$( string ) | A string, as a String , without the leading and trailing spaces in string . | | UCase( string ) | string converted to uppercase as a Variant . | | UCase$( string ) | string converted to uppercase as a String . | | Val( string ) | The number contained in string . | |
