What is a macro?

It can be defined as an executable block of instructions that is stored.

in a module. When running a macro, the lines of code are interpreted that
it contains and triggers the written actions.

A macro can be used to speed up frequent tasks with Excel and

expand its utility by creating, for example, new functions to solve
calculations that would otherwise be impossible to perform.
There are three ways to create a macro:

With the macro recorder.

With the VBA editor.
With a combination of the above.

The fastest way to create a macro is to use the recorder, as it does not
It is necessary to know VBA.
What the macro recorder does is save every single action.
what you are doing. These actions are recorded step by step until it stops the
recording. As you perform actions, the recorder transforms them into
VBA language.
The use of the macro recorder is very simple for creating easy macros, but
when you want to create something more complex, it is necessary to use the language of
programming.

Object-oriented programming
An Excel book is filled with objects that are organized by a
hierarchy. Object-oriented programming is based on the modification of the
properties and methods of these objects to perform the actions that
you want.
In the real world, an object is something tangible, such as a car or a cat,
this object is identified because it has properties and methods. It is considered that
properties are the characteristics and methods are the actions that can be
to perform. In a car, a property would be the color and a method would be the
possibility of turning the steering wheel to the left.

Excel has more than 200 objects. Each element of Excel is an object. A
book (Workbook), a sheet (Worksheet), a chart (Chart) or a pivot table
(PivotTable) are some examples of objects within the Excel model.
Many of the objects are grouped into collections. In the case of the car object,
a collection would represent all the cars in the world. Regarding Excel,
The Workbooks collection refers to all the open Excel workbooks.
The objects of the Excel model are organized by hierarchies, which
it means that some objects can contain other objects.
The most descriptive example for you to understand this concept is the following:

When you open the Excel program, you are actually opening the
objectApplication and at the same time you are opening a blank book
represented by the Workbook object. This book contains an active sheet,
represented by the Worksheet object, which in turn contains a set of
cells, represented by the object Range.
Thanks to this organization, you can refer to any of the objects.
following the hierarchical position it occupies within the object model.

For example, to refer to the book 'Annual Sales.xlsx' one would have to
do it the following way:

Application.Workbooks ("Annual Sales.xlsx")

If you wanted to select cell B2 from the 'Totals' sheet of the 'Sales' workbook
annual.xlsx", you would have to do it following the entire hierarchy:

Application.Workbooks("Annual Sales.xlsx"). _
Select Range "B2" in Worksheets("Totals")

The previous instruction can be simplified in most cases by omitting

the Application object. This is possible because the instruction is created within
Excel (Application).
Workbooks("Annual Sales.xlsx").Worksheets("Totals").Range("B2").Select
If the highest-ranked object is the active object, you can omit it from the reference.
If you have the workbook "Annual Sales.xlsx" open, you could select cell B2 of
the following way:

Select Range "B2" on Worksheets("Totals")

Going a little further down the hierarchy, if the active sheet were 'Totals', you could
select cell B2 like this:

Select Range('B2')

All objects of the same type form a collection. Collections

they allow working with a group of objects as if it were a single object.
Normally, the name of a collection is the plural form of the objects.
what it contains.
For example, Worksheets is the name of the collection that brings together all the
worksheet objects.

A collection is always dynamic, as objects can be added or removed,

like in the case of the pages of a book.
To reference an object within a collection, you can use the
following methods:

Collection! Object
Collection![Object]
Collection("Object")
Collection(var)
Collection(index)

You are a variable of type String that contains the name of the object. Indexes
the number of the position that the object occupies within the collection.

Properties, methods, and events of

Excel objects
At the beginning, I mentioned that object-oriented programming was based on the
modification of the properties and methods of objects.

Properties are the characteristics inherent to the object, which make it

