Visual Basic for Applications (VBA) Function List

The following table describes the functions in the Visual Basic for Applications (VBA) specification, which are implicitly part of the MDX language specification.

Some of the functions are not implemented in mondrian, but are included for completeness. The 'Mondrian version/priority' column indicates which functions are implemented in mondrian, and if not, priority of the development team for adding them. Some functions, such as Beep, will never be implemented in Mondrian MDX.

The MDX language implemented by mondrian, including a list of set of functions implemented, is described in the MDX specification.

NameDescriptionMondrian version / priority
AbsReturns a value of the same type that is passed to it specifying the absolute value of a number.

Syntax

Abs(number)

The required number argument can be any valid numeric expression. If number contains Null, Null is returned; if it is an uninitialized variable, zero is returned.

Remarks

The absolute value of a number is its unsigned magnitude. For example, ABS(-1) and ABS(1) both return 1.
1
Add-
AppActivate-
ArrayReturns a Variant containing an array.

Syntax

Array(arglist)

The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created.

Remarks

The notation used to refer to an element of an array consists of the variable name followed by parentheses containing an index number indicating the desired element. In the following example, the first statement creates a variable named
A
as a Variant. The second statement assigns an array to variable
A
. The last statement assigns the value contained in the second array element to another variable.

Dim A As Variant
A = Array(10,20,30)
B = A(2)
The lower bound of an array created using the Array function is determined by the lower bound specified with the Option Base statement, unless Array is qualified with the name of the type library (for example VBA.Array). If qualified with the type-library name, Array is unaffected by Option Base.

Note A Variant that is not declared as an array can still contain an array. A Variant variable can contain an array of any type, except fixed-length strings and user-defined types. Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.
Not applicable - mondrian has no array data type
AscReturns an Integer representing the character code corresponding to the first letter in a string.

Syntax

Asc(string)

The required string argument is any valid string expression. If the string contains no characters, a run-time error occurs.

Remarks

The range for returns is 0 255 on non-DBCS systems, but 32768 32767 on DBCS systems.

Note The AscB function is used with byte data contained in a string. Instead of returning the character code for the first character, AscB returns the first byte. The AscW function returns the Unicode character code except on platforms where Unicode is not supported, in which case, the behavior is identical to the Asc function.
1
AscBSee AscN/A
AscWSee Asc1
AtnReturns a Double specifying the arctangent of a number.

Syntax

Atn(number)

The required number argument is a Double or any valid numeric expression.

Remarks

The Atn function takes the ratio of two sides of a right triangle (number) and returns the corresponding angle in radians. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.

The range of the result is -pi/2 to pi/2 radians.

To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.

Note Atn is the inverse trigonometric function of Tan, which takes an angle as its argument and returns the ratio of two sides of a right triangle. Do not confuse Atn with the cotangent, which is the simple inverse of a tangent (1/tangent).
1
BeepNot applicable in server
Calendar-
CallByNameExecutes a method of an object, or sets or returns a property of an object.

Syntax

CallByName(object, procname, calltype,[args()])

The CallByName function syntax has these named arguments:

Part Description
object Required; Variant (Object). The name of the object on which the function will be executed.
procname Required; Variant (String). A string expression containing the name of a property or method of the object.
calltype Required; Constant. A constant of type vbCallType representing the type of procedure being called.
args() Optional: Variant (Array).

Remarks

The CallByName function is used to get or set a property, or invoke a method at run time using a string name.

In the following example, the first line uses CallByName to set the MousePointer property of a text box, the second line gets the value of the MousePointer property, and the third line invokes the Move method to move the text box:

CallByName Text1, "MousePointer", vbLet, vbCrosshair
Result = CallByName (Text1, "MousePointer", vbGet)
CallByName Text1, "Move", vbMethod, 100, 100
-
CBoolReturns an expression that has been converted to a Variant of subtype Boolean.

CBool(expression)

The expression argument is any valid expression.

If expression is zero, False is returned; otherwise, True is returned. If expression can't be interpreted as a numeric value, a run-time error occurs.

The following example uses the CBool function to convert an expression to a Boolean. If the expression evaluates to a nonzero value, CBool returns True; otherwise, it returns False.
2
CByteReturns an expression that has been converted to a Variant of subtype Byte.

CByte(expression)

The expression argument is any valid expression.

In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CByte to force byte arithmetic in cases where currency, single-precision, double-precision, or integer arithmetic normally would occur.

Use the CByte function to provide internationally aware conversions from any other data type to a Byte subtype. For example, different decimal separators are properly recognized depending on the locale setting of your system, as are different thousand separators.

If expression lies outside the acceptable range for the byte subtype, an error occurs.
N/A; mondrian has no byte datatype
CCurReturns an expression that has been converted to a Variant of subtype Currency.

CCur(expression)
 
The expression argument is any valid expression.

In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CCur to force currency arithmetic in cases where integer arithmetic normally would occur.

You should use the CCur function to provide internationally aware conversions from any other data type to a Currency subtype. For example, different decimal separators and thousands separators are properly recognized depending on the locale setting of your system.
-
CDateReturns an expression that has been converted to a Variant of subtype Date.

CDate(date)

The date argument is any valid date expression.

Use the IsDate function to determine if date can be converted to a date or time. CDate recognizes date literals and time literals as well as some numbers that fall within the range of acceptable dates. When converting a number to a date, the whole number portion is converted to a date. Any fractional part of the number is converted to a time of day, starting at midnight.

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings. In addition, a long date format is not recognized if it also contains the day-of-the-week string.
1
CDblReturns an expression that has been converted to a Variant of subtype Double.

CDbl(expression)
 
The expression argument is any valid expression.

In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CDbl or CSng to force double-precision or single-precision arithmetic in cases where currency or integer arithmetic normally would occur.

Use the CDbl function to provide internationally aware conversions from any other data type to a Double subtype. For example, different decimal separators and thousands separators are properly recognized depending on the locale setting of your system.
2
CDec  
ChDir  
ChDrive  
Choose  
ChrReturns a String containing the character associated with the specified character code.

Syntax

Chr(charcode)

The required charcode argument is a Long that identifies a character.

Remarks

Numbers from 0 31 are the same as standard, nonprintable ASCII codes. For example, Chr(10) returns a linefeed character. The normal range for charcode is 0 255. However, on DBCS systems, the actual range for charcode is -32768 to 65535.

Note The ChrB function is used with byte data contained in a String. Instead of returning a character, which may be one or two bytes, ChrB always returns a single byte. The ChrW function returns a String containing the Unicode character except on platforms where Unicode is not supported, in which case, the behavior is identical to the Chr function.
1
ChrBSee Chr.-
ChrWSee Chr.1
CIntReturns an expression that has been converted to a Variant of subtype Integer.

CInt(expression)

The expression argument is any valid expression.

In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CInt or CLng to force integer arithmetic in cases where currency, single-precision, or double-precision arithmetic normally would occur.

Use the CInt function to provide internationally aware conversions from any other data type to an Integer subtype. For example, different decimal separators are properly recognized depending on the locale setting of your system, as are different thousand separators.

If expression lies outside the acceptable range for the Integer subtype, an error occurs.

The following example uses the CInt function to convert a value to an Integer:

Dim MyDouble, MyInt
MyDouble = 2345.5678 ' MyDouble is a Double.
MyInt = CInt(MyDouble) ' MyInt contains 2346.

Note. CInt differs from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. When the fractional part is exactly 0.5, the CInt function always rounds it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2.

2
Clear  
CLngReturns an expression that has been converted to a Variant of subtype Long.

CLng(expression)

The expression argument is any valid expression.

In general, you can document your code using the subtype conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CInt or CLng to force integer arithmetic in cases where currency, single-precision, or double-precision arithmetic normally would occur.

Use the CLng function to provide internationally aware conversions from any other data type to a Long subtype. For example, different decimal separators are properly recognized depending on the locale setting of your system, as are different thousand separators.

If expression lies outside the acceptable range for the Long subtype, an error occurs.

The following example uses the CLng function to convert a value to a Long:

Dim MyVal1, MyVal2, MyLong1, MyLong2
MyVal1 = 25427.45: MyVal2 = 25427.55 ' MyVal1, MyVal2 are Doubles.
MyLong1 = CLng(MyVal1) ' MyLong1 contains 25427.
MyLong2 = CLng(MyVal2) ' MyLong2 contains 25428.

Note. CLng differs from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. When the fractional part is exactly 0.5, the CLng function always rounds it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2.

2
CommandReturns the argument portion of the command line used to launch Microsoft Visual Basic or an executable program developed with Visual Basic.

Syntax

Command

Remarks

When Visual Basic is launched from the command line, any portion of the command line that follows
/cmd
is passed to the program as the command-line argument. In the following example,
cmdlineargs
represents the argument information returned by the Command function.

VB /cmd cmdlineargs
For applications developed with Visual Basic and compiled to an .exe file, Command returns any arguments that appear after the name of the application on the command line. For example:

MyApp cmdlineargs
To find how command line arguments can be changed in the user interface of the application you're using, search Help for "command line arguments."
-
CosReturns a Double specifying the cosine of an angle.

Syntax

Cos(number)

The required number argument is a Double or any valid numeric expression that expresses an angle in radians.

Remarks

