vba & excel barry l. nelson iems 465 fall quarter 2003
TRANSCRIPT
![Page 1: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/1.jpg)
VBA & Excel
Barry L. Nelson
IEMS 465
Fall Quarter 2003
![Page 2: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/2.jpg)
VBA
• Is a significant subset of the stand-alone Visual Basic programming language
• It is integrated into Microsoft Office applications (and others, like Arena)
• It is the macro language of Excel• You can add
– Forms for dialog boxes with user– Classes for object definitions– Modules containing procedures
![Page 3: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/3.jpg)
Accessing VBA in Excel
• Tools Macros Visual Basic Editor
• Enter VBA through the navigation buttons in the top toolbars
VBA Design Mode
Visual Basic Editor
Design mode is the time during which no code from the project is running and events from Excel or project will not execute. You can leave design mode by executing a macro or using the Immediate window.
![Page 4: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/4.jpg)
VB Edit Window
Project Explorer
Property Inspector
Code Window
Edit Window Options
![Page 5: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/5.jpg)
Variables
• Declare by Dim
• Better to use Data Types:Dim amount As DoubleDim year As IntegerDim name As String
• Other data types: Boolean, Byte, Currency, Date
• Default (no type) is Variant
![Page 6: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/6.jpg)
Variable(cont’d.)
• % - integer & - long integer! - single # - double@ currency$ - stringanIntegerValue% =3, aString$ = "hallo"
• Can modify with scope (outside procedure)Private I As IntegerPublic billsPaid As Currency
• Make values permanentStatic yourName As String
• Multiple variablesPrivate test, amount, J As Integer
![Page 7: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/7.jpg)
Constants
• [Public|Private] Const constantName [As type] = expression
Public Const PI = 3.1, NumPLANETS = 9Const PI2 = PI * 2Const RELEASE = #1/1/99/#
![Page 8: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/8.jpg)
Control Structures
• DecisionIf anyDate < Now Then anyDate =
Now
If anyDate < Now ThenanyDate = Now
End If
If … Then … Else
![Page 9: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/9.jpg)
Decisions(cont’d.)
If Index = 0 ThenCopyActiveControlClearActiveControl
Else If Index = 1 ThenCopyActiveControl
Else If Index = 2 ThenClearActiveControl
ElsePasteActive Control
End If
![Page 10: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/10.jpg)
Decisions(cont’d.)
Select Case IndexCase 0
CopyActiveControlClearActiveControl
Case 1CopyActiveControl
Case 2ClearActiveControl
Case 3PasteActive Control
Case ElsefrmFind.Show
End Select
![Page 11: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/11.jpg)
Loops/Iterations
• Do … LoopDo While condition
statementsLoop
Dostatements
Loop While condition
![Page 12: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/12.jpg)
Loops(cont’d.)
• For … NextFor counter = start To end [Step increment]
statements Next counter
• For Each … NextFor Each element In group
statementsNext element
![Page 13: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/13.jpg)
Exiting Control Structures
For counter = start To end [Step increment][statement block][Exit For][statement block]
Next [counter [, counter][, …]]
Do [ {While|Until} condition]
[statement block][Exit Do][statement block]
Loop
![Page 14: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/14.jpg)
Exiting(cont’d.)
• Can also exit from a procedure:
Exit Sub
![Page 15: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/15.jpg)
Code Modules
• Excel Objects (ThisWorkbook, Sheet#)
• Modules– Typically we put our code here– A Module is a collection of Subs and
Functions– Insert Module
• Class Modules
• User Forms
![Page 16: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/16.jpg)
Procedures
• Sub (routines)– no value returned– Called without parenthesis
mySub param1, param2– Called with parenthesis
Call mySub(param1, param2)
• Functions– value returned– assign return value to function name
![Page 17: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/17.jpg)
Arguments for Procedures
• Pass by Reference (default)sub stuff(item As String) ‘or sub stuff(ByRef item As String)…stuff(“car”)
• Pass by Value (must be declared)sub stuff(ByVal item As String)
• Note that arguments must be formally declared (as must the return type of functions)
![Page 18: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/18.jpg)
Some Useful Code
• The following are some pieces of code that are useful for doing VBA with Excel.
• See the code on the course web site for other examples.
![Page 19: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/19.jpg)
Finding/Creating a Sheet Dim found As Boolean Dim sheetNext As Worksheet ' Set up mySheet sheet for output found = False For Each sheetNext In Worksheets If sheetNext.Name = “mySheet" Then found = True Exit For End If Next sheetNext If found = True Then Worksheets(“mySheet").Select ActiveSheet.UsedRange.Clear Else Worksheets.Add ActiveSheet.Name = “mySheet" End If
![Page 20: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/20.jpg)
Writing to a Sheet
• Put the absolute value of the variable Fudge in row 2, column 20 of the Sheet named mySheet.
Worksheets(“mySheet”).Cells(2,20) = VBA.Abs(Fudge)
![Page 21: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/21.jpg)
Use an Excel Function
• VBA has a limited number of built-in functions
• You can access any Excel worksheet function.
• This example uses the Excel Max function
W = WorksheetFunction.Max(0, W + S - a)
![Page 22: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/22.jpg)
Running the Code
• Perhaps the easiest way to run the code is to place your cursor in the module you want to run and press the Run Sub/UserForm button.
• Your modules will as appear as Macros that can be run from Excel under
Tools Macros Macros…
![Page 23: VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003](https://reader036.vdocuments.us/reader036/viewer/2022062407/56649daf5503460f94a9cb18/html5/thumbnails/23.jpg)
Finishing Up
• Explore what is available in the IEMS 465 code
• Exercise: Write a Sub that inserts a worksheet named “Count” into the Workbook, then writes the numbers 1,2,…,10 in the first row, the first ten columns. Use a loop to do this.