This post will help you to get basic understanding of several VBA concepts.

You could write almost all things as a VBA code with Recorder function (“Developer” tab). After that you just need to make your code more universal and adaptable. Code snippets described below will help you with that.

1. VBA IS AN OBJECT-ORIENTED PROGRAMMING LANGUAGE

You need to set an object (cell, cell parameter, sheet, row, etc.) and what do you want to do with this object.
Code “Range(“A6″).Select” will select A6 cell.
Code “currentCell.HorizontalAlignment = xlRight” will change cell alignment.
You could use Recorder to get code for all objects and relevant commands. Further we will discuss tools, which could not be recorded that way.

2. CODE FORMATTING

If you do not record code, you should insert it in modules and in subs:

Sub Macro1()
   <commands>
End Sub

Structure well – always indent

As a general rule, whenever the line depends on the function above it should be indented, for example:

IF variable = TRUE THEN
   'Makes a message box pop up
   msgbox "You are correct"
END IF

Use ‘ to add comments in your code – comments go Green in VBA.

3. CONDITIONAL STATEMENTS

Conditional statements allow you to change the way your program executes based on logical values. Standard Excel function “IF” is an example of conditional statements. In VBA you have to options:

a) If, ElseIf, Else (statement)
“If” statement starts a series.
“ElseIf statement continues the series.
“Else” statement is the catchall conditional statement.
“End If” statement is the way you end a set.

b) Select Case (statement)
“Select Case” starts a series. It also notes which variable is being used for the conditional statement.
Case” statements are like “If” or “ElseIf” statements. It allows you to compare a value versus a set of outcomes.

It is better to use CASE option when you interested in one object and have 3 or more variants for it.

4. VARIABLES

You often have to store values when you perform calculations with VBA. For example, you might want to calculate several values, compare them, and perform different operations on them, depending on the result of the comparison. You have to retain the values if you want to compare them.

Example of how to define a variable: “Dim myCell as Range”
“Dim” is the special call to tell the program to make a variable. Variable Name: myCell. “as” is the special separator between variable name and type. Variable Type: Range.

Best Practice: Use “Option Explicit” on top of any coding modules. It will require you to declare your variable types and safe you from potential mistakes in code. Go Tools -> Option – >Require Variable Declaration in VBA Editor.

Storing or using information in a variable:
To store or use information to a variable, think of high school algebra
X = 12 or Y = m*X + B(where m, X, B are number variables)

Same applies to more complex variables (e.g. text variables)
strMyName = “John Smith”

Objects are a special set of variables.
For object variables, the special word “Set” must be used to tell the program that you are referencing and storing a complex object. Example:
Set myRange = Sheet1.Range(“A1:A5”)
Range is a section of cells within Excel (think “Named Ranges”)

5. LOOPS

Loops enable users to execute repeatable actions
There are multiple types of loops and each is useful for different purposes

Loop Types:
“For” loops: “For Next”, “For Each”
“Do” loops: “Do While”, “Do Until”

“For Each” loops are good if you were using complex data formats. For example, arrays go well with “For Each” loops.

Given the programming problem and your coding style, you will choose which loop is best for you. This may not be the most efficient type of loop but if its easier for you or a teammate to understand, the microseconds of extra processing will not matter too much.

a) Looping a set number of times

For <variable> = <StartingNumber> to <EndingNumber>
   <commands>
Next <variable>

b) Looping Until a condition is True

Do Until <variable> < 100
   <commands>
Loop

c) Looping While a condition is True

Do While <variable> >= 100
   <commands>
Loop

The loop below allows to check every cell in a selected range and perform actions with them:

Dim selectedRange As String
Dim currentCell As Range

selectedRange = ActiveWindow.RangeSelection.Address

For Each currentCell In Range(selectedRange).Cells
   <commands>
Next currentCell

6. BOXES

Boxes allow you to inform users of your macros or get information from them. Basic types of boxes:

a) Message box
The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. Example:

MsgBox "VBA is fun"

b) Input box
You can use the InputBox function in Excel VBA to prompt the user to enter a value. Example:

Dim myValue As Variant
myValue = InputBox("Give me some input")

Input box for ranges example:

Set UserRange = Application.InputBox("Please Select Range", Type:=8)

c) Open file box is useful when you need data from a different file. Example:

Dim ReportName As String
ReportName = Application.GetOpenFilename()
Workbooks.Open (ReportName)

d) Retrieve target folder path
When you need to go through each file in a particular folder, you could use this code:

Sub LoopAllFiles()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

myExtension = "*.xls*"
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
   .Title = "Select A Target Folder"
   .AllowMultiSelect = False
   .Show
   myPath = .SelectedItems(1) & "\"
End With

myFile = Dir(myPath & myExtension)


Do While myFile <> ""
   Set wb = Workbooks.Open(Filename:=myPath & myFile)
   DoEvents
   <commands>
   wb.Close SaveChanges:=False
   myFile = Dir
Loop

End Sub

You could also turn off all message boxes (including Save box) with a following command:

Application.DisplayAlerts = False

7. ERORR HANDLERS

On Error Resume Next – instructs to essentially ignore the error and resume execution on the next line of code.
On Error Goto <label> – tells VBA to transfer execution to the line following the specified line label. None of the code between the error and the label is executed, including any loop control statements.

TIP: When you are testing your code, convert these lines in comments.

8. SPEED UP YOUR MACROS

Deactivate the automatic recalculation:

Application.Calculation = xlCalculationManual

Deactivate the automatic updating of the screen:

Application.ScreenUpdating = False

Deactivate the automatic updating of the status bar:

Application.DisplayStatusBar = False

Deactivate the automatic events:

Application.EnableEvents = False

Deactivate the automatic recalculation of the page breaks:

Activesheet.DisplayPageBreaks = False

But don’t forget to set these parameters to TRUE at the end of the code.

9. ASSIGN HOTKEYS

You could assign Hotkeys for your macros with a following command:

Application.OnKey " *Keys* ", " *NameMacro* "

Examples:
Application.OnKey “^%{\}”, “mlnDivide” – ctrl + alt + \ will start macro mlnDivide
Application.OnKey “{F1}”, “”  – Help will not be activated

This code should be written in ThisWorkbook object and for Open event.

Categories
big4macros
Tags
, ,