The Cos function takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.

The result lies in the range -1 to 1.

To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.
1
Count ?
CreateObjectCreates and returns a reference to an ActiveX object.

Syntax

CreateObject(class,[servername])

The CreateObject function syntax has these parts:

Part Description
class Required; Variant (String). The application name and class of the object to create.
servername Optional; Variant (String). The name of the network server where the object will be created. If servername is an empty string (""), the local machine is used.

The class argument uses the syntax appname.objecttype and has these parts:

Part Description
appname Required; Variant (String). The name of the application providing the object.
objecttype Required; Variant (String). The type or class of object to create.

Remarks

Every application that supports Automation provides at least one type of object. For example, a word processing application may provide an Application object, a Document object, and a Toolbar object.
-
CSng ?
CStr ?
CurDirReturns a Variant (String) representing the current path.

Syntax

CurDir[(drive)]

The optional drive argument is a string expression that specifies an existing drive. If no drive is specified or if drive is a zero-length string (""), CurDir returns the path for the current drive.
 
Cvar  
CVDate  
CVErrReturns a Variant of subtype Error containing an error number specified by the user.

Syntax

CVErr(errornumber)

The required errornumber argument is any valid error number.

Remarks

Use the CVErr function to create user-defined errors in user-created procedures. For example, if you create a function that accepts several arguments and normally returns a string, you can have your function evaluate the input arguments to ensure they are within acceptable range. If they are not, it is likely your function will not return what you expect. In this event, CVErr allows you to return an error number that tells you what action to take.

Note that implicit conversion of an Error is not allowed. For example, you can't directly assign the return value of CVErr to a variable that is not a Variant. However, you can perform an explicit conversion (using CInt, CDbl, and so on) of the value returned by CVErr and assign that to a variable of the appropriate data type.
-
DateReturns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by the Calendar property setting. If the calendar is Hijri, Date$ returns a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd (01-30) and yyyy (1400-1523) are the Hijri month, day and year. The equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.
1
DateAddReturns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax

DateAdd(interval, number, date)

The DateAdd function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you want to add.
number Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
date Required. Variant (Date) or literal representing date to which the interval is added.

Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

Remarks

You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.

To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

The DateAdd function won't return an invalid date. The following example adds one month to January 31:

DateAdd("m", 1, "31-Jan-95")
In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is 31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.

If the calculated date would precede the year 100 (that is, you subtract more years than are in date), an error occurs.

If number isn't a Long value, it is rounded to the nearest whole number before being evaluated.

Note The format of the return value for DateAdd is determined by Control Panel settings, not by the format that is passed in date argument.

Note For date, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri. If month values are names, the name must be consistent with the current Calendar property setting. To minimize the possibility of month names conflicting with the current Calendar property setting, enter numeric month values (Short Date format).
1
DateDiffReturns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.


Remarks

You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.

To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.

If date1 or date2 is a date literal, the specified year becomes a permanent part of that date. However, if date1 or date2 is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date1 or date2 expression is evaluated. This makes it possible to write code that can be used in different years.

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

Note For date1 and date2, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.
1
DatePartReturns a Variant (Integer) containing the specified part of a given date.

Syntax

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you want to return.
date Required. Variant (Date) value that you want to evaluate.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.

Remarks

You can use the DatePart function to evaluate a date and return a specific interval of time. For example, you might use DatePart to calculate the day of the week or the current hour.

The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.

If date is a date literal, the specified year becomes a permanent part of that date. However, if date is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date expression is evaluated. This makes it possible to write code that can be used in different years.

Note For date, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.

The returned date part is in the time period units of the current Arabic calendar. For example, if the current calendar is Hijri and the date part to be returned is the year, the year value is a Hijri year.
1
DateSerialReturns a Variant (Date) for a specified year, month, and day.

Syntax

DateSerial(year, month, day)

The DateSerial function syntax has these named arguments:

Part Description
year Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression.
month Required; Integer. Any numeric expression.
day Required; Integer. Any numeric expression.


Remarks

To specify a date, such as December 31, 1991, the range of numbers for each DateSerial argument should be in the accepted range for the unit; that is, 131 for days and 112 for months. However, you can also specify relative dates for each argument using any numeric expression that represents some number of days, months, or years before or after a certain date.

The following example uses numeric expressions instead of absolute date numbers. Here the DateSerial function returns a date that is the day before the first day (
1 - 1
), two months before August (
8 - 2
), 10 years before 1990 (
1990 - 10
); in other words, May 31, 1980.

DateSerial(1990 - 10, 8 - 2, 1 - 1)
Under Windows 98 or Windows 2000, two digit years for the year argument are interpreted based on user-defined machine settings. The default settings are that values between 0 and 29, inclusive, are interpreted as the years 20002029. The default values between 30 and 99 are interpreted as the years 19301999. For all other year arguments, use a four-digit year (for example, 1800).

Earlier versions of Windows interpret two-digit years based on the defaults described above. To be sure the function returns the proper value, use a four-digit year.

When any argument exceeds the accepted range for that argument, it increments to the next larger unit as appropriate. For example, if you specify 35 days, it is evaluated as one month and some number of days, depending on where in the year it is applied. If any single argument is outside the range -32,768 to 32,767, an error occurs. If the date specified by the three arguments falls outside the acceptable range of dates, an error occurs.

Note For year, month, and day, if the Calendar property setting is Gregorian, the supplied value is assumed to be Gregorian. If the Calendar property setting is Hijri, the supplied value is assumed to be Hijri.

The returned date part is in the time period units of the current Visual Basic calendar. For example, if the current calendar is Hijri and the date part to be returned is the year, the year value is a Hijri year. For the argument year, values between 0 and 99, inclusive, are interpreted as the years 1400-1499. For all other year values, use the complete four-digit year (for example, 1520).
1
DateValueReturns a Variant (Date).

Syntax

DateValue(date)

The required date argument is normally a string expression representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.

Remarks

If date is a string that includes only numbers separated by valid date separators, DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form. For example, in addition to recognizing 12/30/1991 and 12/30/91, DateValue also recognizes December 30, 1991 and Dec 30, 1991.

If the year part of date is omitted, DateValue uses the current year from your computer's system date.

If the date argument includes time information, DateValue doesn't return it. However, if date includes invalid time information (such as "89:98"), an error occurs.

Note For date, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri. If the supplied date is Hijri, the argument date is a String representing a date from 1/1/100 (Gregorian Aug 2, 718) through 4/3/9666 (Gregorian Dec 31, 9999).
1
DayReturns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.

Syntax

Day(date)

The required date argument is any Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

Note If the Calendar property setting is Gregorian, the returned integer represents the Gregorian day of the month for the date argument. If the calendar is Hijri, the returned integer represents the Hijri day of the month for the date argument.
1
DDBReturns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.

Syntax

DDB(cost, salvage, life, period[, factor])

The DDB function has these named arguments:

Part Description
cost Required. Double specifying initial cost of the asset.
salvage Required. Double specifying value of the asset at the end of its useful life.
life Required. Double specifying length of useful life of the asset.
period Required. Double specifying period for which asset depreciation is calculated.
factor Optional. Variant specifying rate at which the balance declines. If omitted, 2 (double-declining method) is assumed.


Remarks

The double-declining balance method computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods.

The life and period arguments must be expressed in the same units. For example, if life is given in months, period must also be given in months. All arguments must be positive numbers.

The DDB function uses the following formula to calculate depreciation for a given period:

Depreciation / period = ((cost salvage) * factor) / life
2
DirReturns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir[(pathname[, attributes])]

The Dir function syntax has these parts:

Part Description
pathname Optional. String expression that specifies a file name may include directory or folder, and drive. A zero-length string ("") is returned if pathname is not found.
attributes Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.

Settings

The attributes argument settings are:

Constant Value Description
vbNormal 0 (Default) Specifies files with no attributes.
vbReadOnly 1 Specifies read-only files in addition to files with no attributes.
vbHidden 2 Specifies hidden files in addition to files with no attributes.
VbSystem 4 Specifies system files in addition to files with no attributes.
vbVolume 8 Specifies volume label; if any other attributed is specified, vbVolume is ignored.
vbDirectory 16 Specifies directories or folders in addition to files with no attributes.

Note These constants are specified by Visual Basic for Applications and can be used anywhere in your code in place of the actual values..

Remarks

Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files.
-
DoEventsYields execution so that the operating system can process other events.

Syntax

DoEvents( )

Remarks

The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.
-
DeleteSetting  
Description  
EnvironReturns the String associated with an operating system environment variable.

Syntax

Environ({envstring | number})

The Environ function syntax has these named arguments:

Part Description
envstring Optional. String expression containing the name of an environment variable.
number Optional. Numeric expression corresponding to the numeric order of the environment string in the environment-string table. The number argument can be any numeric expression, but is rounded to a whole number before it is evaluated.


Remarks

If envstring can't be found in the environment-string table, a zero-length string ("") is returned. Otherwise, Environ returns the text assigned to the specified envstring; that is, the text following the equal sign (=) in the environment-string table for that environment variable.

If you specify number, the string occupying that numeric position in the environment-string table is returned. In this case, Environ returns all of the text, including envstring. If there is no environment string in the specified position, Environ returns a zero-length string.
-
EOFReturns an Integer containing the Boolean value True when the end of a file opened for Random or sequential Input has been reached.