distinguish from other objects (name, size, color, location on the screen...)
For example, properties of the Range object can be Name, Value
(value) and Column (column), among many others.
The use of different properties will allow for the modification of characteristics of the
object. For example, you can use the propertyValue to modify the value
what is shown in a cell. It is also possible to use the properties to
modify the appearance of an object. For example, the property Borders allows
change the cell border.
Some properties can also be objects. For example, if you want
Change the font type of the object Range, you must use the Font property.
As fonts have different names, sizes, or styles, there are others
properties that qualify the propertyFont.
For example, if you want to change the font size of cell B2, you must write
the following:

Range("B2").Font.Size = 25

Methods are referred to as the actions you can take with a

object. It can be said that they are commands given to objects to make them do
something about itself. In this way, the objectRange has the
methodsActivate(enable) andClear(delete), among many others.
Implementing a method in the object is very simple. This is the syntax:

Object.Method

In this way, if you want to select cell B2 of the active sheet, you would have to
use the following instruction:

Select("A2")

This is a simple example. Some other methods accept arguments, which are
parameters that allow better specification of the options for the action that must
create the object. For example, if you want to save the current book with the name
"Sales 2016" must use the following instruction:

ThisWorkbook.SaveAs Filename:="Sales 2016.xlsm"

In addition to properties and methods, some objects can also
reacting to events.

An event occurs when a certain situation happens. For example, to open.

a book, printing, or closing a window are events.
With VBA it is possible to program actions when an event occurs.

Imagine that every time you open a book, you want a message to appear.
welcome. To do this, you must work within the Open event of the object
Workbook in the following way:

Private Sub Workbook_Open()

MsgBox "Welcome!"
End Sub
The Developer tab
Can't find the Developer tab in the ribbon? That's it
Why when installing Excel, it is hidden. It should obviously show it beforehand.
to start working with her.

To show the Developer tab, right-click on

any part of the ribbon and select Customize the ribbon
options. Next, check the corresponding checkbox in the
the right part of the dialog box.
In the Developer (or Programmer, depending on the version of Excel you use) tab
you will find the commands that you will use to manage the plugins and the
controls.
File format for macros
The power of macros and the VBA language in general makes it so that a
Excel book is the perfect gateway for our teams to get infected with
virus.

Microsoft took measures several years ago to prevent this misuse of

Excel, creating a special file format to save the books that
they contain macros. Additionally, it included several levels of security.
The extension .xlsm was chosen to designate files with macros. In the
Versions prior to Excel 2007 were imperceptible unless you had
enabled the security levels that respect the macros.

Now, by simply checking the length of the book, you will be able to determine if it contains
some macro and then decide if they want to open it or not, based on the trust that
I generated you.

Whenever you open a book that contains VBA, it will be displayed in a way
default and below the ribbon a message indicating
that macros have been disabled.
A few years ago, viruses within macros became popular. These viruses
they took advantage of seemingly harmless files to make various types of
attack.

To work with files that contain macros, you need to configure

the security restrictions of Excel from the Trust Center.
To see what configuration Excel has at the moment, click on the Security button.
macros that can be found in the Code group of the Developer tab.

The VBA editor

The Visual Basic for Applications editor is the place where development takes place.
they test and modify the macros. It runs in a different window than the one for
Excel can be accessed in these three ways:
Clicking on the Visual Basic button in the Developer tab.
When the macro already exists, pressing the Modify button of the box
Macros dialog, which appears when you press the Macros button of the

developerSheet
Pressing Alt + F11.

The editor window shows everything needed to start creating a macro.

