Tech & IT
Business
Coding & Developer
Finance & Accounting
Academics
Office Applications
Art & Design
Marketing
Health & Wellness
Sounds & Music
Lifestyle
Photography
This plan includes
- Instant access to 11,000+ online courses
- Play & Pause Course Videos
HD Video Recorded Lectures- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Instructor Chat Support
- Cancel Plan Anytime
This plan includes
- Instant access to 11,000+ online courses
- Play & Pause Course Videos
- HD Video Recorded Lectures
- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Instructor Chat Support
- Cancel Plan Anytime
What you'll learn?
- Register and reuse VBA macro in Excel
- Create your own VBA macros in Excel
- Understand the VBA code in Excel macros
- Generate automated reports in Excel with VBA
- Manipulate data in Excel with VBA
- Format data and worksheets in Excel with VBA
- Import and export external data from files to and from Excel using VBA
- Understand and use the VBA editor in Excel to write macros
- Understand the concepts of subroutines, functions, constants and variables in VBA for Excel
- Manage errors in VBA
- Understand and use dialog boxes e.g. msgbox and inputbox
- create interactions between worksheets in Excel using VBA
Course Overview
I created this corse since I have experienced during my live trainings and even from my students of other VBA courses that I released on the Internet, that most of my students are not IT-skilled people. They are just users of Excel and want to go beyond the basic functionalities of Excel and create automated report or automate daily tasks.
For this reason I have created a course with the right pace to make it clear to anyone.
In this course I start explaining what VBA is and why VBA. For example, if you are an user of Excel, e.g. in the Accounting Department, what VBA is and why you need it.
For this reason it is important to understand first of all the concept of object oriented programming and explain how Excel is an application structured in objects whose properties and actions/events can be managed, manipulated and updated using VBA.
I start with an overview of the Developer ribbon, which is the menu section where to record macros, execute macros, access the VBA editor and much more.
I will show and explain the structure of a VBA object and how to record a VBA macro.
Once recorded, I will explain how to read and interpret a VBA code and how to modify it for your goals.
I will show how to make the user interact with the data in the worksheets using userforms and how to format the reports automatically.
I have organized this course focusing on an example which is an Excel file or workbook composed of more worksheets.
At the end the user will be able to exchange data between the worksheets and import/export them to external text and binary files.
In details:
Begin with VBA: why VBA, understand the VBA editor, learn how to record and execute a VBA macro, learn to read and interpret the VBA code, modify the VBA code, modify the macro security level.
Understand the VBA code: the Excel objects and how and where to manage/edit their properties and actions/methods based on their events
The VBA project: what is a VBA project, how it is structure, the components, where to insert your code, how to set the properties of the objects
The VBA programming principles: variables, constants, functions and subroutines. What is the difference between private and public. What is a module. What are the variable types.
Interact with the data: read/retrieve data with loop instructions. Select data using conditional instructions. Strings and string functions.
Interact with the user: exchange data and information using userforms, msgboxes and inputboxes. Understand the VBA control objects on userforms.
VBA commands on worksheets: interact directly on a worksheet using control objects and directly on it and how to trigger actions and events.
Structure of data: understand arrays and their size, how to populate arrays and manage their size. The Type data structure to save data into records. Collections.
Manage external files with VBA: read and write to text and binary files.
Errors: manage errors caused by the interection of the user with Excel.
At the end you will find some exercise to improve your skill and verify your knowledge in VBA.
Pre-requisites
- basic knowledge of Microsoft Office products
- be familiar with computers
Target Audience
- familiarity with the function and basic use of the software
- Who is familiar with computers and the Internet
- familiarity with Windows or Mac computers
- Who owns a copy of Microsoft Office with Excel
Curriculum 66 Lectures 04:48:51
-
Section 1 : Begin with VBA
- Lecture 2 :
- Introduction
- Lecture 3 :
- The prerequisites: the developer tab / ribbon and the Excel file format
- Lecture 4 :
- The VBA Editor: VBE
- Lecture 5 :
- The concept of Macro
- Lecture 6 :
- Record a macro
- Lecture 7 :
- Macro Security
- Lecture 8 :
- Understand the recorded VBA code
- Lecture 9 :
- Modify the code of a recorded macro
-
Section 2 : Understand the VBA Code and the Excel Object Hierarchy
- Lecture 1 :
- The Workbook object
- Lecture 2 :
- The Worksheet object
- Lecture 3 :
- The Cell and Range objects
-
Section 3 : The VBA Project
- Lecture 1 :
- The VBA Project structure and composition
- Lecture 2 :
- The Worksheet Module Code
- Lecture 3 :
- The Worksheet Properties
- Lecture 4 :
- The Worksheet Methods
- Lecture 5 :
- The General variables and subroutines
- Lecture 6 :
- The Workbook Methods
-
Section 4 : The VBA Programming Principles
- Lecture 1 :
- Variables and Constants
- Lecture 2 :
- Subroutines and Functions
- Lecture 3 :
- User Defined Functions
- Lecture 4 :
- VBA Modules
- Lecture 5 :
- The Worksheet Module
- Lecture 6 :
- Public and Private Subroutines and Functions
- Lecture 7 :
- Public and Private Variables
- Lecture 8 :
- Variable Types and Declarations
-
Section 5 : Interact with the data
- Lecture 1 :
- The Loop For .. Next and For each ...Next
- Lecture 2 :
- The Loop While
- Lecture 3 :
- The Loop Do Until
- Lecture 4 :
- The Conditional Instructions: If, Else, ElseIF
- Lecture 5 :
- The Conditional Instructions: Select Case
- Lecture 6 :
- Strings and String Functions
-
Section 6 : Interact with the User
- Lecture 1 :
- The UserForms
- Lecture 2 :
- Build a custom UserForm
- Lecture 3 :
- Customize the events and properties of commands in a UserForm
- Lecture 4 :
- Control the data in textboxes in a UserForm
- Lecture 5 :
- Customize the properties and methods of button controls
- Lecture 6 :
- Dynamically populate a combobox
- Lecture 7 :
- Exchange Data between UserForms and Worksheets
- Lecture 8 :
- MsgBox: Customize your messages and warnings for the User
- Lecture 9 :
- MsgBox: Customize the buttons of your message dialog
- Lecture 10 :
- MsgBox and InputBox: Manage User Queries
-
Section 7 : VBA commands on Worksheets
- Lecture 1 :
- Trigger Actions on Worksheets with buttons
- Lecture 2 :
- DropDowns on Worksheets to select pre-options
- Lecture 3 :
- Trigger actions with VBA controls on a Worksheet
- Lecture 4 :
- Filter data using VBA controls on a Worksheet (Part I)
- Lecture 5 :
- Filter data using VBA controls on a Worksheet (Part II)
- Lecture 6 :
- Improve the performances of subroutines of VBA controls on a Worksheet
-
Section 8 : Structure of Data
- Lecture 1 :
- Arrays: fixed size Arrays
- Lecture 2 :
- Arrays: Array size
- Lecture 3 :
- Arrays: variable size Arrays
- Lecture 4 :
- Collections
- Lecture 5 :
- The Type Data Structure
-
Section 9 : Manage external files with VBA
- Lecture 1 :
- Write to a text file
- Lecture 2 :
- Read from a text file
- Lecture 3 :
- Write to a binary file
- Lecture 4 :
- Read from a binary file
-
Section 10 : Errors
- Lecture 1 :
- Manage errors
- Lecture 2 :
- Ignore errors
-
Section 11 : Conclusions
- Lecture 1 :
- Final Example: prepare the routines
- Lecture 2 :
- Final Example: view and format the results
- Lecture 3 :
- Exercise: Format a given Excel table
- Lecture 4 :
- Exercise: Retrieve the common elements
- Lecture 5 :
- Exercise: Merge Workbooks
- Lecture 6 :
- Exercise: Save data into a CSV file
- Lecture 7 :
- Exercise: Create and find lists
Our learners work at
Frequently Asked Questions
-
How do i access the course after purchase?
It's simple. When you sign up, you'll immediately have unlimited viewing of thousands of expert courses, paths to guide your learning, tools to measure your skills and hands-on resources like exercise files. There’s no limit on what you can learn and you can cancel at any time. -
Are these video based online self-learning courses?
Yes. All of the courses comes with online video based lectures created by certified instructors. Instructors have crafted these courses with a blend of high quality interactive videos, lectures, quizzes & real world projects to give you an indepth knowledge about the topic. -
Can i play & pause the course as per my convenience?
Yes absolutely & thats one of the advantage of self-paced courses. You can anytime pause or resume the course & come back & forth from one lecture to another lecture, play the videos mulitple times & so on. -
How do i contact the instructor for any doubts or questions?
Most of these courses have general questions & answers already covered within the course lectures. However, if you need any further help from the instructor, you can use the inbuilt Chat with Instructor option to send a message to an instructor & they will reply you within 24 hours. You can ask as many questions as you want. -
Do i need a pc to access the course or can i do it on mobile & tablet as well?
Brilliant question? Isn't it? You can access the courses on any device like PC, Mobile, Tablet & even on a smart tv. For mobile & a tablet you can download the Learnfly android or an iOS app. If mobile app is not available in your country, you can access the course directly by visting our website, its fully mobile friendly. -
Do i get any certificate for the courses?
Yes. Once you complete any course on our platform along with provided assessments by the instructor, you will be eligble to get certificate of course completion.
-
For how long can i access my course on the platform?
You require an active subscription to access courses on our platform. If your subscription is active, you can access any course on our platform with no restrictions. -
Is there any free trial?
Currently, we do not offer any free trial. -
Can i cancel anytime?
Yes, you can cancel your subscription at any time. Your subscription will auto-renew until you cancel, but why would you want to?
Instructor
99045 Course Views
2 Courses
