(Online 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