The menu bar: From here you can access most of the functions of VBE
to develop, test, and save the macros.
The standard toolbar: Contains the buttons with the commands
most used.
The Editing toolbar: Here you can find the most commands
useful when writing code.
The project explorer: A tree is shown with all the files that
they find open, which contain the components of the projects.
Code window: It is used to write all the VBA code. Each
Each element of a project has its own code window.
The properties window: allows you to change the properties of the object
selected, while you are in Design Mode.
The Immediate Window: Allows you to test a command while in Mode
Design. It is not shown by default, but you can visualize it by pressing Ctrl + G.
The Local window: Allows you to check the value of a variable at any time.
moment of the macro execution. To display this window, you must press the
Window Local Menu Button Ver.
The Inspection window: Allows you to add objects to see the current value of a
variable when you are in Interrupt Mode. To display it, press the button
corresponding to the menuView.
The Object Inspector: It is very helpful for finding objects, their
properties and associated methods. To display it press F2.
As in most applications, you can modify everything to your liking.
elements of the editor window. You can make these changes by pressing
in the Options button of the Tools menu.
It is possible to modify the following aspects:

The typography of each type of instruction (keywords, instructions,

comments...)
The introduction of the code.
The fitting of the windows.
Error management.
 Procedures in VBA
The Project Explorer window shows all the objects that can
contain code (the objects Sheets, ThisWorkbook, Modules, UserForms).
Within these objects are the procedures. In Excel, there are three types.
of procedure:
Sub
Function
Property

By default, procedures are public (Public) in all

modules. This means that they can be called from anywhere in the
project.
If you set the procedure as Private, it can only be called from others
procedures that are in the same module.
 The procedure Sub

It can be said that a Sub procedure is a set of instructions that

they carry out a series of specific actions.

There are two types of Sub procedures:

General procedures: They are the ones declared within a module.

Event procedures: It is automatically executed when an occurs.
certain situation or context.
All Sub procedures begin with the word Sub followed by the name
that you want to give, and they end with the instruction End Sub.
The Sub instruction can also be preceded by other words that determine
the scope, and it can contain a list of arguments for the procedure
may use them internally.

The Function procedure

Excel has many predefined worksheet functions

likeSUM,SEARCHorSUMIF. But sometimes it is necessary to perform
more complex calculations for which there is no function.
Thanks to the Function procedures, you can create new functions.
thus expanding those that already exist. Just like the Sub procedures, the
Functions also accept arguments that allow for more calculations.
effectively.
Unlike Sub procedures, Function procedures can
to return a value or result, therefore, when declaring the function, it is necessary
specify what type of variable the value returned by the procedure will be.

The Property procedure

This type of procedure is used to create and customize the properties.

of the Excel objects. They are automatically declared as public, although it is
possible to do it as private.
 Variables, constants, and other data

Variables

On many occasions, you will need to store data temporarily to

use them later in other calculations. For example, you can store the
sales values of a product each month to later use it to find the total
annual.
In general, a variable is a small region of memory that is used to
store values or information that will later be used in the execution of the program.

All the information being processed needs to use the RAM

computer, that's why it's good practice to reserve a place to store one.
declare the variable before using it. This is known as variable declaration and,
Although it is not completely necessary, it will help you better understand the code.
once written.
You can declare a variable in two different ways:

Implicit form: The variable is declared at the moment it is going to be used.

It is done by assigning a value to the name.
X = 13
March
Total_anual = 32000

Explicit form: It is necessary to define the name of the variable beforehand.

to use it.
To declare a variable this way you must write the word Dim right away.
of the variable name. Dim Employee.
There is a way to force you to declare variables and that is to introduce the
Instruction Option Explain the beginning of the module, outside of any
procedure. This will cause every time VBA finds a variable without
declare, I inform you that you must declare it before using it.
 Constants

Constants, as their name indicates, are values that never change, that
they maintain their value throughout the execution of the macro. Normally, they
they are used to store difficult-to-remember values that represent constants
(for example, the speed of light, which is 299,792,458 m/s, or the number pi,
what is 3.1415926535897932384626433832795028841971…)

Declaring a constant is done in the same way as you would with a variable.

Operators

A simple operation can be 4+5=9. In this expression, the 4 and the 5 are the
Operands and the sign + the operator. This is the simplest example that comes to mind.
It happens. They are used to modify a value or to create a new one.
VBA has several types of operators:

Arithmetic: They are used to perform mathematical operations, such as addition.

