Visual Basic for Applications (VBA) for Excel

1
Visual Basic for Applications (VBA)for Excel

• Prof. Yitzchak Rosenthal

2
Start the Visual Basic Editor

• Microsoft office applications (e.g. Excel ,
  powerpoint, word, access) have a built-in visual
  basic editor
• Choose menu choice "Tools Macro Visual Basic
  Editor"

3
The Visual Basic Editor
4
Separate Windows
VB Editor Window
Spreadsheet Window

• The VB editor is opened in a separate window from
  the Excel spreadsheet.
• If you close the VB editor you can continue using
  the spreadsheet.
• IF YOU CLOSE THE SPREADSHEET, THE VB EDITOR WILL
  CLOSE

5
Separate Code Sections

• There are different code sections.
• Double click on a sheet or workbook to enter code
  for that sheet or workbook.

Step1 Double click here. Step2 Enter code for
sheet1
6
Insert Module

• Choose the insert modulemenu to create a
  new module.
• Modules contain generic code that is used by any
  sheet.

7
Recording Macros
8
Tools Macro Record New Macro

• Choose the menu choice Tools Macro Record
  New Macro to start the macro recorder.

9
Enter a name

• Enter a name for the Macro and press OK

10
Stop Recording Button

• When you start recording a macro, the Stop
  Recording Toolbar displays.
• Press the stop recording button when you are
  finished performing the steps for the macro.

Stop Recording Button
11
Perform the Steps for the Macro in Excel

• Perform whatever actions you want in Excel.
• Example
• Step 1 insert a new line at the top of the
  spreadsheet.
• Step 2 click on cell A1 to select it
• Then press stop recording.
• See next slide for the VBA code that is created
  by the macro recorder.

12
See code in VB Editor

• Look at the Modules in the VB Editor to see the
  code for the recorded macro.

13
Execute the Macro

• To execute the macro, choose the menu choice,
  Tools Macro Macros

14
Run the Macro

• Then choose the macro and click Run.

15
The Macro is Executed

• See the new row

16
VBA vsVB.Net
17
VBA vs VB.Net

• You can use manyVB.Net concepts and code in VBA
• Like VB.Net, VBA also provides the ability to
  create textboxes, buttons, checkboxes, radio
  buttons, etc. for use in Excel (we will not cover
  how to do that here).
• HOWEVER
• VBA is based on an older version of Visual Basic.
• There are some differences between how to write
  VBA and VB.Net code.

18
MsgBox vs MessageBox.Show

• MsgBox vs MessageBox.show
• VBA MsgBox ("Hello There")
• VB.Net MessageBox.Show("Hello There")

19
No HANDLES clause in VBA

• Event Handlers
• VBA
• No "Handles" clause.
• Name of sub determines which event is handled
• Example Private Sub Button1_Click()
  MsgBox ("hello there") End Sub
• VB.Net
• Needs "Handles" clause
• Name of sub does NOT determine which event is
  handled
