The simple statement - Option Explicit - tightens up your VBA codes. What Option Explicit says is this: if a variable is not declared, then you cannot use it. The use of Option Explicit is twofold, to make sure that you declare variables, and to troubleshoot by generating errors to highlight mistakes if variable names are mistyped later in the Code.

A variable is the opposite of a constant. Values of variables change, constants remain at their initial value. Think of variables are placeholders for items that will change during the code. By declaring and using variables in your VBA, you can manipulate their values later. Technically the computer reserves a place in memory for each variable.

Generally, Dim statements appear at the beginning of a procedure. This arrangement isn't required, but you'll find most developers adhere to this guideline. By grouping them at the beginning, you can find them much more easily.

Variables can contain many types of data:

Some common variant types are:

VBoolean
Logical variables - values are True or False
Integer
These are short (16 bit) integers.
Long
These are long (32 bit) integers.
Currency
Used for quantities which consist of a whole number combined with a fraction with a fixed number of decimal places. As the name implies, they are useful for dealing with monetary values.
Single
These are floating point values, providing around 6 significant figures of accuracy.
Double
These are floating point values, providing around 14 significant figures of accuracy.
Date
A date variable is composed of two floating pointing values, one containing a date and the other containing the time of day.
String
Variable length character strings.
Variant
A variant variable can contain any of the above data types. Variant data variables change their type dynamically according to what is stored in them. Note that variables are assumed to be Variant unless specifically declared to be something else.

By default, VBA does not require that you specify the type of data that each variable is expected to contain. So, if you don't declare your variable types, VBA defines all variables using the Variant data type.

Fine, less work and typing for you. However, this creates several problems:

Performance. Variants require more memory than other common data types, and they can take slightly longer to process.
By using only variants in your code it becomes more difficult to trap errors.
By using variants as default, it is harder to read your code and easily see what each each variable is supposed to contain.

To ensure that you automatically use Option Explicit, go to the Visual Basic Editor (VIBE). Chooseà Toolsà Options. In the Editor tab, make sure that Require Variable Declaration is checked.

After you check this option, each new VBA module that you insert or open will begin with Option Explicit. This tells Excel that all VBA variables must be explicitly declared. That is, with this option checked, you can't just start to use a variable, you've got to declare each variable. The Option Explicit statement can mean the difference between errors and no errors—and all you have to do is turn it on

Note: Enabling the Require Variable Declarations feature for all modules affects only new modules inserted after enabling the feature. You must update any existing modules by adding the Option Explicit statement manually.

The Hungarian convention
The idea is to give extra information about the type of variable. What the Hungarian convention does is to lay down a three letter prefix which instantly tells you what information to expect from the variable. For example, if the variable represents text then it begins with str. Convention dictates that the three letters are lowercase.

int - Integer. Example intWsNum -  from this we can expect a number representing a Worksheet Index number
str - String. Example strUser - a String representing the username
obj - Object. Example objControl - an object that is a Control - a CommandButton, ComboBox etc

There are other less common variables, for instance: err - error, dtm - date, boo - Boolean.

A variation of the Hungarian Convention is to use a one letter prefix s for string, o for object.

Choose meaningful names for your variable, a word or words that describe the purpose of the variable. Best practices suggests the length should be about 8 - 16 characters.

Microsoft seem now to advocate moving away from the Hungarian convention - see this article at MSDN. So i is quit acceptable to use MyNum, MyDate etc

Declared variables are supported by IntelliSense. That means you can choose the variable from the completion drop-down list rather than enter it from the keyboard—thus avoiding typos.

 Excel will amend variables that start as being declared with Uppercase, then subsequently typed with lowercase. This is useful, if you use the convention of using lower case for prefix and then start the name with Uppercase e.g. Dim strMyname as String or Dim MyName as String, hen when entering it into you code se all Lowercase , the typing will be corrected. This helps avoid  "typo" errors

TIP

Generally, Dim statements appear at the beginning of a procedure. This arrangement isn't required, but you'll find most developers adhere to this guideline. By grouping them at the beginning, you can find them much more easily.

You can declare a number of variables in a single line by separating them with commas, as follows:

It's a good idea to place key information about your code - purpose , date of writing etc. in a block above your code. The title block should be immediately above the first line of the macro. Depending on your requirements, your title block could contain either greater or fewer categories of information. You may ant o include Contact details for instance

It is also good practice to add comments to your code, ho knows whn you might need to check what you have done? This also helps others when reviewing your code.

Example code with comments & header:

Option Explicit

'---------------------------------------------------------------------------------------

' Procedure : DynoName

' DateTime : 28/08/2006 11:15

' Author : Roy Cox

' Purpose : To add a Dynamic Named Range

'---------------------------------------------------------------------------------------

'

Sub DynoName()

Dim sNm As String

'pick up errors generated by illegal names

    On Error GoTo Err_Handler

'get the user's choice of name

                 sNm = InputBox("Please Enter Name", "DynamicName", ActiveCell.Value)

'now add the dynamic name

                         ActiveWorkbook.Names.Add Name:=sNm, RefersToR1C1:="=R" & ActiveCell.Row & _

                         "C" & ActiveCell.Column & ":OFFSET(R" & ActiveCell.Row & "C" & _

                         ActiveCell.Column & ",COUNTA(C" & ActiveCell.Column & ")-1,0)"

'eveyhing's OK so quit and avoid error message

       Exit Sub

Err_Handler:

             MsgBox "Please enter a valid name"

End Sub

In VBA, a variable can have one of three scopes.

Local Scope
These variables can be used only within the macro in which they are actually defined. This is the most common type of variable, and it requires no special designator, see Dim sNm above)

You'll find that the term constant has many meanings in VBA. A constant represents a literal value, much in the same way a variable represents a value or an object. The difference is, the value of a constant can't be changed while the code is executing—not even by mistake.

You can declare a number of variables in a single line by separating them with commas, as follows:
Dim variable1 As datatype, variable2 As datatype

Use the Const statement to declare a constant in the form
                 Const constantname As datatype = expression
The use of Public or Private is optional,. So a constant declaration can start with Public, or Private, or just Const. In any case, constantname identifies the constant by name, datatype specifies the constant's data type, and expression is the literal value that the constant equals. The expression argument can't refer to a variable, a result of a function procedure, or include one of VBA's many built-in functions. for example;
Const sAppName as String = "MyApp"
Microsoft Excel Constants

In addition to letting you define your own constants, VBA offers a number of predefined constants, known as intrinsic constants, that you can use to specify specific data types (and other values).

There are two advantages to using these constants. First, you don't have to define them yourself. Second, they improve the readability of your code. The intrinsic constant's name describes its value or purpose, thus making it instantly recognizable. To assign one of these subtypes to a variable, use that subtype's corresponding intrinsic constant as follows:

Cells(1,1).Interior.ColorIndex=vbRed

Tip: Type "Microsoft Excel Constants"  into the Excel VBA help files to get a full list.