About the Tutorial - Current Affairs 2018, Apache Commons

Excel Macros i About the Tutorial An Excel macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently. Audience


Texto en PDF


 Excel Macros

i

About the Tutorial

A
n Excel
macro is an action or a set of actions that you can
record, give a name, save and
run as many times as you want

and whenever you want
.

Macros help
you to save time on repetitive tasks involved in data manipulation and data
reports that are required to be done frequently
.Audience

This guide targets novice developers and those new to
Excel Macros
. After completing this
tutorial, your firm foundation
in
creating macros

will allow you to
use

macros efficiently. Prerequisites

When you record a macro, Excel stores it as
a
VBA code. You can view this code in the
VBA editor. You can understand the code and modify it if you have substantial knowledge
of Ex
cel VBA. However, if you d
o not have sufficient knowledge, then we will suggest you
to go through our short tutorials on VBA.Copyright & Disclaimer



Copyright 201
6

by Tutorials Point (I) Pvt. Ltd.

All the content and graphics published in this e
-
book are the property of Tutorials Point (I)
Pvt. Ltd. The user of this e
-
book is prohibited to reuse, retain, copy, distribute or republish
any contents or a part of contents of this e
-
book in any manner w
ithout written consent
of the publisher.

We strive to update the contents of our website and tutorials as timely and as precisely as
possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.
Ltd. provides no guarantee r
egarding the accuracy, timeliness or completeness of our
website or its contents including this tutorial. If you discover any errors on our website or
in this tutorial, please notify us at
[email protected]
point.com Excel Macros

ii
Table of Contents

About the Tutorial

................................
................................
................................
................................
............

i

Audience

................................
................................
................................
................................
...........................

i

Prerequisites

................................
................................
................................
................................
.....................

i

Copyright & Disclaimer

................................
................................
................................
................................
.....

i

Table
of Contents

................................
................................
................................
................................
............

ii

1.

Excel Macros
–

Overview

................................
................................
................................
..........................

1

2.

Excel Macros
–

Creation

................................
................................
................................
............................

4

Re
cording a Macro

................................
................................
................................
................................
..........

4

Running a Macro

................................
................................
................................
................................
.............

6

Storing a Macro

................................
................................
................................
................................
...............

7

Saving a Macro Enabled File

................................
................................
................................
............................

8

3.

Excel Macros
–

Macros in a Single Workbook

................................
................................
.........................

10

Saving Macros in Personal Macro Workbook

................................
................................
................................

10

Hiding / Unhiding Personal Macro Workbook

................................
................................
...............................

13

Adding / Deleting Macros in Personal Macro Workbook

................................
................................
..............

14

4.

Excel Macros
–

Security

................................
................................
................................
...........................

16

What are Macro Viruses?

................................
................................
................................
..............................

16

Macro Enabled Excel Workbooks

................................
................................
................................
..................

16

Ways of Trusting Macro Enabled Workbook

................................
................................
................................
.

16

Macro Security Settings in Trust Center

................................
................................
................................
........

17

Macro Settings

................................
................................
................................
................................
...............

19

Defining a Trusted Location

................................
................................
................................
...........................

20

Digitally Signed Macros from Reliable Sources

................................
................................
.............................

21

Using Warning Messages
................................
................................
................................
...............................

22

Enabling / Disabling Security Alerts on the Message Bar

................................
................................
..............

22

5.

Excel Macros
–

Absolute References

................................
................................
................................
.......

25

Absolute References

................................
................................
................................
................................
......

25

Ensuring Absolute References

................................
................................
................................
.......................

27

Recording a Macro

................................
................................
................................
................................
........

28

Running a Macro

................................
................................
................................
................................
...........

30

6.

Excel Macros
–

Relative References

................................
................................
................................
........

31

Relative References

................................
................................
................................
................................
.......

31

Using Relative References

................................
................................
................................
.............................

32

Recording a Macro

................................
................................
................................
................................
........

33

Running a
Macro

................................
................................
................................
................................
...........

35

7.

Excel Macros
–

VBA

................................
................................
................................
................................
.

37

Developer Tab on the Ribbon

................................
................................
................................
........................

37

Developer Commands for Macros

................................
................................
................................
.................

38

VBA Editor

................................
................................
................................
................................
.....................

39

Projects Explorer

................................
................................
................................
................................
...........

41Excel Macros

iii
8.

Excel Macros
–

