VBA for Excel Lesson 1: The Visual Basic Editor in Excel (VBE)
Special note for users of Excel 2007: See how to install the Visual Basic Editor from your Office CD.
For users of Excel 1997 to 2003: The first thing that you need to do is to make sure that the security level of Excel is set at either "Low" or "Medium" so that you can use the macros (VBA procedures) that you develop. See how to do this in lessons 10 on Excel VBA Security.
The VBE is integrated into Excel and you can open it from the Excel menu bar "Tools/Macro/Visual Basic Editor" .
The Visual Basic Editor in Excel (VBE)
Open Excel and on your keyboard click on the "ALT" key (left of the space bar), hold and click on the "F11" key (the F11 key is at the top of your keyboard). Here is the Visual Basic Editor. Click again "ALT/F11" and you are back into Excel.
The Three Windows in the Visual Basic Editor
When you want somebody to do some work for you you open your Email program and you send him a message in a language that he understands (English, Spanish, French...). When you want Excel to do some work for you you open the Visual Basic Editor and you write the instructions in a language that Excel understands VBA ( Visual Basic for Application).
You will develop, test and modify VBA procedures (macros) in the Excel Visual Basic Editor (VBE). It is a very user friendly development environment. The VBA procedures developed in the Excel Visual Basic Editor become part of the workbook in which they are developed and when the workbook is saved the VBA components (macros, modules, userforms. etc.) are saved at the same time. When you send the workbook to the "Recycling bin" the VBA procedures are gone.
The Excel Visual Basic Editor
When you work with the VBE there always should be 3 windows showing. The Project window (1), the Code window (2) and the Properties window (3).
If you dot not see these 3 windows on your screen follow the instructions below. Once you have set the three windows properly they will remain in their position when you reopen Excel.
If there are any windows open under the tool bars close them. On the menu bar of the VBE choose "View" and select "Properties Window" . The window can appear minimized, it can be full width at the top of the screen or many other ways. You want it from top to bottom on the right of the screen. To do so click on its blue line at the top, hold and drag it to the bottom right corner. It is now minimized. Click again on its blue header, hold and drag it full left (it will even disappear) toward the vertical middle of the screen. It is now in the following position:
Then go back to the menu bar and select "Project Explorer" . From wherever it is click on its blue header, hold and drag it all the way down in the bottom right corner. It is now free.
Reduce the size of the "Project Explorer" to about the width of the "Properties Window" and one third the height by using the double arrows that appear when you move the mouse over the border of the "Project Explorer" . Click again on its blue header, hold and bring the top left corner of the "Project Explorer" over the top left corner of the "Properties Window" . Then let go.
You now have the "Project Explorer" over the "Properties Windows" in the left part of your screen. The "Code Window" appears when you double click on a sheet name in the "Project Explorer" . You will see later that the " Code Window" also appears when you double click on a userform name or a module name.
You can resize the 3 windows by placing the cursor over the borders (*) and dragging them right, left, up or down.
You can use "ALT/F11" to navigate from Excel to the VBE and back.
--------------------------------------------------------------------------------
Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros
--------------------------------------------------------------------------------
You will discover much more in the complete lesson 1 that you will find in the downloadable tutorial:
The Visual Basic Editor Menu Bar
There are some very useful menu items that you need to discover in the VBE.
The Visual Basic Editor Toolbars
There are also many icons that will help you do your work more rapidly and more easily.
The Visual Basic Editor Object Browser
You will read about all the VBA words in the Object Browser. You will refresh your memory on things that you do not use often, you will get inspired by bits and pieces of code or you will discover interesting things at random.
The VBA Toolbar (before Excel 2007)
The VBA tool bar in Excel is small but it gives you access to powerful programming tools very easily.
The "Developer" Ribbon (Excel 2007)
In the Developer ribbon in 2007 you will find the same functions as in the VBA toolbar of 2003. Like for all the other ribbons the presentation is different but there are no new functions.
--------------------------------------------------------------------------------
VBA for Excel Lesson 2: The Project Window in the Visual Basic Editor of Excel
The Project window shows you all the workbooks that are open. If you have not downloaded the tutorial just open a new workbook and you will see the following:
If you click on the minus (-) signs in the Project window you see the objects that are part of the different workbooks (projects). There are Sheets (usually three in a new workbook) and there is always the ThisWorKbook object in which you store the procedures that you want to start automatically when the workbook is opened.
If you go to Excel and add a sheet (right click on the tab and select Inserte) you will see when you come back to the Visual Basic Editor that there are now 4 sheets in the project window.
You will learn in VBA lesson 14 how to add sheets using VBA:
Sheets.Add
NOTE: If you have a workbook called "FUNCRES.XLA" at the top of the list it is because you have activated the "Analysis Toolpack " add-in to have more functions in Excel. If you double click on the name of the workbook you will be asked to supply a password (the password is not available except to Microsoft's people). Other people develop workbooks and they protect their macros. If you double click on a workbook name and you are required to submit a password it is because the developer has decided not to make his macros visible.
If you have downloaded the VBA tutorial and opened the Excel file "vba-tutorial-editor.xls" plus a new workbook you will see this:
If you click on the minus (-) signs in the Project window you see the objects that are part of the different workbooks (projects). There are Sheets in a workbook, there is always the ThisWorKbook object (in which you store the procedures that you want to start automatically when the workbook is opened) and, there can be Forms (VBA lessons 24 to 33) and Modules (files in which you write and regroup your VBA procedures (macros) one or many per module).
In the picture above you can see that the project "Book1.xls" comprises 3 sheets and ThisWorkbook. "excel-visual-basic-editor.xls" has 6 sheets, one userform, two modules plus the "ThisWorkbook" object. If in the project window you double click on a sheet name, on ThisWorkbook or on Module1 you will see two things happening. In the "Property window" you will see the properties of the selected objects and in the "Code window" you will see the VBA procedures (macros) stored in the object. In "Book1" you will notice that there are no macros present so the code window will be blank except for "Option Explicit".
If you double click on a userform name the form appears over the code window and when you double click on the form or one of its controls (command buttons, text boxes, combo boxes, list boxes and others) the code for the object appears in the code window.
--------------------------------------------------------------------------------
VBA for Excel Lesson 3: The Properties Window in the Visual Basic Editor of Excel
The Properties window shows you the properties of the object that is selected in the Project Window (single click). For example in a new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window.
As you can see a worksheet has 12 properties that you can set in this window and that you can modify programmatically using the VBA language. For example if you go to Excel and change the name on the tab of "Sheet1" (right click on the tab and select Rename) to "Introduction" you will see when you come back to the Visual Basic Editor that the property "Name" (the one without the parentheses) has changed to "Introduction
You can also change the name of a sheet that appears on its tab in Excel directly into the Properties Widow. For example select Sheet2 in the project window and change the property "Name" (the one without the parentheses) to "Switchboard" . GO to Excel and notice that you now have a sheet named "Switchboard" .
You will learn in VBA lesson 14 how to modify the name of a sheet using VBA:
Activesheet.Name = "Introduction"
If you click on "ThisWorkbook" in the Project Window you will see that this object has 30 properties. If you have downloaded the VBA tutorial and opened the Excel file "vba-tutorial-editor.xls" you will see that a module has only one property (its name), that a useform has 35 properties and a list box 37properties.
The "(Name)" property is present for every objects but a lot of properties are very specific to certain objects like the "RowSource" property of the list box or the combo box.
Setting and modifying properties of objects in the Property Windows is not something that you will have to do a lot of except for certain key properties of userforms and their controls (command buttons, text boxes, combo boxes, list boxes and others) . You will see that in section 3, VBA lesson 24 to 33.
--------------------------------------------------------------------------------
VBA for Excel Lesson 4: The Code Window in the Visual Basic Editor of Excel
90% of the VBA work is done in the Code Window writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.
Let's start by creating a small macro in an empty workbook.
- Open a new workbook.
- Go to the Visual Basic Editor,
- Double click on "Sheet1" in the Project Window,
- Place your cursor in the code window at the end of "Option Explicit" and click "Enter" twice to create two empty lines,
- In the code window copy/paste the following macro after the two empty lines
Note: If "Option Explicit" does not appear at the top of the Code Window just copy paste the macro on the first line.
Sub proFirst()
Range("A1" ).Value = 34
Range("A2" ).Value = 66
Range("A3" ).Formula = "=A1+A2"
Range("A1" ).Select
End Sub
Now go to Excel and on the menu bar go to "Tool/Macros" select "proFirst" and click on run.
In section 2 (VBA lessons 11 to 23) you will learn the VBA vocabulary to write macros.
For now take a better look at this very important Code Window.
NOTE: You cannot change the font or its color in the code window. You input appears in black, comments appear in green, reserved words in blue and when you make a mistake the color of the font turns to red.
NOTE: For a lot of users the wheel of the mouse does not work in the code window. To enable your mouse download and install the free fix offered as part of the downloadable tutorial.
The code widow shows the procedures that are part of the element of the project on which you have double clicked in the Project Window (module, sheet, userform, control or ThisWorkbook).
In your new workbook double click on "Sheet2" in the project window and this is what the Code Window looks like:
In the Code Window there is a main window in which appears the code and at the top there are two drop-down lists.
In the left DDList Select "Worksheet" . Immediately the first and final lines of a macro appear in the code window (Private Sub Worksheet_SelectionChange(ByVal Target As Range)). If you wanted to develop a macro that would run automatically when any value in any cell is changed you would write it between these two lines. Click on the small arrow of the DDLIst on the right and you will see this:
These are all the events that could automatically trigger a macro that you would write. A macro could start as the sheet is selected (Activate), as the sheet is calculated (Calculate), etc. You will learn more on events in VBA lesson 9
In the Project Window double click on "ThisWorkbook" .
In the left DDList Select "Workbook" . Immediately the first and final lines of a macro appear in the code window (Private Sub Workbook_Open()). If you wanted to develop a macro that would run automatically when workbook opens you would write it between these two lines. Click on the small arrow of the DDLIst on the right and you will see this:
These are 28 events that could automatically trigger a macro that you would write. A macro could start when the workbook is opened (Open), before the workbook is printed (BeforePrint), etc. You will learn more on events in VBA lesson 9
If you have downloaded the VBA tutorial and opened the Excel file "excel-visual-basic-editor.xls" double click on a module in the Project Window. Now the DDList on the right shows you all the macros that have been developed in the module. Select one and you are taken to the first line of the selected macro. To see the events related to a userform or any of the controls double click on its name in the Project Widow and then double click anywhere on the userform. In the right DDList you will see all the events.
If you right click in the left margin of the code window a shortcut menu is shown. See how to use these menu items in the downloadable tutorial and see everything that can happen in this left margin
--------------------------------------------------------------------------------
VBA for Excel Lesson 5: Adding new Macros in Excel
Now that you have discovered the Visual Basic Editor (VBE) and its 3 windows you can build your VBA procedures (macros).
Most macros are developed in the code window of modules. Below "Option Explicit" and at the top of the window you build a macro by writing:
sub proTest()
(do not use a capital "S" before " sub" then click on "Enter" and you get this:
Sub proTest()
End Sub
The VBE adds the line "End Sub" and capitalizes the "S" of "Sub" . The VBE capitalizes letters when the word is written correctly. This is one interesting feature that you should never forget about. Make it an habit to never use capital letters in the code and let the VBE tell you that there is something wrong by not capitalising letters.
Two exceptions to the manual capitalisation are when you declare variables or when you name macros as above. You will see why in later lessons.
You may now write a procedure within the two lines of code above. For example your VBA procedure could look like this:
Sub proTest()
Sheets("Sheet1" ).Select
Range("C1" ).Select
Do Until Selection.Offset(0, -2).Value = ""
Selection.Value = Selection.Offset(0, -2).Value & " " & Selection.Offset(0, -1)
Selection.Offset(1, 0).Select
Loop
Range("A1" ).Select
End Sub
The procedure above will go down column "C" and assemble the first names of column "A" and the last names of column "B" with a space in between. It will perform this task all the way down until there are no more first names in column "A" .
--------------------------------------------------------------------------------
VBA for Excel Lesson 6: Testing Macros in the Visual Basic Editor for Excel
Testing is the most time consuming part of any VBA project. During the development of a project you will use 20% of your time analysing and designing, 15% programming and 65% testing.
During the testing phase you will correct the bugs, the typos and the logical errors. More importantly you will improve the original project, fine tune it, discover better ways to do things and add code while testing.
Testing a VBA procedure (Macro) from the Visual Basic Editor
If you have not downloaded the Tutorial on VBA open any workbook with a VBA procedure in it, click anywhere within the procedure in the code window and click on the "Run" icon from the toolbar:
In the workbook "vba-tutorial-editor.xls" of the Tutorial on VBA click anywhere within the procedure "proTest" in the code window of the VBE and click on the "Run" icon:
Testing the VBA procedure step by step
Click anywhere within the macro and press the F8 key at the top of your keyboard. The fist line of code turns to yellow and a small arrow appears in the margin. You are in the execution process.
NOTE: While you are running the macro step by step you can stop the execution at anytime by clicking on the stop button in the toolbar.
Each time you click on the F8 key a line of code is executed.
You can click on the small yellow arrow and drag it up and down to retest a line of code.
The line that is highlighted in yellow is the line that will be executed next time you click on F8.
--------------------------------------------------------------------------------
Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros
--------------------------------------------------------------------------------
In no other programming environment can you test a procedure step by step while seeing it at work in the workbook on a single screen.
In the picture below you will see the Visual Basic Editor on the left side of a screen and Excel on the roght side. While you click on the F8 key to execute the macro step by step you can see what is happening in Excel. There is no need to have two screens you just use the friendly functionality of Office that you will discover in lesson 6 of the downloadable tutorial on VBA for Excel (Macros).
--------------------------------------------------------------------------------
VBA for Excel Lesson 7: The Excel Macro Recorder
One of the tools that makes the programming environment in Excel unique is the Excel Macro Recorder.
In this section you will work with the Macro Recorder and you will run the macro that you have recorded.
With the Excel macro recorder you can not develop a macro that will damage Excel or your computer.
Even after more than 15 years of programming we still use the macro recorder daily. Not to learn anymore but to write code (VBA words and sentences) for me. For example why would I write the following sentence when the Macro Recorder will do it for me without any typos:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
To reproduce the sentence above in your own workbook open a new workbook and go to "Tools/Macros/Record New Macros..." . In the window that appears click on "OK" . Select a cell , click on "Edit/Copy" and then on " Edit/Paste Special/Values" , click on "OK" and finally click on the "Esc" key.
You will often use the few lines of code above when you want to copy/paste cells without carrying the format or the formulas, when you want to add while pasting or when you want to paste in columns what you have copied in rows.....
You record a single version of this piece of code and you change manually the components like:
xlPasteFormulas instead of xlPasteValues
xlPasteFormats instead of xlPasteValues
xlAdd instead of xlNone
xlMultiply instead of xlNone
Transpose:=True instead of Transpose:=False
Never forget that the Excel macro recorder is a teacher and will remain a great assistant for the rest of your VBA developer's life.
Sometimes what the macro recorder writes can be significantly simplyfied. You will see how to modify a recorded macro in the next lesson.
--------------------------------------------------------------------------------
VBA for Excel Lesson 8: Modifying a Macro in Excel
You have started using the Macro Recorder to write code for you and noticed that sometimes the MR overdoes it. For example when we develop tables and reports we will often select a set of cells and use the icon to add small borders around all cells:
Range("D4:F10" ).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
You will notice that this is a series of "With/End With" statements (4 for the edges and 2 for the insides. You can copy this code anytime you need it but make sure that you are not generating an error. For example if you use this piece of code on a single row of cells make sure that you remove the section about "xlInsideHorizontal" and if you use this code on a single column of cells make sure that you remove the section about "xlInsideVertical" .
You can remove the lines of code about the "xlDiagonal..." and any of the statements that you do not need. Formatting cells through VBA is time consuming so slim down your code.
--------------------------------------------------------------------------------
VBA for Excel Lesson 9: Starting, Triggering a Macro in Excel (events)
When does the VBA procedure (macro) start? When an EVENT happens. The event is what triggers the VBA Excel procedure.
Clicking on a text box on the worksheet
95% of the VBA procedures that you develop are triggered by a click on a button located on a worksheet.
We prefer using text boxes rather than VBA command buttons because they are much easier to maintain and allow much more creativity in your design. You can use the font that you like and the background color that fits your needs. If you are a little creative you can add 3D effects, special borders and others.
A few note on Excel text boxes:
Always keep the Excel drawing toolbar visible at the bottom of your screen
You create text boxes by a left click on the icon , let the button go, then go to the worksheet left click, hold and stretch the text box. When the border of the active text box is made of diagonal lines you can work the text inside the text box. If you click again on the border it becomes a set of dots and you then can work the text box itself. Right click on the border in any of the two states and you will see that the menus are different.
First you develop a macro in a module in the VBE. Then you click on the text box and when the border becomes a set of dots right click on it and select "Assign a macro" . Select a macro from the list that is offered to you. A simple macro to call a userform would look like this:
Sub proUserFormWeighing()
frmWeighing.Show
End Sub
You can assign a VBA macro to a text box and also to a WordArt, a picture or any other shape from the "Drawing" toolbar.
Once a button (image, word art or text box) has been assigned a macro or an hyperlink you need to select it with a right click to modify it.
Download one of these buttons (right click on it in your browser and choose "Save image as" ). Save it on your desktop:
Insert the image that you have imported on the first sheet "Insert/Picture/From File/Desktop/..........gif" . Once the image has been added to the sheet, right click on the image, select "Assign Macro" and select a macro from the list. Click "OK" .
Now click on the image.
You can "borrow" all kinds of buttons from the Internet or create your own from the "Design" toolbar and use them as triggers for your VBA procedures.
From the Excel Menu
In Excel you can run an Excel VBA macro by going to the menu "Tool/Macro/Macros.." then select the macro from the list and click "Run" .
You can open many workbooks with macros in them. When you go to the menu "Tool/Macro/Macros.." you will notice that you have access to all the macros from all the open workbooks. This means that you can store ALL your useful Excel macros in a single workbook (call it myMacros.xls) and have access to them while the workbook is opened. You can call any Excel macro from any cell in any other workbook that is open. No need to copy your essential macros in all your workbooks just open myMacros.xls and put them to work.
Clicking on a Key of your Keyboard
First you need to program a key. To do so go to "Tool/Macro/Macros.." then select a macro from the list. Click on "Options" and follow the instructions. Assign your macros to upper case keys ("Control/A" instead of "COntrol/a" for example) to make sure that you do not use one of the many lower case keys that are already used by Excel. You can also assign a macro to a key when you use the Excel Macro recorder.
--------------------------------------------------------------------------------
VBA for Excel Lesson 10: VBA Macros Security and Protection in Excel
Special note for users of Excel 2007: See how to install the Visual Basic Editor from your Office CD and set the security level of your Excel.
Sometimes you send a workbook with macros to a colleague. If he can not get them to work it is probably because his security setting is at "High" . Tell him how to change his level by going to the menu bar "Tools/Macros/Security" and follow the instruction.
Each time you open a workbook that contains macros the following dialog window will appear.
Adopt the same attitude as you have with documents attached to Emails. If you know the origin of the file you may enable the macros if not click on "Disable Macros" and you are fully protected. You can look at the workbook but the VBA procedures (macros) are not operational. You can go to the Visual Basic Editor to take a look at the macros. If nothing looks suspicious close the workbook and re-open it enabling the macros.
Password Protecting the code
As an Excel-VBA Developer you might want to protect your code so that nobody else may modify it. In the VBE editor go to "Tools/VBAProject Properties/Protection" . Check the box and submit a password. Make sure that you save the password somewhere that you will remember because cracking Excel VBA passwords is expensive.
--------------------------------------------------------------------------------
Discover More Tips, Ideas and Examples on this Topic
In the complete 33 lessons of the
Downloadable Tutorial on Excel Macros
--------------------------------------------------------------------------------
There are three sections in this website and in the downloadable tutorial on VBA for Excel.
Section 1: Excel Macros Programming: 10 lessons
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will learn about security and discover "events" (an event is what starts the macro).
Section 2: Excel VBA Vocabulary: 13 lessons
Developing a macro is like talking with Excel and to do so you need to use a language called VBA. There are over a thousand VBA words but you will really need only 100 to talk with Excel about workbooks, worksheets, cells, databases, loops and to develop simple and powerful macros.
Section 3: Forms and Controls in VBA for Excel: 10 lessons
The form is a small window that allows the user to submit values that will be used by the macro. To these forms you add controls (command buttons, text boxes, list boxes and others).
--------------------------------------------------------------------------------
No comments:
Post a Comment