Excel VBA Programming Basics PDF

Preview

Summary

Download Excel VBA Programming Basics PDF

Description

Excel 2007 VBA Programming

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Excel and VBA Macro recording ability exists since Excel 4, first with a specific kind of functions, written on a specific kind of sheets, then from Excel 5, with VBA language. Running and recording macros in Excel requires you have the right tools. All of them are stored in a single tab of the ribbon – the Developer tab – that may not be displayed in Excel if you never asked for it. To display this particular tab in Excel’s ribbon, go to Excel Options, then in the Popular part, check the option ‘Show Developer tab in the ribbon’ (that is the third checkbox).

Figure 1 - Excel's options

Figure 2 - Popular Excel options and Showing Developer tab in the Ribbon

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

3 4 5 1

2

6

7

Figure 3 - Ribbon’s Developer tab

1234-

Visual Basic Editor Execute a macro Record new macro Relative References

5- Macro Settings and security 6- Insert Controls 7- Design mode

In Excel 2007, you need to save your workbook as a ‘Macro-Enabled Workbook’ so that the macros you recorded and/or typed can be stored in it. To do you, choose this specific format for your file when saving it.

Figure 4 - Saving a file as an Excel Macro-Enabled Workbook

Making your workbook a .xlsm file will not automatically enable macro execution when you open the workbook. You have to manually enable macros each time you open a workbook that contains some VBA code. To do so, after you opened a file, click on the Options button in the Message bar (between the ribbon and the formula bar), and then choose to enable macros. The default security level can be modified by clicking on the Macro Security button of the ribbon’s Developer tab. Nevertheless, you should never change this.

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Figure 5 - Macro Settings

If you wanted to automatically enable macros for some workbooks, instead of shortening the macro security level, you could add security exceptions, such as Truted Locations. Trusted locations are folders on your hard-drive (or on your network), that you decide to trust. When opening a workbook stored in such a folder, the macros of this workbook will be automatically enabled, whatever the security level is in Macro Settings. Trusted locations can be defined from the ‘Trusted Locations’ part of Trust Center settings (displayed from Excel’s options, or from Macro Security button).

Figure 6 – Trusted Locations

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Recorded macros In order to record a repetitive sequence of operations, record a new macro. Use Record macro command of ribbon’s Developer tab.

Figure 7 – Recording a macro

The macro name must match the identifiers rules of Visual Basic (see in the Visual Basic concepts part of this document). Remember that a macro can be run as soon as the workbook that contains it is opened in Excel. The Personal Macros Workbook is automatically and systematically opened when Excel is running, and therefore, all macros that are stored in it can be executed at any time. While recording, the Stop recording toolbar the button you clicked on to record the macro turns into a Stop recording command. To stop recording the macro, use this button (

).

Every move and selection can be recorded in different ways by Excel’s macro recorder, depending on the state of Use Relative References button. When this button is disabled (displayed as a standard command), moves and selection are recorded in an absolute way: addresses of ranges of cells you select are stored in your macro. If the button is active, moves will be recorded as relative ones, meaning Excel’s macro recorder will make offsets of rows and columns from the active cell. Example: Record a new macro named “Borders_And_Background”, which will use a double solid border and a background colour to the selected range. This macro should be accessible from every workbook you will open next. -

Select a any range; In Ribbon’s Developer tab, click on Record macro button Type the macro name (Borders_And_Background), assign it a shortcut key (optional), and store the new macro in the Personal macro workbook; Click on OK button In the Home tab of the ribbon, open Borders list, and click on Others Borders command In the dialog box, choose the double solid border model, and then click the Outside button; In the Pattern tab, choose the background color ; Validate by OK button; Stop the macro recording.

All Excel useful shortcuts for selection and moves (Ct r l +* , Ct r l +Ar r ows , Ct r l +Ho me , …) are recorded by Excel for what they stand for in Excel’s everyday usage (respectively select an entire

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

table, move to the end of a row or a column, go back to the first cell of the active sheet); see the shortcuts summary at the end of this document, or Excel’s help for more shortcuts. To run a macro in Excel, use Macros button of ribbon’s Developer tab, or the shortcut Al t +F8.

Figure 8 – macros list

The normal execution is run with Run button, you can also run a macro step by step, by using the Step into button, and then by hitting F8 key each time you want to execute a statement, in order to see precisely the effect of each recorded operation. Edit button will send you to Visual Basic Editor and will let you change the content of your macro. Options will let you change both Shortcut key and description of any macro. To assign a macro to a drawn shape, right-click it, and choose Assign macro command; you can also draw command buttons and assign them a macro (use Insert button of Developer tab in the ribbon, and choose then the Button in the Form controls part of the menu).

Figure 9 – Drawing a button You can also customize Quick Access Toolbar and add buttons in it to run macros. To do so, in Excel options, choose Customize in the left side panel, and then choose “Macros” in the “Choose commands from” combo box. Slect your macro, and click on the Add button to add a button associated with the macro in the Quick Access Toolbar.

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Figure 10 –Quick Access Toolbar customizing You can even customize your button icon and text, thanks to the modify bottom just under the right side list.

