Excel VBA - Learn from real case studies and projects

Enable the developer ribbon in Excel, access VBA and the Visual Basic Editor, and customize the ribbon for macro development. Save as an Excel macro-enabled workbook to start VBA projects.

Learn how to use Excel with Visual Basic for Applications and navigate the VBE editor, the project window, and the workbook object to organize and run code.

Discover how macros, routines written in Visual Basic for Applications, can be executed by selecting their names and clicking run, and how to view and manage macros across open workbooks.

Explore the trust center macro security settings, including disable all macros without notification and enable macros automatically, and learn how prompts on reopen control macro enablement and virus risks.

Understand how Excel treats the application and workbook as objects, with the workbook as a child of the application and how the active workbook is referenced.

Explore accessing worksheet properties in Excel VBA, including the name property and sheets count, using the application, workbook, and the worksheet objects to inspect and rename sheets.

Learn to manipulate excel with vba by treating cells and ranges as objects, using the active workbook and sheet, and assigning values such as hello to a range.

Explore the VBA project structure in Excel, learn how workbooks and worksheets are organized as objects, and navigate the editor, project explorer, and properties window.

Explore how worksheets, workbooks, and ranges become objects with properties in Excel VBA, using sheet names and the property name to select and manipulate worksheets.

Explore how worksheet objects expose methods and events, such as selection change and value change, and see how code runs to display messages when values or selections modify a range.

Explore workbook methods in Excel VBA, including open, save, and close, and learn to show a welcome message via a message box using workbook and worksheet events.

Explore Excel VBA objects such as workbook, worksheets, and ranges; learn properties and methods, and distinguish parameters from constants, including naming conventions and integer data types.

Learn how subroutines and functions in excel vba execute code, declare types, return values, and call them from a worksheet to compute integers and constants.

Explore VBA modules as reusable components that hold variables, constants, and functions, enabling library sharing, exporting and importing, and controlling public versus private scope across workbooks.

Learn how Excel interacts with users using forms and how worksheets, workbook, and module objects form a reusable VBA library. Import and export modules to reuse code across sheets.

Explore how public and private subroutines and functions control visibility across modules in Excel VBA, showing how default public scope enables reuse while private hides implementation.

Explore basic VBA variable types such as integer, long, single, double, and string, and learn to use option explicit to require explicit declarations.

Demonstrate how Excel VBA summarizes product data by IBC and family for each month, using a dictionary and arrays to store totals.

Learn to use Excel VBA to find the first and last date columns in a worksheet, define a get first day column function, and iterate with ranges and offsets.

Learn to compute the difference between two dates in Excel vba, determine the months between the start and end dates, and create the necessary worksheets to display the data.

Use excel vba to calculate the months between a start and end date and generate a worksheet for each month in that defined period, naming sheets accordingly.

Learn how to build and populate a VBA dictionary to sum monthly values by family and month, using scripting runtime, looping through worksheet cells.

Create and populate a dictionary in Excel VBA, forming keys from bc and date components (month and year), then sum and update amounts across rows.

Learn to build and populate a VBA dictionary with months as keys and arrays of values, add items, iterate over entries, print results, and prepare data for a monthly table.

Learn how to save report data into a dictionary in Excel VBA by creating and using public dictionaries, and manage multiple dictionaries through basic dictionary operations.

Build and populate a one dimensional family dictionary from a worksheet in Excel VBA, using a row loop to create keys, then print and assign the dictionary.

Parametrize and generalize a VBA function by creating and reusing a dictionary in Excel, calling it with different columns and items to build tables.

Learn how to print dictionary values and create per-worksheet tables in Excel using VBA, including dictionary setup, looping through rows and columns, and populating cells.

Learn to format tables in Excel VBA by recording macros, selecting and adjusting ranges, applying gray fills, auto fit columns, and automating report generation across sheets.

