Visual Basic for Excel - Microsoft VBA Excel Tutorial

Record a simple Excel macro with the developer ribbon, name it, set a shortcut, convert today’s date from a formula to a value, and replay it across sheets.

Open the Visual Basic editor from the Developer tab or Alt+F11 to view the macro code in module one, then edit the sub between sub and end sub and run.

Save Excel workbooks as macro-enabled files (.xlsm) to preserve VBA macros, not as XLSX that lose the code. Recognize the XLSM extension signals a macro is present in incoming files.

Explore the visual basic editor in excel, navigate the vba environment, and manage modules, windows, and options—learn how to insert, export, and test vba code with immediate feedback.

Watch a macro write your VBA in Excel as you record actions, see the Visual Basic Editor generate module code, learn ALT+F11 and how to stop recording.

Explore what a module is in VBA as a dedicated code sheet for procedures, functions, and global declarations; learn to insert, name, organize, export, and import modules in Excel.

Create a manual subprocedure in Excel VBA by inserting a module and writing Sub name() End Sub, then call it from the macro list or run it.

Learn how to call a subroutine in VBA from the VBA editor, F5, or the immediate window, and from Excel via macros, keyboard shortcuts, buttons, ribbons, or workbook events.

Create a function procedure in VBA for Excel by declaring a function, defining parameters, and returning a value. Build Fahrenheit and centigrade converters and explore module insertion and function calls.

Explore how to call VBA functions in Excel using two methods: from a sub procedure or from an Excel cell, with Fahrenheit to centigrade conversions and function validation.

Add comments with the apostrophe to describe VBA routines and use the edit toolbar to quickly comment or uncomment blocks, preserving clarity for future readers.

Declare variables in VBA with option explicit and dim, assign explicit data types (double, string, byte, integer), and follow naming rules to control memory and prevent errors.

Explore how variable scope governs where a variable lives and dies in VBA, from local procedure variables to module and workbook wide public access, using dim, private, and public declarations.

Explore static declarations and variable expiry in VBA. Learn how static retains a value after a procedure and how to reset global and static variables with a reset routine.

Learn how constants replace hard-coded values in vba by declaring them with const, control scope from procedure to module to public, and update a value to affect the entire application.

Explore intrinsic constants like vbRed and vbGreen, inspect them in the immediate window or object browser, and name your own constants to avoid clashes and tidy code.

Explore VBA string types, comparing variable-length strings with fixed-length strings and their memory impact, plus date handling with hash-delimited literals, 65,526 max fixed length, and month-day-year and 24-hour time formats.

Explore Visual Basic for Applications operators, including addition, subtraction, multiplication, division, exponentiation, concatenation, integer division, and mod, alongside the assignment operator, with brackets to enforce order of operations.

Learn how to declare and use arrays in VBA, including choosing index bases (0 or 1), assigning values to indexes, and accessing elements by array name and index.

Learn to create and reference multi-dimensional arrays in VBA, including 2D grids and 3D cubes, using dim with base settings and x, y, z indexing.

Declare a dynamic array and use ReDim to set its number of indexes. Use ReDim Preserve to keep existing values when resizing, and note that reducing indexes loses data.

Master the range object in vba for excel to select and manipulate cells, rows, columns, and named ranges across worksheets and workbooks, including nonconsecutive ranges.

Discover how the cells property lets you reference ranges by row and column numbers, enabling programmatic navigation of sheets and dynamic value assignment in VBA.

Explore the offset property in VBA, which references cells offset from a range using positive or negative row and column shifts, enabling moves like F5 to C10 and value assignment.

Explore the range object's value property, a read/write default that lets you read and set values in single cells and multi-cell ranges across worksheets, including text, numbers, and dates.

Learn how the range.text property, a read-only feature, exposes a cell’s content plus numeric formatting—currency, percent, and thousand separators—and differs from range.value in VBA.

Describe the read-only properties of the range object, including count, column, row, address, and has formula, and show how to read, store, and display these values with examples.

Control font properties, borders, and cell colors in Excel with VBA by manipulating range.font, interior, and range.borders, using bold, italic, underline, size, typeface, and RGB or VB constants.

Format numeric values programmatically in Excel with VBA by applying range.numberFormat to B2:F4, choosing two decimal places, currency, or percentage formats.

Learn how to use VBA to insert and manage formulas in Excel cells with the range.formula property, including sums and an average, and handle quotes and text formatting.

Explore common range object methods in VBA for Excel, including select and copy-paste. Activate the correct sheet, then use go to, clear, and delete with shift options.

Discover what a function is and the types in VBA: built-in VBA, worksheet, and user-defined functions, plus how to access worksheet functions from VBA and implement your own function.

Explore built-in VBA date functions such as date, time, and now, plus the month and MonthName functions, demonstrating nesting and differences between VBA functions and Excel worksheet functions.

Explore how to manipulate text strings in VBA using len, left, right, mid, lower and upper case, trim, and replace functions on the sample string.

Explore how to use the built-in FileLen function in VBA to determine an Excel file’s size, convert bytes to kilobytes, and display results with a message box.

Learn to call Excel worksheet functions from VBA, using Application.WorksheetFunction (or the shorter syntax) to sum, average, min, and max across ranges, and to perform vlookup with named ranges.

Master user defined functions in Excel with VBA. Declare the function in a module with parameters and a return type, then call it from a sub or in a worksheet.