subtract, multiply or divide.
For comparison: They are used to compare expressions returning
TRUE or FALSE.
Logical: With these operators, it is possible to evaluate several expressions at once,
returning TRUE or FALSE.

Arrays

An array, also called a list, is a structure that stores multiple data.

of the same type arranged linearly, under the same name.

An array is characterized by:

Store the data in contiguous memory locations.

It has a single variable name that represents multiple elements. The
Elements are differentiated by an index number.
It is possible to access any element of the array through its index.
 According to its dimension or size, an array can be:

One-dimensional. Also called vectors. The data is stored in a way

linear.
Two-dimensional. Also called matrices. They arrange the data in the form
of table.
Multidimensional. They are the ones with 3 or more indices.
Static. Always maintain the same number of elements. It is used when
the exact number of elements it will contain is known in advance.
Dynamic. Their size can change at any time.
Integrated functions of VBA
Functions are one of the basic elements of programming. In addition to
the worksheet functions provided by Excel, it is possible to create functions thanks to
the Function procedures. In addition, VBA has built-in functions that
you can use within the procedures.

The InputBox function

It is a function that allows interaction with the user by requesting information.

while a macro is running. This is done through a dialog box.

This function returns the input data as a string.

when the user presses the Accept button. If they press the Cancel button or close the
theX returns a string of zero length ("").

The MsgBox function

It is the most used function in VBA. It is used to obtain a response.

Simple as user notification and to display brief messages such as errors and
any relevant data for the execution of the macro.
The MsgBox function does two things: It displays a dialog box to give
information to the user and returns a value of type Integer depending on the
button pressed. If you want to know the button that has been pressed, you must store the value
generated in a variable.
 Type conversion functions

Variables can store different types of data. The type of data

determine the nature of the values that the variable takes.

Sometimes you may need to use these types of data differently.

to the default data type. For this, conversion functions are used.
of type.

Please note that not all data types can be converted to any
another format. When using a type conversion function, it returns the
value of the converted data, but it does not change the stored value.

These are the conversion functions:

Cbool: Converts a number to a boolean value.

Cbyte: Converts a number to byte.
Ccur: Converts a number into currency.
Cdate: Converts a number into a date.
Cdbl: Converts a number to double.
Cdec: Converts a number to Decimal.
Cint: Converts a number to Integer.
CLng: Converts a number to a long integer.
Csng: Converts a number to Single.
CStr: Converts a variable of any type to a String type.
Cvar: Converts any variable to a Variant type.
Convert a text string into a number.
 The verification functions

These types of functions help the user check the type of data it contains.
an expression or variable. These are the validation functions:

IsDate: Checks if an expression can be converted to a date.

IsNumeric: Checks if an expression contains a value that can be
interpret as a number.
IsNull: Checks if an expression contains a null value.
IsEmpty: Checks if an expression contains any value or not yet.
started.
IsObject: Checks if a variable represents a variable of type Object.

Mathematical functions

VBA also has its own mathematical functions to perform

mathematical operations that can be used in the procedures. Almost
they all coincide with some of the sheet functions. I show you some of
they

Abs: Returns the absolute value of a number.

Int: Returns the integer part of a decimal number.
Rnd: Returns a random number between 0 and 1.
Sqr: Returns the square root of a numerical expression.

String functions

VBA also offers you many functions that will allow you to work with variables
of string. These are some of the most commonly used string variables:

Asc: Returns a number from 0 to 255 that represents the ASCII value of a
character.
Chr: It is the inverse function of Asc. It returns the corresponding character for the

ASCII code entered.

Len: Returns the number of characters in a string.
Left: Returns a specified number of characters from the left side of
the chain.
Returns a specified number of characters from the right side of
the chain.
Mid: Returns the n characters from a specified string, starting from
a certain position.
Ltrim: Removes the leading spaces from a string.
Rtrim: Removes the trailing spaces from a string.
Trim: Removes leading and trailing spaces from a string.
Ucase: Converts the characters of the string to uppercase letters.
Lcase: Converts the characters in the string to lowercase.
InStr: Returns the position of a substring within a string.
Replace one string with another.