Understanding Codes

................................
................................
................................
......

42

Viewing a Macro Code in VBA Editor
................................
................................
................................
.............

42

Understanding the Recorded Actions as Parts of Code

................................
................................
.................

43

9.

Excel Macros
-

Assigning Macros to Objects

................................
................................
............................

45

Assigning a Macro to a Shape

................................
................................
................................
........................

45

Assigning a Macro to a Graphic

................................
................................
................................
.....................

49

Assigning a Macro to a Control
................................
................................
................................
......................

49

10.

Excel Macros
-

Running a Macro

................................
................................
................................
.............

53

Running a Macro from View Tab

................................
................................
................................
...................

53

Running a Macro with Shortcut Key

................................
................................
................................
..............

54

Running a Macro through Quick Access Toolbar

................................
................................
...........................

56

Running a Macro in Custom Group

................................
................................
................................
...............

63

Running a Macro by Clicking an Object

................................
................................
................................
.........

73

Running a Macro from the Developer Tab

................................
................................
................................
....

73

Running a Macro from VBA Editor

................................
................................
................................
................

74

11.

Excel Macros
–

Creating a Macro Using VBA Editor

................................
................................
.................

75

VBA Objects and Modules

................................
................................
................................
.............................

75

Creating a Macro by Coding

................................
................................
................................
..........................

77

Running the Macro from VBA Editor

................................
................................
................................
.............

80

Running the Macro from Worksheet
................................
................................
................................
.............

81

12.

Excel Macros
–

Editing

................................
................................
................................
............................

82

Copying a Macro Code
................................
................................
................................
................................
...

82

Renaming a Macro
................................
................................
................................
................................
.........

84

Deleting a M
acro

................................
................................
................................
................................
...........

86

13.

Excel Macro
–

UserForms

................................
................................
................................
........................

88

Creating a UserForm

................................
................................
................................
................................
......

88

Understa
nding the UserForm

................................
................................
................................
........................

89

Controls in the ToolBox

................................
................................
................................
................................
.

90

Message Box Icon Displays

................................
................................
................................
..........................

106

14.

Excel Macros
–

Debugging a Code

................................
................................
................................
.........

107

VBA Debugging

................................
................................
................................
................................
............

107

15.

Excel Macros
–

Configuring a Macro

................................
................................
................................
.....

111

Recording an Auto_Open Macro

................................
................................
................................
.................

111

Limitations of Auto_Open Macro

................................
................................
................................
................

112

VBA Code for Open Event of a Workbook

................................
................................
................................
...

112

Excel Macros

4
A
n Excel
macro is an action or a set of actions that you can
record, give a name, save and
run as many times as you want

and whenever you want
. When you create a macro, you are
recording your mouse clicks and keystrokes
. When you run a saved macro, the recorded
mou
se clicks and keystrokes

will be executed in the same sequence as they are recorded.

Macros help you to save time on repetitive tasks involved in data manipulation and data
reports that are required to be done frequently.

Macro and VBA

You can record and r
un macros with either Excel commands or from Excel VBA.

VBA stands for
Visual Basic for Applications

and is a simple programming language that is
available through Excel Visual Basic Editor (VBE), which is available from the DEVELOPER tab
on the Ribbon.
When you record a macro, Excel generates VBA code. If you just want to
record a macro and run it, there is no need to learn Excel VBA. However, if you want to modify
a macro, then you can do it only by modifying the VBA code in the Excel VBA editor.

You w
ill learn how to record a simple macro and run it with Excel commands in the chapter
-

Creating a Simple Macro
. You will learn more about macros and about creating and / or
modifying macros from Excel VBA editor in the later chapters.

Personal Macro Workb
ook

A macro can be saved in the same workbook from where you recorded it. In that case, you
can run the macro from that workbook only and hence you should keep it open. Excel gives
you an alternative way to store all your macros. It is the personal macro
workbook, where
you can save your macros, which enables you to run those macros from any workbook.

You will learn about Personal Macro Workbook in the chapter
-

Saving all your Macros
in

a
Single Workbook
.

Macro Security

Macros will be stored as VBA code

in Excel. As with the case of any other code, macro code
is also susceptible to malicious code that can run when you open a workbook. This is a threat
to your computer. Microsoft provided with the Macro Security facility that helps you in
protecting your
computer from such macro viruses.

You will learn more about this in the chapter
-

Macro Security
.1.

Excel Macros
–