Syntax

EOF(filenumber)

The required filenumber argument is an Integer containing any valid file number.

Remarks

Use EOF to avoid the error generated by attempting to get input past the end of a file.

The EOF function returns False until the end of the file has been reached. With files opened for Random or Binary access, EOF returns False until the last executed Get statement is unable to read an entire record.

With files opened for Binary access, an attempt to read through the file using the Input function until EOF returns True generates an error. Use the LOF and Loc functions instead of EOF when reading binary files with Input, or use Get when using the EOF function. With files opened for Output, EOF always returns True.
-
ErrContains information about run-time errors.

Remarks
The properties of the Err object are set by the generator of an error Visual Basic, an object, or the programmer.

The default property of the Err object is Number. Because the default property can be represented by the object name Err, earlier code written using the Err function or Err statement doesn't have to be modified.

When a run-time error occurs, the properties of the Err object are filled with information that uniquely identifies the error and information that can be used to handle it. To generate a run-time error in your code, use the Raise method.

The Err object's properties are reset to zero or zero-length strings ("") after an Exit Sub, Exit Function, Exit Property or Resume Next statement within an error-handling routine. Using any form of the Resume statement outside of an error-handling routine will not reset the Err object's properties. The Clear method can be used to explicitly reset Err.

Use the Raise method, rather than the Error statement, to generate run-time errors for system errors and class modules. Using the Raise method in other code depends on the richness of the information you want to return.

The Err object is an intrinsic object with global scope. There is no need to create an instance of it in your code.
 
ErrorReturns the error message that corresponds to a given error number.

Syntax

Error[(errornumber)]

The optional errornumber argument can be any valid error number. If errornumber is a valid error number, but is not defined, Error returns the string "Application-defined or object-defined error." If errornumber is not valid, an error occurs. If errornumber is omitted, the message corresponding to the most recent run-time error is returned. If no run-time error has occurred, or errornumber is 0, Error returns a zero-length string ("").

Remarks

Examine the property settings of the Err object to identify the most recent run-time error. The return value of the Error function corresponds to the Description property of the Err object.
-
ExpReturns a Double specifying e (the base of natural logarithms) raised to a power.

Syntax

Exp(number)

The required number argument is a Double or any valid numeric expression.

Remarks

If the value of number exceeds 709.782712893, an error occurs. The constant e is approximately 2.718282.

Note The Exp function complements the action of the Log function and is sometimes referred to as the antilogarithm.
1
FileAttrReturns a Long representing the file mode for files opened using the Open statement.

Syntax

FileAttr(filenumber, returntype)

The FileAttr function syntax has these named arguments:

Part Description
filenumber Required; Integer. Any valid file number.
returntype Required; Integer. Number indicating the type of information to return. Specify 1 to return a value indicating the file mode. On 16-bit systems only, specify 2 to retrieve an operating system file handle. Returntype 2 is not supported in 32-bit systems and causes an error.


Return Values

When the returntype argument is 1, the following return values indicate the file access mode:

Mode Value
Input 1
Output 2
Random 4
Append 8
Binary 32
-
FileCopy -
FileDateTimeReturns a Variant (Date) that indicates the date and time when a file was created or last modified.

Syntax

FileDateTime(pathname)

The required pathname argument is a string expression that specifies a file name. The pathname may include the directory or folder, and the drive.
-
FileLenReturns a Long specifying the length of a file in bytes.

Syntax

FileLen(pathname)

The required pathname argument is a string expression that specifies a file. The pathname may include the directory or folder, and the drive.

Remarks

If the specified file is open when the FileLen function is called, the value returned represents the size of the file immediately before it was opened.

Note To obtain the length of an open file, use the LOF function.
-
FilterReturns a zero-based array containing subset of a string array based on a specified filter criteria.

Syntax

Filter(sourcesrray, match[, include[, compare]])

The Filter function syntax has these named argument:

Part Description
sourcearray Required. One-dimensional array of strings to be searched.
match Required. String to search for.
include Optional. Boolean value indicating whether to return substrings that include or exclude match. If include is True, Filter returns the subset of the array that contains match as a substring. If include is False, Filter returns the subset of the array that does not contain match as a substring.
compare Optional. Numeric value indicating the kind of string comparison to use. See Settings section for values.


Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption 1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


Remarks

If no matches of match are found within sourcearray, Filter returns an empty array. An error occurs if sourcearray is Null or is not a one-dimensional array.

The array returned by the Filter function contains only enough elements to contain the number of matched items.
?
FixSee Int1
FormatReturns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the year.


Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday


The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the year.
vbFirstFullWeek 3 Start with the first full week of the year.


Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric formats.
Dates and times Use predefined named date/time formats or create user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.


If you try to format a number without specifying format, Format provides functionality similar to the Str function, although it is internationally aware. However, positive numbers formatted as strings using Format dont include a leading space reserved for the sign of the value; those converted using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date formatting, the supplied expression must be Gregorian. If the Visual Basic Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is unchanged. If the calendar is Hijri, all date format symbols (for example, dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar. Format symbols remain in English; symbols that result in text display (for example, AM and PM) display the string (English or Arabic) associated with that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666
?
FormatCurrencyReturns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])

The FormatCurrency function syntax has these parts:

Part Description
Expression Required. Expression to be formatted.
NumDigitsAfterDecimal Optional. Numeric value indicating how many places to the right of the decimal are displayed. Default value is 1, which indicates that the computer's regional settings are used.
IncludeLeadingDigit Optional. Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
UseParensForNegativeNumbers Optional. Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
GroupDigits Optional. Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.


Settings

The IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits arguments have the following settings:

Constant Value Description
vbTrue 1 True
vbFalse 0 False
vbUseDefault 2 Use the setting from the computer's regional settings.


Remarks

When one or more optional arguments are omitted, the values for omitted arguments are provided by the computer's regional settings.

The position of the currency symbol relative to the currency value is determined by the system's regional settings.

Note All settings information comes from the Regional Settings Currency tab, except leading zero which comes from the Number tab.
1
FormatDateTimeReturns an expression formatted as a date or time.

Syntax

FormatDateTime(Date[,NamedFormat])

The FormatDateTime function syntax has these parts:

Part Description
Date Required. Date expression to be formatted.
NamedFormat Optional. Numeric value that indicates the date/time format used. If omitted, vbGeneralDate is used.


Settings

The NamedFormat argument has the following settings:

Constant Value Description
vbGeneralDate 0 Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.
vbLongDate 1 Display a date using the long date format specified in your computer's regional settings.
vbShortDate 2 Display a date using the short date format specified in your computer's regional settings.
vbLongTime 3 Display a time using the time format specified in your computer's regional settings.
vbShortTime 4 Display a time using the 24-hour format (hh:mm).
1
FormatNumberReturns an expression formatted as a number.

Syntax

FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])

The FormatNumber function syntax has these parts:

Part Description
Expression Required. Expression to be formatted.
NumDigitsAfterDecimal Optional. Numeric value indicating how many places to the right of the decimal are displayed. Default value is 1, which indicates that the computer's regional settings are used.
IncludeLeadingDigit Optional. Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
UseParensForNegativeNumbers Optional. Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
GroupDigits Optional. Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.

Settings

The IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits arguments have the following settings:

Constant Value Description
vbTrue 1 True
vbFalse 0 False
vbUseDefault 2 Use the setting from the computer's regional settings.

Remarks

When one or more optional arguments are omitted, the values for omitted arguments are provided by the computer's regional settings.

Note All settings information comes from the Regional Settings Number tab.
1
FormatPercentReturns an expression formatted as a percentage (multipled by 100) with a trailing % character.

Syntax

FormatPercent(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])

The FormatPercent function syntax has these parts:

Part Description
Expression Required. Expression to be formatted.
NumDigitsAfterDecimal Optional. Numeric value indicating how many places to the right of the decimal are displayed. Default value is 1, which indicates that the computer's regional settings are used.
IncludeLeadingDigit Optional. Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.
UseParensForNegativeNumbers Optional. Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.
GroupDigits Optional. Tristate constant that indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. See Settings section for values.

Settings

The IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits arguments have the following settings:

Constant Value Description
vbTrue 1 True
vbFalse 0 False
vbUseDefault 2 Use the setting from the computer's regional settings.

Remarks

When one or more optional arguments are omitted, the values for omitted arguments are provided by the computer's regional settings.

Note All settings information comes from the Regional Settings Number tab.
1
FreeFileReturns an Integer representing the next file number available for use by the Open statement.

Syntax

FreeFile[(rangenumber)]

The optional rangenumber argument is a Variant that specifies the range from which the next free file number is to be returned. Specify a 0 (default) to return a file number in the range 1 255, inclusive. Specify a 1 to return a file number in the range 256 511.

Remarks

Use FreeFile to supply a file number that is not already in use.
-
FVReturns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

FV(rate, nper, pmt[, pv[, type]])

The FV function has these named arguments:

Part Description
rate Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
nper Required. Integer specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pmt Required. Double specifying payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
pv Optional. Variant specifying present value (or lump sum) of a series of future payments. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make. If omitted, 0 is assumed.
type Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months.

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
2
GetAllSettingsReturns a list of key settings and their respective values (originally created with SaveSetting) from an application's entry in the Windows registry.

