In this example the workbook is called "VBA Sample.xlsx" and can be seen at the bottom of the tree. It has one worksheet called Sheet1. There are two code modules, one for the workbook and one for the worksheet. If you double-click on Sheet1, you will see the corresponding code window on the right side, as shown.You will notice that there is already one line of code in the window, even though this is a new workbook that was just created.
This line
Option Explicit
means that explicit declarations are required for all variables. VBA puts that line there automatically because I have selected the option, which applies to all VBA modules in all files. I highly recommend that you include this. From the menu select Toos, Options, and check the option as shown.
Why would you want to force VBA to require declarations when VBA works perfectly well without them?
Humans type code, and humans make mistakes. A very common mistake is to mistype a variable name in one place:
Private Sub Test()
Set MyCell = Range("A1")
If MyCll = "" Then
MsgBox "Your cell is blank!"
End If
End Sub
This code will always display the message box. MyCll is a typo. Because it is undeclared, VBA assumes it has type Variant. Because it was never given a value, when compared to a string it will be interpreted as a null string, so the comparison show here will be True.
If we had required all variables to be declared, and had written this code
Option Explicit
Dim MyCell as Range
Set MyCell = Range("A1")
If MyCll = "" Then
MsgBox "Your cell is blank!"
End If
the code would generate a compilation error due to the undeclared variable, and we would have found our error immediately, before even running the code.