VBA Functions that do more
A few VBA functions
go above and beyond the call of duty. Rather than simply return a value,
these functions have some useful side effects.
Below table lists them.
| Functions with Useful Side Benefits | |
|---|---|
| Function | What is does |
| MsgBox | Displays a handy dialog box containing a message and buttons. The function returns a code that identifies which button the user clicks. |
| InputBox | Displays a simple dialog box that asks the user for some input. The function returns whatever the user enters into the dialog box. |
| Shell | Executes another program. The function returns the task ID (a unique identifier) of the other program (or an error if the function can’t start the other program). |
| GetObject/CreateObject | Returns/Create a reference to an object provided by an ActiveX component. (If you don't understand, don't bother about it. Just remember we use this function to for checking & creating objects in later topics) |
Discovering VBA functions
How do we find out which function does VBA provides?
The best source is the Visual Basic Help system in build in your CAD Application.
I compiled a partial list of functions, which I
share with you in following Table.
I omitted some of the more specialized or obscure functions.
For complete details on a particular function, type the function name into a
VBA module, move the cursor anywhere in the text, and press F1.
| VBA’s Most Useful Built-In Functions | |
|---|---|
| Function | What is does |
| Abs | Returns a number’s absolute value. |
| 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. |
| Choose | Returns a value from a list of items. |
| Chr | Converts an ANSI value to a string. |
| Cos | Returns a number’s cosine. |
| CurDir | Returns the current path. |
| Date | Returns the current system date. |
| DateAdd | Returns a date to which a specified time interval has been added — for example, one month from a particular date. |
| DatePart | Returns an integer containing the specified part of a given date — for example, a date’s day of the year. |
| DateSerial | Converts a date to a serial number. |
| DateValue | Converts a string to a date. |
| Day | Returns the day of the month from a date value. |
| Dir | Returns the name of a file or directory that matches a pattern. |
| Erl | Returns the line number that caused an error. |
| Err | Returns the error number of an error condition. |
| Error | Returns the error message that corresponds to an error number. |
| Exp | Returns the base of the natural logarithm (e) raised to a power. |
| FileLen | Returns the number of bytes in a file. |
| Fix | Returns a number’s integer portion. |
| Format | Displays an expression in a particular format. |
| GetSetting | Returns a value from the Windows registry. |
| Hex | Converts from decimal to hexadecimal. |
| Hour | Returns the hours portion of a time. |
| InputBox | Displays a box to prompt a user for input. |
| InStr | Returns the position of a string within another string. |
| Int | Returns the integer portion of a number. |
| IPmt | Returns the interest payment for an annuity or loan. |
| IsArray | Returns True if a variable is an array. |
| IsDate | Returns True if an expression 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 no valid data. |
| IsNumeric | Returns True if an expression can be evaluated as a number. |
| IsObject | Returns True if an expression references an OLE Automation object. |
| 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. |
| Log | Returns the natural logarithm of a number to base. |
| LTrim | Returns a copy of a string, with any leading spaces removed. |
| Mid | Returns a specified number of characters from a string. |
| Minutes | Returns the minutes portion of a time value. |
| Month | Returns the month from a date value. |
| MsgBox | Displays a message box and (optionally) returns a value. |
| Now | Returns the current system date and time. |
| RGB | Returns a numeric RGB value representing a color. |
| Replace | Replaces a substring in a string with another substring. |
| Right | Returns a specified number of characters from the right of a string. |
| Rnd | Returns a random number between 0 and 1. |
| RTrim | Returns a copy of a string, with any trailing spaces removed. |
| Second | Returns the seconds portion of a time value. |
| Sgn | Returns an integer that indicates a number’s sign. |
| Shell | Runs an executable program. |
| Sin | Returns a number’s sine. |
| Space | Returns a string with a specified number of spaces. |
| Split | Splits a string into parts, using a delimiting character. |
| Sqr | Returns a number’s square root. |
| Str | Returns a string representation of a number. |
| StrComp | Returns a value indicating the result of a string comparison. |
| String | Returns a repeating character or string. |
| Tan | Returns a number’s tangent. |
| 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 or trailing spaces. |
| TypeName | Returns a string that describes a variable’s data type. |
| UBound | Returns the largest available subscript for an array’s dimension. |
| UCase | Converts a string to uppercase. |
| Val | Returns the numbers contained in a string. |
| VarType | Returns a value indicating a variable’s subtype. |
| Weekday | Returns a number representing a day of the week. |
| Year | Returns the year from a date value. |