Date and time functions

With VBA, you can also perform operations related to dates and times.
These are the most commonly used functions:

Date: Returns the current date of the system.

Now: Returns the current date and time of the system.
Time: Returns the current time of the device.
DateDiff: Returns the difference between two dates.
 Programming structures
Programming structures allow you to control the flow of execution.
from the macro, diverting it according to user actions or repeating certain
instructions. These structures exist in all programming languages and
they work almost the same way.

Conditional structures

These types of structures are used for making decisions. They evaluate
an expression and, depending on the result obtained, different actions are taken.
actions. It is possible to evaluate both numerical and text expressions.
The conditional or decision structures are as follows:

If – Then
If – Then – Else
IIf
Select Case

The If - Then structure

 On many occasions you will need to execute one or more instructions only if
a condition is met. If the condition is not met, the flow of the code
continue normally. This is the perfect context to use the instructionIf
– Then. Its syntax is as follows:
If condition Then
Instructions
End If

Condition: It is usually a comparison, but it can be any expression.

numeric that results in a numeric type value. VBA interprets this
value asTrueFalse. Any number different from 0 is considered
True. It considers the values 0 or null as False.
Instructions: The set of orders to be carried out.
When VBA encounters an If - Then statement, it evaluates whether the logical expression
It's True (True) or False (False). If so, execute the
instructions that are found behind Then. Otherwise, it does nothing
and continue executing the instructions found in the next line behind
End If.

The If – Then – Else structure

The previous structure is a bit limited. It only executes code when a condition is met.
the condition.
You will normally need to execute some instructions if the condition is met and
otherwise, if it is not fulfilled. To do this, VBA has the If - Then - structure
Else. Look at the syntax:

If condition Then
Some instructions
Otherwise
Other instructions
End If

This structure allows the flow of execution to branch into two paths.
different depending on whether the condition is met or not. If the condition is true, it
they execute a series of instructions. If it is false, different ones are executed from
the instruction Else until End If.

The If – Then – ElseIf structure

This structure is a variation of If - Then - Else, which is used when it is
It is necessary to evaluate more than one condition. Observe the syntax:

If condition_1 Then
Instruction_1
Instruction_2
……
ElseIf condition_2 Then
Instruction_3
Instruction_4
……
ElseIf condition_3 Then
Instruction_5
Instruction_6
……
……
Else
Instruction_7
Instruction_8
……
End If

In this type of structure, condition_1 is examined first. If it

it evaluates as True, the corresponding block of instructions is executed. If, for
the opposite, the condition is false, condition_2 is evaluated. If it is true, it
execute the following block, and so on. If none of the conditions of
If it meets the condition, the code between Else and End If will be executed.
Nested If - Then structures

It is possible to introduce an If-Then instruction within another. This is called

"nesting" and it occurs frequently when you need to build decisions with
alternatives, when some conditions are based on others.

Select Case

When you have many conditions to evaluate, it can become complicated.

use nested If instructions. That's why VBA offers you the Select structure
Case as an alternative.
With this structure, a condition is evaluated that can yield a result of a
an unspecified number of values, and perform actions based on this result.
Observe the syntax:

Select Case expression

Case Value1
Instruction_1
Instruction_2
……
Case Valor2
Instruction_1
Instruction_2
……
Case Valor3
Instruction_1
Instruction_2
……
……
Case Else
Instruction_1
Instruction_2
……
End Select

Select Case evaluates a single condition at the beginning of the structure. A

Next, compare the result with the values of each case of the structure
And, if it matches, execute the instructions associated with that case.

The With – End With structure

Most objects have many properties. Sometimes it is
it is necessary to modify several properties on the same object, so that
 the name of the object should be repeated as many times as there are properties to be
modify.

