Functions are code that is built in to the VBA standard library. A function may accept zero, one, or multiple arguments and returns a value (although the value maybe something complex, like an object). You can use Excel’s worksheet functions directly in your VBA code. Excel worksheet functions that don’t have a VBA equivalent are methods of the WorksheetFunction object. For example, VBA doesn’t have a function to convert radians to degrees, but Excel has a worksheet function for this procedure, so you can use a VBA instruction such as the following:
Deg = Application.WorksheetFunction.Degrees(3.14)| Function | Action |
|---|---|
| Abs | Returns the absolute value of a number |
| Array | Returns a variant containing an array |
| Asc | Converts the first character of a 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 |
| CLngLng | Converts an expression to a LongLong data type |
| CLngPtr | Converts an expression to a LongPtr 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 |
| CVDate | Converts an expression to a Date data type (for compatibility, not recommended) |
| CVErr | Returns a user-defined error value that corresponds to an error number |
| Date | Returns the current system date |
| DateAdd | Adds a time interval to a date |
| DateDiff | Returns the time interval between two dates |
| DatePart | Returns a specified part of a date |
| DateSerial | Converts a date to a serial number |
| DateValue | Converts a string to a date |
| Day | Returns the day of the month of a date |
| DDB | Returns the depreciation of an asset |
| Dir | Returns the name of a file or directory that matches a pattern |
| DoEvents | Yields execution so the operating system can process other events |
| Environ | Returns an operating environment string |
| EOF | Returns True if the end of a text file has been reached |
| Error | Returns the error message that corresponds to an error number |
| Exp | Returns the base of natural logarithms (e) raised to a power |
| FileAttr | Returns the file mode for a text file |
| FileDateTime | Returns the date and time when a file was last modified |
| FileLen | Returns the number of bytes in a file |
| Filter | Returns a subset of a string array, filtered |
| Fix | Returns the integer portion of a number |
| Format | Displays an expression in a particular format |
| FormatCurrency | Returns an expression formatted with the system currency symbol |
| FormatDateTime | Returns an expression formatted as a date or time |
| FormatNumber | Returns an expression formatted as a number |
| FormatPercent | Returns an expression formatted as a percentage |
| FreeFile | Returns the next available file number when working with text files |
| FV | Returns the future value of an annuity |
| GetAllSettings | Returns a list of settings and values from the Windows Registry |
| GetAttr | Returns a code representing a file attribute |
| GetObject | Retrieves an OLE Automation object from a file |
| GetSetting | Returns a specific setting from the application's entry in the Windows Registry |
| Hex | Converts from decimal to hexadecimal |
| Hour | Returns the hour of a time |
| IIf | Evaluates an expression and returns one of two parts |
| Input | Returns characters from a sequential text file |
| InputBox | Displays a box to prompt a user for input |
| InStr | Returns the position of a string within another string |
| InStrRev | Returns the position of a string within another string from the end of the string |
| Int | Returns the integer portion of a number |
| IPmt | Returns the interest payment for a given period of an annuity |
| IRR | Returns the internal rate of return for a series of cash flows |
| IsArray | Returns True if a variable is an array |
| IsDate | Returns True if a variable is a date |
| IsEmpty | Returns True if a variable has not been initialized |
| IsError | Returns True if an expression is an error value |
| IsMissing | Returns True if an optional argument was not passed to a procedure |
| 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 as a number |
| MonthName | Returns the month of a date 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 |
| PV | Returns the present value of an annuity |
| QBColor | Returns a red/green/blue (RGB) color code |
| Rate | Returns the interest rate per period for an annuity |
| Replace | Returns a string in which a substring is replaced with another string |
| RGB | Returns a number representing an RGB color value |
| Right | Returns a specified number of characters from the right of a string |
| Rnd | Returns a random number between 0 and 1 |
| Round | Returns a rounded number |
| RTrim | Returns a copy of a string with no trailing spaces |
| Second | Returns the seconds portion of a specified time |
| Seek | Returns the current position in a text file |
| Sgn | Returns an integer that indicates the sign of a number |
| Shell | Runs an executable program |
| Sin | Returns the sine of a number |
| SLN | Returns the straight-line depreciation for an asset for a period |
| Space | Returns a string with a specified number of spaces |
| Spc | Positions output when printing to a file |
| Split | Returns a one-dimensional array containing a number of substrings |
| Sqr | Returns the square root of a number |
| Str | Returns a string representation of a number |
| StrComp | Returns a value indicating the result of a string comparison |
| StrConv | Returns a converted string |
| String | Returns a repeating character or string |
| StrReverse | Returns a string, reversed |
| 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 |
| Year | Returns the year of a date |