Syntax

GetAllSettings(appname, section)

The GetAllSettings function syntax has these named arguments:

Part Description
appname Required. String expression containing the name of the application or project whose key settings are requested.
section Required. String expression containing the name of the section whose key settings are requested. GetAllSettings returns a Variant whose contents is a two-dimensional array of strings containing all the key settings in the specified section and their corresponding values.


Remarks

GetAllSettings returns an uninitialized Variant if either appname or section does not exist.
-
GetAttrReturns an Integer representing the attributes of a file, directory, or folder.

Syntax

GetAttr(pathname)

The required pathname argument is a string expression that specifies a file name. The pathname may include the directory or folder, and the drive.

Return Values

The value returned by GetAttr is the sum of the following attribute values:

Constant Value Description
vbNormal 0 Normal.
vbReadOnly 1 Read-only.
vbHidden 2 Hidden.
vbSystem 4 System file.
vbDirectory 16 Directory or folder.
vbArchive 32 File has changed since last backup.


Note These constants are specified by Visual Basic for Applications. The names can be used anywhere in your code in place of the actual values.

Remarks

To determine which attributes are set, use the And operator to perform a bitwise comparison of the value returned by the GetAttr function and the value of the individual file attribute you want. If the result is not zero, that attribute is set for the named file. For example, the return value of the following And expression is zero if the Archive attribute is not set:

Result = GetAttr(FName) And vbArchive
A nonzero value is returned if the Archive attribute is set.
-
GetObjectReturns a reference to an object provided by an ActiveX component.

Syntax

GetObject([pathname] [, class])

The GetObject function syntax has these named arguments:

Part Description
pathname Optional; Variant (String). The full path and name of the file containing the object to retrieve. If pathname is omitted, class is required.
class Optional; Variant (String). A string representing the class of the object.


The class argument uses the syntax appname.objecttype and has these parts:

Part Description
appname Required; Variant (String). The name of the application providing the object.
objecttype Required; Variant (String). The type or class of object to create.


Remarks

Use the GetObject function to access an ActiveX object from a file and assign the object to an object variable. Use the Set statement to assign the object returned by GetObject to the object variable. For example:

Dim CADObject As Object
Set CADObject = GetObject("C:\CAD\SCHEMA.CAD")
When this code is executed, the application associated with the specified pathname is started and the object in the specified file is activated.

If pathname is a zero-length string (""), GetObject returns a new object instance of the specified type. If the pathname argument is omitted, GetObject returns a currently active object of the specified type. If no object of the specified type exists, an error occurs.

Some applications allow you to activate part of a file. Add an exclamation point (!) to the end of the file name and follow it with a string that identifies the part of the file you want to activate. For information on how to create this string, see the documentation for the application that created the object.

For example, in a drawing application you might have multiple layers to a drawing stored in a file. You could use the following code to activate a layer within a drawing called
SCHEMA.CAD
:

Set LayerObject = GetObject("C:\CAD\SCHEMA.CAD!Layer3")
If you don't specify the object's class, Automation determines the application to start and the object to activate, based on the file name you provide. Some files, however, may support more than one class of object. For example, a drawing might support three different types of objects: an Application object, a Drawing object, and a Toolbar object, all of which are part of the same file. To specify which object in a file you want to activate, use the optional class argument. For example:

Dim MyObject As Object
Set MyObject = GetObject("C:\DRAWINGS\SAMPLE.DRW", "FIGMENT.DRAWING")
In the example,
FIGMENT
is the name of a drawing application and
DRAWING
is one of the object types it supports.

Once an object is activated, you reference it in code using the object variable you defined. In the preceding example, you access properties and methods of the new object using the object variable
MyObject
. For example:

MyObject.Line 9, 90
MyObject.InsertText 9, 100, "Hello, world."
MyObject.SaveAs "C:\DRAWINGS\SAMPLE.DRW"
Note Use the GetObject function when there is a current instance of the object or if you want to create the object with a file already loaded. If there is no current instance, and you don't want the object started with a file loaded, use the CreateObject function.

If an object has registered itself as a single-instance object, only one instance of the object is created, no matter how many times CreateObject is executed. With a single-instance object, GetObject always returns the same instance when called with the zero-length string ("") syntax, and it causes an error if the pathname argument is omitted. You can't use GetObject to obtain a reference to a class created with Visual Basic.
-
GetSettingReturns a key setting value from an application's entry in the Windows registry.

Syntax

GetSetting(appname, section, key[, default])

The GetSetting function syntax has these named arguments:

Part Description
appname Required. String expression containing the name of the application or project whose key setting is requested.
section Required. String expression containing the name of the section where the key setting is found.
key Required. String expression containing the name of the key setting to return.
default Optional. Expression containing the value to return if no value is set in the key setting. If omitted, default is assumed to be a zero-length string ("").

Remarks

If any of the items named in the GetSetting arguments do not exist, GetSetting returns the value of default.
-
HelpContext  
HelpFile  
HexReturns a String representing the hexadecimal value of a number.

Syntax

Hex(number)

The required number argument is any valid numeric expression or string expression.

Remarks

If number is not already a whole number, it is rounded to the nearest whole number before being evaluated.

If number is Hex returns
Null Null
Empty Zero (0)
Any other number Up to eight hexadecimal characters


You can represent hexadecimal numbers directly by preceding numbers in the proper range with
&H.
For example,
&H10
represents decimal 16 in hexadecimal notation.
1
HourReturns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day.

Syntax

Hour(time)

The required time argument is any Variant, numeric expression, string expression, or any combination, that can represent a time. If time contains Null, Null is returned.
1
IIfReturns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.


Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.
?
IMEStatusReturns an Integer specifying the current Input Method Editor (IME) mode of Microsoft Windows; available in East Asian versions only.

Syntax

IMEStatus

Return Values

The return values for the Japanese locale are as follows:

Constant Value Description
vbIMEModeNoControl 0 Don't control IME (default)
vbIMEModeOn 1 IME on
vbIMEModeOff 2 IME off
vbIMEModeDisable 3 IME disabled
vbIMEModeHiragana 4 Full-width Hiragana mode
vbIMEModeKatakana 5 Full-width Katakana mode
vbIMEModeKatakanaHalf 6 Half-width Katakana mode
vbIMEModeAlphaFull 7 Full-width Alphanumeric mode
vbIMEModeAlpha 8 Half-width Alphanumeric mode


The return values for the Korean locale are as follows:

Constant Value Description
vbIMEModeNoControl 0 Don't control IME(default)
vbIMEModeAlphaFull 7 Full-width Alphanumeric mode
vbIMEModeAlpha 8 Half-width Alphanumeric mode
vbIMEModeHangulFull 9 Full-width Hangul mode
vbIMEModeHangul 10 Half-width Hangul mode


The return values for the Chinese locale are as follows:

Constant Value Description
vbIMEModeNoControl 0 Don't control IME (default)
vbIMEModeOn 1 IME on
vbIMEModeOff 2 IME off
-
InputReturns String containing characters from a file opened in Input or Binary mode.

Syntax