To avoid repeating the same object many times, the structureWith is used.
End With. The object is named only once, but it can be modified.
properties that are necessary. Its syntax is as follows:
With object
[instructions]
End With

Observe the following example. It shows how to put an entire row in

bold, with a green Comic Sans font and size 14:

Sub example_with()
With Range("1:1").Font
.Bold = True
Comic Sans MS
.Size = 14
.ColorIndex = 4
End With
End Sub

Loop structures
Thanks to this type of structure, it is possible to repeat one or several blocks of
code, a number of times determined by a condition. As long as it continues
By meeting this condition, the block will continue to execute.

A typical example occurs when a user tries to access a file with their
password. If the password matches the one that the application has stored in
if your database, the loop will end, allowing you access. Otherwise,
You will be asked for the password over and over until you enter the correct data.

Among the loop structures, we can find:

For - Next
For Each – Next
Do – Loop

For – Next
It is used when it is necessary to repeat a block of code a number of times.
determined times that are already known beforehand. This structure uses a
variable called counter that increases or decreases its value in each repetition
of the loop. Its syntax is as follows:
For counter = start To end [Step increment]
Instruction block
Next

Counter: it is the variable used to count the 'passes' made by the

loop and it must be numeric. This variable increases or decreases in a way
constant.
Start: it is the numerical value from which the loop begins to count.
Final: it is the numerical value that the counter must reach.
Increment: Optional. It is a positive or negative numeric expression.
If it is positive, start must be less than or equal to end. If it is negative, start
it must be greater than or equal to final. If you omit this parameter, VBA
it will increment the variable by 1.
Block of instructions: these are the instructions that will be executed repeatedly.
Next: is the keyword that indicates to VBA where the loop ends. When the
Execution flow reaches this line returns to the line where the For is located.
 For Each – Next
This type of loop is exclusive to Excel and allows you to iterate through all the elements.
of a collection or matrix. Observe the syntax:

For each element in group

[Instructions]
[Exit For]
[Instructions]
Next [element]

Element: it is the variable used to traverse the collection or the array. If

it is a collection, this variable can be of object type or Variant type. If it is about
a matrix can only be Variant.
Group: it is the name of the set of objects or the matrix.
Instructions: represent the group of instructions to carry out.
Next: indicates the end of the loop.

How to exit For - Next and For Each - Next structures

Sometimes it is not necessary to run all the iterations of the loop that I had.
initially planned. For this operation, there is a very useful instruction:
Exit For.
The following example shows the practical use of Exit For. It is about the
example that I mentioned earlier about the password entry for
access the file:

Sub Example_For_Next_Exit()
Dim password As String
Dim i As Integer
For i = 1 To 5
Password = InputBox("Enter password")
If password = "123" Then
mensaje = 1
Exit For
Otherwise
mensaje = 2
End If
 Next
If message = 1 Then
Welcome to the system
Else
MsgBox "You have forgotten your password, " & _
contact the administrator
vbOKOnly + vbCritical
End If
End Sub

Do - Loop structures

The For - Next structures are perfect when the number is known in advance.
sometimes a code must be executed. But what happens when there is no
specific number?
The Do loops cover this need since they allow executing a block
of code indefinitely until a condition is met. This type of
loop makes it possible to evaluate the condition at the beginning or at the end of the
structure. You can also specify if the loop repeats while (While) the
condition continues to be met or until the condition is met. Thus
According to this, there are two types of do - Loop:
Do – Loop While: It runs while the condition is met.
Do – Loop Until: It runs until the condition is met.
In both types of loops, the instructions are executed first and then evaluated.
the condition.

It is also possible to make the condition is evaluated first, and if it is met,

the code executes while it is fulfilled or until it does.
Do While - Loop: It executes while the condition is met.
Do Until - Loop: It runs until the condition is met.
Examples with the Do loop – Loop While and Until (first you do the
verification and then the instructions are executed.
Just like with for loops, it is also possible to exit early from a
Do loop - Loop. For this, there is the Exit Do statement.
 When VBA encounters the Exit Do statement, the flow of execution of the code
go directly to the next instruction after the loop.

Excel objects
As explained at the beginning of the guide, VBA is an object-oriented language.
The Excel object model contains more than 200 elements ordered by
hierarchical form, although there are four of them that are surely the ones that most
utilities:

Application: Refers to the application (Excel).

Workbook: Refers to the workbook.
Worksheet: Refers to a spreadsheet.
Range: Refers to a range of cells.
 The Application object

It is the object with the highest rank in the object model. It refers to the
application (in this case, Excel).
Provides access to application-level options and settings, such as
for example to the ribbon, to printing or to the size by the font by
defect, among others.
Making modifications at this level implies changes that will be reflected in the
Excel interface and its different tools.

Application Properties

Applications are one of the most important objects of VBA, which is why it has
many properties. Some of them define the environment where it runs
Excel, some control the presentation of the interface and others return objects.
Some of the most important properties are:

Returns the name that appears in the title bar.

Returns the installation path of Microsoft Excel.
Determines the default directory to save the books in
Excel.
StatusBar: It is used to display messages in the status bar.
DisplayFormulaBar: Shows or hides the formula bar.
Determines whether alert dialog boxes are displayed or not.
Determines if Excel runs in full screen.

Application Methods

Methods are the set of actions that an object can perform. It

they show some of them:

OnTime: Allows scheduling procedures to run at a

determined moment.
OnKey: Allows you to schedule procedures to be executed when
press a key or a shortcut.
Wait: Make a pause for a specified time.
Quit: Allows you to exit Excel.

Workbooks and Workbook

The Workbooks object is found below Application. Workbook forms

part of the Workbooks collection. It returns the following properties of the
Application object:

Application.Workbooks: Returns the Workbooks collection.

Application.ActiveWorkbook: Returns the active workbook.
Application.ThisWorkbook: Returns the workbook that contains the executed macro.

Properties of Workbooks and Workbook

You must take into account, when working with objects and collections, that
Many collections share properties with the elements they represent.
Depending on the object, the necessary or available parameters may not be
the same. This is the case of Workbooks and Workbook:

Item: Refers to a specific element of the collection.

Returns the name of a book.
Returns the name and the path of the book.
Saved: It is used to know if a book has been saved or not. It returns False if
it has not been saved yet or changes have continued to be made after saving.
Returns True when the last action taken has been to save it.
 Methods of the Workbooks and Workbook objects

These are the most important methods of these objects:

Allows you to create a new book.

Save As: Equivalent to the Save As command.
Save: Save the changes made to a book since the last time it was saved.
Close: Close the book.
Open: Open a book. Each time a book is opened, a new element is added.
to the Workbooks collection.

Worksheets and Worksheet

An Excel book can contain one or several worksheets that can be

insert, delete or move anywhere. It is also possible to perform other
actions such as renaming them, selecting them, and editing them together.

In VBA, a worksheet is called a Worksheet and the collection of

All the sheets of the book are Worksheets.

Properties of Worksheets and Worksheet

These are the most commonly used properties:

Item: It is used to refer to an object in the collection. You can do it to

through its index number or its name.
Allows you to set the name of the sheet.
CodeName: Returns the internal name of the sheet.
Visible: Allows you to show or hide a spreadsheet.
Count: It is a property of the Worksheets collection. Returns the number of
pages of the book.
UsedRange: To know the used range on the sheet.
 Methods of Worksheets and Worksheet

Add: Add a sheet of any type to the book.

Move: Allows you to organize sheets within a workbook.
Copy a spreadsheet to another location.
Delete: Delete a sheet.

The Range object

The Range object identifies a cell or a set of them, whether they are contiguous or
no.

Range Properties

This object has one of the most properties. The most are shown
important:

ActiveCell: Returns the active cell of the active window.

Returns a Range object that represents a range of cells.
Cells: Another way to refer to the cells.
Returns a Range object that represents a row of the sheet.
Returns a Range object that represents a column of the sheet.
Offset: Returns a Range object shifted from a reference by a number of
rows and columns determined.
Value: Sets the value of a cell or range of cells.
FormulaLocal: Allows you to enter formulas and functions in your own language,
as if you were doing it directly on the sheet.
End: Moves the active cell to the last cell of the row or column.
Font: Set the font type.
Interior: Allows you to modify the cell background.
Border: Allows you to modify the cell's borders.
 Range Methods

These are some of the most commonly used Range methods:

Select: Allows you to select a cell or a set of them.

DataSeries: Allows you to enter a series of data in a range of cells.
Copy a range of cells to another part of the grid.
ClearContents: Deletes the content of the cells while keeping their format.

The forms
Thanks to forms, it is possible to handle data in Excel more conveniently.
intuitive and efficient, since, in addition to speeding up the process, it manages to avoid the
feared data entry errors.

The forms are filled with controls (buttons, text boxes, labels,
etc.) programmable to respond to certain actions (clicks, selection of
data, etc.).

To add a form to Excel from the VBA editor, you first need to
create it through the Insert menu - UserForm. In addition to creating the new
form, in the project explorer the Forms folder is also created
Where will all those you create for that project be housed?

A floating box will also appear in the window where many are shown.
controls for you to start adding them to the form.

A form is a UserForm object, which has its properties, methods and

custom events with which to modify its appearance and behavior.

Properties of forms

The first step in designing a form is to establish the properties.

initials. If the Properties window does not appear on the left side,
Press F4 to show it.
 The Properties window is divided vertically. On the left side, there is
show the property name and on the right side, the value
corresponding. Modifying a property is very easy, you just need to enter the
new value on the right side.
These are the most commonly used properties:

Allows you to set the name of the form.

Allows customizing the title bar of the form.
Set the height of the form.
Sets the width of the form.
Allows you to choose the background color.
Allows setting a type of border.
Defines the color of the border.
StartUpPosition: Sets the exact location of the form on the screen.
Picture: Allows you to select a background image for the form.
Allows aligning the image within the form.
PictureSizeMode: Allows adjusting the size of the image.

Form methods

In addition to modifying the characteristics, you can also change the

behaviors:

These are the most used methods:

Show: Display the form on the screen. If the form is not loaded in the
memory, it first loads it and then shows it.
Load: Load a form on the screen, but does not make it visible.
Hide: Hide the form without downloading it from memory.
Unload: Download the memory form.
 Form Events

Forms also respond to events. These are the most important:

Initialize: It occurs when the form is loaded for the first time with Show or
Load.
Activate: It occurs when the form receives focus and becomes
active.

Form controls

The vast majority of forms I create will serve to input and select
data more easily. To do this you need to insert controls.

Excel has many different types of controls that you can find in
The toolbox (the floating toolbar). To insert
any of them you just have to drag it to the form or click
about him and draw it in the location you want:
Label: It is used to display information that users cannot
modify and to identify the other controls.
Text box (TextBox): It is commonly used to display information or to
enter data. The content can be edited.
List Box: A list of items is displayed for the user
select one or several of them.
Combo box: It is a drop-down list. The user
you can select one of the elements it contains.
Command Button: Most forms count as
minimum with one of these buttons. It is usually used to perform an action.
Marco (Frame): It is used as a container for other controls. It is commonly used.
to group several groups of radio buttons.
Checkbox: It is used for the user to give a
boolean response (Yes/No, True/False).
Option Button: It is used when the user must select
a single option from a group of options.
Image: Insert an image in the form.
MultiPage: This control contains different tabs, each one
of which it is a new page that can hold other controls.
Scroll Bar: Allows for quick scrolling.
length of a list of elements.
Number Button (SpinButton): Allows the user to select a number
clicking on one of the two arrows that compose it.