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

2 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 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

3 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) 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.

4 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.

5 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, 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

6 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)

7 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)

8 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

9 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, 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