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.

Call keyword demonstrates invoking a sub and a function to add numbers, then show the result, while exit keywords allow early termination of a sub or function.

Explore debugging VBA in the VB editor by setting breakpoints, stepping through code with F5, F8, and watch variables to verify calculations and fix errors across modules.

Explore advanced VBA debugging in Excel, using the locals window, watch window, and immediate window to inspect variables, data types, and expressions at breakpoints.

Explore variable data types in VBA for Excel, including byte, boolean, integer, long, single, double, currency, decimal, date, string, variant, and object, with memory and platform considerations.

Enforce variable declaration with option explicit in the VB editor, declare variables using dim as type, and explore common VBA data types like integer, long, double, string, boolean, and date.

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 with descriptive, meaningful names that fit within scope, are unique within scope, start with an alphanumeric character, and remain case insensitive in VBA.

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.

Explore data type conversions in VBA, casting doubles to integers with built-in C-style functions, noting rounding of 3.6 to 4 and the need for sufficient memory for the target type.

Explore how dates and times function in VBA by declaring a date type, using date literals, and casting dates to double to compute day and time differences.

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.

Declare variables for date of birth and today, compute the day difference, and derive age in years by dividing days by 365 within a simple program.

Explore how variable persistence and scope work in this lecture, showing how static variables preserve values across subroutines and how private and public modifiers control module visibility.

Explore procedure scope by examining public versus private access across modules. Show that public subs and functions are accessible across modules, while private scope restricts them to a single module.

Pass arguments between VBA procedures by using a source procedure and a target function, define doubles and integers, and address by reference type mismatches for accurate results.

Explore passing arguments by reference in VBA, where arguments are passed as memory locations by default. See how changes in the target procedure modify the source variables, illustrating reference semantics.

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.

Explore optional arguments in VBA procedures, illustrated with the message box, showing required versus optional parameters, default values, and why optional arguments must be last.

Explore how logical operators and expressions produce boolean results, using comparison operators (>, <, >=, <=) and and/or logic, including string comparisons with case sensitivity.

Examine decision control structures, including simple if, if else, and select case, with boolean flags and age-based conditions to show syntax, nested blocks, and end if requirements.

Explore the select case decision control structure in VBA, using case blocks to handle multiple values with a default else, similar to if else.

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.

Master fixed size arrays in VBA, declare with lower and upper bounds, and access elements with zero-based indexing by default. Learn option base 1 and passing arrays to procedures.

Declare fixed-size arrays in VBA with option base 1, populate and access elements using a(i) in a for loop, and copy to another array when needed.

Explore multi-dimensional arrays in VBA by defining two-dimensional arrays with option base 1, populating them via nested for loops, and accessing elements with a(i, j).

Declare an empty dynamic array without an initial size and resize it later to the exact dimensions. Use the preserved keyword to retain existing values when resizing.

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.

Determine array dimensions and index limits with LBound and UBound for one- and multi-dimensional arrays. Compute the number of rows and columns to enable dynamic looping.

Pass arrays from an Excel worksheet to a VBA function, treat them as variant ranges, copy to a local array, and sum or return results.

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 VBA strings by defining variable length and fixed length types, and by concatenating with the & operator. Use len, left, right, mid, InStr, vbTextCompare, and split to build arrays.

Learn how to format numeric output in strings using the format function, with patterns like hash placeholders, zero padding, and scientific notation to control display.

Define a custom rectangle data type with left and width as doubles using the type keyword, then use it in procedures and calculate its area.

Learn to implement error trapping and handling to prevent crashes, using on error go to, an error handler, message boxes, and resume logic for runtime exceptions like division by zero.

Learn how Excel's object model powers VBA programming by exploring the object hierarchy from application to workbook, worksheet, and range, including properties and methods.

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 how to access object properties in Excel by declaring an object in memory, then using dot notation to access properties and methods of workbook and worksheet objects.

Explore reading and writing to object properties in VBA by accessing workbook and worksheet properties, handling read-only limits, and writing values to cell ranges.

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.

Declare an object variable of type range, create instance with set, assign it to sheet1 range at cell 8 3, modify properties, and release memory by setting it to nothing.

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 referencing range collections in Visual Basic, including defining a range object, selecting multi-cell ranges, using range.count to count cells, and passing lists of cells to a range.

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.

Access built-in Excel worksheet functions from VBA using the worksheet function container, exposing trig, statistical, and math methods like sin, cos, asin, and degree-to-radian conversions.

Learn how to access range object methods in VBA for Excel, including clear, delete, and select, by writing and running a simple sub that manipulates sheet values.

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.

Learn how to use the union method in Excel VBA to combine multiple ranges and set their values at once via the Application object, avoiding looped assignments.

Explore event-driven procedures in VBA for Excel engineering part two, triggering workbook and worksheet events like new sheet creation and sheet activation, and get started with user forms.

Master Microsoft Excel engineering part 2 by designing and implementing VBA user forms, customizing properties, adding labels, text boxes, combo boxes, and handling events for interactive workbooks.