Input(number, [#]filenumber)

The Input function syntax has these parts:

Part Description
number Required. Any valid numeric expression specifying the number of characters to return.
filenumber Required. Any valid file number.


Remarks

Data read with the Input function is usually written to a file with Print # or Put. Use this function only with files opened in Input or Binary mode.

Unlike the Input # statement, the Input function returns all of the characters it reads, including commas, carriage returns, linefeeds, quotation marks, and leading spaces.

With files opened for Binary access, an attempt to read through the file using the Input function until EOF returns True generates an error. Use the LOF and Loc functions instead of EOF when reading binary files with Input, or use Get when using the EOF function.

Security Note When reading from files, do not make decisions about the contents of the file based on the file name extension. For example, a file named Form1.vb may not be a Visual Basic source file.
Note Use the InputB function for byte data contained within text files. With InputB, number specifies the number of bytes to return rather than the number of characters to return.
-
InputB -
InputBoxDisplays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box.

Syntax

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

The InputBox function syntax has these named arguments:

Part Description
prompt Required. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage returnlinefeed character combination (Chr(13) & Chr(10)) between each line.
title Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
default Optional. String expression displayed in the text box as the default response if no other input is provided. If you omit default, the text box is displayed empty.
xpos Optional. Numeric expression that specifies, in twips, the horizontal distance of the left edge of the dialog box from the left edge of the screen. If xpos is omitted, the dialog box is horizontally centered.
ypos Optional. Numeric expression that specifies, in twips, the vertical distance of the upper edge of the dialog box from the top of the screen. If ypos is omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
helpfile Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.


Remarks

When both helpfile and context are provided, the user can press F1 to view the Help topic corresponding to the context. Some host applications, for example, Microsoft Excel, also automatically add a Help button to the dialog box. If the user clicks OK or presses ENTER , the InputBox function returns whatever is in the text box. If the user clicks Cancel, the function returns a zero-length string ("").

Note To specify more than the first named argument, you must use InputBox in an expression. To omit some positional arguments, you must include the corresponding comma delimiter.
-
InStrReturns a Variant (Long) specifying the position of the first occurrence of one string within another.

Syntax

InStr([start, ]string1, string2[, compare])

The InStr function syntax has these arguments:

Part Description
start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified.
string1 Required. String expression being searched.
string2 Required. String expression sought.
compare Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison.


Settings

The compare argument settings are:

Constant Value Description
vbUseCompareOption -1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


Return Values

If InStr returns
string1 is zero-length 0
string1 is Null Null
string2 is zero-length start
string2 is Null Null
string2 is not found 0
string2 is found within string1 Position at which match is found
start > string2 0


Remarks

The InStrB function is used with byte data contained in a string. Instead of returning the character position of the first occurrence of one string within another, InStrB returns the byte position.
mondrian 2.4
InStrBSee InStr.-
InStrRevReturns the position of an occurrence of one string within another, from the end of string.

Syntax

InstrRev(stringcheck, stringmatch[, start[, compare]])

The InstrRev function syntax has these named arguments:

Part Description
stringcheck Required. String expression being searched.
stringmatch Required. String expression being searched for.
start Optional. Numeric expression that sets the starting position for each search. If omitted, 1 is used, which means that the search begins at the last character position. If start contains Null, an error occurs.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. If omitted, a binary comparison is performed. See Settings section for values.

Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption 1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


Return Values

InStrRev returns the following values:

If InStrRev returns
stringcheck is zero-length 0
stringcheck is Null Null
stringmatch is zero-length start
stringmatch is Null Null
stringmatch is not found 0
stringmatch is found within stringcheck Position at which match is found
start > Len(stringmatch) 0


Remarks

Note that the syntax for the InstrRev function is not the same as the syntax for the Instr function.
1
IntReturns the integer portion of a number.

Syntax

Int(number)
Fix(number)

The required number argument is a Double or any valid numeric expression. If number contains Null, Null is returned.

Remarks

Both Int and Fix remove the fractional part of number and return the resulting integer value.

The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

Fix(number) is equivalent to:

Sgn(number) * Int(Abs(number))
1
IPmtReturns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

IPmt(rate, per, nper, pv[, fv[, type]])

The IPmt function has these named arguments:

Part Description
rate Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
per Required. Double specifying payment period in the range 1 through nper.
nper Required. Double specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pv Required. Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional. Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months.

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
2
IRRReturns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).

Syntax

IRR(values()[, guess])

The IRR function has these named arguments:

Part Description
values() Required. Array of Double specifying cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt).
guess Optional. Variant specifying value you estimate will be returned by IRR. If omitted, guess is 0.1 (10 percent).


Remarks

The internal rate of return is the interest rate received for an investment consisting of payments and receipts that occur at regular intervals.

The IRR function uses the order of values within the array to interpret the order of payments and receipts. Be sure to enter your payment and receipt values in the correct sequence. The cash flow for each period doesn't have to be fixed, as it is for an annuity.

IRR is calculated by iteration. Starting with the value of guess, IRR cycles through the calculation until the result is accurate to within 0.00001 percent. If IRR can't find a result after 20 tries, it fails.
2
IsArrayReturns a Boolean value indicating whether a variable is an array.

Syntax

IsArray(varname)

The required varname argument is an identifier specifying a variable.

Remarks

IsArray returns True if the variable is an array; otherwise, it returns False. IsArray is especially useful with variants containing arrays.
2
IsDateReturns a Boolean value indicating whether an expression can be converted to a date.

Syntax

IsDate(expression)

The required expression argument is a Variant containing a date expression or string expression recognizable as a date or time.

Remarks

IsDate returns True if the expression is a date or is recognizable as a valid date; otherwise, it returns False. In Microsoft Windows, the range of valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the ranges vary among operating systems.
1
IsEmptyReturns a Boolean value indicating whether a variable has been initialized.

Syntax

IsEmpty(expression)

The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

Remarks

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.
since mondrian 0.6
IsErrorReturns a Boolean value indicating whether an expression is an error value.

Syntax

IsError(expression)

The required expression argument can be any valid expression.

Remarks

Error values are created by converting real numbers to error values using the CVErr function. The IsError function is used to determine if a numeric expression represents an error. IsError returns True if the expression argument indicates an error; otherwise, it returns False.
?
IsMissingReturns a Boolean value indicating whether an optional Variant argument has been passed to a procedure.

Syntax

IsMissing(argname)

The required argname argument contains the name of an optional Variant procedure argument.

Remarks

Use the IsMissing function to detect whether or not optional Variant arguments have been provided in calling a procedure. IsMissing returns True if no value has been passed for the specified argument; otherwise, it returns False. If IsMissing returns True for an argument, use of the missing argument in other code may cause a user-defined error. If IsMissing is used on a ParamArray argument, it always returns False. To detect an empty ParamArray, test to see if the arrays upper bound is less than its lower bound.

Note IsMissing does not work on simple data types (such as Integer or Double) because, unlike Variants, they don't have a provision for a "missing" flag bit. Because of this, the syntax for typed optional arguments allows you to specify a default value. If the argument is omitted when the procedure is called, then the argument will have this default value, as in the example below:

Sub MySub(Optional MyVar As String = "specialvalue")
If MyVar = "specialvalue" Then
' MyVar was omitted.
Else
...
End Sub
In many cases you can omit the
If MyVar
test entirely by making the default value equal to the value you want
MyVar
to contain if the user omits it from the function call. This makes your code more concise and efficient.
?
IsNullReturns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax

IsNull(expression)

The required expression argument is a Variant containing a numeric expression or string expression.

Remarks

IsNull returns True if expression is Null; otherwise, IsNull returns False. If expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression.

The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as
If Var = Null
and
If Var <> Null
, are always False. This is because any expression containing a Null is itself Null and, therefore, False.
1
IsNumericReturns a Boolean value indicating whether an expression can be evaluated as a number.

Syntax

IsNumeric(expression)

The required expression argument is a Variant containing a numeric expression or string expression.

Remarks

IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False.

IsNumeric returns False if expression is a date expression.
1
IsObjectReturns a Boolean value indicating whether an identifier represents an object variable.

Syntax

IsObject(identifier)

The required identifier argument is a variable name.

Remarks

IsObject is useful only in determining whether a Variant is of VarType vbObject. This could occur if the Variant actually references (or once referenced) an object, or if it contains Nothing.

IsObject returns True if identifier is a variable declared with Object type or any valid class type, or if identifier is a Variant of VarType vbObject, or a user-defined object; otherwise, it returns False. IsObject returns True even if the variable has been set to Nothing.

Use error trapping to be sure that an object reference is valid.
2
Item ?
JoinReturns a string created by joining a number of substrings contained in an array.

Syntax

Join(sourcearray[, delimiter])

The Join function syntax has these named arguments:

Part Description
sourcearray Required. One-dimensional array containing substrings to be joined.
delimiter Optional. String character used to separate the substrings in the returned string. If omitted, the space character (" ") is used. If delimiter is a zero-length string (""), all items in the list are concatenated with no delimiters.
-
Kill  
LastDllError  
LCaseReturns a String that has been converted to lowercase.

Syntax

LCase(string)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Remarks

Only uppercase letters are converted to lowercase; all lowercase letters and nonletter characters remain unchanged.
1
LeftReturns a Variant (String) containing a specified number of characters from the left side of a string.

Syntax

Left(string, length)

The Left function syntax has these named arguments:

Part Description
string Required. String expression from which the leftmost characters are returned. If string contains Null, Null is returned.
length Required; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in string, the entire string is returned.


Remarks

To determine the number of characters in string, use the Len function.

Note Use the LeftB function with byte data contained in a string. Instead of specifying the number of characters to return, length specifies the number of bytes.
mondrian 2.4
LeftBSee Left. 
LenReturns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Syntax

Len(string | varname)

The Len function syntax has these parts:

Part Description
string Any valid string expression. If string contains Null, Null is returned.
Varname Any valid variable name. If varname contains Null, Null is returned. If varname is a Variant, Len treats it the same as a String and always returns the number of characters it contains.


Remarks

One (and only one) of the two possible arguments must be specified. With user-defined types, Len returns the size as it will be written to the file.

Note Use the LenB function with byte data contained in a string, as in double-byte character set (DBCS) languages. Instead of returning the number of characters in a string, LenB returns the number of bytes used to represent that string. With user-defined types, LenB returns the in-memory size, including any padding between elements. For sample code that uses LenB, see the second example in the example topic.

Note Len may not be able to determine the actual number of storage bytes required when used with variable-length strings in user-defined data types.
mondrian 2.4
LenBSee Len. 
LocReturns a Long specifying the current read/write position within an open file.

Syntax

Loc(filenumber)

The required filenumber argument is any valid Integer file number.

Remarks

The following describes the return value for each file access mode:

Mode Return Value
Random Number of the last record read from or written to the file.
Sequential Current byte position in the file divided by 128. However, information returned by Loc for sequential files is neither used nor required.
Binary Position of the last byte read or written.
-
LOFReturns a Long representing the size, in bytes, of a file opened using the Open statement.

Syntax

LOF(filenumber)

The required filenumber argument is an Integer containing a valid file number.

Note Use the FileLen function to obtain the length of a file that is not open.
-
LogReturns a Double specifying the natural logarithm of a number.

Syntax

Log(number)

The required number argument is a Double or any valid numeric expression greater than zero.

Remarks

