(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

Similar courses

This is a mixed version class, appropriate for anyone using Excel 2016/2019/2021/2023, as well as Office 365 subscribers with automatic updates from Microsoft. Attention will be given to nuances between the program versions as applicable for the students in each class. For those using 2013 and earlier, please contact your Learning Consultant before enrolling in class.

More Information

This is a mixed version class, appropriate for anyone using Excel 2016/2019/2021/2023, as well as Office 365 subscribers with automatic updates from Microsoft. Attention will be given to nuances between the program versions as applicable for the students in each class. For those using 2013 and earlier, please contact your Learning Consultant before enrolling in class.

More Information

If you are someone with existing SQL or SQL Server knowledge (or someone highly versed in different data repositories), this is the Power BI course for you. This course is best for students with high PC skills and are experienced/comfortable with technology - if that isn't you, our one or two day Power BI classes might be a better fit for you.

More Information

This is a great class for an overview of Power BI/if Power BI isn't a central part of your job role.

More Information

Doing data analysis work is about more than learning a software program (Excel, Power BI, Tableau, etc.) - you need to understand the concepts and theory too. This one day course gets you up to speed (and can be useful either before or after your software classes).

More Information

Understanding DAX is critical for Power BI users. It is required that you are familiar with Power BI and (if attending virtually) that you have Power BI on the PC to be used for this training event in order to take this class

More Information

This is a mixed version class, appropriate for anyone using Excel 2016/2019/2021/2023, as well as Office 365 subscribers with automatic updates from Microsoft. Attention will be given to nuances between the program versions as applicable for the students in each class. For those using 2013 and earlier, please contact your Learning Consultant before enrolling in class.

More Information

This is a great class for an overview of Power BI/if Power BI isn't a central part of your job role.

More Information

This is a mixed version class, appropriate for anyone using Excel 2016/2019/2021/2023, as well as Office 365 subscribers with automatic updates from Microsoft. Attention will be given to nuances between the program versions as applicable for the students in each class. For those using 2013 and earlier, please contact your Learning Consultant before enrolling in class.

More Information

If you are someone with existing SQL or SQL Server knowledge (or someone highly versed in different data repositories), this is the Power BI course for you. This course is best for students with high PC skills and are experienced/comfortable with technology - if that isn't you, our one or two day Power BI classes might be a better fit for you.

More Information

This class is designed for people new to using AI tools, such as ChatGPT - Gemini - or Copilot, in the workplace. People with experience using these tools for their job functions may find some of the content covered to be beginner or overview level. PLEASE NOTE - students in this class will create ChatGPT, Gemini, and/or Copilot accounts - and should have access to an email account and cell phone in order to move through the various vendors' multi-factor authentication processes during account set-up.

More Information

This class is designed for people new to using AI tools, such as ChatGPT - Gemini - or Copilot, in the workplace. People with experience using these tools for their job functions may find some of the content covered to be beginner or overview level. PLEASE NOTE - students in this class will create ChatGPT, Gemini, and/or Copilot accounts - and should have access to an email account and cell phone in order to move through the various vendors' multi-factor authentication processes during account set-up.

More Information

Understanding DAX is critical for Power BI users. It is required that you are familiar with Power BI and (if attending virtually) that you have Power BI on the PC to be used for this training event in order to take this class.

More Information

This class is designed for people new to using AI tools, such as ChatGPT - Gemini - or Copilot, in the workplace. People with experience using these tools for their job functions may find some of the content covered to be beginner or overview level. PLEASE NOTE - students in this class will create ChatGPT, Gemini, and/or Copilot accounts - and should have access to an email account and cell phone in order to move through the various vendors' multi-factor authentication processes during account set-up.

More Information

No previous experience of Copilot required; however, the student will require an existing Copilot for Microsoft 365 license to participate in hands on exercises, as there is currently no trial license available to Microsoft partners for this product.

More Information

This class is designed for people new to using AI tools, such as ChatGPT - Gemini - or Copilot, in the workplace. People with experience using these tools for their job functions may find some of the content covered to be beginner or overview level. PLEASE NOTE - students in this class will create ChatGPT, Gemini, and/or Copilot accounts - and should have access to an email account and cell phone in order to move through the various vendors' multi-factor authentication processes during account set-up.

More Information

This class is designed for people new to using AI tools, such as ChatGPT - Gemini - or Copilot, in the workplace. People with experience using these tools for their job functions may find some of the content covered to be beginner or overview level. PLEASE NOTE - students in this class will create ChatGPT, Gemini, and/or Copilot accounts - and should have access to an email account and cell phone in order to move through the various vendors' multi-factor authentication processes during account set-up.

More Information

4 Half Day Sessions

More Information

This class is a follow-up to the more basic/entry level AI classes offered at Logical Operations. Students in this class should have prior experience in an AI course offered by Logical Operations. It is recommended that students have previously taken either one of our "How to Create and Use a Prompt" classes or one of our "Getting the Most out of your Spreadsheets/Documents/Presentations with GenAI" classes.

More Information