English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 10 Hours | 0.98 GB
Learn Excel VBA from scratch with Dan Strong! I’ve trained over 50K students on YouTube, check it out!
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!
Table of Contents
The Range Object
1 Intro and Course Workbooks download
2 Get the Developer Tab and Intro to Developer Tab
3 Getting started with Range object
4 Creating Your First Macro From Scratch
5 Adding a Secondary Command. . .
6 Debugging in Excel VBA
7 Saving a Macro-Enabled Workbook .xlsm
8 Several Ways to trigger your Macro
9 Affecting Multiple Cells with One Range Command – 2 Methods
10 Adding Strings of Text to a Cell
11 Using a Named Range with the Range Object
Range Properties
12 .VALUE Property
13 .TEXT Property
14 .ROW and .COLUMN Properties
15 .SELECT Property
16 .COUNT Property
17 .ADDRESS Property
18 .FORMULA Property
19 .NUMBERFORMAT Property
20 FONT.BOLD UNDERLINE or ITALIC Properties
The Cells Object
21 The Cells Object
22 Using Column Letters instead of Numbers
23 Using Cells Positionally within a Range
24 Affecting ALL Cells in a Worksheet
25 Using Range Object with Cells Object
Variables
26 Intro to Variables
27 Declaring Variables – Variable Types
28 Declaring Variables – Types Demo
29 Calling a Procedure from Another
30 Public and Private Variables
31 Using Constants
32 BONUS Passing Variables to Another Procedure – ByVal and ByRef
Toolbars and Menus
33 File Menu walkthrough
34 Edit Menu walkthrough
35 View Menu walkthrough
36 View Menu2 – Watch Window Locals Window and Immediate Window
37 Watch Window Wonders
38 Insert and Format Menus
39 Debug Menu Important
40 Run and Tools Menus
Super Important Tools and Excel Logic
41 Determining the Last Row in your Data Set
42 Determining the Last Column in your Data Set
43 Determining the Next Row in your Data Set
44 Recording a Macro
45 BONUS – Dynamic Sorting
46 Absolute vs Relative References
47 Using With and End With
48 Comparison Operators in Excel VBA
49 If Then Statement
50 Using Is Not Equal To
51 Logical Operator NOT
52 If Then Else Statement
53 If Then ElseIF Statement. . .
54 Comparitive Operators with Text and Numbers
55 Use If Then Statements in a One-Liner of code
56 Using GOTO and Labels
57 Select Case as Alternative to IF THEN Statements
58 Message Box with Yes and No Buttons
59 Relative Positioning using Offset
60 BONUS – User Defined Functions 101
61 BONUS – UDF Lesson 2
Loops and Report Writing Basics
62 Intro to Loops the For Next Loop
63 Fun with our For Next Loop
64 Beginning our first Report using Loops
65 Using an InputBox
66 Adding InputBox to Our First Report to make it Dynamic
67 Add a Button to Open Our Report
68 Add a Cool Looking Button or Image to Open Report
69 Our First Printable Report part 1 – Declaring and Setting the Sheets
70 Printable Report Part 2 – Getting Items On Report Sheet
71 Printable Report Part 3 – Clearing Last Report
72 Printable Report Part 4 – Ensuring Visibility and Autoselect Report Sheet
73 Using PrintPreview Automatically
74 Using PrintOut to Send Directly to Default Printer
75 Handling Debug Error When Cancelling InputBox
76 For Loop Going Backwards using STEP
77 The FOR EACH Loop
78 FOR EACH Loop – Practical Examples
79 Intro to DO LOOPS
80 DO Until
81 Loop Until
82 DO While
83 Loop While
84 EXIT DO – Multiple Exits using your own Criteria along the Way
EVENTS Worksheet Events made Easy
85 Intro to Worksheet Events and Selection_Change
86 Worksheet Activate Event
87 Worksheet Deactivate Event
88 BeforeDelete Event – for Worksheet Deletion
89 BeforeDoubleClick Event – Trigger a Macro when Double Clicking
90 BeforeRightClick Event – Trigger a Macro when Right-Clicking
91 Calculate Event
92 How to Disable Events on the Workbook
93 Intro to Change Event
94 Change Event with EnableEvents toggle – Avoid endless loop
95 Change Event Triggered with Custom Range Using Intersect
96 FollowHyperlinks Event
97 Final Thoughts on Worksheet Events and Review Other Events
98 Events Exercise 1
WorkBook Events
99 Workbook Open
100 Workbook Activate
101 Before Save
102 After Save
103 Before Close
104 Before Print
105 Deactivate
106 NewChart
107 NewSheet
108 Sheet Activate
109 Delete DoubleClick RightClick Calculate Change
110 SheetDeactivate
111 Follow Hyperlink
112 SheetSelectionChange
113 WindowActivate
114 WindowDeactivate
115 WindowResize
Activex Controls on Worksheets
116 Activex Controls vs Forms Controls
117 Button Click – Wage Calculator
118 Keydown Event
119 BONUS Capturing Shift Alt Ctrl or some combination using the Shift variable
120 KeyPress Event
121 MouseMove Event
122 MouseMove Event with Application.Statusbar – fun and useful
123 MouseUp and MouseDown Events – Left Middle Rightclick Shift Alt Ctrl
124 GotFocus and LostFocus Events
125 Use a Checkbox for its VALUE
126 Checkbox Event Without a Button
127 LinkedCell Property with a Checkbox
128 Option Buttons Intro – What is an Option Button and Grouping
129 Grouping Alignment and Aesthetics for Controls
130 Homemade Quiz using Option Buttons and a Command Button
131 Compliance Checker for your Forms or Quizzes Part 1
132 Compliance check part 2
133 SpinButton with Cell A1
134 SpinButtons with Textboxes and Numbers
135 SpinButton BONUS – Move Textbox Position with SpinButton
136 SpinButton with Dates in a Textbox
137 Activex Label Control – Basics
138 ComboBox – Fill with ListFillRange
139 Creating a Dynamic Range
140 ComboBox – How to use Multiple Columns
141 Using .ADDITEM to Get Specific items in your Combobox List
142 Noteworthy Properties of a Combobox
143 Multiple Columns in Combobox with .ADDITEM and List ListCount etc
144 ListBox Basics using Dynamic Range
145 Listbox Click event and Dynamic Options
146 BONUS – Manipulating and Using Multiselect Items in a Listbox
147 ListBox – Referencing Column 0 and 1 in a cell Dynamically
148 Dynamic ListBoxes Using .ADDITEM and More
149 Make and Utilize INVISIBLE Columns – Special Trick
150 Using a Scroll Bar on a worksheet
151 Pictures on a Worksheet plus using FollowHyperlink Method
152 Toggle Button
153 A Brief Note on Alternate 3rd Party Controls
Userforms – the best thing. EVER
154 Userform – Hide Show and Unload
155 Sample Database and Creating a Data Entry Form
156 Fill Status combobox with Rowsource when Userform starts – 2 methods
157 New Employee Data Entry Form
158 Format Textbox Numbers as Numerals Clearing out Form upon Submission
159 Set the Tab Order for Userform Controls
160 Set Default Values to Make Data Entry Easier
161 Avoid Duplicate Employee ID and other Fun Automation Tricks
162 Edit Mode for Employee Database – Part 1
163 Edit Mode for Employee Database – Part 2
164 Edit Mode Pt 3 – Adding Automation and Save Button
165 Userform Report with Filters – Part 1
166 Userform Report with Filters – Part 2
167 Applying the Dynamic Filters to Reports
168 Clear out Last Report
169 Report Generation and Aesthetics Headers Footers Repeat Rows etc.
170 Concluding Thoughts. . .
BONUS Section – VBA Functions
171 InStr Function – Search for Text Within Other Text
172 The EVALUATE Function – Mac-Daddy of all functions
173 Sum Function in Excel VBA – EASY
174 Count Function in VBA
Resolve the captcha to access the links!