The natural logarithm is the logarithm to the base e. The constant e is approximately 2.718282.

You can calculate base-n logarithms for any number x by dividing the natural logarithm of x by the natural logarithm of n as follows:

Logn(x) = Log(x) / Log(n)

The following example illustrates a custom Function that calculates base-10 logarithms:

Static Function Log10(X)
Log10 = Log(X) / Log(10#)
End Function
1
LTrimReturns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).

Syntax

LTrim(string)
RTrim(string)
Trim(string)

The required string argument is any valid string expression. If string contains Null, Null is returned.
1
MidReturns a Variant (String) containing a specified number of characters from a string.

Syntax

Mid(string, start[, length])

The Mid function syntax has these named arguments:

Part Description
string Required. String expression from which characters are returned. If string contains Null, Null is returned.
start Required; Long. Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string ("").
length Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.


Remarks

To determine the number of characters in string, use the Len function.

Note Use the MidB function with byte data contained in a string, as in double-byte character set languages. Instead of specifying the number of characters, the arguments specify numbers of bytes. For sample code that uses MidB, see the second example in the example topic.
mondrian 2.4
MidBSee Mid.-
MinuteReturns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.

Syntax

Minute(time)

The required time argument is any Variant, numeric expression, string expression, or any combination, that can represent a time. If time contains Null, Null is returned.
1
MIRRReturns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).

Syntax

MIRR(values(), finance_rate, reinvest_rate)

The MIRR function has these named arguments:

Part Description
values() Required. Array of Double specifying cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt).
finance_rate Required. Double specifying interest rate paid as the cost of financing.
reinvest_rate Required. Double specifying interest rate received on gains from cash reinvestment.

Remarks

The modified internal rate of return is the internal rate of return when payments and receipts are financed at different rates. The MIRR function takes into account both the cost of the investment (finance_rate) and the interest rate received on reinvestment of cash (reinvest_rate).

The finance_rate and reinvest_rate arguments are percentages expressed as decimal values. For example, 12 percent is expressed as 0.12.

The MIRR function uses the order of values within the array to interpret the order of payments and receipts. Be sure to enter your payment and receipt values in the correct sequence.
2
MkDir -
MonthReturns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Syntax

Month(date)

The required date argument is any Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

Note If the Calendar property setting is Gregorian, the returned integer represents the Gregorian day of the week for the date argument. If the calendar is Hijri, the returned integer represents the Hijri day of the week for the date argument. For Hijri dates, the argument number is any numeric expression that can represent a date and/or time from 1/1/100 (Gregorian Aug 2, 718) through 4/3/9666 (Gregorian Dec 31, 9999).
1
MonthNameReturns a string indicating the specified month.

Syntax

MonthName(month[, abbreviate])

The MonthName function syntax has these parts:

Part Description
month Required. The numeric designation of the month. For example, January is 1, February is 2, and so on.
abbreviate Optional. Boolean value that indicates if the month name is to be abbreviated. If omitted, the default is False, which means that the month name is not abbreviated.
1
MsgBoxDisplays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked.

Syntax

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt Required. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return linefeed character combination (Chr(13) & Chr(10)) between each line.
buttons Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If omitted, the default value for buttons is 0.
title Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
helpfile Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.


Settings

The buttons argument settings are:

Constant Value Description
vbOKOnly 0 Display OK button only.
vbOKCancel 1 Display OK and Cancel buttons.
vbAbortRetryIgnore 2 Display Abort, Retry, and Ignore buttons.
vbYesNoCancel 3 Display Yes, No, and Cancel buttons.
vbYesNo 4 Display Yes and No buttons.
vbRetryCancel 5 Display Retry and Cancel buttons.
vbCritical 16 Display Critical Message icon.
vbQuestion 32 Display Warning Query icon.
vbExclamation 48 Display Warning Message icon.
vbInformation 64 Display Information Message icon.
vbDefaultButton1 0 First button is default.
vbDefaultButton2 256 Second button is default.
vbDefaultButton3 512 Third button is default.
vbDefaultButton4 768 Fourth button is default.
vbApplicationModal 0 Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal 4096 System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton 16384 Adds Help button to the message box
VbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems


The first group of values (05) describes the number and type of buttons displayed in the dialog box; the second group (16, 32, 48, 64) describes the icon style; the third group (0, 256, 512) determines which button is the default; and the fourth group (0, 4096) determines the modality of the message box. When adding numbers to create a final value for the buttons argument, use only one number from each group.

Note These constants are specified by Visual Basic for Applications. As a result, the names can be used anywhere in your code in place of the actual values.

Return Values

Constant Value Description
vbOK 1 OK
vbCancel 2 Cancel
vbAbort 3 Abort
vbRetry 4 Retry
vbIgnore 5 Ignore
vbYes 6 Yes
vbNo 7 No


Remarks

When both helpfile and context are provided, the user can press F1 to view the Help topic corresponding to the context. Some host applications, for example, Microsoft Excel, also automatically add a Help button to the dialog box.

If the dialog box displays a Cancel button, pressing the ESC key has the same effect as clicking Cancel. If the dialog box contains a Help button, context-sensitive Help is provided for the dialog box. However, no value is returned until one of the other buttons is clicked.

Note To specify more than the first named argument, you must use MsgBox in an expression. To omit some positional arguments, you must include the corresponding comma delimiter.
-
NowReturns a Variant (Date) specifying the current date and time according your computer's system date and time.

Syntax

Now
1
NPerReturns a Double specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

NPer(rate, pmt, pv[, fv[, type]])

The NPer function has these named arguments:

Part Description
rate Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
pmt Required. Double specifying payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
pv Required. Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional. Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
2
NPVReturns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.

Syntax

NPV(rate, values())

The NPV function has these named arguments:

Part Description
rate Required. Double specifying discount rate over the length of the period, expressed as a decimal.
values() Required. Array of Double specifying cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt).


Remarks

The net present value of an investment is the current value of a future series of payments and receipts.

The NPV function uses the order of values within the array to interpret the order of payments and receipts. Be sure to enter your payment and receipt values in the correct sequence.

The NPV investment begins one period before the date of the first cash flow value and ends with the last cash flow value in the array.

The net present value calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the value returned by NPV and must not be included in the cash flow values of values( ).

The NPV function is similar to the PV function (present value) except that the PV function allows cash flows to begin either at the end or the beginning of a period. Unlike the variable NPV cash flow values, PV cash flows must be fixed throughout the investment.
2
Number ?
OctReturns a Variant (String) representing the octal value of a number.

Syntax

Oct(number)

The required number argument is any valid numeric expression or string expression.

Remarks

If number is not already a whole number, it is rounded to the nearest whole number before being evaluated.

If number is Oct returns
Null Null
Empty Zero (0)
Any other number Up to 11 octal characters


You can represent octal numbers directly by preceding numbers in the proper range with
&O
. For example,
&O10
is the octal notation for decimal 8.
1
PartitionReturns a Variant (String) indicating where a number occurs within a calculated series of ranges.

Syntax

Partition(number, start, stop, interval)

The Partition function syntax has these named arguments:

Part Description
number Required. Whole number that you want to evaluate against the ranges.
start Required. Whole number that is the start of the overall range of numbers. The number can't be less than 0.
stop Required. Whole number that is the end of the overall range of numbers. The number can't be equal to or less than start.


Remarks

The Partition function identifies the particular range in which number falls and returns a Variant (String) describing that range. The Partition function is most useful in queries. You can create a select query that shows how many orders fall within various ranges, for example, order values from 1 to 1000, 1001 to 2000, and so on.

The following table shows how the ranges are determined using three sets of start, stop, and interval parts. The First Range and Last Range columns show what Partition returns. The ranges are represented by lowervalue:uppervalue, where the low end (lowervalue) of the range is separated from the high end (uppervalue) of the range with a colon (:).

start stop interval Before First First Range Last Range After Last
0 99 5 " :-1" " 0: 4" " 95: 99" " 100: "
20 199 10 " : 19" " 20: 29" " 190: 199" " 200: "
100 1010 20 " : 99" " 100: 119" " 1000: 1010" " 1011: "


In the table shown above, the third line shows the result when start and stop define a set of numbers that can't be evenly divided by interval. The last range extends to stop (11 numbers) even though interval is 20.

If necessary, Partition returns a range with enough leading spaces so that there are the same number of characters to the left and right of the colon as there are characters in stop, plus one. This ensures that if you use Partition with other numbers, the resulting text will be handled properly during any subsequent sort operation.

If interval is 1, the range is number:number, regardless of the start and stop arguments. For example, if interval is 1, number is 100 and stop is 1000, Partition returns " 100: 100".

If any of the parts is Null, Partition returns a Null.
2
PmtReturns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

Pmt(rate, nper, pv[, fv[, type]])

The Pmt function has these named arguments:

Part Description
rate Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
nper Required. Integer specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pv Required. Double specifying present value (or lump sum) that a series of payments to be paid in the future is worth now. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional. Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months.

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
2
PPmtReturns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

PPmt(rate, per, nper, pv[, fv[, type]])

The PPmt function has these named arguments:

Part Description
rate Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
per Required. Integer specifying payment period in the range 1 through nper.
nper Required. Integer specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pv Required. Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional. Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months.

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
2
PVReturns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.

Syntax

