visual basic for applications companion slides
DESCRIPTION
These are companion slides to the video at http://blip.tv/file/3011639TRANSCRIPT
![Page 1: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/1.jpg)
Visual Basic for Applications
Companion slides to http://blip.tv/file/3011639
© The Stanford Decisions and Ethics Center
decision.stanford.edu
![Page 2: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/2.jpg)
On our agenda for today…
• Visual Basic for Applications
![Page 3: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/3.jpg)
Buttons and Macros
• Lets create a simple button
![Page 4: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/4.jpg)
Debugging
• MsgBox
• Debug.print (Immediate Window – Ctrl+G)
![Page 5: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/5.jpg)
Iteration Logic
• While Loops Dim MyArray(5) As Double For I = 1 To 5 MyArray(I) = Rnd Next I For Each n In MyArray Debug.Print n Next n
Array Indexing Starts from 1
![Page 6: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/6.jpg)
Iteration Logic
• While-Wend Loops
Dim MyArray(5) As Double I = 0
While i<5 MyArray(I) = Rnd
I = I+1 Wend For Each n In MyArray Debug.Print n Next n
![Page 7: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/7.jpg)
Iteration Logic
• Do While
Do While conditionblah blah
Loop
Doblah blah
Loop While condition
![Page 8: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/8.jpg)
Iteration Logic
• Do Until
Do Until conditionblah blah
Loop
Doblah blah
Loop Until condition
![Page 9: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/9.jpg)
Conditional Logic
• Simple Conditionals
If condition_holds Then Do_something
![Page 10: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/10.jpg)
Conditional Logic
• Nested Conditionals
If condition_holds Then Do_something
ElseIf another_condition_holds Then Do_something_else
End If
![Page 11: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/11.jpg)
Conditional Logic
• More Nesting
If condition_holds Then Do_something
ElseIf another_condition_holds Then Do_something_else
ElseIf another_condition_holds Then Do_something_else
Else do_some_default_thing
End If
![Page 12: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/12.jpg)
Example
• Armstrong Numbers
• Sum of the cubes of the Armstrong number = number itself
E.g. 153
1^3 + 5^3 + 3^3 = 1 + 125 + 27 = 153
![Page 13: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/13.jpg)
Reading and Writing
• From and to cells
• From and to cells in different worksheets
![Page 14: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/14.jpg)
Functions and Subroutines
Sub Blah()
End Sub
Function Blah()
Blah = …
End Function
![Page 15: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/15.jpg)
Refactoring
Any fool can write code that a computer
understands. Only a good programmer
can write code that a human understands.
Martin Fowler, in his classic, Refactoring
![Page 16: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/16.jpg)
Refactoring
is changing design without changing functionality
Important refactorings for VBA• Decompose into Small Sub/Functions• Rename to use Intention-Revealing Names
Main MotivationONCE and ONLY ONCE
![Page 17: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/17.jpg)
Getting Inputs
Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") If Quantity = "" Then Exit Sub If Quantity >= 0 Then Discount = 0.1 If Quantity >= 25 Then Discount = 0.15 If Quantity >= 50 Then Discount = 0.2 If Quantity >= 75 Then Discount = 0.25 MsgBox "Discount: " & Discount
![Page 18: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/18.jpg)
Select Case Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "" Exit Sub Case 0 To 24 Discount = 0.1 Case 25 To 49 Discount = 0.15 Case 50 To 74 Discount = 0.25 End Select MsgBox "Discount: " & Discount
![Page 19: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/19.jpg)
Reffering to cells
Range(“name”).Value = NewValueRange(“A5”).value = …
Cells(..,..).Offset(..,..).ValueActiveCell.Offset(..,..).ValueRange(“..”).Offset(..,..).Value
ActiveSheet.Cells.ClearContents
![Page 20: Visual Basic For Applications Companion Slides](https://reader033.vdocuments.us/reader033/viewer/2022061301/548f863db47959da588b4671/html5/thumbnails/20.jpg)
Getting VBA Help
• Make sure the Help for VBA module is installed when installing Excel 2003
• Use ObjectBrowser