OverviewExcel Macros

5
Absolute References and Relative References

While recording a macro, you can use either absolute references or relative references for the
cells on which
you are clicking. Absolute references make your macro run at the same cells
where you recorded the macro. On the other hand, relative references make your macro run
at the active cell.

You will learn about these in the chapters
-

Using Absolute References

for a Macro

and
Using
Relative References for a Macro
.

Macro Code in VBA

You can record and run macros from Excel even if you do not know Excel VBA. However, if
you have to modify a recorded macro or create a macro by writing VBA code, you should learn
E
xcel VBA. You can refer to the Excel VBA tutorial in this tutorials library for this.

However, you should know how to view the macro code.
You can learn how to access VBA
editor in Excel and about the different parts of the VBA editor in the chapter
–

Excel VBA.

You can learn how to view the macro code in Excel VBA editor and
you can understand the
macro code in the chapter
-

Understanding Macro Code
.

Assigning Macros to Objects

You can assign a macro to an object such as a shape or a graphic or a

control. Then, you can
run the macro by clicking on that object. You will learn about this in the chapter
-

Assigning
Macros to Objects
.

Running Macros

Excel provides several ways to run a macro. You can choose the way you want to run a macro.
You will l
earn about these different possible ways of running a macro in the chapter
-

Running
a Macro
.

Creating a Macro Using VBA Editor

If you decide to write the macro code, you can learn it in the chapter
-

Creating a Macro Using
VBA Editor
. However, the
prerequisite is that you should have Excel VBA knowledge.

Editing a Macro

You can modify macro code in Excel VBA editor. If you want to make extensive changes, you
should have Excel VBA knowledge. But, if you want to make only minor changes to the code
or

if you want to copy the VBA code from a recorded macro to another macro, you can refer
to the chapter
-

Editing a Macro
.

You can rename a macro and even delete it. You will learn about this also in the same chapter.

User Forms

Excel Macros

6
A Form is normally used to collect required information. It will be self
-
explanatory making the
task simple. Excel User Forms created from Excel VBA editor serve the same purpose,
providing the familiar options such as text boxes, check boxes, radio button
s, list boxes,
combo boxes, scroll bars, etc. as controls.

You will learn how to create a User Form and how to use the different controls in the chapter
–

User Forms.
Debugging
Macro

Code

At times, a macro may not run as expected.
You might have created the macro

or you might
be using a macro supplied to you by someone. You can debug the macro code just as you
debug any other code to uncover the defects and correct them. You will learn about this in
the chapter
-

Debugging
Macro

Code
.

Configuring a Macro to Run on Opening a Workbook

You can make your macro run automatically when you open a workbook. You can do this
either by creating an Auto_Run macro or by writing VBA code for workbook open event. You
will learn

this in the chapter
-

Configuring a Macro to Run on Opening a Workbook
.Excel Macros

7
You can create a macro with Excel commands by recording the key strokes and mouse clicks,
giving the macro a name and
specifying how to store the macro. A macro thus recorded can
be run with an Excel command.

S
uppose you have to collect certain results repeatedly in the following format
–Instead of creating the table each time, you can have a macro to do it
for you.

Recording a Macro

To record a macro do the following
–



Click
t
he VIEW
t
ab on
t
he Ribbon.



Click
Macros in
t
he Macros group.



Select

Record Macro
from

t
he dropdown lis
t
.

2.

Excel
Ma
cros

–

CreationExcel Macros

8 The
Record Macro

dialog box appears.



Type
MyFirstMacro

in the Macro name box.



Type
A Simple Macro

in the Description box

and

click

OK.
Remember that whatever key strokes and mouse clicks you
do,
will
be

recorded now.

Excel Macros

9


Click
in

the cell B2.



Create the table.



Click
in

a different
cell in the worksheet.



Click
the VIEW tab on the Ribbon.



Click
Macros.



Select

Stop Recording
from

the dropdown list.Your macro recording is completed.

The first step to click on a particular cell is important as it tells where exactly the macro has
to start placing the recorded steps. Once you are done with the recording, you have to click
Stop Recording to avoid recording of unnecessary steps.

Runn
ing
a

Macro

You can run the macro you have recorded any number of times you want. To run the macro,
do the following
-



Click on a new worksheet.

Note the
active cell.
In our case,

it is A1.



Click
the
VIEW

tab on the Ribbon.



Click
Macros
.



Select

View Macros

