Excel's VBA can be a powerful tool useful for a multitude of purposes if you know how to leverage its capabilities, debug issues, and mitigate for specific limitations. In this introductory course, you'll begin by using subroutines in VBA to perform operations. You'll then define functions and reference and edit cell ranges and Excel sheets with VBA. After that, you'll invoke subroutines with relative cell references, record macros in Excel, and debug macros in VBA. You'll insert columns and sheets from VBA and format cells based on a condition in VBA both manually and using a FormatConditions object. Finally, you'll illustrate how clearing formatting using a FormatConditions object will only clear formatting created using a FormatConditions object, not by using if-else conditionals.
customize Excel menus to display developer features, enter the VBA console, identify the default macro settings in the trust center, create an auto-open macro, display a message-box from VBA, associate the auto-open macro with an Excel workbook, and save that workbook as a macro-enabled (.xlsm) workbook
create an auto-close macro, contrast the working of the auto-open and auto-close macros, compare functions and sub-routines in VBA, create a function that accesses the user name using the Excel object model, invoke that function from a worksheet cell, access and modify the contents of the active cell from VBA, use cell references with cell addresses from VBA, and invoke subroutines using the Macros>Run menu
edit the contents of a range of cells using direct cell references, an object of the Range class, and ActiveSheet.Range(), Sheet().Range(), and ActiveSheet
use VBA macros to autofit rows and columns, toggle the word-wrap setting of a range of cells, create a new macro using the Macro Recording feature, assign a keyboard shortcut to a recorded macro, and invoke that macro using the macro pane and keyboard shortcut
use relative references while recording a macro and recognize how it affects the output, accept user input using the InputBox function, and insert sheets into a Workbook from VBA
insert rows and columns from VBA, understand how errors in macros are displayed, and debug an error encountered while running a VBA macro
merge cells and center them and hide and unhide rows and columns
implement conditional formatting using VBA with a FormatConditions object, select all cells in a range that satisfy a certain condition, apply specific formatting to those cells, change the text and background color of cells, add buttons to an Excel workbook and associate them with specific macros
perform conditional formatting operations using VBA and simulate the use of arrow keys from within VBA using xlDown to go from the active cell to the last entry in a contiguous range
format a range of cells without using a format conditions object and recognize how removing formatting using a FormatConditions object will only remove formatting formatted using a FormatConditions object
insert a formula into a worksheet cell using absolute references from VBA, identify the limitations of xlDown when used without a clearly defined contiguous range around it, and use relative cell references and the size of a range to mitigate these limitations