Topic 7 - Advanced excel awesomeness: Macros, web queries, and other cool stuff. The topics discussed in this chapter are: Macros, function descriptions, data tables, data controls (drop down, spin buttons), conditional formatting, sparkle charts, vlookup and hlookup, pivot tables, slicer, web queries. | 1 Topic: 7 Advanced Excel Awesomeness: Macros, Web queries, and other cool stuff Financial Modeling L. Gattis 2 References Financial Modeling 3rd Edition by Simon Benninga Ch. 34: Array Functions and Formulas Ch. 36: User Defined Functions with VBA Ch. 37: VBA Types and Loops Ch. 41: Web Queries Topics Macros Function Descriptions Data Tables Data Controls (Drop Down, Spin Buttons) Conditional Formatting Sparkle Charts Vlookup and hlookup Pivot tables, Slicer Web Queries 3 4 Macro A User Function is used to make calculations and return a value to a cell or range of cells A macro is used to automate tasks, for example: Copy value or formula from one cell to another cell Display messages Macros (also called subroutines) are also stored in modules and have a similar structure as functions Sub macroname () executable statements End sub Macros are run from developer/macro or alt-f8 Macros must have no parameters 5 Macro Example Sub cells1demo() Cells(1, 2) = 5 End Sub Sub cells2demo() Cells(2, 2) = "How" Cells(2, 3) = "You" Cells(2, 4) = "Doin?" End Sub The following macros use the “cells” object and writes values in a specific excel cells using (r,c) indexing (Cell Reference Base is 1) 6 Running Macros Alt-F8 or Developer Tab/ Macro Attaching macros to art objects Insert art object Right-click, assign macro Attaching macros to quick access toolbar Right-click on toolbar Select Customize Select Quick Access Toolbar Drop-Down: Select Macros Select Macro from list Add, OK --- it’s now at the top of your workbook Creating short-cut keys (Ctrl-???) Tools/macro/macro (alt-f8) Select macro Select Options Type shortcut key letter 7 Macro Examples Sub cells3demo() For i = 1 To 12 For j = 1 To 12 Cells(i, j) = i*j Next j Next i End Sub Sub cells4demo() For i = 1 to 10 Cells(15+i,5) = Cells(12,12) Next i End Sub What is this macro going to create? 8 Recording Macros Developer Tab Record new macro Perform tasks: ., copy matrix and paste into another range, then change the . | 1 Topic: 7 Advanced Excel Awesomeness: Macros, Web queries, and other cool stuff Financial Modeling L. Gattis 2 References Financial Modeling 3rd Edition by Simon Benninga Ch. 34: Array Functions and Formulas Ch. 36: User Defined Functions with VBA Ch. 37: VBA Types and Loops Ch. 41: Web Queries Topics Macros Function Descriptions Data Tables Data Controls (Drop Down, Spin Buttons) Conditional Formatting Sparkle Charts Vlookup and hlookup Pivot tables, Slicer Web Queries 3 4 Macro A User Function is used to make calculations and return a value to a cell or range of cells A macro is used to automate tasks, for example: Copy value or formula from one cell to another cell Display messages Macros (also called subroutines) are also stored in modules and have a similar structure as functions Sub macroname () executable statements End sub Macros are run from developer/macro or alt-f8 Macros must have no parameters 5 Macro Example Sub cells1demo() Cells(1, 2) = 5 End Sub Sub cells2demo() .