Format the headlines of a table by using a separate routine to copy data and include all rows and columns, delivering a more formal reporting style in Excel VBA.

Get rows by range values in Excel VBA by looping through a range to find the row where the value equals BC, then capture the matching row.

Verify that amounts are printed per time period using Excel VBA to pull data from the source, clean formatting and trailing spaces, and generate a workbook with monthly reports.

Demonstrates inserting complex Excel formulas into ranges using native formulas, including sum, sumproduct, and array formulas with rc notation to extract July values from MHR data.

Learn to use the formulaArray property in Excel VBA to insert complex array formulas into ranges, looping through rows and columns to compute first and last day of month.

Evaluate the time to execute one routine versus another by capturing a start time with now, defining start as double or long, and computing the duration in minutes and seconds.

Build and automate a dashboard in excel using vba, with drop-down driven data, date-driven updates, and external data import from an access database to create tables, charts, and graphs.

Prepare the date fields on the application worksheet using Excel VBA in a macro-enabled workbook, illustrating range based and today concepts with dropdowns and dashboard groundwork.

Add command buttons to an Excel worksheet and assign macros to drive data-driven reports. Build dashboards by sourcing data from worksheets or an Access database and automate report generation.

Add a combobox to a worksheet in Excel VBA, populate it with country options from pre-generated lists, and display related projects on the sheet.

Create an Excel VBA macro to open a new workbook and export data into it, with routines that respond to country selection and project context.

Prepare a metadata file containing the data to export to the new workbook using pre-generated country data and modules for projects in Excel VBA.

Use ole db in Excel vba to retrieve data from an external Excel file without opening it, by building a connection string, querying worksheets, and loading results into a dashboard.

Refine the OLE DB macro to retrieve data from a workbook with several sheets, parameterize imports, and organize into tables for easy credit report generation.

Develop a VBA routine to create and assign formulas to table columns, enabling automated date calculations and priority management.

Develop a routine in Excel VBA to create and assign formulas to table columns, reproduce tables, and extend them with more complex variants.

Practice formatting customization for multiline headers in Excel VBA through real case studies, analyzing why code behaves inconsistently on different PCs and troubleshooting table-related issues.

Explore Excel VBA techniques to add formatting customization to multiline headers by automating the merging of adjacent columns with the same name, counting columns, and identifying the last position.

Learn how to add formatting customization to multiline headers in an Excel VBA table, configure ranges, and apply conditional formatting to enhance header appearance.

Explore an Excel VBA routine to automatically apply borders and colors to a selected range, with practical steps for formatting and working with range selections.

Apply and test formatting routines to Excel table ranges using VBA, counting rows and columns, and adjusting table formatting.

Learn how to apply a background pattern to a range in Excel VBA, add borders, and trigger formatting with a button while using simple if-then checks to verify results.

Apply and test all our formatting routines to Excel table ranges, including bold styling and conditional formatting, using real case studies and projects.

Learn how to apply conditional formatting to ranges within an Excel table, including setting up rules and changing font color to highlight data.

Apply our routines to another Excel table by recording a macro, creating and naming a table, and manipulating columns and ranges to update values efficiently.

Prepare the dashboard by organizing main data input fields in Excel VBA, copy ranges into the dashboard, set up data columns and ranges, and demonstrate formula-driven values for ongoing updates.

Build and test a dashboard in Excel VBA by configuring the main data input fields, creating a holidays range named holidays, and validating task duration formulas.

Create and populate summary tables by generating Excel formulas, configure parameters, and manage date ranges to automate calculations in VBA projects.

Include formulas in the ranges of the calendar table using VBA, and explore calculating differences between two dates while referencing a table to populate the calendar and handle weekends.

Explore complex Excel formulas and VBA routines to calculate durations, working days, and holidays, test with ranges and functions, account for weekends, and format results in tables.

Apply format information to the detail table, using borders, colors, and number formats, and perform a practical exercise to format the data.

