Variables

String variables: store characters enclosed in quotation marks

1. Use a DIM statement to create the variable

DIM variable_name As STRING

2. Use an assignment statement to assign a value to a variable

variable_name = value

3. Use the InputBox function to get input from the user

InputBox(Prompt:= prompt [,Title:= title] [,Default:= default]

4. Use the concatenation operator (&) to link strings together

string1& “ “ & string2

5. Use the Val function to convert a string into a number

Val(String:=string)

6. Use the Option Explicit statement in each module’s General Declaration section to prevent undeclared variables (Tools à Options à Require Variable Declaration [under Editor tab])

Working with strings

Display messages to user	MsgBox
Extract part of a string	Mid, Left, Right
Find length of a string	Len
Input text from user	InputBox
Trim leading and/or trailing spaces	LTrim, RTrim, Trim
Format	Format
Returns the position where one string begins with another string	Instr
Returns a string in uppercase	UCase
Replaces one string with another	Replace
Reverses the order of text in a string	StrRev

Date variables: store date and time information

1. Use a DIM statement to create the variable

DIM variable_name As DATE

2. Use the Date, Time, Now functions to assign the current system date and time
3. Use the Format function to control the appearance of dates and times

Format(Expression:= expression, Format:= format)

e.g., Format(Expression:=#11/5/2002#, Format:=”Short Date”) à 11/5/02

4. Use the DateAdd function to add/subtract a specified time interval to a date or time

DateAdd(Interval:= interval, Number:= number, Date:= date)

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

e.g., DateAdd(Interval:=”yyyy”, Number:=2, Date:=#1/1/2002#) à 1/1/2004

5. Use the DateDiff function to calculate the number of time intervals between two specified dates or times

DateDiff(Interval:= interval, Date1:= date1, Date2:= date2)

6. Use the DateValue function to return the date equivalent of a string

DateValue(Date:= stringExpression)

e.g., DateValue(Date:=”11/5/02”) converts the string “11/4/02” to a date

Working with dates

Returns the specified part of a date	DatePart
Returns the interval of time between two dates	DateDiff
Returns the result of adding/subtracting a specified period of time to a date	DateAdd
Returns the text string associated with a month number	MonthName

Other variables

Data type	Storage size	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True or False
Integer	2 bytes	-32,768 to 32,767
Long (long integer)	4 bytes	-2,147,483,648 to 2,147,483,647
Single (single-precision floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double (double-precision floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency (scaled integer)	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	14 bytes	+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001
Date	8 bytes	January 1, 100 to December 31, 9999
Object	4 bytes	Any Object reference
String (variable-length)	10 bytes + string length	0 to approximately 2 billion
String (fixed-length)	Length of string	1 to approximately 65,400
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double
Variant (with characters)	22 bytes + string length	Same range as for variable-length String
User-defined (using Type)	Number required by elements	The range of each element is the same as the