Visual Basic for Applications - Excel VBA - The full course
- Descrição
- Currículo
- FAQ
- Revisões
The course is composed of more than 200 videos. Each lesson is composed of one of more videos. The videos are created by experts developing applications using Visual Basic for Application (VBA) for MS Excel or MS Access for small, middle and large companies as consultants and as managers and coach for internal processes. These experts have created complex tools to create the reporting structure and manage the business processes. An extended version will include also real examples to be used directly in working and office processes.
You will learn from the basic concepts to the principles to build your own applications in Excel using VBA.
You will understand and digest the concept of object oriented programming and remove your barriers from being scared to see programming code.
You will be able to retrieve data from databases or other files and present them in Excel files.
You will create automated procedure to format data, display information and create complex reports and graphs.
-
1
Welcome to the Course - IntroductionIntroduction the VBA - Visual Basic for Applications - The Complete Course
-
2
Introduction
Overview of Visual Basic for Applications (VBA):
What is Visual Basic for Applications (VBA)
VBA and Microsoft Excel
What is Object Oriented Programming in VBA
What are the Objects in Excel and how to refer to them via VBA
-
3
The power of Excel with VBA, The VBA code, Recording, Saving and Running Macros
Introduction to Visual Basic for Applications (VBA)
How to access and use VBA in Microsoft Excel
How to record a macro in VBA
How to run a macro in VBA
How to edit a macro in VBA at your needs
-
4
Overview of the Excel Application as a Tool (Version 2013)
Overview of Excel version 2013
-
5
Macro Security
-
6
Protect your work
How to protect a Worksheet in Excel
How to protect a Worksheet in Excel and make some cells only scrollable and/or selectable
How to protect a VBA Project from being viewed
-
7
VBE – The VBA editor, VBA toolbar (Part I)
How to access the VBA Editor or VBE
The property window in VBE
The List of Projects and Modules in VBA
Where to find Forms and Modules in VBE
How to add a Form or Module in VBA
-
8
VBE – The VBA editor, VBA toolbar (Part II)
The VBE Menu
The VBA Toolbar
Where to find the commands and objects for Forms in VBE
How to edit the content of a Form in VBE
How to edit the code of a Form in VBE
Write your code in VBE
-
9
VBE – The VBA editor, VBA toolbar - Version 2013
Overview of the VBE in the version 2013
-
10
The Developer Ribbon
Overview of the Developer Ribbon in Excel version 2013
-
11
Understanding the code, Remove the concept “VBA is HARD!”, The Debugging tool
How to write your code in Visual Basic for Applications (VBA)
How to assign VBA code to Excel Objects
The Steps to write a simple VBA code
-
12
VBE to analyze recorded macros, Objects, Methods, Properties, Code cleaning and
What is an Object in Visual Basic for Applications (VBA)
What are Methods, Events and Properties of an object in Visual Basic for Applications (VBA)
Analyze a recorded macro in Visual Basic for Applications (VBA)
-
13
Private and Public functions and subroutines, variables by val and optional vari
What is a Function in Visual Basic for Applications (VBA)
What is a Sub or Subroutine in Visual Basic for Applications (VBA)
Public and Private Functions and Sub in Visual Basic for Applications (VBA)
-
14
Functions, subroutines, constants and variables
The difference between a Function and a Sub in Visual Basic for Applications (VBA)
Variables in Visual Basic for Applications (VBA)
Constants in Visual Basic for Applications (VBA)
-
15
Recap on Functions and SubroutinesIn thie quiz you will evaluate your knowledge about the difference between functions and subroutines and their properties
-
16
Variables, Private and Public variables
Type of variables in Visual Basic for Applications (VBA)
Private and Public variables in Visual Basic for Applications (VBA)
-
17
Recap on Variable and Constants
What is the difference between variables and constants
-
18
Declare functions
Function declarations in Visual Basic for Applications (VBA)
-
19
Options: Explicit, Implicit
Explicit or Implicit variables in Visual Basic for Applications (VBA)
Explicit and Implicit declarations in Visual Basic for Applications (VBA)
-
20
Declare variables, declare functions in libraries, Types
How to declare a variable in Visual Basic for Applications (VBA)
How to declare a function in a library in Visual Basic for Applications (VBA)
What is a Type in Visual Basic for Applications (VBA)
How to declare Types in Visual Basic for Applications (VBA)
-
21
Dimension variables (Dim, Redim, Redim Preserve)
Define the dimension of a variable in Visual Basic for Applications (VBA)
Re-define the dimension of a variable at runtime in Visual Basic for Applications (VBA)
Modify the dimension of variables in Visual Basic for Applications (VBA)
-
22
Dimension variables
Verify your knowledge about the size of a variable set
-
23
Objects, Excel Objects (Worksheets as objects, Workbooks as objects)
Details on Objects in Microsoft Excel and how to refer to them in Visual Basic for Applications (VBA)
The Workbook Object in Visual Basic for Applications (VBA)
The Worksheet Object in Visual Basic for Applications (VBA)
The Range and Cells Object in Visual Basic for Applications (VBA)
-
24
Worksheet properties and events
Worksheet properties in Visual Basic for Applications (VBA)
Worksheet events and methods in Visual Basic for Applications (VBA)
-
25
Workbook properties and events
Workbook properties in Visual Basic for Applications (VBA)
Workbook events and methods in Visual Basic for Applications (VBA)
-
26
Cells and Ranges as objects, VBA object in Excel sheets
The Cells objects and their properties in Visual Basic for Applications (VBA)
The Range objects and their properties in Visual Basic for Applications (VBA)
How to refer to Excel cells in Visual Basic for Applications (VBA)
How to define a range in Excel and refer to in Visual Basic for Applications (VBA)
-
27
Assign events to objects in Excel sheets
How to add VBA controls in an Excel worksheet
How to assign events to VBA controls in an Excel Worksheet
-
28
Objects in VBA and Excel
Verify your knowledge in Object Oriented Programming and check if you can distinguish the objects in Excel
-
29
The Range Object
Overview of the Range Object in Visual Basic for Applications (VBA)
How to refer to a range by address in Visual Basic for Applications (VBA)
How to refer to a range by rows and columns in Visual Basic for Applications (VBA)
-
30
Range properties and selection tips, Refer to Ranges in other sheets
The properties of the Range object in Visual Basic for Applications (VBA)
The special range object: the Selection object in Visual Basic for Applications (VBA)
The properties, methods and events of the Selection object in Visual Basic for Applications (VBA)
How to refer to ranges in other worksheets in Visual Basic for Applications (VBA)
-
31
The Offset property, Columns and Rows to refer to RangesHow to refer to elements/cellswithin a range by means of rows and columns in Visual Basic for Applications (VBA)
The Offset property of ranges in Visual Basic for Applications (VBA)
How to use the Offset property to get an element in an Excel range in Visual Basic for Applications (VBA)
-
32
Retrieve the address in ranges, Size and resize ranges
How to retrieve the address of a range object in Visual Basic for Applications (VBA)
How to set the size of a range object in Visual Basic for Applications (VBA)
How to select a range with Visual Basic for Applications (VBA)
-
33
Columns and Rows to refer to Ranges, Join multiple ranges
How to refer to a range or element of a range by row and column in Visual Basic for Applications (VBA)
How to join ranges in Visual Basic for Applications (VBA)
-
34
Merge ranges, select ranges
How to merge ranges in Visual Basic for Applications (VBA)
How to select ranges in Visual Basic for Applications (VBA)
-
35
Intersect ranges
How to intersect ranges in Visual Basic for Applications (VBA)
-
36
IsEmpy function
How to verify if an object is empty in Visual Basic for Applications (VBA)
-
37
CurrentRegion property
The CurrentRegion property in Visual Basic for Applications (VBA)
How to retrieve the current region of an object or range in Visual Basic for Applications (VBA)
-
38
Areas Collection properties
The Area Collection properties in Visual Basic for Applications (VBA)
-
39
Evaluate positions in Excel sheets with the range properties (End, Row, Column)
How to evaluate the position of elements in an Excel worksheet using the range properties in Visual Basic for Applications (VBA)
The End property of an Excel range in Visual Basic for Applications (VBA)
The parameters of the End property of an Excel range in Visual Basic for Applications (VBA)
-
40
identify ranges and cells in the object hierarchy, the value property
identify ranges and cells in the object hierarchy, the value property, define a range with the "set" instruction
-
41
Format ranges – create an automatic routine
How to create a macro to automatically format a range in Visual Basic for Applications (VBA)
How to add borders and colors to a range in Excel using Visual Basic for Applications (VBA)
-
42
Special cells
Special cells in Excel with Visual Basic for Applications (VBA)
The SpecialCells method of Excel ranges in Visual Basic for Applications (VBA)
How to refer to cells of specified types and values in Visual Basic for Applications (VBA)
How to select the cells containing data in Excel using Visual Basic for Applications (VBA)
-
43
Cells and Ranges
-
44
The Msgbox object, Msgbox properties, Predefined Dialogs
How to show messages in Visual Basic for Applications (VBA)
The Msgbox Dialog and object in Visual Basic for Applications (VBA)
-
45
Standard Excel Dialogs, Customize a dialog box
How to manage the standard Excel Dialog forms in Visual Basic for Applications (VBA)
How to customize an Excel dialog box in Visual Basic for Applications (VBA)
-
46
VBA constants, VBA constants for dialogs
The parameters and constants to use to customize the dialog boxes in Excel using Visual Basic for Applications (VBA)
-
47
Combine VBA constants for complex statements in dialogs
How to customize query dialog boxes in Excel using Visual Basic for Applications (VBA)
The SaveAs queries in an Excel Message Dialog box in Visual Basic for Applications (VBA)
How to customize messages and the behavior of the message boxes in Excel using Visual Basic for Applications (VBA)
-
48
The InputBox
How to input data in Excel using the InputBox in Visual Basic for Applications (VBA)
How to customize data entry in Visual Basic for Applications (VBA)
-
49
How to find VBA constants and their references
The VBA Constants in Visual Basic for Applications (VBA)
How to find the VBA constants in Visual Basic for Applications (VBA)
-
50
Dialog Boxes in VBA
You will check your knowledge about customizing dialog boxes in VBA
-
51
User-defined functions, Excel functions in VBA functions
How to manage and customize Excel functions using Visual Basic for Applications (VBA)
Manage Excel User-Defined functions with Visual Basic for Applications (VBA)
-
52
Custom Excel functions, complex functions
How to customize complex and combined Excel functions using Visual Basic for Applications (VBA)
-
53
For..Next Loops
Understand the For..Next Loop in Visual Basic for Applications (VBA)
-
54
Do Loops, While..Wend loops, Do While Loops, Do Until Loops
The Do,Do..While,Do..Until,While..Wend Loops in Visual Basic for Applications (VBA)
-
55
For Each – The VBA loop: loop in objects
Special Loops in Visual Basic for Applications (VBA): the for each loop, the loop for objects
How to refer to elements of object arrays in Visual Basic for Applications (VBA)
-
56
Loops in VBA
-
57
Operators, The MOD instruction, The logical instructions
Operators in Visual Basic for Applications (VBA)
Create Math functions in Visual Basic for Applications (VBA)
Logical Operators in Visual Basic for Applications (VBA)
The MOD Instruction in Visual Basic for Applications (VBA)
-
58
The IF instruction, IF, ELSE, ELSEIF…
Conditional instructions in Visual Basic for Applications (VBA)
The IF condition in Visual Basic for Applications (VBA)
IF..Then..Else...ElseIf in Visual Basic for Applications (VBA)
-
59
With instruction
The With Instruction in Visual Basic for Applications (VBA)
-
60
Instruction to interrupt the execution of the code
How to interrupt the execution of a macro in Visual Basic for Applications (VBA)
-
61
Select case instruction
The select case instruction in Visual Basic for Applications (VBA). An alternative to the If instruction
-
62
Conditional Instructions
You will verify your knowledge of conditional instructions inVBA
-
63
Formulas, R1C1 style formulas, Display R1C1 style formulas in Excel,
How to customize Excel formulas for ranges and cells with Visual Basic for Applications (VBA)
-
64
Retrieve ranges in Excel objects
How to get ranges in Excel worksheets using Visual Basic for Applications (VBA)
-
65
Conditional formatting of R1C1 formulas
How to implement the Excel Conditional Formatting instruction with Visual Basic for Applications (VBA)
-
66
Range properties to be used to retrieve information, The R1C1 reference style
How to get information about Excel ranges in Visual Basic for Applications (VBA) based on the properties of the range object
-
67
Excel Formulas
You will verify your knowledge about how to manage and customize Excel fomulas with VBA
-
68
Events and properties in worksheets, workbooks and charts
Understand the events and methods for Excel worksheets and workbooks and customize them in Visual Basic for Applications (VBA)
-
69
The Workbook object
The events of the Workbook object in Visual Basic for Applications (VBA)
-
70
The Chart object
How to customize an Excel Chart by manipulating the Chart object in Visual Basic for Applications (VBA)
-
71
The Worksheet object, Default events (used and not used)
Understand the default events of the Excel worksheet object in Visual Basic for Applications (VBA)
-
72
Custom events and properties within Excel objects
Understand the default events of Excel objects
Understand the properties of Excel objects
Manipulate events and properties of Excel object with Visual Basic for Applications (VBA)
-
73
The Application object
What is the Application object in Visual Basic for Applications (VBA)
The properties and events of the Application objects in Visual Basic for Applications (VBA)
-
74
Excel Objects events and properties
You will verify your knowledge about the default properties and events of the main Excel objects: Workbook, Worksheet, Chart and the Application object
