Each part will include training and culminate in the completion of an in-class project. After completion of each in-class Part, you will have a project-based “midterm” exam that you will complete on your own and return to your instructor for assessment before moving to the next part in the track.
Part One includes two projects. The first project will give you the skills you need to write VBA code that retrieves data from one workbook and pastes it into another. The data will then be appended to existing data on another sheet tab in the destination workbook. Both workbooks will be saved and closed as a result of the VBA code. Your second project will result in the creation of an interactive user form. You will learn to write code for the controls on the form to process user inputs and generate PivotTable reports based on the users’ form selections.
Part Two includes two projects. The first project will give you the skills you need to create an advanced form that will allow users to import ranges of data in one workbook into a different workbook. The imported data will be manipulated using VBA built-in functions. You will write code to export to a Word file formatted as a report. The second project will focus on customizing the Excel interface using VBA. Specifically, you will create an Excel Add-in and modify shortcut menus in the Excel application. You will also use Workbook events to further automate your work. We will use some of the code created in Part One in this part.
Part Three focuses on querying a database and using the query results for reports in Excel. In this part you will write simple and complex queries to retrieve results from a database. You will study the structure of a well-designed database and query the database itself to investigate the data within its tables. You will query one database table, filter the results, and limit the results. You will query multiple related database tables and multiple unrelated database tables. Further, you will create calculations in queries that do not reside in the database and use previous VBA code to format the results into a cohesive report in Excel.