Microsoft Excel solves lot of our problems by simple use of formulae. However, lot of times you will notice that during moving or sorting data, we end up doing, we end up doing same tasks again and again. Sometimes, a simple program can automate the job and Excel has an easy-to-use programming language built into all versions, VBA - Visual Basic for Applications.
VBA is derived from Visual Basic 6 (the most used programming language until Microsoft .NET). However, VBA is still well suited for getting jobs done quickly and easily. It may not be be as fancy as building large applications but for it is superb for automating Excel and building Excel-based applications. Knowledge of VBA will also help you in other Microsoft Office products.
VBA (Macros) for Excel is one of the most sought after skill sets IF you work involves working with spreadsheets, a LOT of data and repetitive tasks. Professionals learning SAS Business Analytics, too, learn VBA as a pre-requisite.
This 8 hrs.+ program assumes that you are not a programmer and are at a Intermediate/Advanced level of Excel user. It will teach you how to write, edit and test Macros codes, which can be used to automate Excel based tasks and functions.
Macros - Nuts and Bolts
How are macros created?
TECHNIQUES:RECORDING AND WRITING USING VBA
How to save a macro?
TECHNIQUES: .XLSM FILE EXTENSION
How to record a simple macro?
How to edit a recorded macro?
How to change the short-cut key of a recorded macro?
TECHNIQUES:USING THE OPTION BUTTON TO CHANGE THE SHORT-CUT KEY OF A RECORDED MACRO
How to save a recorded macro?
TECHNIQUES:USE OF DIFFERENT FILE EXTENSIONS TO SAVE THE RECORDED MACRO E.g..XLSX, and XLSM
Learn how to apply Macro Security settings.
TECHNIQUES: USE OF TRUST CENTER TO APPLY MACRO SECURITY
Understandwhat are the different ways in which you can run a macro.
Defining a Storage Location for Macro
TECHNIQUES:USE OF THE OPTION viz., THIS WORKBOOK, PERSONAL WORKBOOK AND NEW WORKBOOK.
Understanding the In and Out of programming
Learn to enable the developer tab on various versions of Microsoft Excel
Understanding the Visual Basic Editor Window
Writing a small VBA code
Learn to create sub procedure using Sub Macroname()
Understanding what Comments are and how to add comments in VBA procedure?
TECHNIQUES: Using comment block and uncomment block to add and remove comments.
Learn to use private and public procedure
Understanding how to call sub procedure
Understanding how to pass arguments to a procedure by value and reference
Learn how to create functions in VBA
·Understand how to use conditions in Functions
·Learn how to use conditions If...Then....Else in VBA
Learn how to create your own custom functions in Excel VBA and add it as a user defined functions
Understand what variables are and how to assign them.
TECHNIQUES: USING VARIABLE NAME = VALUE
Scope of a variable
Life of a Variable
Understand what are data types and how are they used in the VBA procedure
Understanding what constants are and how to use them
Understanding what operators are and types of operators
How to create and use MsgBox in VBA
Understanding how to add arguments to a MsgBox in VBA
Here are the three arguments that we will use :MSGBOX([TEXT], [BUTTONS], [TITLE])
How to create and use Input Box in VBA.
What is the Range Object and how to use it in VB Editor
Range(“A1”).Value = ”abc”
What is the Cell Object and how to use it in VB Editor
Returns a Long value that represents the number of objects in the collection
Learn how to use Selection object.
This example teaches you how to select entire rows and columns in Excel VBA.
Dim sets a Variable (a value that can be changed depending upon the input) while Set is a constant value that should remain constant, unless changed by another program
Learn how to use Worksheet object
The following properties for returning a Worksheet object are described in this section:
·Learn how to use Workbook object
ou can delete workbooks by using the KILL command.
TECHNIQUES: Kill "C:\WorkbookName.xlsx"
Learn how to use Application object.
Use the Application property to return the Application object. The following example applies the Windows property to the Application object.
See a list of all the different objects with their methods, properties, events and constants using Object browser.
How to use offset function in excel
How to use Range.Offset property
Learn and understand what Loops are
You can write Visual Basic code that makes decisions and repeats actions.
Looping allows you to run a group of statements repeatedly. Some loops repeat statements until a condition are False; others repeat statements until a condition are True. There are also loops that repeat statements a specific number of times or for each object in a collection.
Learn and understand For-Next Loop
Learn and understand Do-While Loop
Learn and understand Do Loop
Assignment on the Loops
Learn and understand For each-Next loop
Learn and understand how event are actions performed by users to trigger the Excel VBA to execute code
·Learn and understand how to create Workbook Close Event
TECHNIQUES:-to execute instructions immediately before the workbook is closed, choose BeforeClose
Learn and understand how to create workbook activate and deactivate events
Additional chapter on Activate-Deactivate Events
Learn and understand how to create worksheets activate and deactivate
Learn and understand how to create Application Events.
The basic steps to using application level events are
Yoda Learning is a team of 12+ industry professionals focused on developing “Project-based" learning solutions, which enable the learners explore real world situations and thus, make the learning process engaging and practical. Our Courses on Technology includes everything including Web Development, Mobile App Development, Data Analytics and Design.
The collective work-experience of 80+ yrs. spans across multiple industries like Manufacturing, IT, Sales, Finance, Marketing, Operations, HR and functional domains such as Office suites, Mobile applications, Digital Marketing, Database Technologies, Networking).