Excel Macros VBA + Power Query (2 in 1 course)

Enable the developer tab, access Visual Basic, and create or rename macros in Excel to automate tasks with VBA. This introduction covers recording, editing, assigning macros, and message boxes.

Explore the VB editor in Excel, navigating from the web editor to code view, and learn VBA objects, properties, methods, and debugging with the immediate window.

Explore how the range object powers VBA by writing values to cells and controlling properties such as interior and font colors to format worksheets.

Enable the option explicit for variable declaration to aid debugging, and write your first macro using Sub and MsgBox with a prompt, buttons, and a title, then run it.

Record a macro in absolute mode from the developer tab, format numbers as millions, calculate 2020 vs 2019 change, and apply icon-based conditional formatting.

Explore how a macro in absolute mode uses hard-coded ranges for conditional formatting and formulas. Understand why this approach is not dynamic for data and compare it to relative mode.

Learn how to record macros in relative mode, compare it with absolute references, and apply relative references using offset and conditional formatting to automate tasks in Excel.

Learn to record Excel macros in absolute and relative mode, convert text to numbers, clean data with text to columns, and save macros to the personal macro workbook.

Learn to test and refine a recorded Excel macro using absolute and relative references, alter the code in Visual Basic, and customize a macro tab for easy access.

Declare variables with dim to allocate memory and define scope within a procedure, or in a module with public declarations to retain values after procedures end.

Declare string variables, control their lifetime across procedures and modules with option explicit, and verify retention using the immediate window in vba.

Learn to declare and use VBA data types such as string, date, long, byte, boolean, and double, and apply them to variables like names, birth dates, revenue, and profit.

Master loops in VBA, including for, while, and do until, to format data. Extract first names from column E to H, identify birth day, and compute average price and profits.

Learn to use InStr to locate the space, and the Left function to extract the first name, then dump it into column H within a for next loop.

Explore Excel VBA macros to use if statements, format dates to derive the day of birth, and map agenda values to gender with a practical loop.

Use VBA loops to compute average price from revenue and quantity, format results as dollars, and calculate profits by subtracting cost of goods sold, all via a run button.

Create a time-based welcome message in Excel using a VBA user form, a dynamic label caption, and time checks to show good morning, afternoon, evening, or night.

Build an employee database userform in Excel with VBA, featuring dynamic age calculation, date format validation, image upload, and data entry saved to the worksheet.

Automate merging product details across many rows into a single cell per product with a VBA macro, then use lookups to retrieve details from the completed sheet.

Clean messy data by splitting hyphen-delimited text into date, revenue, and profit with a VBA macro, using split, loops, numeric checks, and date and currency formatting.

Loop over multiple Excel files using VBA to open each workbook, format dates and currency, and save changes after running a formatting macro.

Loop through multiple workbooks to identify unique dates and compute daily totals for revenue and profit. Use a VBA macro to extract, sort, and sum daily totals with currency formatting.

Automate weekly profits in Excel using VBA loops to sum daily profits by weekday (Sunday to Saturday), using an array of days and currency formatting.