PV(rate, nper, pmt[, fv[, type]])

The PV function has these named arguments:

Part Description
rate Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
nper Required. Integer specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pmt Required. Double specifying payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
fv Optional. Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months.

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
2
QBColorReturns a Long representing the RGB color code corresponding to the specified color number.

Syntax

QBColor(color)

The required color argument is a whole number in the range 015.

Settings

The color argument has these settings:

Number Color Number Color
0 Black 8 Gray
1 Blue 9 Light Blue
2 Green 10 Light Green
3 Cyan 11 Light Cyan
4 Red 12 Light Red
5 Magenta 13 Light Magenta
6 Yellow 14 Light Yellow
7 White 15 Bright White


Remarks

The color argument represents color values used by earlier versions of Basic (such as Microsoft Visual Basic for MS-DOS and the Basic Compiler). Starting with the least-significant byte, the returned value specifies the red, green, and blue values used to set the appropriate color in the RGB system used by Visual Basic for Applications.
2
Raise  
RandomizeSee Rnd.2
RateReturns a Double specifying the interest rate per period for an annuity.

Syntax

Rate(nper, pmt, pv[, fv[, type[, guess]]])

The Rate function has these named arguments:

Part Description
nper Required. Double specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods.
pmt Required. Double specifying payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
pv Required. Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
fv Optional. Variant specifying future value or cash balance you want after you make the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.
type Optional. Variant specifying a number indicating when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.
guess Optional. Variant specifying value you estimate will be returned by Rate. If omitted, guess is 0.1 (10 percent).


Remarks

An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).

For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.

Rate is calculated by iteration. Starting with the value of guess, Rate cycles through the calculation until the result is accurate to within 0.00001 percent. If Rate can't find a result after 20 tries, it fails. If your guess is 10 percent and Rate fails, try a different value for guess.
2
Remove  
ReplaceReturns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing substring to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.


Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption 1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


Return Values

Replace returns the following values:

If Replace returns
expression is zero-length Zero-length string ("")
expression is Null An error.
find is zero-length Copy of expression.
replace is zero-length Copy of expression with all occurences of find removed.
start > Len(expression) Zero-length string.
count is 0 Copy of expression.


Remarks

The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and and concludes at the end of the expression string. It is not a copy of the original string from start to finish.
1
Reset  
RGBReturns a Long whole number representing an RGB color value.

Syntax

RGB(red, green, blue)

The RGB function syntax has these named arguments:

Part Description
red Required; Variant (Integer). Number in the range 0255, inclusive, that represents the red component of the color.
green Required; Variant (Integer). Number in the range 0255, inclusive, that represents the green component of the color.
blue Required; Variant (Integer). Number in the range 0255, inclusive, that represents the blue component of the color.


Remarks

Application methods and properties that accept a color specification expect that specification to be a number representing an RGB color value. An RGB color value specifies the relative intensity of red, green, and blue to cause a specific color to be displayed.

The value for any argument to RGB that exceeds 255 is assumed to be 255.

The following table lists some standard colors and the red, green, and blue values they include:

Color Red Value Green Value Blue Value
Black 0 0 0
Blue 0 0 255
Green 0 255 0
Cyan 0 255 255
Red 255 0 0
Magenta 255 0 255
Yellow 255 255 0
White 255 255 255
2
RightReturns a Variant (String) containing a specified number of characters from the right side of a string.

Syntax

Right(string, length)

The Right function syntax has these named arguments:

Part Description
string Required. String expression from which the rightmost characters are returned. If string contains Null, Null is returned.
length Required; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in string, the entire string is returned.


Remarks

To determine the number of characters in string, use the Len function.

Note Use the RightB function with byte data contained in a string. Instead of specifying the number of characters to return, length specifies the number of bytes.
1
RightBSee Right.-
RmDir -
RndReturns a Single containing a random number.

Syntax

Rnd[(number)]

The optional number argument is a Single or any valid numeric expression.

Return Values

If number is Rnd generates
Less than zero The same number every time, using number as the seed.
Greater than zero The next random number in the sequence.
Equal to zero The most recently generated number.
Not supplied The next random number in the sequence.


Remarks

The Rnd function returns a value less than 1 but greater than or equal to zero.

The value of number determines how Rnd generates a random number:

For any given initial seed, the same number sequence is generated because each successive call to the Rnd function uses the previous number as a seed for the next number in the sequence.

Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.

Note To repeat sequences of random numbers, call Rnd with a negative argument immediately before using Randomize with a numeric argument. Using Randomize with the same value for number does not repeat the previous sequence.

Security Note Because the Random statement and the Rnd function start with a seed value and generate numbers that fall within a finite range, the results may be predictable by someone who knows the algorithm used to generate them. Consequently, the Random statement and the Rnd function should not be used to generate random numbers for use in cryptography.
2
RoundReturns a number rounded to a specified number of decimal places.

Syntax

Round(expression [,numdecimalplaces])

The Round function syntax has these parts:

Part Description
expression Required. Numeric expression being rounded.
numdecimalplaces Optional. Number indicating how many places to the right of the decimal are included in the rounding. If omitted, integers are returned by the Round function.
1
RTrimSee LTrim.1
SaveSetting -
SecondReturns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute.

Syntax

Second(time)

The required time argument is any Variant, numeric expression, string expression, or any combination, that can represent a time. If time contains Null, Null is returned.
1
SeekReturns a Long specifying the current read/write position within a file opened using the Open statement.

Syntax

Seek(filenumber)

The required filenumber argument is an Integer containing a valid file number.

Remarks

Seek returns a value between 1 and 2,147,483,647 (equivalent to 2^31 1), inclusive.

The following describes the return values for each file access mode.

Mode Return Value
Random Number of the next record read or written
Binary,
Output,
Append,
Input Byte position at which the next operation takes place. The first byte in a file is at position 1, the second byte is at position 2, and so on.
-
SendKeys  
SetAttr  
SgnReturns a Variant (Integer) indicating the sign of a number.

Syntax

Sgn(number)

The required number argument can be any valid numeric expression.

Return Values

If number is Sgn returns
Greater than zero 1
Equal to zero 0
Less than zero -1

Remarks

The sign of the number argument determines the return value of the Sgn function.
1
ShellRuns an executable program and returns a Variant (Double) representing the program's task ID if successful, otherwise it returns zero.

Syntax

Shell(pathname[,windowstyle])

The Shell function syntax has these named arguments:

Part Description
pathname Required; Variant (String). Name of the program to execute and any required arguments or command-line switches; may include directory or folder and drive.
windowstyle Optional. Variant (Integer) corresponding to the style of the window in which the program is to be run. If windowstyle is omitted, the program is started minimized with focus.


The windowstyle named argument has these values:

Constant Value Description
vbHide 0 Window is hidden and focus is passed to the hidden window.
vbNormalFocus 1 Window has focus and is restored to its original size and position.
vbMinimizedFocus 2 Window is displayed as an icon with focus.
vbMaximizedFocus 3 Window is maximized with focus.
vbNormalNoFocus 4 Window is restored to its most recent size and position. The currently active window remains active.
vbMinimizedNoFocus 6 Window is displayed as an icon. The currently active window remains active.


Remarks

If the Shell function successfully executes the named file, it returns the task ID of the started program. The task ID is a unique number that identifies the running program. If the Shell function can't start the named program, an error occurs.

Note By default, the Shell function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed.

Security Note If you do not enclose the path and file specification in quotes, there is a security risk if the file name or a path node contains spaces. If the path node specification is not inside quotes, for example
\Program Files
and a program named
Program.exe
had been installed in C:\, for example by illicit tampering, Windows would execute it instead of
MyFile.exe
-
SinReturns a Double specifying the sine of an angle.

Syntax

Sin(number)

The required number argument is a Double or any valid numeric expression that expresses an angle in radians.

Remarks

The Sin function takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the hypotenuse.

The result lies in the range -1 to 1.

To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.
1
SLNReturns a Double specifying the straight-line depreciation of an asset for a single period.

Syntax

SLN(cost, salvage, life)

The SLN function has these named arguments:

Part Description
cost Required. Double specifying initial cost of the asset.
salvage Required. Double specifying value of the asset at the end of its useful life.
life Required. Double specifying length of the useful life of the asset.


Remarks

The depreciation period must be expressed in the same unit as the life argument. All arguments must be positive numbers.
2
Source  
SpaceReturns a Variant (String) consisting of the specified number of spaces.

Syntax

Space(number)

The required number argument is the number of spaces you want in the string.

Remarks

The Space function is useful for formatting output and clearing data in fixed-length strings.
1
SplitReturns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax

Split(expression[, delimiter[, limit[, compare]]])

The Split function syntax has these named arguments:

Part Description
expression Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limit Optional. Number of substrings to be returned; 1 indicates that all substrings are returned.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.


Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption 1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.
-
SqrReturns a Double specifying the square root of a number.

Syntax

Sqr(number)

The required number argument is a Double or any valid numeric expression greater than or equal to zero.
1
StrReturns a Variant (String) representation of a number.

Syntax

Str(number)

The required number argument is a Long containing any valid numeric expression.

Remarks

When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied.

Use the Format function to convert numeric values you want formatted as dates, times, or currency or in other user-defined formats. Unlike Str, the Format function doesn't include a leading space for the sign of number.

