Common Code Used in Projects

The following are some tips and tricks that are used when working with most VBA projects.

Use Option Explicit

It is recommended that you use the “Option Explicit” declaration at the beginning of each excel code module.  The use forces you to declare all variables and then checks to be sure that the variables conform to how you define them.  For instance:

Option Explicit

Sub variables()
  Dim tVar as String

  tVar = "Dog"

  Range("A1").value = tVar
  Range("B1").value = ttVar
End Sub

The result when this macro is run would place Dog in cell A1 and and when it got to the row placing a value in cell B1 it would throw an error “Compile Error: Variable not defined”.
To fix the problem you could change the code to the following:

Option Explicit

Sub variables()
  Dim tVar as String
  ttVar    as String

  tVar = "Dog"
 

  Range("A1").value = tVar
  Range("B1").value = ttVar
End Sub

Another reason to use defined variables is that you can determine how much of the computer’s resources are going to be used by your project. Each defined variable type sets aside a certain amount of memory for it’s use. If you don’t declare and define the variable it will use the max amount of memory thus making your project less efficient.

To have the Option Explicit line on all new modules, go to the settings in the VB Editor and check the box “Require Variable Declaration”.  The following are the different types of variables you can use as well of the memory size that is set aside.

Data Type Size in Memory Description Range of Values
Byte 1 byte Represents an unsigned (non-negative) number
often used for binary data
0 to 255
Boolean 2 bytes A simple True or False value True or False
Integer 2 bytes Integer (no decimals) -32,768 to +32,767
Long 4 bytes Long Integer (no decimals) -2,147,483,648 to 2147483647
Single 4 bytes Single Precision Floating Point Number -3.4e38 to +3.4e38
Double 8 bytes Double Precision Floating Point Number -1.8e308 to +1.8e308
Currency 8 bytes A Floating Point Number with a fixed number of decimal places -922,337,203,685,477.5808 to 922,337,203,685,477
Date 8 bytes Date & Time – The Date type is represented internally by a floating point number.
The integer part of the number represents the date,
and the decimal portion represents the time.
1st January 100 to 31st December 9999
Object 4 bytes A reference to an object Any Object Reference
String varies Holds a series of characters. The String type can be defined
to have a fixed or a variable length, although it is most commonly
defined to have a variable length
Fixed – Up to 65,500
Variable up to about 2 Billion Characters
Variant varies Can hold Dates, Floating Point Numbers or Strings of Characters,
and should therefore be used when you are not sure what type of data to expect.
Number – same as Double Type
String – same as String Type

 

Application Functions

Application.Volatile = True

Application.EnableEvents = False

Application.Interactive = False ‘prevents user interference with the macro
Application.DisplayAlerts = False ‘suppress prompts and alerts
Application.ScreenUpdating = False ‘suppress screen repainting


With…End With Statement

Executes a series of statements that repeatedly refer to a single object or structure so that the statements can use a simplified syntax when accessing members of the object or structure.

You can execute a series of statements that refer to a single object to simplify the syntax of those referrals.  The structure looks like this:

With object Expression
   ' [ statements ]
End With

If you take he above “Application” statements and combine them together into a With – End With structure it would look like this:


Application Functions

Option Explicit
Sub Macro1()
    With Application
        .Calculation = xlCalculationManual 'Turns on manual calculation
        .ScreenUpdating = False 'Stops user interference
        .EnableEvents = False 'Turns off spreadsheet events
        .DisplayAlerts = False 'Turns off prompts and alerts
    End With

[Enter your code here]

' Resets all settings to what they were before the macro ran
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub

Simple 1-line copy method

When you record a macro and simply copy one cell to another, it takes four steps to make it happen.  To make the code easier to read you can turn four rows of code into one.

Here’s the recorded process:

Range("B2").Select
Selection.Copy
Range("C2").Select
ActiveSheet.Paste

The following does the same thing but more gracefully:

Range("B2").Copy Range("C2")

This does the copying and pasting without selecting the cells.  The first part is the source followed by a space then the destination is stated (don’t add the “paste” at the end – it won’t work).  This is working on the same worksheet, but you can copy between different worksheets or workbooks easily enough.


Cell and Range functions

ActiveCell.Value = 1 ‘Assigns the number 1 to a cell
Selection.Value = 12 ‘Assigns the number 12 to the selection
MsgBox ActiveWindow.RangeSelection.Count ‘States the number of cells selected
ActiveCell.ClearContents ‘Clears the contents only
MsgBox ActiveSheet.Name ‘Displays the Active Sheet Name
MsgBox ActiveWorkbook.FullName ‘Displays Name and Directory Path of Active Workbook
Worksheets("Sheet1").Range("Input").Value = 100 ‘Enters the value 100 in the “Input” named cell
ActiveSheet.Range("A1:B10").Value = 2 ‘Enters 2 into the range A1:B10 (20 cells)
Range("C1:C10 A6:E6") = 3 ‘Enters 3 into the intersect C6
Worksheets("Sheet1").Cells(1, 1) = 9 ‘Puts a 9 into A1 First Row First Column
ActiveSheet.Cells(3, 4) = 7 ‘Puts a 7 into D3 Third row Fourth Column
ActiveCell.Cells(2, 1) = 5 ‘Places a 5 directly below the “Active Cell”
Columns("AH:AH").Select ‘Selects the entire column AH
Selection.EntireColumn.Hidden = True ‘Hides a column based on selection