Variables

A variable is a temporary holding place for data while your application is running. When your application stops running, the variable is destroyed . Variables reside in memory, which is much faster than being written to the hard disk.

You've actually already seen variables in action, because arguments are variables. These are used to store the details of what information is to be passed into a procedure. Within the procedure itself we can have other variables, to store other information.

Let's look at a simple example. Looking back to our ice cream making procedure from earlier, and how we set the return value of the function, we used IsMissing() to determine if the argument was missing or not, and then used this to make a decision.

 If IsMissing(VanillaToBeAdded) Then   VanillaToBeAdded = True Else   VanillaToBeAdded = False End If

Now what happens if we need to make this decision twice in the same procedure? We could do this:

 If IsMissing(VanillaToBeAdded) Then ' some more code here If IsMissing(VanillaToBeAdded) Then

This is a little wasteful because we are calling the same function twice with the same argument, so the result will be the same. What we can do is use a variable to store the result of the function, and then test the variable:

   blnMissing = IsMissing (VanillaToBeAdded)     If blnMissing Then   VanillaToBeAdded = True Else   VanillaToBeAdded = False End If

Now if we need to see if the argument is missing more than once, we don't have the overhead of running the function every time. Remember how we said that using procedures allows us to call a set of instructions just by a single name ? That saves us having to type out all of those lines again. Well, assigning a variable to the result of a function saves VBA from having to call the function more than once. In this case IsMissing() is not a very complex function, and therefore runs very quickly, but if we were calling a long and complex function, then calling it more than once would be a little wasteful.

Don't worry too much about the If statement, as we'll be covering that in the next chapter.

You can see that to set the value of a variable, we use the equals sign. The general rule is:

 Variable = Value

In the above example we set our variable to hold the return value from a function, but you can set variables directly. For example:

 blnMissing = True strName = "Janine Lloyd" intAge = 27 ccyPrice = 24.95

Declaring Variables

You've seen a few examples of variables but may not realize exactly what they are, or why we need them. The reason is that VBA is very ordered, and it can't put things just anywhere . If VBA is storing something, it needs a place to store it, and that place has to be suitable. Declaring a variable tells VBA to put aside some memory to store the variable in, and specifying the variable type tells VBA what sort of data will be stored. That way it knows how much memory to put aside, as different variable types take different amounts of memory.

We have variable types to store different types of information. Strings, for example, need to be stored in a different way from numbers , and the variable type tells VBA how to handle that variable as well as how to store it.

To declare a variable we use the Dim statement:

 Dim VariableName As VariableType

VariableName is the name of the variable, and follows the same conventions as procedure names . We also frequently prefix the variable name with the type of data it is going to hold. For example, in the above example we used the variable blnMissing , using bln as the prefix to indicate a Boolean value (which can be either True or False ). Keep in mind that wherever you see a variable used, the prefix indicates its data type.

VariableType indicates the type of data that this variable will hold, for example string, number, date, boolean, and so on. We'll also look at data types in more detail later on.

For our boolean variable we would have used the following declaration:

   Dim blnMissing As Boolean

Variable names are not case-sensitive, and VBA will convert variables to the case they were declared in. There is no specific place where variables must be declared, but by convention they are generally put at the top of procedures. This makes them all easy to find.

There are many different data types all designed to hold specific kinds of data.

Manipulating Variables

Remember back to your math classes? Remember how you used to have all this stuff about x, y and z, and if x is 3, then z should be half of y? And you used to write things like this:

 x = y2 + z2

This is manipulating variables, and you can do it just the same in VBA. You can use any of the standard math operators just like you did back then:

 intX = intY * intY + intZ * intZ

Addition, subtraction, it all works the same way, with all of the numeric data types.

When dealing with strings, though, you can't do some of this. But one thing you can (and will) do is join (another term is concatenate ) strings together. For this you use the & sign, as follows:

   Dim FirstName As String     Dim LastName As String     Dim FullName As String     FirstName = "Janine"     LastName = "Lloyd"     FullName = FirstName & LastName

This gives you:

JanineLloyd

