EXCEL VBA
House Rules
► Always Mute
► Chat if you need to leave, if you have questions,
and clarifications
► In case if you cant hear or understand me, chat,
or call my attention.
► Water Break is every one hour, remind me, If
needed
https://www.online-stopwatch.com/countdown-
timer/
By Automated Business
Ice Breaker
► Introduce Yourself
► Name, can be nickname
► Work, Position, Industry
► Excel knowledge
By Automated Business
MODULE
►FUNDAMENTALS
►SAMPLE CODING
►APPLICATION
►ASSIGNMENT
What is MS Excel?
► Microsoft Excel, spreadsheet application
launched in 1985 by the Microsoft
Corporation. Excel is a popular spreadsheet
system, which organizes data in columns and
rows that can be manipulated through
formulas that allow the software to perform
mathematical functions on the data.
By Automated Business
Excel is typically used for:
► Analysis
► Data entry
► Data management
► Accounting
► Budgeting
► Data analysis
► Visuals and graphs
► Programming
► Financial modeling
► And much, much more!
What Is VBA?
► Visual Basic for Application is a human-
readable and editable programming code
that gets generated when you record a
macro. Today, it is widely-used with other
Microsoft Office applications such as MS-
Word, MS-Excel, and MS-Access.
By Automated Business
What Is Excel VBA?
► Excel VBA is Microsoft’s programming
language for Office applications such as MS-
Excel, MS-Word, and MS-Access. Macros are
what most people who write VBA code use.
By Automated Business
Is VBA necessary for Excel?
►
VBA is not necessary for regular Excel
usage, but it becomes essential when
you want to automate tasks, create
custom functions, or build more
advanced solutions.
By Automated Business
What is the difference between Excel
and VBA?
►Excel is a spreadsheet software used
for data analysis and calculations,
while VBA is a programming language
used to extend Excel's functionality
and automate tasks.
By Automated Business
What is VBA coding?
►VBA coding refers to writing
instructions in the Visual Basic for
Applications (VBA) language to create
macros, automate tasks, and add
custom functionality to Excel.
By Automated Business
Is Excel VBA a good skill?
►Yes, Excel VBA is a valuable skill,
especially for professionals working
with data analysis, finance, or project
management. It can significantly
improve productivity and open up
opportunities for more complex tasks.
By Automated Business
Is Excel VBA easy to learn?
►The difficulty of learning Excel VBA
depends on your programming
background. For beginners, it may
take some time to grasp the concepts,
but with practice and resources, it can
be learned effectively.
By Automated Business
What is the use of VBA in Excel?
►VBA in Excel is used to automate
repetitive tasks, build custom
functions, create user-defined forms,
manipulate data, and add interactive
features to workbooks.
By Automated Business
Is VBA free with Excel?
►Yes, VBA is included for free in Excel.
It is a built-in feature of Microsoft
Excel and is available in all versions of
Excel, including the Office 365
subscription. Users can start using VBA
without additional cost.
By Automated Business
EXCEL VBA
FUNDAMENTALS
Enable Developer Option in Excel
► Right-click on the ribbon (anywhere) click
on the Customize the Ribbon option.
By Automated Business
Enable Developer Option in Excel
► Go to Customize the Ribbon and select the
Developer checkbox.
By Automated Business
Enable Developer Option in Excel
► Go to Customize the Ribbon and select the
Developer checkbox.
By Automated Business
Trust Center
By Automated Business
By Automated Business
VBA Editor Interface
► You can open the VBA interface by using
the ALT + F11 keyboard shortcut, or you
can go to the Developer tab and click on
Visual Basic.
By Automated Business
VBA Editor Interface
► You can open the VBA interface by using
the ALT + F11 keyboard shortcut, or you
can go to the Developer tab and click on
Visual Basic.
By Automated Business
By Automated Business
VBA Editor Interface
► Choose between New or Record
By Automated Business
Create an Excel Macro using a Command
Button
► Now that you have enabled the developer
tab and have some familiarity with the VBA
editor, let’s start creating a macro by using
a command button.
► To place a command button on your
worksheet
By Automated Business
Create an Excel Macro using a Command
Button
► Go the Developer tab > Insert > ActiveX
Controls > Command button.
By Automated Business
Create an Excel Macro using a Command
Button
► Drag the command button on your
worksheet.
By Automated Business
Assigning a Macro to a Command Button
► Right-click on the command buttons and
select View Code.
By Automated Business
Assigning a Macro to a Command Button
► Add the following lines of code shown
below.
By Automated Business
Assigning a Macro to a Command Button
► Close the VBA editor and click on the
command button on the worksheet. Make
sure to deselect the design mode.
By Automated Business
2 Ways to Add ShortCut for Macros
By Automated Business
2 Ways to Add ShortCut for Macros
By Automated Business
Create a Message Box
► The MsgBox is a dialog box in Excel that will
prompt a message on your worksheet. To
create a MsgBox, add the following lines of
code to the command button.
By Automated Business
Create a Message Box
► Now, when you click on the button, you will
get the following message.
By Automated Business
Here are several principal reasons to
consider VBA programming in Office.
► Automation and repetition. ...
► Extensions to user interaction. ...
► Interaction between Office applications. ...
► Doing things another way. ...
► Using code to make applications do things. ...
► The Object Model. ...
► Methods. ...
► Properties.
By Automated Business
ACTIVITY ONE
►BASIC DATA ENTRY
By Automated Business
click record macro
select data range
copy
go to database
select a1
long press ctrl then arrow down
click use relative reference
arrow down
click use relative reference
paste special values transpose
By Automated Business
USAGES, PRATICAL &
RECOMMENDED
BASIC DATA ENTRY
By Automated Business
ACTIVITY FILE
►UPLOAD TO GOOGLE
DRIVE
►FILENAME: “WEEK NO. +
EXERCISE NO. + NAME”
By Automated Business
ACTIVITY TWO
►LOCK VBA
By Automated Business
Protecting VBA
► Go to TOOLS, Choose VBAProject
Properties.
By Automated Business
Protecting VBA
► Choose Protection,
► CHECK “Lock project
for viewing”
► Input your Password
then click “OK”
By Automated Business
By Automated Business
ACTIVITY THREE
►PRINT PREVIEW
By Automated Business
WHY NOT
PRINT-OUT?
PRINT PREVIEW
By Automated Business
ACTIVITY FILE
►UPLOAD TO GOOGLE
DRIVE
►FILENAME: “WEEK NO. +
EXERCISE NO. + NAME”
By Automated Business
By Automated Business
APPLICATION
COMBINATION OF
FORMULAS AND VBA
By Automated Business
APPLICATION
Vlookup
A VLOOKUP function exists of 4 components:
1) The value you want to look up;
2) The range in which you want to find the value and the
return value;
3) The number of the column within your defined range,
that contains the return value;
4) 0 or FALSE for an exact match with the value your are
looking for; 1 or TRUE for an approximate match.
Syntax: VLOOKUP([value], [range], [column number],
[false or true])
By Automated Business
APPLICATION
Vlookup
from other database
By Automated Business
APPLICATION
If Statement
The IF function in Excel performs a
logical comparison between two values.
The result of the IF function is either
TRUE or FALSE. For example, we can test
if the value in cell B2 is greater than the
value in cell A2. If so, the result is TRUE,
if not, the result is FALSE.
By Automated Business
APPLICATION
If Statement
for control purposes
By Automated Business
APPLICATION
Sample work
Within 30 minutes
Create your own Excel VBA Project
Consolidating all your learnings from these modules
You can include other codes and formulas.
After, we will call some volunteers to present and
discuss their project.
By Automated Business Template
APPLICATION
Save Macro-Enabled
By Automated Business
ASSIGNMENT
►CREATE YOUR OWN
EXCEL VBA, AND
PRESENT IT
By Automated Business
ASSIGNMENT
UPLOAD TO GOOGLE DRIVE
CREATE YOUR OWN TEMPLATE
FILENAME: “WEEK NO. +
EXERCISE NO. + NAME”
By Automated Business
By Automated Business
TIPS
►KNOW WHAT YOU
NEED
►KNOW WHAT YOU
WANT
By Automated Business
TOOLS
►YOUTUBE
►FACEBOOK
By Automated Business
LIKE, FOLLOW, and SHARE
►AUTOMATED BUSINESS
TEMPLATE
https://www.facebook.com/pr
ofile.php?id=100088634994537
&mibextid=9R9pXO
By Automated Business
LIKE, FOLLOW, and SHARE
►QUALITY FINANCIALS
https://www.facebook.com/
profile.php?id=100090186236
168&mibextid=ZbWKwL
By Automated Business
JOIN GROUP
►AUTOMATED BUSINESS
TEMPLATE
By Automated Business
THANK YOU!
Article:
https://www.google.com/search?q=what+is+vba+in+excel&sca_esv=5886096
01&sxsrf=AM9HkKnme-
EaAVXLu2Ar_EBiCUjSi7Szgg%3A1701925841130&ei=0VNxZY3QB_niseMP-
7iIkAY&oq=what+is+vba&gs_lp=Egxnd3Mtd2l6LXNlcnAiC3doYXQgaXMgdmJhKg
IIATILEAAYgAQYigUYkQIyCxAAGIAEGIoFGJECMgUQABiABDIFEAAYgAQyBRAAGI
AEMgUQABiABDIFEAAYgAQyBRAAGIAEMgcQABiABBgKMgUQABiABEiBJ1C8BFibE
nACeAGQAQCYAW-gAfkCqgEDMy4xuAEDyAEA-
AEBwgIKEAAYRxjWBBiwA8ICDRAAGIAEGIoFGEMYsAPCAg4QABiABBiKBRiRAhix
A8ICCBAAGIAEGLEDwgIREAAYgAQYigUYkQIYsQMYgwHCAgsQABiABBixAxiDAeI
DBBgAIEGIBgGQBgo&sclient=gws-wiz-serp