• Example Private Sub Button1_Click( ByVal
  sender As System.Object, _ ByVal e
  As System.EventArgs) _ Handles
  Button1.Click MessageBox.Show("Hello
  there") End Sub

20
DIM may not use initialization value

• VB.Net
• In VB.Net you can declare a variable with Dim and
  give it an initial value all in one step.Dim
  number as Integer 1
• VBA
• In VBA you MAY NOT use an initialization value in
  a Dim statement.
• Therefore the code above would not be legal.
• Instead do the followingDim number as
  Integernumber 1

21
Call

• Calling a Sub
• VBA
• Must use the "Call" keyword if sub is in a
  different module
• Don't use parentheses if the sub doesn't contain
  any parameters
• Example Call DoIt
• VB.Net
• Just use the name of the Sub
• Example DoIt( )

22
Writing Your Own Code
23
Sub

• Place all code to be executed in a "subroutine"
• Will explain more soon ...

Sub doSomething ( ) MsgBox ("Hello
There") MsgBox ("How are you doing?") End Sub
24
How to run (i.e. execute) a sub

• To execute the subroutine
• first place cursor anywhere in the Sub
• second Choose menu choiceRun Run Macro

Sub doSomething ( ) MsgBox ("Hello There")
MsgBox ("How are you doing?") End Sub
25
Other ways to execute a sub

• To execute the subroutine
• first place cursor anywhere in the Sub
• second Press F5 OR press the "Run
  macro" button

Sub doSomething ( ) MsgBox ("Hello There")
MsgBox ("How are you doing?") End Sub
26
What happens when you run this subroutine?
27
Running the subroutine

• Excel spreadsheet displays
• First message box appears.
• Press OK
• Second Message box appears
• Press OK
• subroutine is finished (returns to the code
  window)

Sub doSomething ( ) MsgBox ("Hello
There") MsgBox ("How are you doing?") End Sub
When you press OK the 2nd message box will appear
(not shown)
28
Functions
29
Functions

• You can define your own VBA functions for use in
  the Excel spreadsheet.

30
Sample Function

• This function calculates the sum of the values
  from lowNumber to highNumber

Function summation(lowNumber As Integer,
highNumber As Integer) as Integer Dim count
As Integer summation 0 For
count lowNumber To highNumber summation
summation count Next count End
Function
31
Using the sample function in Excel
Formulas View
ValuesView
32
Rules for Functions

• The function has a type specified AFTER the
  parameter list
• Function summation(lowNumber As Integer,
  highNumber As Integer) as Integer
• The name of the function is used as a variable
  inside the function
• The entire function call becomes the value that
  the name of the function had when the function
  finished executing.

33
Quick Intro to VB for the Novice
34
Syntax

• Syntax
• syntax means the "grammar" of (or the rules for
  writing) a programming language or command

35
Syntax of SUB
These are required

• First line
• Must type "Sub" (without the quotes)
• make up a name for the sub
• must type parentheses
• Body
• Use VBA statements
• Last Line
• must type "End Sub" (without the quotes)

VBA statements go here
make up a name for the Sub (more later ...)
Sub doSomething ( ) MsgBox ("Hello
There") MsgBox ("How are you doing?") End Sub
36
More than one sub

• You can have many Subroutines
• Each subroutine must have a unique name
• When you execute a subroutine only it runs, not
  the other subroutines

37
Example
Sub doSomething ( ) MsgBox ("Hello
There") MsgBox ("How are you doing?") End
Sub Sub doSomethingElse ( ) MsgBox ("I am
having fun.") MsgBox ("Are you having fun?") End
Sub

• Each sub has a unique name
• You can run each sub separately

38
Rules for subroutine names

• Rules for Subroutine names
• must start with a letter
• can include
• letters (e.g. A B C a b c etc.)
• digits (e.g. 0 1 2 etc.)
• underscores (i.e. _ )
• may not be a VBA "keyword"
• some keywords are "sub", "end", "dim" (we'll see
  more later)
• may NOT include any "special" characters (e.g.
  !_at_ etc)
• Examples on next slide ...

39
Examples

• Legal subroutine names
• mySubroutine
• hello
• born2ride
• born_to_ride
• Illegal subroutine names
• 3cheers
• may not start with a number
• bagelslox
• may not include characters other than letters or
  nubmers or underscore ( _ )
• name with spaces
• may not include spaces in the name

40
END OF PRESENTATION

• The following slides are in progress

41
MsgBox ("Your message goes here")
42
MsgBox( )

• MsgBox ( ) is used to display a dialog box with a
  message to the user.
• Syntax
• You must include the word "MsgBox" (without the
  quotes) followed by a set of parentheses
• Place the message in the parentheses
• For now (until I teach you otherwise) assume that
  the entire message should be enclosed in quotes
  (later we'll learn more about when you don't need
  the quotes ...)
• For now (until I teach you otherwise) assume that
  the entire statement must be typed on one line
  (later we'll learn how to break up a long line
  ...)
• Example on next slide ...

43
Example
Put the message in quotes.

• Sub showAMessageBox( ) MsgBox ("This will be
  displayed in a message box") End Sub

44
Numbers

• If your message only includes a number, you do
  NOT have to put it in quotes (we'll explain why
  later)
• Example The following is perfectly fineSub
  showANumber( ) MsgBox(123)End Sub

No quotes necessary around a number.
45
Expressions
46
Only one "value"
47
String Expression

• String expressions"This is a constant string
  expression""This is another string expression"
• Numeric expressions334

48

• msgbox(ltstring expressiongt)

49
Differences between VB.Net and VBA
50
MsgBox vs MessageBox.Show

• VB.Net MessageBox.Show(my message)
• VBA msgbox(my message)

51
No HANDLES clause in VBA

• There is no Handles clause in VBA.
• In VBA the NAME of the sub indicates which events
  it handles
• Examplebutton1_click is the name of the click
  handler for the button named button1