But this doesn't look too good, since the first name and last name are right next to each other, which is probably not quite what you wanted. What would be better was if we could add a space in the middle of the two names. You might think that you'd need to create a new variable and assign it a string that just contains a space, but you can actually concatenate strings without them being in a variable:

   FullName = FirstName & " " & LastName

This just adds a space in between the two strings. You're also not limited to just using strings in the middle of expressions, as the following lines show:

   FullName = "Janine " & LastName     FullName = FirstName & " Lloyd"

So don't think that once you've stored a value in a variable it's untouchable. You can continue to manipulate it as much as you wish.

Variable Types

The following table shows the standard variable types and how they are used:

Type	Used For
Boolean	True or False values only
Byte	Single values between 0 and 255
Currency	Numbers with 4 decimal places, ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date	Store dates in the range 1 January 100 to 31 December 9999, and times from 0:00:00 to 23:59:59
Double	Floating point numbers in the range -1.7976931348623110 ³⁰⁸ to -4.9406564584124710 ^-324 for negative numbers and 4.9406564584124710 ^-324 to 1.7976931348623210 ³⁰⁸ for positive numbers
Hyperlink	Text that identifies a hyperlink address
Integer	Whole numbers in the range -32,768 to 32,767
Long	Whole numbers in the range -2,147,483,648 to 2,147,483,647
Object	Any type of Object
Single	Floating point numbers in the range -3.40282310 ³⁸ to -1.40129810 ^-45 for negative numbers and 1.40129810 ^-45 to 3.40282310 ³⁸ for positive numbers
String	Character data with up to 2 billion (2 ³¹ ) characters for a variable length string, or 64,000 (2 ¹⁶ ) for fixed length strings
Variant	Different types of data (we'll go into detail later)

You can also define your own complex types of data; this will be covered later in the book, when we look at advanced programming topics.

Important

It is extremely important to declare your variables as the correct type, otherwise unpredictable results can occur, both during the writing and running of your code.

Boolean

Booleans are used to stored values that can only be one of two values - True or False . They allow us to store values that can be used to make decisions with.

When numbers are converted to Boolean , becomes False and any other number becomes True . When converting from Boolean to a number, False becomes and True becomes -1 .

Byte

The Byte data type is probably one of the least used. Not because it's not useful, but because the type of data it holds is less used than other types of data. A Byte can hold a single value between and 255 . By itself this isn't great, but in a large, multidimensional array it can be used to hold binary data, such as the bitmap for an image. Don't worry. This is not something you are typically going to do in an Access application.

You can assign a value to a Byte like so:

   bytChar = 243

Currency

The Currency type is specifically designed for dealing with numerical currency information, such as prices. It is often more accurate than Single or Double values because it doesn't suffer from rounding errors. This is due to the way the data is stored internally by VBA. A currency value can be assigned like this:

   ccyPrice = 19.99

Double and Single

Double and Single values are for floating point numbers (rather than a fixed number of decimal positions ) which aren't currency based. They have a greater range than Currency but can suffer from rounding problems if a particularly large number of decimal places is used. The reason we have both Single and Double is that they take up different amounts of memory - Single being the smaller. Therefore, if you know your number will not exceed the range of a Single , use a Single because it will take up less memory. However, you should always consider the largest possible value that the variable could store, because assigning a value that is outside the range of a variable will cause an error.

Singles or Doubles can be assigned like so:

   sngNumber = 123.456     dblNumber = 789.012

Integer and Long

Integer and Long are used for dealing with whole numbers. Like Single and Double they take up different amounts of memory, and Integer is the smaller. This of course means that the Integer has a smaller range, so remember not to assign a very large number to it, otherwise an error will be generated.

Integers and Longs can be assigned like this:

   intNumber = 123     lngLongNumber = 123456

String

Strings are used to hold any form of character data, such as names, descriptions, and so on. When assigning Strings you must use double quotes, like this:

   strName = "Janine Lloyd"     strDescription = "Jan's favorite ice cream is Strawberry Cheesecake"

Object

The Object type is used to hold objects for which we don't know the exact object type. Usually we know what type of object we are dealing with ( Recordset , Form , a Word Document , and so on), but there are times when the type of object is decided after the code has been written, (such as when the program is running, or if the user decides upon the object type). You'll see examples of this later in the book.

You use a Set statement to assign a value to an object:

   Set objWordDocument = Word.ActiveDocument

Hyperlink

A Hyperlink allows us to store details about Internet hyperlinks , such as web addresses. The Hyperlink variable contains several pieces of information, and we discuss this more in Chapter 19, Optimizing Your Application when we look at developing features for the Internet.

Variant

Since a Variant can hold different types of data, in fact any type shown in the previous table, we'll examine it in more detail. If you read any of the documentation you might see that the Variant is the default variable type, which means that if you leave off the As Type clause from a Dim statement, you'll get a Variant . For example, both statements below declare the variable as a Variant :

   Dim varAnyThing     Dim varAnyThing As Variant

However, it's a good idea to explicitly declare variants, as it makes your code easier to maintain if you can easily see what your variables are.

Since a Variant can hold any type of data, you can do things like this:

   Dim varAnyThing As Variant     varAnyThing = 31     varAnyThing = "Any old thing"

This first assigns the variable an integer value of 31 , and then a string. VBA doesn't care what type of data it is.

So far this isn't any advantage over an Integer or a String , but what about the following:

   Dim varAnyThing As Variant     varAnyThing = 31     varAnyThing = varAnyThing & " is half of 62"

This firstly stores an integer in the Variant . We then append a string onto the end of the number, which doesn't really make any sense. But, since a Variant can hold different types of data, VBA realizes what is happening and treats the first number as though it was a string. The result of this is that we end up with a string containing:

 31 is half of 62

Let's look at a converse example:

   Dim varAnyThing As Variant     varAnyThing = "31"     varAnyThing = varAnyThing + 10

This time we start with a string (note the quotation marks), and add a number onto it. VBA recognizes that the string contains a number and converts it into a number, and adds 10, giving us a result of 41.

Where this can get confusing is under these circumstances:

   Dim varAnyThing As Variant     varAnyThing = "31"     varAnyThing = varAnyThing + "10"

Does this convert both strings to numbers? Well, no, it doesn't. You've seen the plus sign being used for addition, but it can also be used to concatenate strings together, so the answer you get here is a string containing 3110 . Remember that a number inside a string is treated as a string. This behavior is by design. If you need to treat a value as a number, either use conversion functions or declare the variable as a specific numeric type. It is inadvisable to use a Variant to combine data in this way.

This illustrates an important point: VBA will convert variants into the appropriate type when they are used in expressions.

If you use the + operator on variants, remember the following:

If both values are strings, but contain numbers, they are treated as strings, and joined together.
If both values are numbers, then they are treated as numbers.
If one of the values is a number and the other a string, VBA attempts to convert the string into a number. If successful, the two values are added together. If unsuccessful , an error occurs.

This latter case can be demonstrated with the following code:

   Dim varAnyThing As Variant     varAnyThing = 31     varAnyThing = varAnyThing + "thirty four"

This gives a Type Mismatch error, indicating that we are trying to do addition with incompatible types.

Determining the Type of a Variant

You've seen that Variant s can hold different types of data, but what do you do if you need to find out the exact type of data held in a variant? VBA provides a function called VarType() for this purpose, which returns a value to indicate the exact data type. The table below shows these values:

Value	Variant type
	Empty (unitialized)
1	Null (no valid data)
2	Integer
3	Long Integer
4	Single
5	Double
6	Currency
7	Date
8	String
9	Object
10	Error value
11	Boolean
12	Variant (only used with arrays of variants)
13	Data access object
14	Decimal value
17	Byte
36	User Defined Type
8192	Array

There are a few things here that you haven't seen yet. The Empty and Null values are described a little later in this chapter. The Decimal value doesn't exist as a type on its own, and can only exist as sub-type of a variant. We won't be covering it here. The User Defined Type will be covered later in the book.

Try It Out-Examining a Variant

Open up IceCream03.mdb , and select Modules .
Add a new module by selecting New from the toolbar; when saving the module call it Chapter 3 Code :
In the Visual Basic Editor, create a new subroutine called VariantExample :

Add the following code:

 Sub VariantExample()   Dim varAnyThing As Variant     varAnyThing = 12.345     Debug.Print VarType(varAnyThing)     varAnyThing = 12.345     varAnyThing = varAnyThing & " is a number"     Debug.Print VarType(varAnyThing)     varAnyThing = 12.345     varAnyThing = varAnyThing + "10"     Debug.Print VarType(varAnyThing)     varAnyThing = 12345     Debug.Print VarType(varAnyThing)     varAnyThing = 123456     Debug.Print VarType(varAnyThing)   End Sub

Switch to the Immediate window (remember Ctrl-G is a shortcut for this) and type VariantExample , followed by the Enter key.

How It Works

You can see that five values have been printed out, and that two are the same. Let's see what value VarType was looking at:

Value	Is a	So VarType returns
12.345	Double	5
12.345 is a number	String	8
12.345 + "10"	Double	5
12345	Integer	2
123456	Long	3

Notice that the two numbers with decimal points are assigned to a Double . This is because VBA plays it safe and picks the largest numeric type, rather than a Single . Also notice that when dealing with 123456 a Long is automatically picked, as VBA realizes that this is too big a number to fit into an Integer .

The Empty Value

When numeric variables are declared they have an initial value of , and strings are initially a zero length string (that is, ""). But when a variant data type is first declared it is empty , because VBA doesn't know what type of data it is going to hold, so an initial value cannot be assigned. The empty value is special in that it is not the same as , "", or the null value, which we'll look at a little later.

A variant has the empty value before it is assigned. When used in expressions, a variant that is empty is treated as 0 or "", depending upon the expression. For example:

   Dim varAnyThing As Variant     varAnyThing = varAnyThing + 1.23

This leaves varAnyThing as 1.23 , since it was initially treated as 0.

Assigning any value to a variant overwrites the empty value. You can use the IsEmpty() function to determine whether a variant has been assigned or not.

   If IsEmpty(varAnyThing) Then

The Null Value

The null value is another value special to the variant. In fact, a variant is the only data type that can hold this value - if you assign null to any other type of value an error will occur. You can use the IsNull() function to test for the null value:

   If IsNull(varAnyThing) Then

The null value behaves differently from the empty value, as it is said to propagate through expressions. That means that if you use a null value in an expression, the whole expression will become null. For example:

   Dim varAnyThing As Variant     varAnyThing = Null     varAnyThing = varAnyThing + 1.23

Unlike the empty value, this doesn't result in 1.23 , but Null .

Try It Out-How Null Propagates

Insert a new procedure called VariantStringTest into your existing module - it doesn't matter if it is a subroutine or a function.

Add the following code:

   Dim varFirstName As Variant     Dim varLastName As Variant     Dim varFullName As Variant     varFirstName = "Janine"     varLastName = Null     varFullName = varFirstName & varLastName     Debug.Print varFullName

Run the procedure by typing VariantStringTest into the Immediate window. Notice that only the first name is printed.
Now change the & into a + and run the procedure again.

How It Works

When you use &, and one of the expressions is null , it is treated as a zero-length string. So initially we only see the first name. If both parts of the name had been null an empty string would have been returned. Using the + operator, however, a null value is returned. This is because nulls propagate through expressions when the + operator is used, meaning that if any part of the expression is null , then the whole expression is null.

This shows one very important point:

Important

Do not use the + operator to concatenate strings.

When to Use Variants

There are some good reasons for using variants:

They make coding very easy, since you don't have to worry about data types.
They are the only data type that can hold a null value. Databases often use null values to show that data hasn't been assigned, so you often have to use a Variant if you are unsure about the source of the data.
They have to be used when dealing with Optional arguments to procedures if you want to use the IsMissing() function.

But, remember that there are also some very good reasons for not using them:

Variants slow your application because every time a variant is accessed, VBA must determine the type of data that is being stored.
They can encourage bad programming. Assigning the correct data type to a variable allows VBA to automatically prevent certain errors from occurring.

In general you should always use the explicit data types, rather than variants.

Date

The Date type is one that requires a little explanation, since it can often be confusing when using it in VBA. The first thing to remember is that when assigning a date variable to a specific date you need to enclose the date within # signs, to tell VBA that the value is a date (and not a numerical division):

   datToday = #12/31/02#

You can type in dates in almost every recognizable format, and VBA will convert them into an internal value that it uses to for all dates and times. This is partly to stop errors in code, but also to get around the year 2000 problem. Years from 00 to 29 are treated as 2000 to 2029, and 30 to 99 are treated as 1930 to 1999. When a date is displayed, Access uses a format based on the current Windows Regional settings. This enables users in different regions to view such values in a format appropriate for their country.

Date arithmetic is something that is quite common. We often want to find out the differences between two dates, or how many days ahead a certain date is. The great thing about dates is that you can use + and - just as you can with normal numbers. For example:

   Dim datToday As Date     Dim datNextWeek As Date     Dim datLastWeek As Date     Dim datePaymentDate As Date     datToday = Date()     datNextWeek = datToday + 7     datLastWeek = datToday - 7     datPaymentDate = datToday + 30

Date() is a function that returns the current date. You can then just add or subtract days from a date variable and VBA works it all out for you, including the running over of months and years if necessary. If you want to work with values other than days, such as weeks or months, then there are some other functions you can use:

DateAdd() allows us to add dates together.
DateDiff() allows us to work out the difference between two dates.
DatePart() allows us to extract a specific part of a date.

Let's have a look and see how these can be used, and then we'll explain more about them.

Try It Out-Dates

Create a new procedure called DateTest in the module you're using for this chapter.

Add the following code:

 Sub DateTest()   Dim datDec As Date     Dim datMay As Date     datMay = #5/1/2002#     datDec = #12/1/2002#     Debug.Print DatePart("m", datDec)     Debug.Print DateDiff("m", datMay, datDec)     Debug.Print "August is " & DateAdd("m", 3, datMay)   End Sub

Now switch to the Immediate window, and type in DateTest followed by the Enter key to run the procedure.

How It Works

Let's take this line by line, and look at each of these new functions in turn .

With the first statement we used DatePart to find out a specific part of the date:

   Debug.Print DatePart("m", datDec)

DatePart takes four arguments, two of which are optional. (We'll look at the optional arguments when we discuss the Format function):

 DatePart(Interval, Date [, FirstDayOfWeek [, FirstWeekOfYear]])

Interval identifies which part of the date to return. We used " m " which stands for month and it returned 12, for December. We could have used " yyyy " for the year, " d " for the day, or one of the others. We won't go into the other intervals here, but you can always look them up in the help file if you want.

With the second statement we used DateDiff to find the difference between dates:

   Debug.Print DateDiff("m", datMay, datDec)

DateDiff takes five arguments, of which the latter two are again optional:

 DateDiff(Interval, Date1, Date2 [, FirstDayOfWeek] [, FirstWeekOfYear])

The interval has the same settings as for DatePart , and we used " m " to return the difference in months. Date1 and Date2 are the two dates we want to find the difference between. In our case we wanted to find the number of months' difference between May and December.

For the third line we used DateAdd to add a number to a date:

   Debug.Print "August is " & DateAdd("m", 3, datMay)

DateAdd takes three arguments

 DateAdd(Interval, Number, Date)

Interval has the same settings as before. Number is the number of Intervals to be added to Date . We added 3 months to May, to get a date in August. You can use negative numbers to subtract numbers from a date. The date printed appears in the format as set in the Regional Settings in the Control Panel.

Times

The Date type also stores times, either on their own, or as part of a date. VBA will accept a date and/or time in a variety of formats. Time values are separated by colons and date parts are separated by forward slashes , hyphens, spaces, and commas. You can assign a time to a date variable like so:

   Dim datTime As Date     datTime = #3:20:15#

which sets it to twenty past three and fifteen seconds in the morning. As you type this in it will be converted by VBA to:

   datTime = #3:20:15 AM#

If you type in a 24-hour date (for example, 15:35) it is converted to 12- hour format and PM is added. Again this is only the internal storage, and not how times are shown on forms.

To assign both a date and a time you use this format:

   datTime = #6/30/2002 3:20:15#

Other Useful Date and Time Fuctions

There are a few useful date and time functions that you haven't seen already:

Time() returns the current time
Date() returns the current date
Now() returns the current date and time

You can use these quite effectively in your code to work out the difference between the current date and a user-supplied date, or for calculating a date a number of days in advance. For example, let's assume you are printing an invoice and want to give the payee 60 days; you could use this formula:

   Dim datPayBy as Date     datPayBy = Date() + 60     Debug.Print "Payment is due by " & datPayBy

Try It Out-Using DatePart in a Query

You can see that although these date functions are quite simple, they give you quite a lot of power. For example, imagine you have sales data and you need to find out how many sales have taken place on a month-by-month basis. You can do a summary query quite easily, but how do you group by month? Well, using the DatePart() function you could do this easily. Return to the Access Database Window and do the following:

Create a new query based upon the sales data from tblSales . You can do this from the main database window by selecting the Queries , and then double-clicking on Create query in Design view . When the Show Table dialog appears select tblSales , click Add , and then click Close .
Add the DateOrdered and Quantity fields to the query. You can do this by just double-clicking on these.
In the DateOrdered field, change the field so that it looks like this:
```
   SalesMonth: DatePart ("m", [DateOrdered])   
```
Click on the View Total menu so that the Total row appears in the Design view.
In the DateOrdered column add some sorting, click in the Sort field and choose Ascending . Also make sure the Show checkbox is still selected, so that the field appears in the query.
In the Quantity column make this a summary query, by changing the Total field from Group By to Sum . Sum is one of several aggregate functions that return a single value for a group of records; aggregate functions allow us to evaluate a set of records. The query should now look like this:
Now run the query:

So, not only can you use functions in code, you can use them in queries too. If you want to keep this query, don't forget to save it.

Formatting Variables

You've seen from the examples above that when used within VBA, dates and times are stored using a standard format, but when they are printed out they use the local settings of the Windows user. This happens for numbers too. But what happens if you want to print out dates in a different format, or specify a format for a number? The solution to this is to use the formatting functions supplied with VBA.

Formatting Numbers

To format numbers you can use one of the formatting functions:

FormatCurrency() to format a number into a currency style.
FormatNumber() to format a number into a normal number style.
FormatPercent() to format a number into a percent style.

All of these functions have several arguments, most of which are optional. They allow us to change the number of digits after the decimal point, whether or not to include a leading 0 for fractions, whether or not to use parentheses for negative numbers, and whether or not a grouping separator is used. Look at the help file in the VBA IDE for the exact details of these arguments.

You can see the difference between these functions easily. Consider this code:

   Dim dblNumber As Double     dblNumber = 1234.567     Debug.Print dblNumber     Debug.Print FormatCurrency(dblNumber)     Debug.Print FormatNumber(dblNumber)     Debug.Print FormatPercent(dblNumber)

This code gives the following output:

 1234.567 ,234.57 1,234.57 123,456.70%

You can see some interesting things here:

All functions use the system's Regional Options to determine the grouping of numbers and the number of decimal places.
FormatCurrency() adds the local currency sign to the front of the number.
FormatNumber() adds no special formatting apart from grouping and rounding.
FormatPercent() multiplies the number by 100 and adds a percent sign to the end of the number. (Although this looks odd, it makes sense - 0.5 is 50%).

Formatting Dates

To format dates you can use the FormatDateTime function, which takes two arguments. This first is the date to be formatted, and the second ( optionally ) is the format. Have a look at the code below:

   Dim datDateTime As Date     datDateTime = #11/30/2002 10:54:17#     Debug.Print datDateTime     Debug.Print FormatDateTime(datDateTime, vbGeneralDate)     Debug.Print FormatDateTime(datDateTime, vbLongDate)     Debug.Print FormatDateTime(datDateTime, vbLongTime)     Debug.Print FormatDateTime(datDateTime, vbShortDate)     Debug.Print FormatDateTime(datDateTime, vbShortTime)

The values used for the second argument are constants, predefined by VBA. We'll be looking at constants later in the chapter.

This code gives the following output:

 11/30/2002 10:54:17 AM 11/30/2002 10:54:17 AM Saturday, November 30, 2002 10:54:17 AM 11/30/9 10:54

This shows that the general format is used by default when printing dates.

Custom Formats

The functions described above for formatting numbers and dates are all very well, but what if you want to specify the format yourself. For this you can use the Format function, which has the following syntax:

 Format (Expression [, Format [, FirstDayOfWeek [, FirstWeekOfYear]]])

As you can see three of the arguments are optional. Let's look at them in turn.

Expression is the item of data to be formatted.
Format is the format we want the data to appear in. There are some predefined formats that allow quick formatting, but you can specify your own format too.
FirstDayOfWeek allows us to specify which is the first day of the week. By default this is Sunday, but you could set this to Monday if you prefer to start your week on a Monday, as some countries do. This is quite important when converting dates into week numbers, as the day the week starts on could affect which week a date appears in. There are intrinsic constants for all days of the week - see the help file for a list.
FirstWeekOfYear allows us to specify which week is defined as the first of the year. By default this is the week in which January 1 ^st occurs, but it could be the first week with at least four full days, or the first full week. This is important when dealing with week numbers, especially as it allows you to set the starting week of a fiscal year.

We are only going to concern ourselves with the first two arguments. In fact, we're really going to look at the second argument - the format.

For dates you have a number of options for the format. You may either specify a named format string to the Format function or an intrinsic constant to the FormatDateTime function. Intrinsic constants will be explained in greater detail later on.

Short Date , which is the same as FormatDateTime with vbShortDate
Short Time , which is the same as FormatDateTime with vbShortTime
Long Date , which is the same as FormatDateTime with vbLongDate
Long Time , which is the same as FormatDateTime with vbLongTime
A custom format

For the custom format you can use, among others, a combination of:

d or dd for the day number
ddd for the shortened day name, for example Mon
dddd for the full day name
m or mm for the month number
mmm for the shortened month name, for example Mar
mmmm for the full month name
yy for the two-digit year
yyyy for the four-digit year
h or hh for the hour
m or mm for the minute
s or ss for the seconds
AMPM for an AM/PM indicator
Any other text, which is printed out verbatim.

For example, with datDateTime set to 11/30/1998 10:54:17 AM:

Format	Results in
Format(datDateTime, "Short Date")	11/30/02
Format(datDateTime, "Long Date")	Saturday, November 30, 2002
Format(datDateTime, "dd mmm yy")	30 Nov 2002
Format(datDateTime, "yyyy-mm-dd")	2002-11-30
Format(datDateTime, "mm dddd hh:mm")	11 Saturday 10:54
Format(datDateTime, "yymmddhhnnss")	021130105400

You can see that although m and mm appear to be shared between months and minutes, their use is often worked out depending upon the context. To be more explicit, n may be used to specify minutes.

Formatting numbers follows similar lines, where you can use:

Currency (giving the same as FormatCurrency )
Percent (giving the same as FormatPercent )
A custom format

For a custom format you can use a combination of:

to display a digit or a zero. This allows you to pad the string with zeros.
# to display a digit. Nothing is displayed if no number is present.
. for a decimal placeholder. You always use a decimal point here and not the placeholder specified in the regional settings.
% for a percentage placeholder. Like FormatPercent the number is multiplied by 100.
, for a thousands separator. You always use a comma here and not the separator in the regional settings.

For example, with dblNumber set to 12345.678:

Format	Results in
Format(dblNumber "0.00")	12345.68
Format(dblNumber "000000.00")	012345.68
Format(dblNumber "#####0")	123456
Format(dblNumber "###,##0.00")	12,345.68
Format(dblNumber "Currency")	$12,345.68

You can see that you can put any number of these placeholders in the format string to achieve exactly what you want. The latter example depends upon the regional settings in Windows and will display currency values for the current locale.