Memahami MS Excel VBA Dony Pranadiyanta ST VBA
Memahami MS Excel VBA Dony Pranadiyanta, ST
VBA - Excel • VBA is Visual Basic for Applications • The goal is to demonstrate how VBA can be used to leverage the power of Excel • • VBA syntax and usage the Excel VB programming environment the Excel object model an application
VBA - Excel • What advantage is there in using VBA extend Excel – new functions makes it possible to use the Execl GUI environment makes it possible to respond to events (mouse, …) makes Windows programming API accessible Excel can be used to control Automation servers (other software components that expose an API through COM) • by understanding how to use the Excel object model with VBA it is a small step to using Excel as an Automation server (controlled by other program) • • •
VBA - Excel • In order to run VBA code your security settings must be properly set • Tools | Macro | Security… • At least Medium security must be set – each macro will require user verification to run • Signed code can be run in all cases
Objectives • Create a macro using the macro recorder • Work with the Project Explorer and Properties window of the VBA Editor • Edit a sub procedure • Run a sub procedure • Work with VBA objects, properties, and methods • Create an input box to retrieve information from the user New Perspectives on Microsoft Office Excel 2007 5
Objectives • Create and run If-Then control structures • Work with comparison and logical operators • Create message boxes • Customize the Quick Access Toolbar • Customize Excel New Perspectives on Microsoft Office Excel 2007 6
Developing an Excel Application • If the Excel Developer tab is not on the Ribbon, click the Office Button, click the Excel Options button, click Popular in the Excel Options dialog box (if necessary), click the Show Developer tab in the Ribbon check box to insert a check mark, and then click the OK button New Perspectives on Microsoft Office Excel 2007 7
Working with the Visual Basic Editor • In the Code group on the Developer tab, click the Macros button • Click the macro name in the Macro name box, if necessary, and then click the Edit button New Perspectives on Microsoft Office Excel 2007 8
Working with the Visual Basic Editor • A project is a collection of macros, worksheets, data-entry forms, and other items that make up the customized application you’re trying to create • Project Explorer is the window in the Visual Basic Editor that displays a hierarchical list of all currently open projects and their contents • The Project Explorer window is dockable • An object is any element within the Excel working environment such as a worksheet, cell, workbook, or even Excel itself New Perspectives on Microsoft Office Excel 2007 9
Working with the Visual Basic Editor • A property is an attribute of an object that defines one of its characteristics, such as its name, size, color, or location on the screen • You can view a list of properties for any object in the Properties window • A module is a collection of VBA macros • The Code window displays the VBA macro code associated with any item in Project Explorer New Perspectives on Microsoft Office Excel 2007 10
Working with the Visual Basic Editor New Perspectives on Microsoft Office Excel 2007 11
Working with Sub Procedures • A sub procedure performs an action on your project or workbook, such as formatting a cell or displaying a chart • A function procedure returns a value • A property procedure is used to create custom properties for the objects in your project • Syntax refers to the set of rules that specify how you must enter certain commands so that VBA interprets them correctly • A comment is a statement that describes the behavior or purpose of a procedure, but does not perform any action New Perspectives on Microsoft Office Excel 2007 12
Working with Sub Procedures New Perspectives on Microsoft Office Excel 2007 13
Referring to Objects • VBA is an object-oriented programming language, in which tasks are performed by manipulating objects New Perspectives on Microsoft Office Excel 2007 14
Referring to Objects • Objects are often grouped into collections, which are themselves objects, called collection objects New Perspectives on Microsoft Office Excel 2007 15
Referring to Objects • VBA organizes objects and object collections in a hierarchy with the Excel application at the top and the individual cells of a workbook at the bottom New Perspectives on Microsoft Office Excel 2007 16
Referring to Objects New Perspectives on Microsoft Office Excel 2007 17
Applying Methods • A method is an action that can be performed on an object, such as closing a workbook or printing the contents of a worksheet New Perspectives on Microsoft Office Excel 2007 18
Working with Variables and Values • A variable is a named element in a program that can be used to store and retrieve information • Every variable is identified by a unique variable name • Dim variable as type New Perspectives on Microsoft Office Excel 2007 19
Retrieving Information from the User New Perspectives on Microsoft Office Excel 2007 20
Working with Conditional Statements New Perspectives on Microsoft Office Excel 2007 21
Working with Conditional Statements New Perspectives on Microsoft Office Excel 2007 22
Working with Conditional Statements New Perspectives on Microsoft Office Excel 2007 23
Working with Conditional Statements New Perspectives on Microsoft Office Excel 2007 24
Creating a Message Box • Msg. Box Prompt, Buttons, Title New Perspectives on Microsoft Office Excel 2007 25
VBA – The Basics • Data types • • • Integer Long Single Double Currency String Byte Boolean Date Object Variant 2 byte integer 4 byte floating point 8 byte real upto 64 K characters 1 byte 2 byte true or false 8 bytes 4 bytes – an object reference 16 bytes + 1 byte / character
Variables • Declare by Dim • Better to use Data Types: Dim amount As Double Dim year As Integer Dim name As String • Other data types: Boolean, Byte, Currency, Date • Default (no type) is Variant
Variable(cont’d. ) • % - integer & - long integer ! - single # - double @ currency $ - string an. Integer. Value% =3, a. String$ = "hallo" • Can modify with scope (outside procedure) Private I As Integer Public bills. Paid As Currency • Make values permanent Static your. Name As String • Multiple variables Private test, amount, J As Integer
Constants • [Public|Private] Const constant. Name [As type] = expression Public Const PI = 3. 1, Num. PLANETS = 9 Const PI 2 = PI * 2 Const RELEASE = #1/1/99/#
VBA – The Basics • The variant data type is special – a variant can hold any type of data • A variable declared as variant (the default) can hold anything • The actual type of the data is kept in the data • It adds flexibility but at a cost – it requires more processing at compute time to determine what it is and how to handle it
VBA – The Basics • Variables • must start with a letter • can contain _ and numbers • cannot exceed 255 characters in length • Within a procedure declare a variable using Dim variable As type • If a variable is not declared it will be created when used, the type will be Variant • Use Option Explicit in the declarations section to require declaration of variables • VBA variables have scope restrictions • variables declared in a procedure are local to that procedure • variables declared in a module can be public or private
VBA – The Basics • String variables Dim variable As String * 50 • The first form is variable length • The second form is limited to 50 characters • the variable will be space filled if string is < 50 characters • the string will be truncated if the contents are > 50 characters • the Trim and RTrim functions are useful for working with fixed length strings • Boolean variables contain either True or False
VBA – The Basics • The Object type is used to store the address (a reference) of an object • this form can be used for As Object any object Dim variable • this is referred to as late-binding, the object types are checked at runtime (slower) • The declaration of a specific object is • this form will only store Excel Worksheet objects, an attempt to put anything Dim variable As Worksheet else into it will result in an error • this is referred to as early-binding, the object types are checked at compile time (faster)
VBA – The Basics • Arrays are declared using Dim A (1 To 10) As Double Dim B (1 To 10, 1 To 10) As Double Dim C (4, 4, 4) As Integer Dim D () As Double • Arrays can be multidimensional • The lower bound starts at zero • can explicitly specify lower bound • can use Option Base command to reset to something other than 0 Option Base 1 • The last form above is a dynamic array – it must be dimensioned using Re. Dim before it can be used • Use Re. Dim Preserve to retain any existing entries in array - only the upper bound of array can be changed
VBA – The Basics • Constants are declared using Const pi = 3. 14159 Const pi As Double = 3. 14159 • Constants have the same scope limitations as variables
VBA – The Basics • User defined data types • can only be defined in the declarations section of a Module • can be Public or Private in scope Public Type System. Info CPU As Variant Memory As Long Color. Bits As Integer Cost As Currency Purchase. Date As Date End Type • Declare variable with this type Dim My. System As System. Info • Referencing fields My. System. CPU = “Pentium” If My. System. Purchase. Date > #1/1/2006# Then … End If
VBA – The Basics • Watch out for Dim a, b, c As Integer • it is equivalent to Dim a As Variant Dim b As Variant Dim c As Integer
VBA – The Basics • Objects • VBA can use pre-defined objects – such as intrinsic Excel objects • VBA can create user-defined objects – Class Modules • Declaring a variable to contain an object Dim variable As class Dim variable As New class • the first form declares that the variable will contain a reference to an object of the named class • the second form declares the variable then creates an instance of the class • To instantiate a class Set variable = New class
VBA – The Basics • Objects • To declare a variable that will refer to an instance of the Excel Worksheet class Dim ws 1 As Worksheet • To put a reference into it Set ws 1 = Worksheets(“Sheet 1”) • This fragment will print the name of the worksheet “Sheet 1” Dim ws 1 As Worksheet Set ws 1 = Worksheets("sheet 1") Debug. Print ws 1. Name
VBA – The Basics • Objects - Collections • There is a special form of objects known as Collections • They contain references to other objects and collections • It is the mechanism by which the object hierarchy is defined • By convention, collection names are usually plural • Workbooks – list of Workbook objects • Worksheets – list of Worksheet objects • Range – list of objects that represent cells, columns, rows • The following example iterates through Workbooks collection For Each ws In Worksheets Debug. Print ws. Name Next
VBA – The Basics • Statements • VBA implements common programming statements • logical statements • looping statements • expressions
VBA – The Basics • Logical statements • The If Then Else statement is the basic logic test If a>10 Then … End If If a>10 Then … Else. If a<0 Then … Else … End If
VBA – The Basics • Logical statements • The Select statement can be used to replace a multi-way if statement Select Case expression Case expr 1 … Case expr 2 … Case Else … End Select
VBA – The Basics • Loop statements • Various Do loop forms Do While expr … Loop Do Until expr … Loop Do … Loop While expr Do … Loop Until expr
VBA – The Basics • Loop statements • A common For loop For i=1 To 10 Debug. print i Next i For i=1 To 10 Step 2 Debug. print i Next i
VBA – The Basics • Loop statements • Another For loop For Each element In group … Next element • Commonly used to iterate through collections For Each ws In Worksheets Debug. Print ws. Name Next
VBA – The Basics • Procedures in VBA are either Macros or Functions • a macro does not return a value Sub Name() … End Sub • a function will return a value Function Name() As Double … End Sub • Property functions (Get and Let) are used in Class Modules to provide access to private properties
VBA – The Basics • Dealing with runtime errors • The On Error statement will trap errors … On Error Go. To label • The error name is a label in the code … On Error Go. To check … check: … • In the error code a Resume statement will cause the statement that caused the error to be executed again • In the error code a Resume Next statement will restart execution on the statement after the one that caused the error
VBA – Programming Environment • Excel (all Office components) have a VBA programming environment, VBIDE • It consists of • the Visual Basic Editor • an Object Browser • debugging support • These are accessed from the Excel menu • There is also a set of CHM files that document the components and object models of Office (for version 10) • the Excel file is named VBAXL 10. CHM • XLMAIN 10. CHM is the main help file for Excel and contains VBAXL 10. CHM within it • each version of Office has its own set of files • http: //www. ae. gatech. edu/classes/ae 6382/documents/MS_Scripting/Office 10/
VBA – Programming Environment • To start the Visual Basic Editor from Excel select • Tools | Macro | Visual Basic Editor
VBA – Programming Environment • Once in the VB Editor – to start the Object Browser select • View | Object Browser
VBA – Programming Environment • In the Object Browser to select only the Excel objects use the pull down menu at the upper left
VBA – Programming Environment • To view information on the Worksheet object and its Name property
VBA – Programming Environment • The usual way to create code is to double-click on one of the Worksheet or Workbook objects in the Project Explorer and enter the code.
VBA – Programming Environment • By using the menu item Insert it is possible to add other types of procedure containers • forms (GUI) • modules for library-like procedures • class modules for defining classes
Edit Window Options VB Edit Window Project Explorer Code Window Property Inspector
VBA – Excel Objects • The top level object is the Workbook. • A workbook corresponds to an open XLS file • Each Workbook object is kept in the Workbooks collection • To open a workbook file use • Dim wb = Workbooks. Open(“filename) • To close a workbook use the Close method on the workbook object or the Workbooks entry • Other methods for use with Workbooks are Save and Save. As
VBA – Excel Objects • The Worksheet corresponds to a Sheet • Each Worksheet object is kept in the Worksheets collection • To add a single new Worksheet • Dim ws = Worksheets. Add(count: =1) • ws. Visible = False will hide the sheet • ws. Delete will delete the sheet • Worksheets(“Sheet 1”). Delete will delete a sheet
VBA – Excel Objects • The Range object (collection) corresponds to a set of cells • To set a value into a cell • Worksheets(“Sheet 1”). Range(“A 5”). Value = 3. 14159
Data Exchange between Excel and Visual Basic
Outline • Data exchange between Excel and Visual Basic. • Programming VB in Excel
Exchange data with. csv file • . csv file is “comma separated value” file. • . csv file is plain text file. • . csv file can only save one worksheet. For multiple worksheets in a workbook, you need to use several. csv files. • You need do some editing to remove titles or labels before you use VB to read it.
Example
Code Private Sub Command 1_Click() Dim payment(1 To 50) As Single, commission(1 To 50) As Single Dim index As Integer Open App. Path & "commission. csv" For Input As #1 index = 1 Do While Not EOF(1) Input #1, payment(index), commission(index) index = index + 1 Loop Close #1 End Sub
Using VB in Excel • Excel uses a special version of Visual Basic, which is called Visual Basic for Application (VBA) • User can use VBA to create customized Excel function (VBA procedures), which can be much more powerful than the build-in Excel functions.
Security issues • VBA procedures in Excel is also called Macros. • Macros are two-edged swords. • Macros viruses are malicious VBA procedures.
Setting the security level
Security Level • High (default) • Medium. We should set the security to this level to run our VBA procedure. • Low (not recommended)
Medium level security • If you set the security level to medium, every time you open a Excel file containing macro, Excel will ask you if you want to enable the macro.
Example
VBA function code Function Comm(Sales_V As Single) as Single If Sales_V <500 Then Comm=Sales_V*0. 03 Elseif Sales_V>=500 and Sales_V<1000 Then Comm=Sales_V*0. 06 Elseif Sales_V>=1000 and Sales_V<2000 Then Comm=Sales_V*0. 09 Elseif Sales_V>=200 and Sales_V<5000 Then Comm=Sales_V*0. 12 Elseif Sales_V>=5000 Then Comm=Sales_V*0. 15 End If End Function
Edit VBA program in Excel
Insert modules • The VBA procedure should be defined in a module. • If you don‘t have an existing module, create one by using the insert>Module menu.
Write code in visual basic editor
Final result
Passing a range of cells to VBA procedure • The previous example shows how to pass one value to a VBA function. • However, in Excel it is possible to pass several cells to a build-in function. E. g. SUM(a 5: a 7) • We call a 5: a 7 is a range. • User defined VBA function can also get a range of cells as input.
Data type: range • VBA defines a specific data type: range • Range is just like a object: it has properties. • Important properties of range • Count : How many cells in the range • Value : Value of the cell (it works when range refers to only one cell)
Accessing individual cells • We use a special form of For…Next loop to access each cell in a range Function meanvalue(Input. Range As Range) As Single Dim cl As Range For Each cl In Input. Range ‘some code here to get the value ‘cl. value keep the value of the cell Next cl End Function Red word are key word in Visual Basic
Program task • Define a VBA function that calculate the average of a range of cells. • We define the function’s name as meanvalue.
code Function meanvalue(Input. Range As Range) As Single Dim cl As Range ‘cl is used to get individual cell Dim index As Integer, sum As Long index = 1 Re. Dim inputarray(1 To Input. Range. Count) As Single For Each cl In Input. Range inputarray(index) = cl. Value ‘save cell value into array index = index + 1 Next cl sum = 0 For i = 1 To Input. Range. Count sum = sum + inputarray(i) Next meanvalue = sum / Input. Range. Count End Function
Use your defined VBA function
- Slides: 81