Figure 11 – Modifying a Quick Access Toolbar button

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Visual Basic Editor This is in this software that you’ll write and modify your procedures. To display it, use Tools – Macro – Visual Basic command, or Al t +F1 1 , or the Visual Basic Editor button of the Visual Basic toolbar.

Project Explorer Code Window

Properties Window

Figure 12 - Visual Basic Editor Interface

Thanks to a right click in the project explorer, you can manage your projects (insert, delete, export or import items, and access a project’s properties). Double-click on an item to display its module in the code window. The properties window displays the properties of the selected object in the project explorer. You can change the value of each property. If you want to rename a module, you have to change the value of its (Name) property after having selected it. You’ll edit, modify and create new procedures in the code window, which offers you useful tools: - Syntax colorization: statements items are colorized according to what they are (keywords will appear in blue, identifiers and operators in black, syntax errors in red, comments in green…) - Auto-complete tool: when you call a recognized identifier, the auto-complete list helps you to complete your instruction, by displaying all the known members that belong to this identifier. You can also call this tool explicitly by using the Ctrl + Space shortcut, whenever you need to remember what starts by the characters you typed. - Express-information and tooltips: when calling a function, or method, you get information on their arguments list. The Visual Basic toolbar gives you many other useful commands.

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

1

2

3

4

5

6

7

8

9

10 11 12 13

14 15 16 17

18

Figure 13 – Visual Basic Editor toolbar

1- Display Excel 2- Insert 3- Save the project (or workbook) 4- Cut 5- Copy 6- Paste

7- Find 8- Cancel 9- Redo 10- Run a procedure 11- Break 12- Reset 13- Creation Mode

14- Project explorer 15- Properties window 16- Objects browser 17- Toolbox 18- Help

To configure Visual Basic Editor, go to the Tools – Options command of Visual Basic Editor.

Figure 14 - VBE options

All options should be checked, except Auto-syntax check if you’re used to write Visual Basic code.

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

VBA projects structure A project is an Excel file that contains some VBA code. It is constituted with different kind of objects that can contain code: Excel related objects (worksheets, workbooks and charts), in which should only be written event procedures; modules that contains general procedures and userforms that are custom dialog boxes. Modules let you organize your procedures as you want and create as many modules as you need in your project. Keep in mind that the better your code is organized, more efficient will you be when searching for a specific procedure. Try to store in the same module related procedures. For example, all printing oriented procedures could be stored in a module named “modPrinting”; all the calculation tools in a “modCalculation” module and so on… A procedure is a specialized block of statements that realizes a specific task. There are two types of procedures in Basic: subs (or macros) and functions. Subs have an effect, but no result; functions will normally return a calculated value. The procedures are the smallest executable piece of code in Basic, and every executable statement has to be written in a procedure. Statements compose the procedures. A statement (or instruction) is simply the technical term for a line of code. When you run a whole procedure, Visual Basic interpreter runs it statement after statement (step by step), of course in the order you wrote them in your procedure! In a statement, there can be up to four types of items: symbols (operators), identifiers (names of variables, constants, objects, procedures…), keywords (words of the Basic syntax) and constant values (digital values, text strings and dates directly and simply written in your code). A program is a set of procedures that call one another in order to perform advanced and complex operations, from high-level procedures (procedures run from Excel that realize complex operations) to low-level procedures (procedures that make really simple and repetitive operations and are usually called by higher-level procedures).

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

VBA syntax General concepts Code comments Comments are lines that are written in your modules, but are not executed. The objective of these statements is to make your code more readable by indicating to what aims the procedure or a specific block of code for example, or also to indicate what a variable stands for, or the reason of making such a condition. Comments have no impact on the execution time of a procedure (a procedure won’t be less efficient because there are comments in it). Comments always start by an apostrophe symbol, or the REM keyword. Examples: ’This is a comment and won’t be executed at run-time REM That is another comment statement I=0 ’This is an end-of-line comment

Continuation sequence When a statement is very long, you can write it on several consecutive lines, thanks to the continuation sequence, which has to be written at the end of each line that is not the last of your statement. In Basic, continuation sequence is “ _” (a space character followed by an underscore). Example: Dim i As _ Byte ’This instruction spreads now over two lines

Code indent In a procedure, some instructions will structure the code (conditional and repetitive statements, procedure declaration…). When this type of instruction is written, you should use a left-side indent for all the instructions that are contained by the structure. The first indent is set by using the Tab key; if you want to unset a previous indent level, you can use backspace key or Shift + Tab shortcut. Finally, to indent or outdent a block of statements, select it and use Tab or Shift + Tab shortcuts to change their indent level. Example: Sub Indent () If CurrentPrice > 0 Then MsgBox "Positive price !" End If End Sub

Identifiers naming rules You have to give a name to everything you own and use in your project (the project itself, its modules, classes, procedures, variables, constants…). The name of an item is known as an identifier for the item it represents. Every Basic syntax identifier has to respect these rules: - It must be 255 characters maximum; - These characters may be letters, figures or underscores; - The first character have to be either a letter or an underscore symbol; - Visual Basic keywords are forbidden (and, in a general way, don’t use names that already exist in your project or in Excel interface, such as SUM, AVG…);

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