Note The Str function recognizes only the period (.) as a valid decimal separator. When different decimal separators may be used (for example, in international applications), use CStr to convert a number to a string.
1
StrCompReturns a Variant (Integer) indicating the result of a string comparison.

Syntax

StrComp(string1, string2[, compare])

The StrComp function syntax has these named arguments:

Part Description
string1 Required. Any valid string expression.
string2 Required. Any valid string expression.
compare Optional. Specifies the type of string comparison. If the compare argument is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison.


Settings

The compare argument settings are:

Constant Value Description
vbUseCompareOption -1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


Return Values

The StrComp function has the following return values:

If StrComp returns
string1 is less than string2 -1
string1 is equal to string2 0
string1 is greater than string2 1
string1 or string2 is Null Null
1
StringReturns a Variant (String) containing a repeating character string of the length specified.

Syntax

String(number, character)

The String function syntax has these named arguments:

Part Description
number Required; Long. Length of the returned string. If number contains Null, Null is returned.
character Required; Variant. Character code specifying the character or string expression whose first character is used to build the return string. If character contains Null, Null is returned.


Remarks

If you specify a number for character greater than 255, String converts the number to a valid character code using the formula:

character Mod 256
1
StrReverseReturns a string in which the character order of a specified string is reversed.

Syntax

StrReverse(expression)

The expression argument is the string whose characters are to be reversed. If expression is a zero-length string (""), a zero-length string is returned. If expression is Null, an error occurs.
1
SwitchEvaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.


Remarks

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

None of the expressions is True.


The first True expression has a corresponding value that is Null.
Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.
?
SYDReturns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period.

Syntax

SYD(cost, salvage, life, period)

The SYD function has these named arguments:

Part Description
cost Required. Double specifying initial cost of the asset.
salvage Required. Double specifying value of the asset at the end of its useful life.
life Required. Double specifying length of the useful life of the asset.
period Required. Double specifying period for which asset depreciation is calculated.


Remarks

The life and period arguments must be expressed in the same units. For example, if life is given in months, period must also be given in months. All arguments must be positive numbers.
2
TanReturns a Double specifying the tangent of an angle.

Syntax

Tan(number)

The required number argument is a Double or any valid numeric expression that expresses an angle in radians.

Remarks

Tan takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.

To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.
1
TimeReturns a Variant (Date) indicating the current system time.

Syntax

Time

Remarks

To set the system time, use the Time statement.
1
TimerReturns a Single representing the number of seconds elapsed since midnight.

Syntax

Timer

Remarks

In Microsoft Windows the Timer function returns fractional portions of a second.
1
TimeSerialReturns a Variant (Date) containing the time for a specific hour, minute, and second.

Syntax

TimeSerial(hour, minute, second)

The TimeSerial function syntax has these named arguments:

Part Description
hour Required; Variant (Integer). Number between 0 (12:00 A.M.) and 23 (11:00 P.M.), inclusive, or a numeric expression.
minute Required; Variant (Integer). Any numeric expression.
second Required; Variant (Integer). Any numeric expression.


Remarks

To specify a time, such as 11:59:59, the range of numbers for each TimeSerial argument should be in the normal range for the unit; that is, 023 for hours and 059 for minutes and seconds. However, you can also specify relative times for each argument using any numeric expression that represents some number of hours, minutes, or seconds before or after a certain time. The following example uses expressions instead of absolute time numbers. The TimeSerial function returns a time for 15 minutes before (
-15
) six hours before noon (
12 - 6
), or 5:45:00 A.M.

TimeSerial(12 - 6, -15, 0)
When any argument exceeds the normal range for that argument, it increments to the next larger unit as appropriate. For example, if you specify 75 minutes, it is evaluated as one hour and 15 minutes. If any single argument is outside the range -32,768 to 32,767, an error occurs. If the time specified by the three arguments causes the date to fall outside the acceptable range of dates, an error occurs.
1
TimeValueReturns a Variant (Date) containing the time.

Syntax

TimeValue(time)

The required time argument is normally a string expression representing a time from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.), inclusive. However, time can also be any expression that represents a time in that range. If time contains Null, Null is returned.

Remarks

You can enter valid times using a 12-hour or 24-hour clock. For example,
"2:24PM"
and
"14:24"
are both valid time arguments.

If the time argument contains date information, TimeValue doesn't return it. However, if time includes invalid date information, an error occurs.
1
TrimSee LTrim.1
TypeNameReturns a String that provides information about a variable.

Syntax

TypeName(varname)

The required varname argument is a Variant containing any variable except a variable of a user-defined type.

Remarks

The string returned by TypeName can be any one of the following:

String returned Variable
object type An object whose type is objecttype
Byte Byte value
Integer Integer
Long Long integer
Single Single-precision floating-point number
Double Double-precision floating-point number
Currency Currency value
Decimal Decimal value
Date Date value
String String
Boolean Boolean value
Error An error value
Empty Uninitialized
Null No valid data
Object An object
Unknown An object whose type is unknown
Nothing Object variable that doesn't refer to an object


If varname is an array, the returned string can be any one of the possible returned strings (or Variant) with empty parentheses appended. For example, if varname is an array of integers, TypeName returns "
Integer()
".
2
UCaseReturns a Variant (String) containing the specified string, converted to uppercase.

Syntax

UCase(string)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Remarks

Only lowercase letters are converted to uppercase; all uppercase letters and nonletter characters remain unchanged.
mondrian 2.4
ValReturns the numbers contained in a string as a numeric value of appropriate type.

Syntax

Val(string)

The required string argument is any valid string expression.

Remarks

The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized. However, the function recognizes the radix prefixes
&O
(for octal) and
&H
(for hexadecimal). Blanks, tabs, and linefeed characters are stripped from the argument.

The following returns the value 1615198:

Val(" 1615 198th Street N.E.")
In the code below, Val returns the decimal value -1 for the hexadecimal value shown:

Val("&HFFFF")
Note The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.
1
VarTypeReturns an Integer indicating the subtype of a variable.

Syntax

VarType(varname)

The required varname argument is a Variant containing any variable except a variable of a user-defined type.

Return Values

Constant Value Description
vbEmpty 0 Empty (uninitialized)
vbNull 1 Null (no valid data)
vbInteger 2 Integer
vbLong 3 Long integer
vbSingle 4 Single-precision floating-point number
vbDouble 5 Double-precision floating-point number
vbCurrency 6 Currency value
vbDate 7 Date value
vbString 8 String
vbObject 9 Object
vbError 10 Error value
vbBoolean 11 Boolean value
vbVariant 12 Variant (used only with arrays of variants)
vbDataObject 13 A data access object
vbDecimal 14 Decimal value
vbByte 17 Byte value
vbUserDefinedType 36 Variants that contain user-defined types
vbArray 8192 Array


Note These constants are specified by Visual Basic for Applications. The names can be used anywhere in your code in place of the actual values.

Remarks

The VarType function never returns the value for vbArray by itself. It is always added to some other value to indicate an array of a particular type. The constant vbVariant is only returned in conjunction with vbArray to indicate that the argument to the VarType function is an array of type Variant. For example, the value returned for an array of integers is calculated as vbInteger + vbArray, or 8194. If an object has a default property, VarType (object) returns the type of the object's default property.
2
WeekdayReturns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

Weekday(date, [firstdayofweek])

The Weekday function syntax has these named arguments:

Part Description
date Required. Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, vbSunday is assumed.


Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday


Return Values

The Weekday function can return any of these values:

Constant Value Description
vbSunday 1 Sunday
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday


Remarks

If the Calendar property setting is Gregorian, the returned integer represents the Gregorian day of the week for the date argument. If the calendar is Hijri, the returned integer represents the Hijri day of the week for the date argument. For Hijri dates, the argument number is any numeric expression that can represent a date and/or time from 1/1/100 (Gregorian Aug 2, 718) through 4/3/9666 (Gregorian Dec 31, 9999).
1
WeekdayNameReturns a string indicating the specified day of the week.

Syntax

WeekdayName(weekday, abbreviate, firstdayofweek)

The WeekdayName function syntax has these parts:

Part Description
weekday Required. The numeric designation for the day of the week. Numeric value of each day depends on setting of the firstdayofweek setting.
abbreviate Optional. Boolean value that indicates if the weekday name is to be abbreviated. If omitted, the default is False, which means that the weekday name is not abbreviated.
firstdayofweek Optional. Numeric value indicating the first day of the week. See Settings section for values.


Settings

The firstdayofweek argument can have the following values:

Constant Value Description
vbUseSystem 0 Use National Language Support (NLS) API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
1
Width  
YearReturns a Variant (Integer) containing a whole number representing the year.

Syntax

Year(date)

The required date argument is any Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

Note If the Calendar property setting is Gregorian, the returned integer represents the Gregorian year for the date argument. If the calendar is Hijri, the returned integer represents the Hijri year for the date argument. For Hijri dates, the argument number is any numeric expression that can represent a date and/or time from 1/1/100 (Gregorian Aug 2, 718) through 4/3/9666 (Gregorian Dec 31, 9999).
1

Author: Julian Hyde; last modified January 2008.
Version: $Id$ (log)
Copyright (C) 2008-2009 Pentaho