Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
If a VBA function that's equivalent to one you use in Excel is not available, you can use Excel's worksheet functions directly in your VBA code. Just precede the function with a reference to the WorksheetFunction object. For example, VBA does not have a function to convert radians to degrees. Because Excel has a worksheet function for this procedure, you can use a VBA instruction such as the following:
Deg = Application.WorksheetFunction.Degrees(3.14)
The WorksheetFunction object was introduced in Excel 97. For compatibility with earlier versions of Excel, you can omit the reference to the WorksheetFunction object and write an instruction such as the following:
Deg = Application.Degrees(3.14)
| Note | There are no new VBA functions in Excel 2007. |
| 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 |
| 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 |
| Function | Action |
|---|---|
| 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 |