VBA Introduction

Information about VBA Introduction

Published on March 6, 2009

Author: ankush85

Source: authorstream.com

Content

A Brief Introduction to VBA : 1 A Brief Introduction to VBA Overview : 2 Overview The purpose of this presentation is to provide you with a very brief introduction to VBA, a programming language that is embedded within the Microsoft Office suite of software products. The goals of this presentation are modest: they are to get you started, to provide you with the basic tools and background that you will need to be immediately able to start working on your projects. This is not a comprehensive guide to VBA – it is a very complex language – rather, it is designed to teach you the rudiments of the language. Overview : 3 Overview In particular this presentation will : Show you how to record a macro and then edit it. Explain the differences between macros, functions and subroutines. Discuss how to insert modules for functions and subroutines, and how to enter functions and subroutines. Discuss how the keys to program flow and control. Introduce you to two different types of loops. Discuss how to perform basic debugging operations. Macros and VBA : 4 Macros and VBA VBA is Visual Basic for Applications. It is a modified version of Microsoft’s well-known Visual Basic language. Although many users treat VBA as nothing more than a macro-language, in truth it can be much more. It allows: moderately complex programming, one to include in VBA programs some of the functions that are built into Excel, it allows the user to read data from and write data into Excel, and it allows the user to create programs that can use various components from the Microsoft suite of products. The key to realize is that VBA is a tool to use to supplement Excel. Macros and VBA : 5 Macros and VBA Most users of Excel are at least somewhat familiar to macros. We will start with a simple Macro example, and then examine the structure and code of the example to begin to gain insight into VBA. To begin, open a new workbook in Excel Click on the Tools menu, and look toward the bottom. One of your options will be “Macros”. Click on the Macro’s button. The second choice will be “Record New Macro”. Click on that choice. You will then receive a pop-up window, asking you to name your macro (the default will be something like Macro1). Type in “Create_text”. Make sure that in the window where it says “Save In”, that “This WorkBook” is selected. Macros and VBA : 6 Macros and VBA Click on “OK” to begin recording your Macro. The macro we are going to create we only want to do something simple: Insert a few lines of text and today’s date in a few specific places in the spreadsheet. To record this macro, all you have to do is demonstrate to Excel what you want the macro to do. First, press the F5 key – this is the “goto” key. You will then be prompted for which cell you wish to go to: enter a1. You will be taken to cell A1. Enter the following text: TITLE PAGE MACRO FOR FINN 6210. Macros and VBA : 7 Macros and VBA After typing in the line of text (and, of course, pressing the “enter” key), press the F5 button again, this time tell Excel to go to cell c15. Once you get to cell c15, enter “=today()”. This is a function that simply puts today’s date in the cell. Now, let’s stop the macro recording. To do this click on the little blue square in the macro popup box that is on the screen. To demonstrate that the macro works, clear the entire spreadsheet. To do this, press ctrl-A to highlight the entire spreadsheet and then press the delete key. Macros and VBA : 8 Macros and VBA Now we can demonstrate that the macro works. Again click on the “tools” menu and select “macro”. This time, however, select the first option “Macros”. This will bring up a list of all macros in the spreadsheet (there will likely only be one, the one you just created labeled “create_text”). Select “create_text”, and then click on the RUN button. This will run your macro. Macros and VBA : 9 Macros and VBA We can now examine the macro. The macro is really just a small subroutine written in VBA. To see the macro text, just once again click on Tools, Macro, and then click once on your macro name. Then click on the “Edit” button. This will bring up a new item on your application bar – the Microsoft Visual Basic Editor (we’ll show another way of bringing this up later.) Your program should look something like this: Macros and VBA : 10 Macros and VBA Sub create_text() ' ' create_text Macro ' Macro recorded 7/28/2003 ' ' Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = “FINN 6210 Title Page" Range("A2").Select Application.Goto Reference:="R10C3" ActiveCell.FormulaR1C1 = "=TODAY()" Range("C11").Select End Sub Macros and VBA : 11 Macros and VBA Notice that the “macro” is enclosed between two lines: sub create_text() end sub Formally a macro is a subroutine to visual basic. The line sub create_text() tells visual basic that a subroutine named create_text is beginning. VBA assumes that each line that follows is part of that subroutine until it reaches the line end sub at which point it knows the subroutine is over. Macros and VBA : 12 Macros and VBA Each line of the program is executed sequentially – that is, one after the other. Let’s look at what each line does The first several lines (all beginning with ') are simply comment lines that VBA ignores. ' ' create_text Macro ' Macro recorded 7/28/2003 ' Macros and VBA : 13 Macros and VBA Application.Goto Reference:="R1C1“ This line is how the macro tells the spreadsheet to go to cell A1. “Application.Goto” means for VBA to use the “GOTO” function from the application – in this case from Excel (remember VBA can be used from Word, Access, and other Office applications.) So literally “Application.Goto” means, use the function named “goto” embedded in this application. The second part of this line , Reference:=“R1C1” tells VBA to go to row 1, column 1 (which is column “A”). Note that later on, when we want to go to cell C10, we use the same line, but with Reference:”R10C3”. Macros and VBA : 14 Macros and VBA The “Application.” construct is important in VBA. For the most part, you can access any of Excel’s built in functions that way! The next line is: ActiveCell.FormulaR1C1 = “FINN 6210 Title Page" This line says to make the contents of the current cell the text which is to the right of the equal sign. The line that follows that is saying that after entering the text, go down one column (the default action in Excel.) Range("A2").Select Macros and VBA : 15 Macros and VBA The “Application.Goto” command is used again, this time to go to cell c10. Application.Goto Reference:="R10C3" Finally, the “today” function is inserted in cell R10C13: ActiveCell.FormulaR1C1 = "=TODAY()“ Notice that the only difference between that line and the line where we entered our text, is that instead of entering just the text, it puts the equal signs inside of the quote. Macros and VBA : 16 Macros and VBA Notice that you can easily modify your macro now that you are in the program. For example, you could modify the text to say “My modified title page”. Just go to the line that says ActiveCell.FormulaR1C1 = "Financial Modeling Title Page“ and replace “Financial Modeling Title Page” with “my modified title page”. Similarly if you wanted to enter something other than today’s date in the cell C10, you could replace “=today()” with anything you wanted. Macros and VBA : 17 Macros and VBA Using the Macro record feature has a lot of advantages, especially if you would like to learn the appropriate VBA code or command, but it is of only limited use when trying to develop more complex program – such as a program than can take conditional actions, or that repeat an action for a specific (or even conditional) number of times. To do that, we really need to be able to create programs without first recording them as macros. We will examine how to do this in the next several sections. Creating a Program : 18 Creating a Program So how do you create a program in VBA? Its relatively easy. To make our example clear, shut down Excel, and then restart it, opening a clean spreadsheet. Once again, click on the “Tools” menu and select “Macro”. This time however, select the option on the menu labeled “Visual Basic Editor”. This will open up a new window (and new item on your application bar at the bottom of you screen.) Creating a Program : 19 Creating a Program This new screen – the VBA editor – is very busy and has a lot of very useful tools on it, but for now we are largely going to ignore them and focus on building some very simple tools. To do this we first need to find a place to enter our new program: we must create a “module” in which to store it. Do to this click on the “insert” menu and then click on “module”. This will open up a white-screen on the right side of the screen. We are now almost ready to begin, but first we have to decide what type of program to create. Subroutines vs. Functions : 20 Subroutines vs. Functions As mentioned a moment ago, Macros are considered subroutines within VBA. In actuality, however, there are two types of programs in VBA that we will examine, subroutines and functions. Subroutines are the more general of the two – they perform any number of activities. Functions are more specific types of programs. They accept a fixed number of inputs and then return a single value back to the user. The nice thing about a function is that you can call it from within excel in exactly the same way that you call an Excel function – by putting the = sign in front of the name! Functions : 21 Functions Because they are somewhat more limited (and also because you are more likely to use them first), we will begin with functions. Let us begin with a very simple function, one that takes two numbers, let’s call them a and b, adds them together and then returns them to the user. Let’s call this function adder. Thus, once written, we will call it from Excel entering the line: =adder(a,b) Where a and b would be replaced by actual numbers. Functions : 22 Functions To begin with we have to tell Excel we are creating a function and tell it the name of that function. To do this we enter the following: function adder(a, b) This says that you are creating the function adder and that the user will supply to parameters, a and b. Notice that as soon as you type this in, Excel automatically adds the line end function Functions : 23 Functions All of the lines for this function must go between those two lines. We are going to make the simplest possible function: on the first line simply type: adder = a+b That is it, we have completed the function. What this says is to assign the function adder (and return to the cell in the spreadsheet) the sum of the parameters a and b. Functions : 24 Functions Go back to your main spreadsheet (click on the Excel tab on the bottom of your screen). Select any cell you like, type in =adder(5,5) Notice that 10 is now returned placed in the cell. Go to another cell, type in =adder(12,12) Notice that 24 is now entered in the new cell, but 10 is kept in the old cell. Functions : 25 Functions Indeed, you can notice that you can use the adder function within excel just like you would use any Excel function. For example, the Excel Function max(y,z) places in the cell the greater of the two values y or z. Type in the following line: =max(10,adder(13,-2)) The adder function evaluates to 11, and that is the max of 10 and 11, and so it is returned. Functions : 26 Functions You can even have recursive calling of a function (meaning the function can call itself): =adder(5,adder(3,6)) Will equal 14. In many ways Excel will treat this as if it were a built-in function. For example, if you click on the fx button, it will call up all of Excel’s function. If you click on the function type drop down menu, you will see “User Defined” as one of your options. Click on User Defined and you will find “adder”. Click on that and it will bring up a function box with instructions on using adder. Functions : 27 Functions Of course you most likely will be doing more complex tasks within a function. Let’s say that you wanted to determine the present value of a single cash flow to be received in N years when the interest rate is r. You could type in the following: function pres_val(cf,r,n) Again, excel will put the “end function” line in for you. This says that your function will need three inputs, the cash flow, the discount rate, and the number of periods before you receive the cash flow. Functions : 28 Functions We may want to calculate an intermediate result and to temporarily store it. We will need a variable to store those numbers. To create a variable in a VBA program, use the dim command. Let’s create a variable, pv_factor Function pres_val(cf,r,n) Dim pv_factor End function Functions : 29 Functions We can now use this variable in an intermediate role (albeit a rather silly one in this example Function pres_val(cf,r,n) Dim pv_factor pv_factor = (1+r)^-n pres_val = cf*pv_factor End function Functions : 30 Functions What the program does now is clear: It creates the variable pv_factor to hold the present value factor determined by r and n. It then determines the actual present value by multiplying pv_factor by the cash flow. It then returns the present value back to the calling function or routine. Functions vs. Subroutines : 31 Functions vs. Subroutines For our purposes, there are very few differences between programming a function and a subroutine. There is one very important difference, however: A function cannot directly write data back to the spreadsheet, it can only return the one value that it calcualtes. A subroutine can write data back to the spreadsheet whenever it wants. So how does a subroutine read and write data to the spreadsheet? There are actually many ways, but the easiest to use and understand is the cells(r,c).value method. Functions vs. Subroutines : 32 Functions vs. Subroutines To read data directly from a spreadsheet into a variable, use the following command: variable = cells(row,column).value where variable is the name of the variable, row is the row number of the cell containing the value, and column is the column number (not letter, you must use a number!) Example: Let’s say that you wanted to read the contents of cell b4 and to store it in the variable r. The line to do this would read: r = cells(4,2).value Functions vs. Subroutines : 33 Functions vs. Subroutines Writing data back to the spreadsheet is just as easy. Let’s say that you have calculated a variable x that you want to write back to cell d8. You would use the following command to do this: cells(8,4).value = x Let’s look at an example of this in an actual spreadsheet (the spreadsheet is named “input-output example.xls” and is on my web-site.) Other Items : 34 Other Items We have not yet gotten into a couple of other key issues, how to control the flow of your program, and how to make loops. We will examine the “flow control.xls” spreadsheet to do this. This spreadsheet has three functions embedded in it: Maxfinder(a,b) Sumcount(x) Fact(x) The purpose of these functions is to demonstrate how to control the flow of your VBA program. Let’s look at each of these functions. Other Items : 35 Other Items Maxfinder(a,b) This function simply returns the greater of a or b. While there is obviously a built-in excel function that can do this, I include it in this spreadsheet because it is the simplest way to illustrate the most basic control structure in programming: the if-then statement. The if-then-else statement is simply a way of telling VBA that if a condition is true to take one set of actions, and if it is not true to take a second set of actions. Generally, the format is: If (condition is true) then code to be executed if condition is true which can be many lines long… Else code to be executed if the condition is not tru which can also be many lines long… Endif Other Items : 36 Other Items Here is the code for the function MaxFinder ' A very simple use of an if-then loop. Function MaxFinder(a, b) Dim temp If a > b Then temp = a Else temp = b End If MaxFinder = temp End Function Another Example : 37 Another Example Finally, there is another example spreadsheet on my web-paged named “Starting Point.xls.” This spreadsheet contains an extremely well-documented subroutine that will read cash flows and an interest rate from the spreadsheet, will calculate the present value of those cash flows, and will return that present value to the spreadsheet. This subroutine uses all of the tools that we have developed in this set of slides.

Related presentations


Other presentations created by ankush85

Nanotechnology
03. 04. 2009
0 views

Nanotechnology

Human Resource Management System
06. 03. 2009
0 views

Human Resource Management System

Nanotechnology
27. 03. 2009
0 views

Nanotechnology

INTRODUCTION TO NANOTECHNOLOGY
20. 11. 2009
0 views

INTRODUCTION TO NANOTECHNOLOGY

Nanotechnology in Sports
22. 11. 2009
0 views

Nanotechnology in Sports

Computer Architecture
14. 07. 2009
0 views

Computer Architecture

HR
18. 03. 2009
0 views

HR

Hospital Management System
08. 04. 2009
0 views

Hospital Management System

Welcome to Visual Basic
06. 03. 2009
0 views

Welcome to Visual Basic

HTML
20. 04. 2009
0 views

HTML

Computer Languages
03. 06. 2013
0 views

Computer Languages

Taking Care of Computer
03. 06. 2013
0 views

Taking Care of Computer

Understanding Camera
16. 10. 2012
0 views

Understanding Camera

Photography Technical Terms
25. 09. 2012
0 views

Photography Technical Terms

Basics of Photography
25. 09. 2012
0 views

Basics of Photography

AIR MUSCLES
03. 01. 2012
0 views

AIR MUSCLES

Molecular Nanotechnology
24. 11. 2009
0 views

Molecular Nanotechnology

Nanotech Innovation
22. 11. 2009
0 views

Nanotech Innovation

FINANCIAL  MARKET
16. 05. 2009
0 views

FINANCIAL MARKET

Operating System
19. 04. 2009
0 views

Operating System

Management Control
10. 07. 2009
0 views

Management Control

Accounting Principles
01. 07. 2009
0 views

Accounting Principles

Balance Sheet
21. 07. 2009
0 views

Balance Sheet

Balance Sheet Auditing
01. 07. 2009
0 views

Balance Sheet Auditing

BRANCH AND BOUND
05. 03. 2009
0 views

BRANCH AND BOUND

THE  THREE  BRANCHES  OF GOVERNMENT
13. 06. 2009
0 views

THE THREE BRANCHES OF GOVERNMENT

Structure of Atom
11. 05. 2009
0 views

Structure of Atom

Compression Techniques
05. 03. 2009
0 views

Compression Techniques

DYNAMIC PROGRAMMING
06. 03. 2009
0 views

DYNAMIC PROGRAMMING

Quantum Mechanics
25. 08. 2009
0 views

Quantum Mechanics

Marketing Plan
19. 06. 2009
0 views

Marketing Plan

Book Keeping
24. 06. 2009
0 views

Book Keeping

DFDS
02. 10. 2008
0 views

DFDS

Semiconductors
25. 04. 2009
0 views

Semiconductors

Organic  Chemistry
28. 04. 2009
0 views

Organic Chemistry

Atoms Molecules and Ions
17. 06. 2009
0 views

Atoms Molecules and Ions

Covalent Bond
25. 04. 2009
0 views

Covalent Bond

Cost Accounting Standards
25. 06. 2009
0 views

Cost Accounting Standards

Crisis Management
19. 06. 2009
0 views

Crisis Management

Marketing
18. 03. 2009
0 views

Marketing

Business Strategy
27. 04. 2009
0 views

Business Strategy

Time Management
23. 03. 2009
0 views

Time Management

Networking Protocols
23. 05. 2009
0 views

Networking Protocols

Network Layer
23. 05. 2009
0 views

Network Layer

Final Accounts
24. 06. 2009
0 views

Final Accounts

ARTIFICIAL  INTELLIGENCE
03. 04. 2009
0 views

ARTIFICIAL INTELLIGENCE

Play with C
08. 04. 2009
0 views

Play with C

Software Development Cycle
23. 03. 2009
0 views

Software Development Cycle

THE GREEDY METHOD
06. 03. 2009
0 views

THE GREEDY METHOD

JOB SEQUENCING
06. 03. 2009
0 views

JOB SEQUENCING

Electricity and Magnetism
29. 04. 2009
0 views

Electricity and Magnetism

Optical Fiber
24. 05. 2009
0 views

Optical Fiber

Quality Assurance
06. 03. 2009
0 views

Quality Assurance

Object-oriented Design
11. 04. 2009
0 views

Object-oriented Design

A.R. Rahman
08. 03. 2009
0 views

A.R. Rahman

Hollywood Female Celebrities
13. 03. 2009
0 views

Hollywood Female Celebrities

Flow nets
04. 09. 2009
0 views

Flow nets

Energy and Nanotechnology
22. 11. 2009
0 views

Energy and Nanotechnology

LOGIC  DESIGN
06. 03. 2009
0 views

LOGIC DESIGN

VB-IDE
06. 03. 2009
0 views

VB-IDE

DLF IPL FINAL
25. 05. 2009
0 views

DLF IPL FINAL

MINIMUM SPANNING TREES
06. 03. 2009
0 views

MINIMUM SPANNING TREES

CPU Scheduling
05. 03. 2009
0 views

CPU Scheduling

Virtual Memory
05. 03. 2009
0 views

Virtual Memory

POK
09. 05. 2009
0 views

POK

2D Transformations
05. 03. 2009
0 views

2D Transformations

Greedy Algorithms
05. 03. 2009
0 views

Greedy Algorithms

BACKTRACKING
05. 03. 2009
0 views

BACKTRACKING

DIVIDE And CONQUER
06. 03. 2009
0 views

DIVIDE And CONQUER

ELEMENTARY DATA STRUCTURES
06. 03. 2009
0 views

ELEMENTARY DATA STRUCTURES

JPEG Compression
06. 03. 2009
0 views

JPEG Compression

Mpeg-compression
06. 03. 2009
0 views

Mpeg-compression

NP - HARD
06. 03. 2009
0 views

NP - HARD

TRAVELLING SALESPERSON PROBLEM
06. 03. 2009
0 views

TRAVELLING SALESPERSON PROBLEM

Introduction to Java
09. 03. 2009
0 views

Introduction to Java

Java  Basics
09. 03. 2009
0 views

Java Basics

Heuristic Search
13. 03. 2009
0 views

Heuristic Search

HSM
19. 03. 2009
0 views

HSM

Tata's  Nano  Car
25. 03. 2009
0 views

Tata's Nano Car

Air Cranes
08. 04. 2009
0 views

Air Cranes

Newborn Care
10. 04. 2009
0 views

Newborn Care

Photosynthesis Process
10. 04. 2009
0 views

Photosynthesis Process

ActionScript
13. 04. 2009
0 views

ActionScript

Xml
15. 04. 2009
0 views

Xml

Snakes mis use
17. 04. 2009
0 views

Snakes mis use

Php Web Development
21. 04. 2009
0 views

Php Web Development

Cricket Intro
21. 04. 2009
0 views

Cricket Intro

Cricket Umpiring and Rules
21. 04. 2009
0 views

Cricket Umpiring and Rules

Arm and Forearm
23. 04. 2009
0 views

Arm and Forearm

Elements Ions Isotopes
25. 04. 2009
0 views

Elements Ions Isotopes

Chemical Bond
25. 04. 2009
0 views

Chemical Bond

Discovering Newtons Laws
29. 04. 2009
0 views

Discovering Newtons Laws

FreeFall
29. 04. 2009
0 views

FreeFall

Digital photography
26. 04. 2009
0 views

Digital photography

Health Effects of Alcohol
26. 04. 2009
0 views

Health Effects of Alcohol

Poetry Terminology
27. 04. 2009
0 views

Poetry Terminology

Indian Force
05. 05. 2009
0 views

Indian Force

Machine Intelligence
14. 05. 2009
0 views

Machine Intelligence

Data Link Layer
16. 05. 2009
0 views

Data Link Layer

Database Development Cycle
15. 05. 2009
0 views

Database Development Cycle

Queue
15. 05. 2009
0 views

Queue

Presentaion Skills
23. 04. 2009
0 views

Presentaion Skills

Network Layers
23. 05. 2009
0 views

Network Layers

Narmada Dam, India
23. 05. 2009
0 views

Narmada Dam, India

User Datagram Protocol
24. 05. 2009
0 views

User Datagram Protocol

Linear Momentum
28. 05. 2009
0 views

Linear Momentum

Stack and Queue
04. 06. 2009
0 views

Stack and Queue

Information Management
19. 06. 2009
0 views

Information Management

Role of Senior Management
19. 06. 2009
0 views

Role of Senior Management

Wake Up India
07. 06. 2009
0 views

Wake Up India

Adobe Flex 3.0
13. 06. 2009
0 views

Adobe Flex 3.0

Adobe Flex Presentation
13. 06. 2009
0 views

Adobe Flex Presentation

Introduction to Adobe Flex
13. 06. 2009
0 views

Introduction to Adobe Flex

Adobe Flash Media Server
13. 06. 2009
0 views

Adobe Flash Media Server

Dreamweaver
13. 06. 2009
0 views

Dreamweaver

Adobe Flash
13. 06. 2009
0 views

Adobe Flash

Flash CS4 Professional
13. 06. 2009
0 views

Flash CS4 Professional

Adobe Flash Lite
13. 06. 2009
0 views

Adobe Flash Lite

Digital Camera
13. 06. 2009
0 views

Digital Camera

Mozilla_Firefox
15. 06. 2009
0 views

Mozilla_Firefox

Managerial Accounting
27. 06. 2009
0 views

Managerial Accounting

Accounting  Information  System
01. 07. 2009
0 views

Accounting Information System

RETAILING AND MARKETING
06. 07. 2009
0 views

RETAILING AND MARKETING

ACCOUNTING IN BUSINESS
05. 07. 2009
0 views

ACCOUNTING IN BUSINESS

STRATEGIC RETAIL MANAGEMENT
05. 07. 2009
0 views

STRATEGIC RETAIL MANAGEMENT

Reiki
01. 08. 2009
0 views

Reiki

Using Buttons in PowerPoint
26. 04. 2009
0 views

Using Buttons in PowerPoint

Nanotechnology  for  Students
21. 08. 2009
0 views

Nanotechnology for Students

NANOSCIENCE
25. 08. 2009
0 views

NANOSCIENCE

Fundamentals of Nanoscience
27. 08. 2009
0 views

Fundamentals of Nanoscience

Nanoscience in Nature
27. 08. 2009
0 views

Nanoscience in Nature

Applied Mechanics
04. 09. 2009
0 views

Applied Mechanics

Accounts Payable Training
27. 06. 2009
0 views

Accounts Payable Training