(In-Person Delivery) Programming and Data Wrangling with VBA and Excel
Description
In this course, you will develop and deploy VBA modules to solve business problems. You will:
- Identify general components of VBA and their appropriate use in solving business solutions.
- Record VBA macros to automate repetitive tasks.
- Use reference tools built into Excel to get help on VBA programming language and objects used in the Excel VBA environment.
- Write VBA code to create a custom worksheet function.
- Eliminate, avoid, or handle errors in VBA code, and optimize its performance.
- Control how and when macros run.
- Develop UserForm objects to create custom dialog boxes and windows.
- Use VBA to read and write data from local files and cloud services.
- Use VBA to clean and transform data.
- Run programs and commands outside of Excel and share VBA projects with other users.
Lesson 1: Using VBA to Solve Business Problems
Topic A: Use Macros to Automate Tasks in Excel
Topic B: Identify Components of Macro-Enabled Workbooks
Topic C: Configure the Excel VBA Environment
Lesson 2: Automating Repetitive Tasks
Topic A: Use the Macro Recorder to Create a VBA Macro
Topic B: Record a Macro with Relative Addressing
Topic C: Delete Macros and Modules
Topic D: Identify Strategies for Using the Macro Recorder
Lesson 3: Getting Help on VBA
Topic A: Use VBA Help
Topic B: Use the Object Browser to Discover Objects You Can Use in VBA
Topic C: Use the Immediate Window to Explore Object Properties and Methods
Lesson 4: Creating Custom Worksheet Functions
Topic A: Create a Custom Function
Topic B: Make Decisions in Code
Topic C: Work with Variables
Topic D: Perform Repetitive Tasks
Lesson 5: Improving Your VBA Code
Topic A: Debug VBA Errors
Topic B: Deal with Errors
Topic C: Improve Macro Performance
Lesson 6: Controlling How and When Macros Run
Topic A: Prompt the User for Information
Topic B: Configure Macros to Run Automatically
Lesson 7: Developing Custom Forms
Topic A: Display a Custom Dialog Box
Topic B: Program Form Events
Lesson 8: Using VBA to Work with Files
Topic A: Use VBA to Get File and Directory Structure
Topic B: Use VBA to Read Text Files
Topic C: Use VBA to Write Text Files
Lesson 9: Using VBA to Clean and Transform Data
Topic A: Automate Power Query
Topic B: Transform Data Using VBA and Workbook Functions
Topic C: Use Regular Expressions
Topic D: Manage Errors in Data
Lesson 10: Extending the Programming Environment Beyond the Workbook
Topic A: Run Other Programs and Commands
Topic B: Share Your VBA Projects