Excel VBA Essentials: Automate with Macros

About Course

In this course you will learn over 50 Microsoft Excel VBA and macro examples. These are real examples that have worked in practice for many years

Writing VBA code correctly is the hardest part. We will learn to write clear and concise macros using the most effective methods for solving problems

Creation of 3 full-fledged tools using Excel VBA. Step by step we will analyze all aspects of programming from the idea to the final product.

The great thing about this course is its simple explanation. We will progress from simple to complex, starting with the basics and ending with advanced VBA tools

The course includes high quality videos and various techniques to keep you interested

You will also have a large number of ideas for automating your tasks. The explanation of writing macros is based on examples of writing interesting calculators (break-even point calculation, loan calculator…), which will also increase your awareness of financial instruments.

Improve your current Excel files and automate your work with VBA
Use the included Excel VBA example files in your work
Simplify work routines
Excel formulas and basic functions can't do everything. Use VBA when you reach your limits
Master Excel VBA and macros
Reduce manual work to just one click of a button
Create simple solutions to complex problems by augmenting Excel functions with macros
Customize VBA code found on the Internet to suit your needs

Course Content

Introduction

Introduction
DOWNLOAD: Working course files

Beginning of work

Activating the Developer tab
Cells and ranges
Creating your first macro
Add text to a cell
Using a named range
Debugging process
Saving a workbook
Adding a button
Record a macro
Exercise 2 – Assignment
Exercise 2 – Solution
Test 1 – Object Range

Range Properties

Range.Value property
Range.Text property
Range.Row and Range.Column property
Range.Select property
Range.Count property
Exercise 3.1 – Assignment
Exercise 3.1 – Solution
Range.Address property
Range.Formula property
Range.NumberFormat property
Property Range.Font.Bold, Italic, Underline
Exercise 3.2 – Assignment
Exercise 3.2 – Solution
Test 2 – Range Properties

Cell properties

Variables

Important Tools

Absolute and relative cells
With … End With statement
Comparison Operators
If…Then Statement
Using the NOT EQUAL operator
Using the Boolean Not Operator
Exercise 6.1 – Assignment
Exercise 6.1 – Solution
If Then ElseIf Construction
Comparison operators with text and numbers
If … Then construction in one line
Using Labels and the GoTo Command
Select Case Design
Message box with YES and NO buttons
Exercise 6.2 – Assignment
Exercise 6.2 – Solution
Using the Offset function
Test 5 – Important Tools

Basics of cycles and reports

Basics of cycles. Loop For…Next For
For…Next For loop addition
Making our first report using a loop
Using InputBox
Adding an InputBox to a Report
Adding a button to run a report
Adding a beautiful button
Print report part 1 – Declaring or setting up sheets
Report for printing part 2 – Transferring data to the report sheet
Print report part 3 – Clearing the last report
Printable report part 4 – Automatic display of the report sheet
Exercise 7.1 – Assignment
Exercise 7.1 – Solution
Automatic use of Preview
Sending for printing
Fixing an error when clicking Cancel buttons
Using a step in a For…Next For loop
Loop For Each…Next
For Each … Next Loop – Practical Examples
Exercise 7.2 – Assignment
Exercise 7.2 – Solution
Introduction to Do… Loop
Loop Do…Until
Loop…Until
Do…While Loop
Loop…While
Exit Do – Using a criterion to exit a loop
Test 6 – Basics of Loops and Reports

Worksheet Events

Introduction to Worksheet Events and the Worksheet_SelectionChange Event
Worksheet_Activate event
Worksheet_Deactivate event
Worksheet_BeforeDelete event
Worksheet_BeforeDoubleClick event
Worksheet_BeforeRightClick event
Calculate event
Exercise 8.1 – Assignment
Exercise 8.1 – Solution
How to disable workbook events
Introduction to the Change event
Change event with EnableEvents command – avoiding infinite loop
Fire a Change event on a custom range (Intersect method)
FollowHyperlinks event
Review of remaining events
Exercise 8.2 – Assignment
Exercise 8.2 – Solution
Test 7 – Worksheet Events

Workbook Events

Workbook_Open event
Workbook_Activate event
Workbook_BeforeSave event
Workbook_AfterSave event
Workbook_BeforeClose event
Workbook_BeforePrint() event
Workbook_Deactivate event
Workbook_NewChart event
Workbook_NewSheet event
Exercise 9 – Assignment
Exercise 9 – Solution
Workbook_SheetActivate event
Events SheetBeforeDelete, SheetBeforeDoubleClick, SheetBeforeRightClick, Sh

ActiveX controls

Form controls vs. ActiveX
Control element – Button
Keydown event
Keypress Event
MouseMove event
MouseDown and MouseUp events
GotFocus and LostFocus events
CheckBox element
LinkedCell property of the CheckBox element
OptionButton element
Creating a Test Using OptionButton and CommandButton
Checking whether a group of radio buttons is filled – method 1
Checking whether a group of switches is full – method 2
SpinButton element
Setting the Color Scale Using SpinButton
Label element
ComboBox element
Creating a dynamic range
Creating multiple columns of a ComboBox element
ComboBox element – using the AddItem parameter
Creating Multiple Columns Using AddItem and List
ListBox Element – Basics
LIstBox element – LinkedCell and ListIndex properties
Creating a Dynamic List Using the AddItem Command
Hiding List Item Columns
ScrollBar Element – Basics
Building a break-even analysis using scroll bars
Picture element with FollowHyperlink method
Toggle Button Element

Forms of management

Control forms – launch, hide and close
Sample database and creation of a data entry form
Filling data for the ComboBox element
Data entry form
Formatting text into numbers. Clearing the form
Changing the navigation order using TabIndex
Setting up required fields and duplicate ID entries
Database Editing Mode – Part 1
Database editing mode – part 2
Database editing mode – part 3

Student Ratings & Reviews

No Review Yet