Excel VBA Programming Functions - Cheat Sheets

1 Excel VBA Programming FunctionsVisual Basic for applications (VBA) gives experienced Excel users a wide range ofoptions for creating Excel spreadsheets and customizing how they look and of Functions , the table following shows Excel VBA Functions and what It DoesAbsReturns the absolute value of a numberArrayReturns a variant that contains an arrayAscConverts the first character of string to its ASCII valueAscBConverts the first byte of the first character of a string to its charactercode valueAscWConverts the first character of a string to its Unicode character codeAtnReturns the arctangent of a numberCallByNameGet or set a property or invoke a methodCBoolConverts an expression to booleanCByteConverts an expression to byte data typeCCurConverts an expression to currency data typeCDateConverts an expression to date data typeCDblConverts an expression to double data typeCDecConverts an expression to decimal data typeChooseSelects and returns a value from a list of argumentsChrConverts an ANSI value to a stringChrBReturns a single-byte character associated with a specific charactercodeChrWReturns a Unicode character string associated

2 With a specific charactercodeCIntConverts an expression to integer data typeCLngConverts an expression to long data typeCommandReturns the argument portion of the command line used to launch anapplicationCosReturns the cosine of a numberCreateObjectCreates an OLE Automation objectCSngConverts an expression to single data typeCStrConverts an expression to string data typeCurDirReturns the current directory pathCVarConverts an expression to variant data typeCVErrReturns a user-defined error numberDateReturns the current system dateDateAddReturns a date with a specific date interval added to itDateDiffReturns a date with a specific date interval subtracted from itDatePartReturns an integer containing a specific part of a dateDateSerialConverts a date to a serial numberDateValueConverts a string to dateDayReturns the day of the month of a dateDDBR eturns the depreciation of an asset for a specific time period using thedouble-declining balance methodDirReturns the name of a file or directory that matches a patternDoEventsYields execution so the operating system can process other eventsEnvironReturns a string associated with an operating system environmentvariableEOFR eturns True if the end of a text file has been reachedErrorReturns the error message the corresponds to an error numberExpReturns the base of the natural logarithms (e)

3 Raised to a powerFileAttrReturns the file mode for a text fileFileDateTimeReturns the date and time when a file was last modifiedFileLenReturns the number of bytes in a fileFilterReturns a subset of a larger array based on filtering criteriaFixReturns the integer portion of a numberFormatDisplays an expression in a particular formatFormatCurrencyReturns a number as a string, formatted as currencyFormatDateTimeReturns a number as a string, formatted as a date and/or timeFormatNumberReturns a number as a formatted stringFormatPercentReturns a number as a string, formatted as a percentageFreeFileReturns the next file number available for use by the Open statementFVReturns the future value of an annuity based on periodic, fixedpayments and a fixed interest rateGetAllReturns a list of key settings and their values (originally Settingscreated with SaveSetting) from an application s entry in the WindowsregistryGetAttrReturns a code representing a file attributeGetObjectRetrieves an OLE Automation object from a fileGetSettingReturns a key setting value from an application s entry in the WindowsregistryHexConverts from decimal to hexadecimalHourReturns the hour of a timeIIfReturns one of two parts, depending on the evaluation of an expressionInputReturns a specific number of characters from an open text fileInputBReturns a specific number of bytes from an open text fileInputBoxDisplays a box to prompt a user for inputInStrReturns the position of a string within another stringInStrBReturns the byte position of a string within another stringInStrRevReturns the position of a string within another string, beginning at theback end of the stringIntReturns the integer portion of a numberIPmtReturns the interest payment for a given period of an annuity based onperiodic.

4 Fixed payments and a fixed interest rateIRRR eturns the internal rate of return for a series of periodic cash flowsIsArrayReturns True if a variable is an arrayIsDateReturns True if a variable is a dateIsEmptyReturns True if a variable has been initializedIsErrorReturns True if an expression is an error valueIsMissingReturns True if an optional argument was not passed to a procedureIsNullReturns True if an expression contains no valid dataIsNumericReturns True if an expression can be evaluated as a numberIsObjectReturns True if an expression references an OLE Automation objectJoinReturns a string created by joining a number of substrings contained inan arrayLBoundReturns the lower bound of an arrayLCaseReturns a string converted to lowercaseLeftReturns a specified number of characters from the left of a stringLeftBReturns a specified number of bytes from the left of a stringLenReturns the length of a string, in charactersLenBReturns the length of a string, in bytesLocReturns the current read or write position of a text fileLOFR eturns the number of bytes in an open text fileLogReturns the natural logarithm of a numberLTrimReturns a copy of a string with no leading spacesMidReturns a specified number of characters from a stringMidBReturns a specified number of bytes from a stringMinuteReturns the minute of a timeMIRRR eturns the internal rate of return for a series of periodic cash flows(using different rates)

5 MonthReturns the month of a dateMonthNameReturns a string indicating the specified monthMsgBoxDisplays a modal message boxNowReturns the current system date and timeNPerReturns the number of periods for an annuity based on periodic, fixedpayments and a fixed interest rateNPVR eturns the net present value of an investment based on a series ofperiodic cash flows and a discount rateOctConverts from decimal to octalPartitionReturns a string variant indicating where a number occurs in acalculated series of rangesPmtReturns the payment for an annuity based on periodic, fixed paymentsand a fixed interest ratePPmtReturns the principal payment for a given period of an annuity basedon periodic, fixed payments and a fixed interest ratePVReturns the present value of an annuity based on periodic, fixedpayments to be paid in the future and a fixed interest rateQBColorReturns the RGB color code corresponding to the specified colornumber (used for compatibility with Quick Basic)

6 RateReturns the interest rate per period for an annuityReplaceReturns a string where one substring has been replaced with anotherRGBR eturns a number representing an RGB color valueSLNR eturns the straight-line depreciation of an asset for a single periodSpaceReturns a string with a specified number of spacesSpcPosition output in an output streamSplitReturns an array consisting of a number of substringsSqrReturns the square root of a numberStrReturns a string representation of a numberRightReturns a specified number of characters from the right of a stringRightBReturns a specified number of bytes from the right of a stringRndReturns a random number between 0 and 1 RoundRounds a number to a specific number of decimal placesRTrimReturns a copy of a string with no trailing spacesSecondReturns the second of a timeSeekReturns the current position in a text fileSgnReturns an integer that indicates the sign of a numberShellRuns an executable programSinReturns the sine of a numberStrCompReturns a value indicating the result of a string comparisonStrConvReturns a string variant converted as specifiedStringReturns a repeating character or stringStrReverseReverses the character order of a stringSwitchEvaluates a list of expressions and returns a value associated with thefirst expression in the list that is TrueSYDR eturns the sum-of-years digits depreciation of an asset for a specifiedperiodTabPositions output in an output streamTanReturns the tangent of a numberTimeReturns the current system timeTimerReturns the number of seconds since midnightTimeSerialReturns the time for a specified hour, minute.

7 And secondTimeValueConverts a string to a time serial numberTrimReturns a string containing a copy of a specified string without leadingspaces and trailing spacesTypeNameReturns a string that describes the data type of a variableUBoundReturns the upper bound of an arrayUCaseConverts a string to uppercaseValReturns the numbers contained in a stringVarTypeReturns a value indicating the subtype of a variableWeekdayReturns a number representing a day of the weekYearReturns the year of a dat