-

The identifier has to be unique in its scope space.(two items that might interact one with the other can’t have the same name).

Visual Basic is case insensitive. Name and NAME, for example, will identify the same variable. You should also give names that make sense to your identifiers.

Using constant values directly in the code Numbers are written normally. The decimal separator of VBA is the dot character, whatever the Regional parameters of your computer. Examples: curActualPrice=85 decVATRate=0.196

’Stores 85 in curActualPrice variable ’Stores 0.196 in decVATRate variable

Text strings have to be written between quotation marks. Example: ’Stores "Clemence" text string in strUserName variable strUserName="Clemence"

Constant date values have to be written between sharps characters, and in an American format (mm/dd/yyyy).

Boolean values are both Basic keywords: True and False (which return respectively -1 and 0). Example:

Variable declaration: required or facultative? By using the Option Explicit in a module you’ll have to declare variables in every procedure of this module. This is something you should do, in order to write a better quality code. These words can be written automatically by Visual Basic Editor, thanks to the “Require variable declaration” option, that should be activated.

Using variables A variable is a place you create in memory (in the computer’s RAM) in order to store a value that you will have to use in your program, and that will change during its execution. The life of a variable is set by three steps. First, you must declare your variable (if you respected what is written in the previous paragraph!). You can do that either in a procedure, with the Dim keyword, or directly in a module with the Public keyword. The place you declare your variable will affect its scope and lifetime. After the declaration keyword, you’ll write the identifier of the variable you declare, followed by the As keyword, and finally by the data type you want to give to your variable, that can be one of the below mentioned types.

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Type Byte Integer Long Single (real) Double (real)

Currency String (text) Boolean (logical) Date Variant

Possible values 0 to 255, integer values only -32768 to 32767, integer values only -2 147 483 648 to 2 147 483 647, integer values only -3,402823E38 to -1,401298E-45 for negative values, 1,401298E-45 à 3,402823E38 for positive values -1,79769313486231E308 to -4,94065645841247E-324 for negative values 4,94065645841247E-324 to 1,79769313486231E308 for positive values -922 337 203 685 477,5808 to 922 337 203 685 477,5807 Up to 2 billion characters True (-1) or False ( 0)

Size (in bytes) 1 2 4 4 8

8 1/8

From 01/01/100 to 31/12/9999 Default data type (this is the type of every variable that is not explicitly typed)

8 -

Examples: ’Declares a global Variable Public strAppName as String Sub Variables_Declaration() Dim intUserAge As Byte Dim blnLoggedUser As Boolean ’Declares two string variables Dim strUserLastName As String, ’Beware: the second variable ’Variant ! Dim dtUserBirth, dtUserHire As End Sub

’Declares a Byte variable ’Declares a Boolean variable strUserFirstName As String is a Date, but the first

one

is

a

Date

A variable declared in a procedure will only be usable in this procedure (it’s a local variable); a variable declared in a module with the Public keyword is usable in every procedure of every module of the project (it’s a global variable). The places of the program in which you can use a variable define the scope of the variable. You can also create constants in your programs, thanks to the Const keyword instead of Dim in a procedure, or just after the Public keyword in a module. A constant is a place in memory that you have to give a value when it’s declared, and won’t change during the program execution. Examples: ’Declares a global constant Public VAT_RATE As Currency = 0.196 Sub Constants_Declaration() Const PRINTER_NAME AS String=”Ariane” … End Sub

’Declares a String constant

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

Operators Operators are symbols you use to realize several operations in your code. They are sorted in categories, according to their operation types.

Assignment operator The equal sign is VBA assigning operator. Thanks to it, you store its right-side value in its left-side item. Example: ’Stores "Clemence" in the strUserName variable strUserName = "Clemence"

Arithmetic operators Symbol +

Operation Addition

-

Subtraction

* / \

Multiplication Real Division Euclidien Division (integer division) Rest of the integer division Power

Mod ^

Examples curTotal=curFOT + curVAT intAge=intAge + 1 curVAT=curTotal – curFOT intAge=intAge – 1 intAgeInDays=intAge*365 dblRealResult= intPieces / intPersons intAge=(dtToday-dtBirth)\365 intRest=intPieces Mod intPersons intTwoCubed=2^3

Concatenation operator Concatenation operation consists in assemblying two text strings in a single one. You could represent it as the text addition. The ampersand symbol (&) is the natural concatenation operator in Basic. Example:

Beware: when used between two text strings, the + symbol is equivalent to the ampersand operator. That can be very ambiguous in specific contexts (for example “1”+”1”=”11”!) However, if you want to concatenate two strings, use the & operator.

Comparison operators These operators let you compare two values and give the logical (or Boolean) result of this comparison: either True, or False. Operator Meaning = Equals to < Less than

Greater than

Eric G uya d e r 2007-2012 – e ric g uya de r@ ya ho o .fr

>=

Greater than or equals to Different from

Text comparison Like operator let you compare two text strigs in a more flexible way then ...