Learn how to use the go to statement and labels to jump within a VBA subprocedure, define labels, and control flow with exit sub, without jumping between procedures.

Master VBA control flow with the if then else statement, using hour to decide a time-based good morning or good afternoon greeting, and ending with end if.

Master using if statements with and/or logical operators and nested ifs to evaluate multiple time-based criteria, producing good morning, afternoon, or evening messages efficiently.

Take advantage of the else if clause to replace multiple if statements, speeding up execution and producing cleaner, shorter code that handles morning, afternoon, evening, and night messages.

Discover how to use select case in Visual Basic for Excel to replace if statements, using an hour variable and range cases to generate morning, afternoon, evening, or night messages.

Explore the for next loop in VBA, including syntax, counters, and optional step, then use offset to fill ranges, search values, activate cells, and exit for.

Learn to loop through collections such as workbooks, worksheets, and ranges with for next and for each. Apply actions to each element, such as deleting the first row.

Explore how do while loops in VBA for Excel work, showing condition-based repetition without a counter, with end condition variants and a practical copy-down example using ActiveCell and Offset.

Explore how workbook and worksheet events trigger VBA procedures in Excel, stored on dedicated code sheets, with events like workbook_open and worksheet_change.

Explore workbook open and before close events in VBA to display a welcome message, log opens on a log sheet, and track dates and times by inserting rows.

Explore workbook events that trigger actions on closing or saving, using the before close and before save cues to prompt, cancel, and log timestamps in the log sheet.

Utilize workbook event triggers to respond to sheet activate and sheet deactivate, using SH to capture the current sheet names and lastSheet to track transitions.

Use worksheet triggers to run code when a sheet is activated or deactivated. Implement per-sheet triggers to display a message, insert date, and move focus, distinguishing them from workbook triggers.

Explore three useful worksheet triggers in VBA for Excel: before double-click, before right-click, and change, showing how to cancel defaults, update targets, and enforce numeric input.

Explore using Excel's application events to automate actions with Application.OnTime and Application.Speech, and customize keyboard behavior with Application.OnKey, including workbook open triggers and reset on close.

Master error trapping in Excel VBA by using on error goto and errRoot label to handle runtime and syntax errors with friendly messages.

Catch and ignore errors in VBA by using on error resume next, allowing the code to skip problematic cells and continue cube root values across a range.

Learn to interact with users during Excel VBA errors by using on error goto, a yes/no message box, and a restart point to continue processing a range of cells.

Learn to handle VBA errors using the on-error statement and the Err object, capturing error numbers and descriptions to craft user friendly messages.

Identify issues beyond errors by stepping through code and inspecting flow, including ifs and loops. Use message boxes or debug.print to show pre and post values in the immediate window.

Set and toggle breakpoints in VBA to pause code, inspect variable values in the immediate window, and step through with F8, then continue or step over/out.

Learn to use watches in VBA to monitor a variable and break when x is greater than 1000, then inspect the watch window for debugging.

Speed up VBA in Excel by turning off screen updating, manual calculation, and alerts; shorten code using with and end with structures and shorter variable names.

Learn how to use the MsgBox function in VBA to interact with users, capture responses, and handle different button configurations with select case and default buttons.

Explore using the input box in Visual Basic for Excel to collect text or numbers with prompts, titles, and defaults, handling cancel and numeric validation.

Use application.inputbox with type eight to let users select a range on the active sheet, capture it as a range, and display its address or warn if nothing is selected.

Learn to replace message and input boxes with interactive user forms in excel vba. Create forms with buttons, labels, and options, and link them to worksheet cells while handling input.

Create an Excel user form in the VBA editor, resize it, use the toolbox and properties to rename, set the caption, add controls, and store code with the form.

Learn to add and configure command buttons on an Excel user form, name them (cmdOK, cmdCancel), set defaults, and link actions with VBA to show, unload, and respond to clicks.

Group option buttons in a frame on a VBA user form, offer upper, lower, and title case choices, set a default, and enable alt-key accelerators for quick selection.

Wire a VBA form to Excel, using option buttons to apply upper, lower, or title case. Loop through selected constant cells and apply case with VBA and Excel's proper function.

Learn to call a user form from Excel VBA, validate the selected range contains text before changing case, and handle errors with messages and a keyboard shortcut.

Explore checkbox controls in a user form for Excel VBA, linking checkboxes to cells, using default values and keyboard accelerators, and handling actions with the ok button.

Master excel vba user form controls by using a combo box and list box to display and select months, with data from sheet ranges or add item initialization.

Learn how to add and customize images on Excel user forms using command buttons and image controls, including clip art, picture size modes, borders, transparency, and click actions.

Learn to add a label and a text box on a user form, set and access their values, and display a greeting with a label or message box.

Learn to build multi-page user forms with tabbed pages, navigate between them using next buttons, and set the active tab via the multi-page control while arranging controls on each page.

Use the ref edit control to let users highlight a range. Convert it to a range object from txtRange.value and style with red interior and white font.

Configure horizontal and vertical scroll bars and a spin control, set min, max, small and large change values, and update labels with current values to drive VBA actions.

Learn to improve Excel user forms by controlling tab order and aligning controls for a professional interface. Apply tab stop settings and alignment techniques to create tidy, usable VBA forms.