from the dropdown list.

Excel Macros

10

The
Macro dialog box appears.O
nly the macro that you recorded appears in the Macros list.



Click
the macro name
–

MyFirstMacro in the Macro dialog box.

The

description you typed
while recording the macro will get displayed.

Macro description allows you to identify for
what purpose you have recorded the macro.



Click
the Run button.

The same table that you have created while recording the macro will
appe
ar in just a split of a second.

Excel Macros

11

You have discovered the magic wand that Excel provides you to save time on mundane tasks.
You
will
observe the following
–



Though the active cell before running the macro was A1, the table is placed
in
the cell
B2
as you have recorded.



In addition
, the active cell became E2, as you have clicked
that cell before you stopped
recording.

You can run the macro
in
multiple
worksheets with different active cells before running the
macro and observe
the

s
ame
conditions as given above
.

Just keep a note of this and you will
understand later in this tutorial why it has occurred so.

You can also have a macro recording that places your recorded steps
in
the active cell. You
will learn how to
do this as you progress in the tutorial.

Storing
a

Macro

You might wonder how to save the macro
s

that
are
created. In th
is
context
you need to know
-



Storing a macro



Saving a macro
enabled file

As and when you create a macro, you can choose where to store that particular macro. You
can do this in the
Record Macro

dialog box.

Click
t
he box
-

Store macro in
.

The following three options are
available
-



This Workbook.

Excel Macros

12


New Workbook.



Personal Macro WorkbookThis Workbook

This

is the default option. The macro will be stored in your current workbook from where you
created the macro.

New Workbook

This

option, though available, is not recommended. You will be asking Excel to store the macro
in a different new workbook and mostly
it is not necessary.

Personal Macro Workbook

If you create several macros that you use across your workbooks,
Personal Macro Workbook

provides you with the facility to store all the macros at one place. You will learn more about
this option in the next ch
apter.

Saving
a

Macro Enabled File

If you had chosen
This Workbook

as the option for storing the macro, you would need to
save your workbook along with the macro.

Excel Macros

13
Try to save the workbook. By default, you would be asking Excel to save the workbook as a
n

.xls file.

Excel displays a message saying that an Excel feature VB project cannot be saved in
a macro free workbook, as shown below.Note:

If you click
Yes, Excel will save your workbook as a macro free .xls file and your macro
th
at you stored with This Workbook option will not get saved. To avoid this, Excel provides
you an option to save your workbook as a
macro
-
enabled

workbook that will have .xlsm
extension.



Click
No in the warning message box.



Select Excel Mac
ro
-
Enabled Workbook (*.xlsm) in the Save as type.



Click Save.

Excel Macros

14

You will learn more about these in later chapters in this tutorial.

Excel Macros

15
Excel provides you with a facility to store all your macros in a single workbook. The workbook
is called
Personal Macro Workbook

-

Personal.xlsb.
It is
a hidden workbook stored on your
computer, which opens every time you open Excel.

This enables you to ru
n
your macros
from
any

workbook.

There will be a single
Personal Macro Workbook

per computer and you cannot
share it across computers. You can view and run the macros in your
Personal Macro Workbook

from any workbook on your computer.

Saving Macros
in

Per
sonal Macro Workbook

You can save macros in your
Personal Macro Workbook

by selecting it as the storing option
while recording the macros.

Select
Personal Macro Workbook

from the drop down list
under
the
category
Store macro

in
.

Record your second macro.



Give macro details in the Record Macro dialog box as shown below.

3.

Excel Macros
–

Macros

in

a Single Workbook

Excel Macros

16


Click OK.Your recording starts.

Create a table as shown below.

Stop recording.

Excel Macros

17


Click
the VIEW tab on the Ribbon.



Click
Macros.



Select

View Macros

from
the dropdown list.

The
Macro dialog box appears.The

macro name appears with a prefix PERSONAL.XLSB! indicating that the Macro is in the
Personal Macro Workbook.

Save your workbook.

It will get saved as

an .xls file as the macro is not in your workbook

and
c
lose Excel.

You will get the following message regarding saving the changes to the Personal Macro
Workbook
–

Excel Macros

18

Click
the Save button.

Your macro is saved in the
Personal.xlsb

file on your computer.Hiding /
Unhiding

Personal Macro Workbook

Personal Macro Workbook

will be hidden
,

by
default. When you start Excel, the personal
macro
workbook is loaded but you can
no
t see it because it

