Programming and Data Wrangling with VBA and Excel ISI-1597

Upcoming dates

See every class on the schedule

About this class

What you'll be able to do

• VBA (Visual Basic for Applications) enables you to enhance and extend the capabilities of
• Microsoft® Excel® and other applications in the Microsoft® 365 application suite. You can
• 3 Days Instructor - led
• nly worksheet
• functions, and you can automate a wide range of tasks involving the collection, processing,
• analysis, and visualization of data. This course will give you a good foundation for
• understanding, creating, and using VBA in your own Excel workbook s, show you how to
• work with data across different applications, and how to package the macros and functions
• other users.
• This course is primarily designed for students who want to gain the skills necessary to use
• VBA to automate tasks in Excel such as collecting data from external sources, cleaning,
• and manipulating data. The target student may also want to learn how to create custom
• In this course, you will analyze and visualize data using Excel and associated tools. You
• will:
• pivot tables and charts. This level of skill could be acquired by taking the Microsoft Excel
• 1 - 3 .
• 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.
• 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.
• C ourse Content
• Module 1: Using VBA to Solve Business Problems
• Use Macros to Automate Tasks in Excel
• Identify Components of Macro - Enabled Workbooks
• Configure the Excel VBA Environment
• Module 2: Automating Repetitive Tasks
• Use the Macro Recorder to Create a VBA Macro
• Record a Macro with Relative Addressing
• Delete Macros and Modules
• Identify Strategies for Using the Macro Recorder
• Module 3: Getting Help on VBA
• Use VBA Help
• Use the Object Browser to Discover Objects You Can Use in VBA
• Use the Immediate Window to Explore Object Properties and Methods
• Module 4: Creating Custom Worksheet Functions
• Create a Custom Function
• Make Decisions in Code
• Work with Variables
• Perform Repetitive Tasks
• Module 5: Improving Your VBA Code
• Debug VBA Errors
• Deal with Errors
• Improve Macro Performance
• Module 6: Controlling How and When Macros Run
• Prompt the User for Information
• Configure Macros to Run Automatically
• Module 7: Developing Custom Forms
• Display a Custom Dialog Box
• Program Form Events
• Module 8: Using VBA to Work with Files
• Use VBA to Get File and Directory Structure
• Use VBA to Read Text Files
• Use VBA to Write Text Files
• Module 9: Using VBA to Clean and Transform Data
• Automate Power Query
• Transform Data Using VBA and Workbook Functions
• Use Regular Expressions
• Manage Errors in Data
• Module 10: Extending the Programming Environment Beyond
• the Workbook
• Run Other Programs and Commands
• Share Your VBA Projects

Course outline

Before you attend