L04a VBA intro procedure v1.1.pptx - Excel Modeling VBA Intro Projects Objects Properties Methods Dr Ronald K CHUNG FINA0404/3351 – Spreadsheet

L04a VBA intro procedure v1.1.pptx - Excel Modeling VBA...

This preview shows page 1 - 16 out of 59 pages.

Excel Modeling: VBA Intro: Projects, Objects, Properties & Methods Dr. Ronald K. CHUNG FINA0404/3351 – Spreadsheet Modelling in Finance
Image of page 1

Subscribe to view the full document.

Macro v. VBA? Macros: used to automate commonly used (or complex) operations What are these? Formatting Visual basic for applications (VBA): programming language 2 ways to create macros: Macro recorder – for simple macros Writing visual basic code VBA Complex code…, e.g., loops ©EQCC 2
Image of page 2
Outline: VBA Intro to VBA (v. Excel) Working with VBA: the visual basic editor Comments, code and key words Objects, properties and methods Defining and using variables Message and input boxes IF statements Basic error trapping Simple loops Other useful practices 3 ©EQCC
Image of page 3

Subscribe to view the full document.

VBA Essentials an organized set of statements to carry out certain tasks/procedures. …procedures are stored in modules 4 ©EQCC
Image of page 4
Key Differences: Excel v. VBA in Excel almost everything happens only once and each cell represents only one thing. We often use the same variable again and again Do not have to tell it specifically in what order the actions have to be carried out. 5 ©EQCC
Image of page 5

Subscribe to view the full document.

VBA Programming Advantages: Complex Excel models: formulas can become very long (easy to make mistakes, hard to trace logic) In large Excel models, the same formula may be copied into dozens/hundreds of cells. Every time such a formula is changed, it has to be recopied Unless there is good documentation, anyone other than the person who created the Excel models may have great difficulty understanding and updating them (v. VBA… can trace code). 6 ©EQCC
Image of page 6
VBA… also is already built into Excel. offers many built-in functions used in other Office® applications, e.g., Word closely related to Visual Basic (VB), which is a widely used and more powerful general programming language. 7 ©EQCC
Image of page 7

Subscribe to view the full document.

VBA can be used to… Create user defined functions Excel functions, e.g., pv(rate, nper, pmt, [fv], [type]) User defined… saved (personal) instructions by users Create a sub(routine) 8 ©EQCC
Image of page 8
©EQCC Working with VBA to open editor: Alt+F11 (/ Alt-L-V), and back What features are there? Projects Modules Objects Properties Methods (Codes) 9
Image of page 9

Subscribe to view the full document.

Other VBE windows four other windows Properties Window, Immediate Window, Locals Window, and Watch Window make your Code and Project Explorer windows as large as possible and easier to use. 10 ©EQCC
Image of page 10
Where things are… The Code window: the big window Project Explorer window: the narrow long window along the left side of your screen with the title “Project” in its title bar. If you do not see it, press Ctrl+R to open it. …where “Modules” reside Each open workbook = a project Find the node titled Modules a “+” next to it, to show (unhide) all the modules 11 ©EQCC
Image of page 11

Subscribe to view the full document.

Modules …where VBA organizes functions and subroutines Default module name: “Module” followed by a # 12 ©EQCC
Image of page 12
Insert Module Left click on module#, or… 13 ©EQCC
Image of page 13

Subscribe to view the full document.

Delete Modules Delete: Alt-F-R (Right click to select delete BEWARE : do not type “yes” [for export before remove]) Rename: Either… in properties box, F4 14 ©EQCC
Image of page 14
Exporting and Importing Modules
Image of page 15

Subscribe to view the full document.

Image of page 16
  • Spring '17
  • Visual Basic for Applications

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes