The Worksheet module is used to automate actions related to worksheets and cells. It allows you to write code that interacts with worksheets such as copying, pasting, formatting cells and ranges, adding calculations, looping through cells and ranges, as well as handling events that occur on the worksheet like sheet activation. Some example uses of the Worksheet module include formatting an entire worksheet with conditional formatting, automatically calculating totals when data is entered, looping through cells to populate drop down lists, handling the sheet activate event to clear filters or sort data.
The Workbook module allows you to write code that automates tasks related to entire workbooks and all its worksheets. Using the Workbook module you can open, close, save workbooks, add or delete worksheets, protect and unprotect workbooks, loop through all worksheets, handle events like workbook open and close. Some examples of using the Workbook module are consolidating data from multiple workbooks into a summary file, protecting a workbook when it is closed, runningmacros when the workbook is opened, looping through all worksheets to copy formats or formulas.
The Application module provides the ability to automate actions in Excel itself and control the Excel application. You can use it to insert, move and delete graphics, adjust window views, modify Excel settings and options. Some key uses of the Application module include – recording and running macros when Excel starts or closes, setting Excel calculation options, changing Excel UI options like screen updating, alertNotification, iterating sheets using object properties like ActiveSheet, Sheets, Worksheets etc. Setting Application level events like SheetChange and SheetCalculate.
The ChartObject module enables automating actions related to charts and graphs. You can use it to add, modify, format and delete chart objects programmatically. Some examples are looping through worksheets to insert consistent charts, automatically updating pie charts when data changes, formatting chart titles, labels and legend based on cell values, resizing charts on sheet resize.
The color module allows modifying and setting colors in Excel through VBA. You can define and use color index values, RGB component values or names to modify font colors, interior colors, line styles etc. This is useful when you want to standardize or dynamically set colors in your worksheets, charts through VBA.
The DataObject module lets you work with data objects like data catalogs, data connections, queries and query tables programmatically. You can use it to create parameters for pass-through queries, refresh data connections and query tables, build dynamic SQL statements to control which data is retrieved. This is useful for automating retrieval and manipulation of external database data in Excel.
The DialogSheet module allows displaying custom userforms, inputboxes and msgboxes to prompt for user inputs and display outputs or messages. This is commonly used to build guided wizards or application-like interfaces in Excel through VBA. You can add controls like textboxes, labels, buttons; write validation and input handling code directly in the dialog module.
The Shell and FileSystemObject modules enable automating tasks involving files, folders and commands through Windows Shell and filesystem. Using Shell you can open files, run executables and batch files. FilesystemObject provides methods to work with folders and files – create/delete folders, copy/move files, get file attributes, names etc. This opens up opportunities like automating file operations, running external applications from Excel.
The Outlook module when referenced allows integrating Outlook functionality into Excel project via VBA. You can automate common tasks like sending emails, working with calendar items, contacts and meeting requests directly from VBA. This is useful for automating reports distribution, meeting updates synchronization etc. between Excel and Outlook.
The above covers some of the most commonly used VBA modules in Excel and brief examples of how each one can be leveraged. Modules provide an object oriented way to structure your VBA code and automate various tasks related to workbooks, worksheets, charts, userforms, external files and applications etc. Understanding which module to use and how enables you to build powerful solutions by automating many repetitive tasks through Excel VBA macros.