i
s hidden. You can unhide it
as follows
-



Click
the
VIEW

tab on the Ribbon.



Click
Unhide in the Window group.Excel Macros

19
The
Unhide dialog box appears.PERSONAL.XLSB appears in the Unhide workbook box

and click OK. Now you can
view the macros saved in the personal macro workbook.

To hide the personal macro workbook, do the following
–



Click
on

the personal macro workbook.



Click
the VIEW tab on the Ribbon.

Excel Macros

20


Click
Hide on the Ribbon.

Runn
ing Macros
Saved
in Personal Macr
o Workbook

You can run the macros saved in personal macro workbook from any workbook. To run the
macros, it does not make any difference whether the personal macro workbook is hidden or
unhidden.



Click
View Macros.



Select

the macro name
from
the macros list.



Click
the Run button.

The macro will run.

Adding / Deleting Macros in Personal Macro Workbook

You can add more macros in personal macro workbook by selecting it for Store macro in
option while recording the macros, as you had see
n earlier.

You can delete a macro in personal macro workbook as follows
–



Make sure that the personal macro workbook is unhidden.



Click
the macro name in the View Macros dialog box
.



Click
the Delete button.

If the personal macro workbook is hidden,

you will get a message saying
“
C
annot edit a macro
on a hidden workbook
”.Unhide the personal macro workbook and delete the selected macro.

The macro will not appear in the macros list.

However, when
you create a new macro and
save it in your personal workbook or
delete

any macros that it contains, you
will be

prompted
to save the personal workbook just as
in the case

you saved it first time.
Excel Macros

21
End of ebook preview

If you liked what you saw
…

Buy it from our store @
https://store.tutorialsp
oint.com

Documentos PDF asociados:

About the Tutorial - Current Affairs 2018, Apache Commons ...
Master Agreement between the Department of Veteran Affairs ...
Career Center - Duke Student Affairs
Infernal Affairs By Jane Heller - aroundmyhouseconsignment.com
Clinical Reminders - U.S. Department of Veterans Affairs
Department of Veterans Affairs VA Handbook 5975.1 November ...
BACKGROUND - United States Department of Veterans Affairs
DEPARTMENT OF VETERANS AFFAIRS Federal Supply Service ...
Curso de Guitarra - Wikimedia Commons
Interpretación de Radiografias - Wikimedia Commons
TOPONIMIA DE AGUASCALIENTES - Wikimedia Commons
Logistica de Transporte - Wikimedia Commons
ESTRUCTURAS Y ARQUITECTURA - Wikimedia Commons
Uso de funciones en Excel - Wikimedia Commons
MEDIOS DE CONTRASTE - Wikimedia Commons
Agitprop in Soviet Russia - Digital Commons
Instrucciones Nivel Basico - Wikimedia Commons
Propuesta Mesa Directiva 2016 - 2018 y Congreso ALAT 2018 ...
Current Therapy in Equine Medicine
Current Surgical Therapy - Springer
Current Diagnosis & Treatment in Cardiology 2nd Ed ...
PDF CURRENT Otorrinolaringologia: Diagnóstico e Tratamento ...
Current State of Legislation - UNICEF
Prevalence of and factors associated with current asthma ...
Halitosis: A Review of Current Literature - jdh.adha.org
(PDF) Current Medical Diagnosis And Treatment 2017 ...
Current Therapy In Vascular And Endovascular Surgery
Monográfico: ENDOUROLOGÍA Y LÁSER CURRENT TECHNIQUES FOR ...
Title: CURRENT Diagnosis and Treatment Psychiatry, 3/e
3 CURRENT THEMATIC APPERCEPTION TESTS FOR CHILD AND ...
CURRENT Medical Diagnosis And Treatment Pu28740 New ...
The Current Drug Development Paradigm: Responding to US ...
Onychomycosis: Current Trends in Diagnosis and Treatment
CAMERON CURRENT SURGICAL THERAPY 11TH EDITION
COMPARISON OF DISSOLUTION PROFILES: CURRENT GUIDELINES - SEFIG
CURRENT Medical Diagnosis and Treatment (2015) 54th ...
SERIES NAME Original Title if Current ... - Maggie Shayne
Calendario 2018 & Giorni festivi 2018
JURISPRUDENCIA Roj: SJPI 25/2018 - ECLI: ES:JPI:2018:25
1 UML Tutorial - UdG