Microsoft Excel Engineering (Part 2)

Set up the VBA editor in Excel, configure debugging toolbars, manage code formatting and comments, and run sub procedures with F5.

Declare function arguments with explicit data types using as, and enforce option explicit to avoid variants; choose integer or double to handle values and prevent overflows.

Learn to declare and assign variables in VBA, using the equal operator for integers, strings, booleans, and dates (dates use hash symbols) to drive logic.

Learn how to declare and use constants to protect values from modification, such as pi, and compute area with pi times radius squared, while reassigning a constant triggers an exception.

Explore numeric operations, including add, subtract, multiply, divide, and modulus, with order of operations, parentheses, and exponentiation, plus debugging tips for division by zero.

Learn to pass arguments by value. A value type creates a new memory variable in the target function, leaving the source procedure's number unchanged.

Master for loops in VBA, including for i = 1 to 5 with next i and step; accumulate sums and build nested loops for multi dimensional iterations.

Explore conditional loops such as do loops, do while, and do loop until, and learn how to exit to prevent infinite loops, unlike the predefined for loop.

Pass integer arrays to procedures in Excel engineering, using variant parameters and parentheses for array arguments, and review the retarget and array target example with elements 1 to 4.

Learn to pass arrays from VBA to Excel by declaring an empty array, resizing to a 5 by 1 shape, filling values, and returning it with control shift enter.

Explore built-in object references in Excel and how to access them with VBA. Learn to use the VB Editor and Properties window to work with application, workbook, and worksheet objects.

Learn to use the with end with structure to shorten repeated object references when updating range properties like value, column width, and row height in a worksheet.

Explore object collections in Excel VBA by accessing worksheets through the workbook's worksheet collection, using count to loop through sheets, and renaming them to data 1, data 2, and beyond.

Explore the cells property in VBA, accessing cells by row and column, using it with ranges, applying interior color, and transferring values into a two-dimensional array.

Explore the for each loop in VBA to iterate over worksheet collections within a workbook, modify cells across all sheets, and automate tasks using for each and next.

Learn to use the offset property in VBA to move from a starting cell like A1 on sheet 1 down and right to set the current cell value.