Excel Macro Terminology

Excel Macro Terminology: An Introduction of Excel Macros / VBA Series 1.2. Click here to learn About Excel Macros

Where does the Macro code go or reside? Macro codes are written in word processing documents called Modules.

There are two types of code modules: 1) Standard modules and 2) Class modules.

Most probably you will be writing VBA codes in Standard Modules only. 

Class Modules are only required when you want to create your own custom Excel Objects.

https://www.youtube.com/watch?v=bjuhZ9ML5k0

Where do Modules reside? Modules reside in Visual Basic Editor (VBE) window. 

We have already learnt that macros can also be known as procedures. 

A procedure is nothing but a sequence of statements executed as one whole unit.

You can create as many Procedures as you want but you can’t nest one
procedure into another.

There are two types of Procedures:

1) Sub procedures 2) Function procedures. (UDF-User defined functions)

Another important concept in VBA is Events. Events are actions that happens in response to another action. For Example: A mouse click can insert a Worksheet.

VBA Procedures can contain components such as statements, contain keywords, operators, variables, constants, and expressions.

A statement in Visual Basic is a complete instruction.

There are 3 kinds of statements in Excel VBA:

1) Declaration Statements
2) Assignment Statements
3) Executable Statements

Declaration statements are used to name and define procedures, variables, arrays, and constants.

Example:

Sub ApplyFormat()
Const limit As Integer = 33
Dim myCell As Range
‘ More statements
End Sub

Assignment statements assign a value or expression to a variable or constant.

Example:

Sub Question()
Dim yourName As String
yourName = InputBox(“What is your name?”)
MsgBox “Your name is ” & yourName
End Sub

An executable statement initiates action. It can execute a method or function, and it can loop or branch through blocks of code. Executable statements often contain mathematical or conditional operators.

Example:

Sub ApplyFormat()
Const limit As Integer = 33
For Each c In Worksheets(“Sheet1”).Range(“MyRange”).Cells
If c.Value > limit Then
With c.Font
.Bold = True
.Italic = True
End With
End If Next c
MsgBox “All done!”
End Sub

KEYWORDS: A word or symbol recognized as part of the Visual Basic programming language; for example, a statement, function name, or operator.

VARIABLES: A Variable is used to store temporary information that is used for execution within the Procedure, Module or Workbook.

CONSTANTS: Constants are a special type of variable that do not change.

Note: VBA has many built-in constants that are referred to as intrinsic constants.

Data Type: The characteristic of a variable that determines what kind of data it can hold.

Data types include Byte, Boolean, Integer, Long, Currency, Decimal, Single, Double, Date, String, Object, Variant (default), and user-defined types, as well as specific types of objects.

ARRAY: An Array is also a variable which serves as a holding container for a group of individual values, called elements, that are of the same data type.

There are 2 kinds of Arrays:

1) Static and
2) Dynamic Arrays

Other Excel Tutorials & Articles: 

List of free excel learning sites