Visual Basic For Applications (VBA) - Excel

Explore advanced data types in VBA, declare and convert variables, use line-continuation with underscores, and concatenate results. Learn how dates, integers, currency, and constants influence operations.

Learn VBA arrays, including zero-based indexing, single and multi-dimensional layouts, and populating them with formulas. Introduce a user defined type for customer info (company, contact, sales) across an array.

Apply VBA and Excel functions to compute roots, input data, call functions and subs, and use worksheet functions like pi for area calculations and decimal to binary conversion.

Master if statements in VBA for Excel, including one-line and multi-line forms, else and end if, and, or, not operators to control logic.

Learn how to replace if statements with VBA's select case in Excel, using time, weekday, quantity, discount, and color examples with case ranges and else.

Explore VBA looping constructs, including the do loop, for loop, and for each loop, with examples using X as date and month comparisons to control flow and active cell.

Demonstrate while loops in Excel VBA by implementing the Collatz sequence and a prime check, updating the active cell until the result reaches 1 or a prime is confirmed.

Explore VBA in Excel by reversing digits with a while loop and by pricing bonds from cash flows using yield to maturity, par value, and coupon rate.

Learn how to use for loops in VBA for Excel to sum numbers 1 through 10, with a step, and input and message boxes, including a random guessing game.

Learn how to use for each loops in VBA for Excel to iterate over workbooks, worksheets, and cells, applying actions like uppercase conversion and summing a selected range.

Explore creating and using functions in VBA for Excel, compare functions to subroutines, pass arguments, and build examples like square root, reverse text, and simple sums.

Explore practical VBA functions in Excel by building and calling custom functions like remove vowels, a tiered commission calculator, and a month name helper with optional indices and arrays.

Learn practical VBA function and sub examples in Excel, exploring greet me, output methods, error handling, and text manipulation with if, select case, and randomization.

Explore Visual Basic for Applications in Excel with function examples, including counting by range, word count techniques, and randomize sorting, while emphasizing option explicit and robust error handling.

Demonstrate calling subs with ByRef and ByVal in VBA, showing how ByRef changes the original variable while ByVal uses a copy, leaving the main value unchanged.

Explore bubble sort fundamentals by applying multiple passes, swapping adjacent elements with a by ref array, and organizing random numbers into an array for sorting.

Learn to read and write arrays in Excel VBA by transferring a 100 by 100 array to and from cells and setting the range equal to the array.

Explains on-the-fly array creation in VBA, using split and join to form arrays, iterating with for each, and redim. Implements a max-sort by swapping the max to the end.

Explore how to build interactive Excel interfaces with VBA by using user forms and ActiveX controls, wiring events, calling subs, and showing forms to collect input.

This lecture demonstrates creating a VBA user form with text boxes and option buttons, validating a name, and writing the name and sex to the next empty row in Sheet1.

Use a spin button to drive a text box and update active cell in Excel. Initialize min/max, sync the spin button with the text box, and press Ok to commit.

Demonstrates how to read a selected range into a listbox and write the chosen item back to a worksheet cell using a VBA user form and listbox.

Demonstrate bond pricing in Excel with VBA by using an ActiveX command button and input boxes to collect rate, coupon, and periods, compute discounted cash flows, and total price.

Explore Monte Carlo option pricing in Excel using data tables and VBA, computing call and put values with present value and random paths.

Price swaps and forward rate agreements in VBA by computing zero-bond prices and bond arrays with delta, using two pricing formulas selectable via option buttons.

Explore building an Excel VBA bond pricing tool with drop-down market selection, data tables, and discount formulas across bond, money market, and commercial paper markets, plus a rotatable surface chart.

Count weekly entries in Excel using a VBA macro with start and output positions, activated by a button, handling blank rows between weeks.

Explore two VBA methods to compute statistics, including mean, min, max, and standard deviation, from a user-selected range: worksheet functions and a manual for-each loop approach.

Apply a VBA routine to sum values in column f across sheets. Use activate, range selection, and a for loop with go to to tally each sheet's total.