VBA: Getting Started with VBA in Excel
VBA 7.1 (Office 2019)
| Beginner
- 13 videos | 1h 55m 9s
- Includes Assessment
- Earns a Badge
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.
WHAT YOU WILL LEARN
-
Discover the key concepts covered in this course
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
Summarize the key concepts covered in this course
IN THIS COURSE
-
2m 36s
-
11m 51s
-
3. Using Functions from VBA9m 21s
-
4. Modifying Cells from VBA10m 8sIn this video, you'll learn more about accessing sheets and ranges from VBA. You'll change the formatting of cell ranges and access the ActiveSheet, as well as other sheets in an Excel book. FREE ACCESS
-
5. Using Excel's Macro Record Feature10m 59sIn this video, you'll learn how to use VBA macros to autofit rows and columns and toggle the word-wrap setting of a range of cells. The demo will then show you how to record a macro and edit it to tweak it and make it do exactly what you had in mind. FREE ACCESS
-
6. Accepting User Input with VBA11m 35s
-
7. Inserting Rows and Columns from VBA11m 4s
-
8. Merging and Formatting Cells from VBA9m 26s
-
9. Implementing Conditional Formatting Using VBA12m 19s
-
10. Simulating Range Selection Operations from VBA8m 20s
-
11. Understanding the Limits of a FormatConditions Object7m 37s
-
12. Inserting Formulas into Cells Using VBA7m 24s
-
13. Course Summary2m 30s
EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE
Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.
Digital badges are yours to keep, forever.
