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.Volatile = True
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:
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:
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
||‘Assigns the number 1 to a cell|
||‘Assigns the number 12 to the selection|
||‘States the number of cells selected|
||‘Clears the contents only|
||‘Displays the Active Sheet Name|
||‘Displays Name and Directory Path of Active Workbook|
||‘Enters the value 100 in the “Input” named cell|
||‘Enters 2 into the range A1:B10 (20 cells)|
||‘Enters 3 into the intersect C6|
||‘Puts a 9 into A1 First Row First Column|
||‘Puts a 7 into D3 Third row Fourth Column|
||‘Places a 5 directly below the “Active Cell”|
||‘Selects the entire column AH|
||‘Hides a column based on selection|