The Ultimate Excel Programmer Course
About This Course
Teach Excel to Do Your Work FOR YOU. . .
Microsoft Office is everywhere, installed on over 750 million computers, but most users only know how to set up a basic table or maybe even do a few formulas here and there.
In my course, I teach you how to take Excel by the horns and make it do whatever you want, whenever you want. It can go through loads of information and create a printable report for you. You can make custom forms so that you can access, analyze, edit, or add new information quickly to your data tables/ worksheets.
Excel programming utilizes a simple but effective tool called “VBA” – the hidden programming language that runs quietly in the background while you work. It’s very easy and straight-forward to use.
I’ll show you the easiest tricks to learn this basic language in a fun, progressive method. Learn at your own pace. With each of my short, info-packed lectures, you’ll learn another essential skill that you can immediately use. You’ll find yourself handling these Automation tools instantly and in any spreadsheet you already use every day. If there’s one thing I’m good at – and my students are good at – it’s AUTOMATION.
My motto is, “If I’m not making everybody’s job easier, quicker and more enjoyable, I don’t deserve to have this job” – and that’s what I live by.
Take this course and access your true potential.
Oh, and I want to be the first to hear about your New Raise you get once you’re making Excel Programs and running everything on autopilot for your co-workers!
What are the requirements?
- You should already have Microsoft Excel (version 2003, 2007, 2010 or 2013). Obviously the newer the better, but VBA works the same in all versions. It’s just that newer versions have more options, thus more VBA Commands to control those options.
- Very very basic Excel skills needed for this course (like opening the program, how to click on cells and type things.) You really don’t need any Excel experience, because I’ll take you from Start to Finish in this course. I was just trying to be funny.
What am I going to get from this course?
- Automate and Customize data entry forms
- Choose the right Loop for each task
- Master the CELLS and RANGE objects in multiple scenarios
- Create multiple Variable styles to match your need
- Customize your VBA Editor and Understand all the Toolbars and options
- Debug and Troubleshoot code like a boss!
- Record, Modify or Write Macros from scratch
- Make Custom Formulas/Functions on the fly
- Breeze through IF THEN statements and conquer all the Logical Operators
- Batch out inter-active MessageBoxes, InputBoxes and give users CHOICES!
- Generate Basic Reports that can be printed
- Add filters to report menus to narrow the records
- Take control of forms, Buttons, Drop-down menus, Checkboxes and option buttons AND so much more. . .
- Trigger code from a number of different methods – from Clicking on a cell – to De-Selecting a worksheet.
- Set up Special Commands when a workbook: Opens or closes, is selected, any cell or certain cells are selected, right before printing, etc. . .
- Manipulate Userforms for data entry, report generation, editing tables/databases – ALL within your control. Restrict the flow of data OR make the Userform(s) responsive, calculating, INTUITIVE.
- Streamline your work and the work of others.
- Put Excel ON AUTOPILOT. . .
What is the target audience?
- This Excel course is for anyone who wants to learn Automation secrets in Excel VBA. It’s for complete newbies and/or students looking for a refresher or Reference tool, to pick and choose relevant lessons for their projects. No prior programming knowledge is needed.
- This course is probably not for you if you’re looking to learn super advanced report authoring, which will be covered in a future course specifically on this topic. We will, however, make sure you know how to make good solid reports from your data and many tricks to make them look good.
- I don’t think Office 365 has programming abilities with VBA as of yet, so be wary of this if you only use Office 365. They may release that as a feature in the future, but we’re not there yet.
- If you’re using Excel for Mac, you will find that this course won’t cover all the various differences in Excel Mac, as this course was filmed using Excel for PC. Some Mac users have taken this course, but it’s best with Excel for PC.
|Section 1: The Range Object|
Intro and Course Workbooks download
Get the Developer Tab and Intro to Developer Tab
How to Leave a Udemy Review
Getting started with Range object
Creating Your First Macro From Scratch
Adding a Secondary Command. . .
Debugging in Excel VBA
Saving a Macro-Enabled Workbook (.xlsm)
Several Ways to trigger your Macro
Affecting Multiple Cells with One Range Command – 2 Methods
Adding Strings of Text to a Cell
Using a Named Range with the Range Object
Range Object Quiz
|Section 2: Range Properties|
.ROW and .COLUMN Properties
FONT.BOLD, UNDERLINE or ITALIC Properties
|Section 3: The Cells Object|
The Cells Object
Using Column Letters instead of Numbers
Using Cells Positionally within a Range
Affecting ALL Cells in a Worksheet
Using Range Object with Cells Object
|Section 4: Variables|
Intro to Variables
Declaring Variables – Variable Types
Declaring Variables – Types Demo
Calling a Procedure from Another
Public and Private Variables
BONUS! Passing Variables to Another Procedure – ByVal and ByRef
|Section 5: Toolbars and Menus|
File Menu walkthrough
Edit Menu walkthrough
View Menu walkthrough
View Menu2 – Watch Window, Locals Window and Immediate Window
Watch Window Wonders!
Insert and Format Menus
Debug Menu (Important!)
Run and Tools Menus
|Section 6: Super Important Tools and Excel Logic|
Determining the Last Row in your Data Set
Determining the Last Column in your Data Set
Determining the Next Row in your Data Set
Recording a Macro
BONUS! – Dynamic Sorting!
Absolute vs Relative References
Using With and End With
Comparison Operators in Excel VBA
If Then Statement
Using Is Not Equal To (<>)
Logical Operator, NOT
If, Then, Else Statement
If, Then, ElseIF Statement. . .
Comparitive Operators with Text and Numbers!
Use If Then Statements in a One-Liner of code
Using GOTO and Labels
Select Case as Alternative to IF THEN Statements
Message Box with Yes and No Buttons
Relative Positioning using Offset
BONUS – User Defined Functions 101
BONUS – UDF Lesson 2
Super Important Info Quiz
|Section 7: Loops and Report Writing Basics|
Intro to Loops; the For Next Loop
Fun with our For Next Loop
Beginning our first Report using Loops
Using an InputBox
Adding InputBox to Our First Report to make it Dynamic
Add a Button to Open Our Report
Add a Cool Looking Button or Image to Open Report
Our First Printable Report part 1 – Declaring and Setting the Sheets
Printable Report Part 2 – Getting Items On Report Sheet
Printable Report Part 3 – Clearing Last Report
Printable Report Part 4 – Ensuring Visibility and Autoselect Report Sheet
Using PrintPreview Automatically
Using PrintOut to Send Directly to Default Printer
Handling Debug Error When Cancelling InputBox
For Loop Going Backwards using STEP
The FOR EACH Loop
FOR EACH Loop – Practical Examples
Intro to DO LOOPS
EXIT DO – Multiple Exits using your own Criteria along the Way!
Loops and Reports Quiz
|Section 8: EVENTS: Worksheet Events made Easy|
Intro to Worksheet Events and Selection_Change
Worksheet Activate Event
Worksheet Deactivate Event
BeforeDelete Event – for Worksheet Deletion
BeforeDoubleClick Event – Trigger a Macro when Double Clicking
BeforeRightClick Event – Trigger a Macro when Right-Clicking
How to Disable Events on the Workbook
Intro to Change Event
Change Event with EnableEvents toggle – Avoid endless loop!
Change Event Triggered with Custom Range Using Intersect
Final Thoughts on Worksheet Events and Review Other Events
Worksheet Events Quiz
|Section 9: WorkBook Events|
Delete, DoubleClick, RightClick, Calculate, Change
|Section 10: Activex Controls on Worksheets|
Activex Controls vs Forms Controls
Button Click – Wage Calculator
BONUS: Capturing Shift, Alt, Ctrl or some combination using the Shift variable
MouseMove Event with Application.Statusbar – fun and useful
MouseUp and MouseDown Events – Left, Middle, & Rightclick, Shift, Alt & Ctrl
GotFocus and LostFocus Events
Use a Checkbox for its VALUE
Checkbox Event Without a Button
LinkedCell Property with a Checkbox
Option Buttons Intro – What is an Option Button and Grouping
Grouping, Alignment and Aesthetics for Controls
Homemade Quiz using Option Buttons and a Command Button
Compliance Checker for your Forms or Quizzes Part 1
Compliance check part 2
SpinButton with Cell A1
SpinButtons with Textboxes and Numbers
SpinButton BONUS – Move Textbox Position with SpinButton
SpinButton with Dates in a Textbox
Activex “Label” Control – Basics
ComboBox – Fill with ListFillRange
Creating a Dynamic Range
ComboBox – How to use Multiple Columns
Using .ADDITEM to Get Specific items in your Combobox List
Noteworthy Properties of a Combobox
Multiple Columns in Combobox with .ADDITEM and List, ListCount etc
ListBox Basics using Dynamic Range
Listbox Click event and Dynamic Options
BONUS – Manipulating and Using Multiselect Items in a Listbox
ListBox – Referencing Column 0 and 1 in a cell Dynamically!
Dynamic ListBoxes Using .ADDITEM and More
Make and Utilize INVISIBLE Columns – Special Trick!
Using a Scroll Bar on a worksheet
Pictures on a Worksheet, plus using FollowHyperlink Method
A Brief Note on Alternate, 3rd Party Controls
|Section 11: Userforms – the best thing. EVER.|
Userform – Hide, Show and Unload
Sample Database and Creating a Data Entry Form
Fill Status combobox with Rowsource when Userform starts – 2 methods
New Employee Data Entry Form!
Format Textbox Numbers as Numerals, Clearing out Form upon Submission
Set the Tab Order for Userform Controls
Set Default Values to Make Data Entry Easier
Avoid Duplicate Employee ID and other Fun Automation Tricks
Edit Mode for Employee Database – Part 1
Edit Mode for Employee Database – Part 2
Edit Mode Pt 3 – Adding Automation and Save Button
Userform Report with Filters – Part 1
Userform Report with Filters – Part 2
Applying the Dynamic Filters to Reports
Clear out Last Report
Report Generation and Aesthetics, Headers, Footers, Repeat Rows etc.
Concluding Thoughts. . .
|Section 12: BONUS Section – VBA Functions|
InStr Function – Search for Text Within Other Text
The EVALUATE Function – Mac-Daddy of all functions
Sum Function in Excel VBA – EASY!
Count Function in VBA
Hi! My name is Daniel and i’m 29. I’ve lived most my life in Scott City, Missouri, United States. I’ve been using Excel since my early teens, and very early on realized that I absolutely love it. After working on various projects in my spare time for years, improving the lives of those that used my programs, I knew this was more than just a hobby for me; it’s my passion. A few years back, I got my first opportunity to program in Excel, but when I landed the job I was asked to re-write my predecessor’s coded programs with fancy databases and automated Crystal reports! I found my Excel skills somewhat lacking. I HAD TO LEARN VBA! I studied and studied what I could online and in books, but I wasn’t able to learn much that way. I had to dive into my first project for months and months – basically just teaching myself and “figuring it out”. I realized that if only someone was out there teaching Excel VBA like Mike Gervin (ExcelIsFun) taught Excel, making everything simple and visual – then Anyone could learn VBA! I started a YouTube channel, mostly for myself as a reference tool, but people started to bite. People flooded my inbox with questions and comments that I was literally changing their lives for the better. My passion is and always has been teaching, and I’ve turned thousands of people into Excel programmers. Now, with Udemy, I’m going to teach the good stuff – the stuff I kept secret; the Advanced and “not-so-Advanced-but-Awesome” Excel VBA. The things that make people hire you – that make you irresistable and irreplacable. Building REAL Programs, Advanced Reporting and Data Analysis. Dashboards and live data. Automation. My main focus is to create an online community of professional Excel VBA developers and beginners who create Real Programs, solve Real Problems and change Real Lives.
- Lectures 0
- Quizzes 0
- Duration 50 hours
- Skill level All level
- Language English
- Students 33080
- Assessments Self