Leila Gharani – Unlock Excel VBA & Excel Macros

Leila Gharani – Unlock Excel VBA & Excel MacrosAutomate Complex Tasks with Microsoft Excel VBA & Excel Macros (Real-World Projects included). Achieve More. Save time.What you’ll learnEnhance your current Excel files & automate your work with VBA.Master Excel VBA & MacrosDirectly apply the Excel VBA example files included to your own work.Reduce manual routine tasks to the click of a button.Make your work-life easier.Create simple solutions to complex problems by levering Excel functionality with Macros.Formulas & Excel core functionality can’t do everything. Use VBA when you reach a limitation.Easily adjust VBA code you find online to fit your purpose.Here’s Your Learning JourneyYou can view the detailed curriculum below. Click on “See a full outline” button below to expand the lecture list.See anything that jumps out? Which lesson do you think you’d begin with?Section 1: Introduction & ScopeCourse Structure & ContentImportant Compatibility & Setup InformationCourse Resources: Download Course Project FilesCourse ExpectationsCourse Outline for Quick ReferenceQuick Check-inSection 2: Your First Excel MacroOverview: Macros & Visual Basic Editor (VBE)Steps to Your First Macro RecordingVisual Basic Editor: What You Need to KnowProject: Dynamic Selection (Special Cells) – Perfect for Flexible MacrosProject: Copy (Consolidate) Data – Absolute versus Relative Macro Recording7 Ways to Run Macros / VBA code (incl. creative & modern buttons)OFFICE 365 Update: Change from Comments to NotesActivity: Record a Macro that Deletes all Notes/CommentsWhat’s the Difference Between Macros and VBA?Quiz: Test Your Knowledge on MacrosSection 3: The Object ModelOverview: VBA Object Model (What you need to become an expert in VBA)VBA’s Code Reference Object LibraryVB Color Guidelines & Keyboard ShortcutsExcel VBA Object ModelObject PropertiesObject MethodsHow to Find the Object, Property & MethodConclusion: Key Takeaways & Message from MeTest Your Knowledge about the Object ModelSection 4: Referencing Ranges, Worksheets & Workbooks with VBAOverview: Referencing Ranges, Worksheets & WorkbooksReferring to Ranges & Writing to Cells in VBAMost Useful Range Properties & Methods4 Methods to Find the Last Row of your RangeCopying & resizing a variably sized rangeProperly Referencing WorksheetsProperly Referencing WorkbooksActivity: Save a Hardcoded copy of WorkbookProject: Save Hard-coded Copy & Macro-free version of WorkbookConclusion: Key Takeaways for Referencing RangesSection 5: Working with VariablesOverview: Role of Assignment & VariablesData Types for Better PerformanceDeclaring Variables, Arrays & Constants (Role of Option Explicit)Using Object Variables (Set statement)Scoping VariablesActivity: Revisit Hard-coded Workbook Project & Use Variable AssignmentTest Your Knowledge on Variables and data typesKey Takeaways for Working with VariablesSection 6: Looping through Collections & Making DecisionsOverview: Why Collections are a VBA EssentialWith & End With for Easier Code WritingFor Each to Loop Through Collections (sheets, ranges etc.) in one goIF Then (Else, ElseIF) for Conditional OutcomesSelect Case as Alternative for Many IF StatementsGoto Statement to Change Program FlowActivity: Get the Number of Formulas on the worksheetProject: Get the Number of Formulas on the WorksheetBonus: Unhide All Sheets in One GoKey Takeaways for Collections & Decision Making in VBASection 7: Useful Built-in FunctionsOverview: VBA versus Worksheet FunctionsMost Useful VBA FunctionsMessage Box (also with Yes, No buttons)Input Box (VBA InputBox Function)Input Box that can Select Ranges (Excel InputBox Method)Activity: Show Top 3 Values in a Message Box Based on Range SelectedProject: Show Top 3 Values in a Message box based on Range SelectedKey Takeaways Built-in FunctionsSection 8: Debugging, Error Handling & Procedure ScopeOverview: Debugging, Handling Errors & Procedure ScopeDebugging Options: Watch, Locals, Immediate Windows & MoreError Handling: Different MethodsFaster Code by Suppressing Pop-ups & Flickering ScreenProcedure Scope & Running one Sub from AnotherPassing Arguments to Sub Procedures (By Ref, By Val)Activity: Get the Total Number of Formulas (or Comments) Used in a WorkbookProject: Get the Total Number of Formulas (or Comments) Used in a WorkbookKey Takeaways Debugging, Error Handling & Course Map StatusSection 9: Main Project #1: Create a Tool to Automate Table of ContentsProject overview: Table of Contents (TOC) with one clickTOC Project: The BasicsTOC Project: Adding HyperlinksTOC Project: Testing the VBA CodeTOC Project: Adding Error Handling & More ChecksTOC Project: Adding to Personal Macro Workbook (Make it Available to all Files)First Milestone Completed!Section 10: Looping in VBA (controlling the flow of code)Section Overview: Looping in VBAFor Next Counter Loops (Simple Example)For Next Nested Loop (Loop through Text & Cells)For Next Reverse Loop (Delete Filtered/Hidden Rows)Do Until & Do While LoopPractical Example of Do Loop (with Inputbox)Find Method for Quicker Lookup ResultsFind Method with Do Loop – Multiple MatchesAdd a Timer to Test & Speech to InformActivity: Document all Comments in Workbook in a SheetProject: Document all Comments in Workbook in a SheetUseful VBA Statements & Key takeaways for Looping in VBASection 11: Working with ArraysOverview: Working with Arrays in VBAOne Dimensional Arrays (and transferring back to sheet)Dynamic Arrays (size adjusts depending on a condition)Preserving Dynamic Arrays (adding more elements to the existing array)Two Dimensional ArraysVariant Arrays: Quick Method to Write to CellsActivity: Create a New Worksheet per Company with Specific InformationProject: Create a New Worksheet per Company with Specific InformationKey Takeaways ArraysSection 12: Working with Files, Folders & Text FilesOverview: Working with Folders & FilesDIR Function: Check if Files or Folders ExistGetOpenFileName Method: User Selects a FileFileDialog Property: Loop Inside a FolderCreate CSV File from Data in ExcelPrinting & Writing to Text Files for More ControlActivity: Export Data to Text file (fix Delimiter to Semicolon)Project: Export Data to Text file (fix Delimiter to Semicolon)Key Takeaways Files & FoldersSection 13: Excel Tables, Formulas & Pivot TablesOverview: (Pivot) Tables & FormulasUsing Excel Formulas in VBAExcel Tables: Use the Right SyntaxPivot Tables & VBA: What you Need to KnowActivity: Update Existing Formulas with VBA by Adding IFERRORProject: Update Existing Formulas with VBA by Adding IFERRORKey Takeaways (Pivot) Tables & FormulaSection 14: Main Project #2: Regional Reporting ToolWhat You’ll Learn (& Should be Aware of..)Project Overview: Regional Sales Reporting ToolImporting Data from Multiple Files using MultiSelectSetting up a Template as the Basis for Different ReportsExporting Reports as Excel files (Pivot Table & Hard Coded Reports)Fine-tuning Report Export Procedure (Double-check if user wants to overwrite)Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon)Track the List of Tasks Completed with VBAFinal Touches & Creating Interface to Hide Specific Tabs with a PasswordKey Takeaways & Tips for VBA based Excel ToolsSecond Milestone Completed!Section 15: Interacting with Other Applications (Word, PowerPoint etc.)Overview & Important Concepts when Interacting with Other ApplicationsProject PDF: Save Specific Sheets as a PDF FileProject Email: Automatically Create Personalized Emails with AttachmentsProject Microsoft Word: Create Personalized Letters in Word from ExcelProject Microsoft PowerPoint: Export Excel Sheets to PowerPoint PresentationKey Takeaways when Working with other ApplicationsSection 16: Worksheet and Workbook EventsOverview & What are Event Procedures?Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close)Worksheet Selection Change: Dynamic Font Color of Selected RangeRefresh PivotTables Automatically & Concept of Range IntersectionResetting a Dependent Drop-down when the first Drop-down is ChangedActivity: Link Page Header to Cell Value (BeforePrint Event)Project: Link Page Header to Cell Value (BeforePrint Event)2 Useful Tips when Working with Events & Key TakeawaysSection 17: Working with Userforms & ActiveX ControlsOverview: UserForms & ActiveX ControlsActiveX Controls: What you Need to KnowActiveX Check Box to Fix Scroll Area & Toggle Screen SettingsActiveX to Show and Hide HelpActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks)ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting)UserForm Basics: What You Need to Get StartedUserForm for Data Entry (Input & Check Boxes)Practical UserForm to Document External Links & Comments (Combo Box & Option button)Bonus: ListBox to Print Multiple Pages in ONE GoUserForm Checklist & Course Map StatusSection 18: Main Project #3: Invoice Generation ToolTool Overview: What You Will LearnSetting up an Input UserForm to Collect Master DataWorking with Multi-Column ListBoxesEdit Master Data with UserFormSearchable ListBox (with Selection Change event)Enhanced Searchable ListBox: Write Selection back to Excel TableMultiPage UserForm to Create Invoices & Email (Setup)Export PDF & Excel Workbook for Each Invoice based on ListBox SelectionCreate Email in Outlook & Attach PDF InvoiceFinal Updates & DebuggingLast Milestone Completed!Section 19: Creating Customized Functions / FormulasSection Overview: Function ProceduresSetting up a Simple Function (including Optional Arguments)Function to Calculate Age (Result in Years & Months)Functions that Split Text and Numbers from Cell ValueUsing Functions in Sub ProceduresActivity: Function That Sums based on Cell ColorProject: Function That Sums based on Cell ColorKey Takeaways Function Procedures in VBA & Course Map StatusSection 20: Working with ChartsThe Chart Object Library & Section OverviewCreate and Modify a Chart with VBAAnimated Charts: Different MethodsShow Charts inside UserFormsKey Takeaways: ChartsSection 21: BONUS! CODE BOOKDOWNLOAD VBA Code BookSection 22: Summary & Final WordsYour Next Steps & more LearningDescriptionGo from Beginner to Microsoft Excel VBA Expert!I’ll take you step-by-step through engaging video tutorials and teach you everything you need to know to be able to use VBA to improve your work flow, save time and automate your current Excel projects.What differentiates this course from the other Microsoft Excel VBA & macros courses? It includes:Over 50 Microsoft Excel VBA & macro examples. These are solutions my clients have needed through the years. They are likely to be useful for you too.Using the right VBA syntax is the most difficult part. An important topic which isn’t covered enough in other VBA courses. In this course you understand why you write things in certain ways.Create 3 Complete Excel VBA Tools. We go from idea to final product and cover each step.Over 100 pages of documentation (including useful code) so you can easily refer to it when in need.Simple explanations for complex concepts.High quality video and different delivery methods to keep things interesting.Lots of ideas to automate your current tasks.Learn from Google or from this Course?I’ve been there! I first learnt VBA from Google.It was truly a light-bulb moment, when I realized that Google has some answers – it could give me some of the code I was looking for – but there two things missing:The WHY – why does the code work in one situation but not another.Structure – topic sequence, real-case examples, workbooks, documentation.Don’t waste your timeDon’t waste your time on poor quality videos and confusing explanations. In this Excel VBA course, complex topics are taught in a simple way by an instructor with 17 years of experience. The content has been carefully crafted and clearly organized. The videos are high quality and include animations to emphasize points. Most importantly you learn VBA by doing.“I have already tried some VBA classes but so far I was never able to apply what I learnt to my own work. This course is different. I have already used some of the techniques Leila is teaching and applied it to my work files. I was able to reduce manual work for me and my colleagues by creating my own macros from scratch. Highly recommended!!!” — Randy Taylor“Leila is the real deal… there are many VBA courses on Udemy (some of which I’ve taken) that merely demonstrate the functionalities. Leila explains the important foundation concepts and how the model is set up allowing you to truly create VBA code from thought rather than copying and pasting from various places. Her class is professionally constructed, with resources and content for each lecture, practice content for you to try things yourself and good Q&A opportunities to answer specific questions. Trust me, this is the course you want to take.”  — Justin FazzioWhat you can look forward to in this course:Understand how to use the right syntax in VBAUseful Excel VBA codes you can directly use for your workUse the macro recorder for simpler tasks and improve the codeWorking with the Visual Basic EditorDebugging tools and error handling methodsDifferent ways to reference ranges and cellsDifferent methods to loopHow to work with data typesPivotTables in Excel using VBAImporting data from other files and exporting data to text filesInteracting with other applications (Word, PowerPoint, Outlook)ActiveX and Userforms to enhance the Excel interfaceBasically all the knowledge you need to automate tasks in ExcelWho this course is for:Business Professionals who’d like to simplify & automate work flow.Data analysts.You currently use Excel and like to improve your skills.There are no reviews yet.Add a Review Cancel replyYou must be logged in to post a review.