Learn to generate and format a ticket table on a separate sheet using Excel VBA, with an exercise to turn given data into a table.

Learn to build a ticket table from master data, create and format a table, and insert slicers using a recorded macro to enable dynamic filtering for reporting.

Create and run VBA macros to set up data selection fields in the calculation sheet, enabling customized data filtering with modules, constants, and parameterization for dynamic reports.

Prepare the ticket detail table in the calculation sheet in Excel VBA by setting ranges, creating rows and columns, adding labels, copying data, and testing the results.

Generate a calendar in the ticket detail table using Excel VBA by manipulating ranges, worksheets, and formulas, with steps for selecting columns, copying formulas, and verifying dates.

Develop and populate the ticket detail table by recording a macro to generate and copy formulas across ranges, validate table names, and adjust country selections.

Learn to generate Excel lookup formulas for table ranges using macros and recording techniques, set up ranges, handle max and integer calculations, and format results for reliable data retrieval.

Demonstrate applying conditional formatting to a table's calendar based on dates in Excel VBA, highlighting days within the start date and end date and leaving date outside the calendar uncolored.

Learn Excel VBA through real case studies by completing formulas for the ticket detail table, formatting the grid with borders and colors using macros, and managing ranges, rows, and columns.

Learn to prepare ticket summary tables in Excel using formulas, copy data across columns, define references, apply formatting, and test the resulting table.

Learn to prepare ticket summary tables in Excel using VBA, generate formulas, and organize call data across rows and columns while recording macros.

Prepare ticket summary tables and generate reliable Excel formulas by applying real-case project insights, adjusting cell references, and validating values across the dashboard.

Learn to prepare ticket summary tables in Excel using VBA, including generating formulas, applying conditional formatting, and building a collection sheet with macros.

Apply Excel VBA to complete data tables by recording macros and setting conditional formatting based on string values, using formulas and operators to color-code cells.

Discover how to automatically create and populate Excel VBA tables from master data, detect tables and ranges with a macro, and prepare a dashboard-ready dataset.

Prepare the meta data for export by storing it in a VBA dictionary, organizing a table with columns for later use in the Excel VBA workflow.

Explore Excel VBA to locate table positions and addresses using the areas property of ranges. Learn through real case studies and projects to apply these techniques.

Retrieve and export Excel tables into a new workbook (Part I) by building, reading, and organizing data ranges, setting headers, and generating reports with VBA.

Create named ranges with dynamic addresses using Excel formulas and the Name Manager. Build and verify macros to define range names and apply them to charts and reports.

Learn to create named ranges with variable addresses in Excel using formulas and VBA macros, including copying routines, using parameters, and preparing charts in upcoming lessons.

Generate multi-series charts in Excel VBA by referencing named ranges, recording macros, and assembling a dashboard with dynamic chart titles and organized data.

Generate multi-series charts in Excel VBA by referencing variable named ranges, configure data labels, and apply chart formatting across worksheets.

Create multi series charts by referencing dynamic named ranges with VBA, building a dashboard, and using the offset function to define data ranges.

Learn how to generate and format charts in Excel VBA by building a reusable routine that sets titles, axes, and series formatting across dashboards.

Learn to assign or modify the chart type of an Excel chart with VBA, enabling dynamic data visualization in Excel.

Learn to format a chart with vba, including axes and titles, through real case studies and projects.

Apply the VBA macros to generate additional charts by updating named ranges, formulas, and chart titles, then validate outputs and complete the two chart exercises.

Apply the developed Excel VBA solutions to other charts across different chart types. Adjust legends and labels on the dashboard and verify ticket statuses like reopen and sold tickets.

Create a final report and replicate chart designs to build a complete dashboard, execute macros, and manage their execution in a file, preparing to apply solutions to other charts.

Apply the developed macro solutions to other charts by executing the macro, defining labels, and enabling scrollable charts for flexible data visualization.