Page 1 of 38
VBA for Excel Lesson 1: The Visual Basic Editor in Excel (VBE)
The first thing that you need to do is to make sure that t he 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". 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 and you are back to Excel. Nothing to install it has been there all the time nobody has cared to tell you so. The Visual Basic Editor (VBE from now on) is the friendly programming environment environment within 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 f riendly development development environment. The VBA procedures developed 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 componen components ts (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 that are showing. The Project window (1), the Code window (2) and the Properties window (3) like below.
Page 2 of 38
You might not see the 3 windows on your screen. We will fix that right now and it can be a little tricky. But once you have set them they will stay in their position always. So the following exercise is a ONE TIME only exercise bear with me. If there are any widow 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: 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.
Page 3 of 38
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. The Visual Basic Editor Menu Editor Menu Bar
The Toolbars
Here are the two toolbars that should always be visible at the top of the VBE. The "Standard" toolbar (top) and the "Edit" toolbar (bottom).
Page 4 of 38
The Object Browser
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:
Page 5 of 38
If you click on the minus (-) signs in the Project window window you see the objects that 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 earn 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.
Page 6 of 38
If you click on the minus (-) signs in the Project window window you see the objects that 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 automaticall automatically y when the workbook is opened) and, there can be Forms ( VBA lessons lesso ns 24 to 33) and Modules (files in which you write and regroup your your VBA procedures (macros) one or many per module). module). In the picture above you can see that the project "Book1.xls" comprises 3 sheets and ThisWorkbook. "excel-visual-basic-editor.xls "excel-visual-basic-editor.xls"" has 6 sheets, one userform, two modules modules plus the "ThisWorkbook"" object. If in the project window you double click on a sheet name, on "ThisWorkbook ThisWorkbook or on Module1 you will see two things happening. In the "Property window" you see the properties of the selected objects and in the "Code window" window" you see the VBA procedures (macros) stored in the object. In "Book1" you will notice that there are no macros anywhere so the code window will be blank except for "Option Explicit" that you have discovered in lesson 1. 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 workbook if you single click on "Sheet1" "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window.
Page 7 of 38
As you can see a worksheet has 12 properties that you can set in this window but that you can also modify programmatically. 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 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 earn in VBA lesson 14 how to modify the name of a sheet using VBA: Activesheet.Name Activesheet.N ame = "Introduction"
If you click on "ThisWorkbook" in the Project Window or you will see that this object has 30 properties. If you have download downloaded ed the VBA tutorial and opened the Excel file "excel-visualbasic-editor.xls" you will see that a modue has only one property (and a very important one, that a useform has 35 properties and a list box 37. The "(Name)" property is present for every objects but a lot of properties are very specific to other objects like the "RowSource" property of the list box. Setting and modifying properties of objects in the Property Windows is not something that you will have to do a lot except for certain key properties of userforms and their controls (command
Page 8 of 38
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" doesn't appear at the top of the Code Window just copy paste the macro on the first line. Sub proFirst() Range("A1").Value = 34 Range("A1").Value Range("A2").Value Range("A2").Valu e = 66 Range("A3").Formula Range("A3").Fo rmula = "=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 everything there is to know about writing macros (coding). Let's take a better look at this very important 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 doesn't work in the code window. To correct this frustrating situation download and install the free fixer offered as part of the downloadable tutorial. 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:
Page 9 of 38
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". "Worksheet". Immediately the first and final lines of a macro appear appear in the code window (Private Sub Worksheet_SelectionChan Worksheet_SelectionChange(ByVal ge(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". "Workbook". Immediately the first and final lines of a macro appear appear in the code window (Private Sub Workbook_Open()). If you wanted to develop a macro that would run automatically 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:
Page 10 of 38
These are 28 events that could automatically trigger a macro that you would write. A macro could start when the workbook is opened (Open), (Open), before the workbook is printed (BeforePrint), etc. You will learn more on events in VBA lesson 9 If you have download downloaded ed 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: Writing 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" at the top of the window you build a macro by writing: sub proTest() (don't use a capital "S" before "sub" than click on "Enter" and you get this: Sub proTest() End Sub
The VBE adds the line "End Sub" and capitalises the "S" of "Sub". The VBE capitalises 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 not capitalising letters. Two exceptions to the manual capitalisation are when you declare variables or when you name macros like above. You will see why in later lessons. Now what is left is to 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.Va lue = Selection.Offset(0, -2).Value & " " & Selection.Offs Selection.Offset(0, et(0, -1)
Page 11 of 38
Selection.Offset(1, Selection.Offs et(1, 0).Select Loop Range("A1").Select End Sub
The procedure above will go down column "C" and assemble first names of column "A" and 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 Testing Macros in the Visual Visual Basic Editor for Excel Testing the entire procedure
Click anywhere within a VBA procedure (macro) in the code window and click on the "Run" icon of the toolbar:
and the VBA procedure is executed. You can stop the execution by clicking on the "Esc" " Esc" key at the top of your keyboard. Testing a userform
Bring the userform that you want to test in the code window (double click on its name in the project window). Click on the "Run" icon of the toolbar:
and the userform starts running. You can stop the execution by clicking on the X (top/right corner of the userform) unless you have deactivated this X (see how in " vba-form-spin-button.xls" one of the 25 downloadable Excel spreadsheets ). Testing the entire 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. We are in the execution process.
The line that is highlighted in yellow is the line that will be executed next time you click on F8. While you are running the macro step by step you can at any time click on the "Run" icon of the toolbar to run the rest of the macro or stop the execution at anytime by clicking on the stop button of the toolbar.
Testing parts of VBA procedures
You can add breakpoints to macros by selecting a line and clicking on the F9 key of your keyboard. The background background of the line where a breakpoint is installed turns to brown.
To remove the breakpoint select the line and click on the F9 key again.
Page 12 of 38
When you click on the "Run" icon of the toolbar the execution will stop at the breakpoint where you can choose to run the rest of the procedure step by step or click again on the "Run" icon to complete the execution (after verifying the partial result). Testing a macro with the Excel split screen function
In no other programming environment can you test a procedure step by step while seeing it at work on one single screen. Like in the picture below you can see the Visual Basic Editor in half of the screen and Excel in the other half. You can test your macro using the tips above and see them at work in Excel. The image below represents the VBA procedure (macro) "proTest" of the workbook "vba-tutorialeditor.xls" being tested with the split screen function "On". Like most people you have only one screen in front of you. There nothing to install, no programming programming is needed the Split Screen function is there within Excel and you just need to learn what button to click on to activate it. In the image below note that in the Excel window the first name and the last name in cells A11 and B11 have been assembled in cell C11 and in the VBA window note that the macro is about to select one cell down from C11 to run the loop another time You could even step back one line in the macro, modify the names on line 6 and resume testing the macro step by step. You can change values in Excel and even lines of VBA in the macro while testing. Discover the very easy way to do this in the complete version of lesson 6 in the downloadable tutorial on macros for Excel
Page 13 of 38
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 cannot develop a macro that will damage Excel or your computer. The bolder you are in your trials the more you will learn.
Even after more than 15 years of programming I 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, Selection.PasteSpecial Paste:=xlPasteValues, Operation:= Operation:=xlNone, xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode Application.CutC opyMode = False
To reproduce the sentence above in your own workbook open a new workbook go to "Tools/Macros/Record "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", Special/Values", click on "OK" and finally click on the "Esc" key. You will use often 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 The Excel macro recorder is the best teacher that you can have and will remain the best assistant for the rest of your VBA developer' developer's s life. The Excel Macro Recorder has tendencies to overdo it sometimes. We 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 I develop tables and reports I will often select a set of cells and use the icon
to add small borders around all cells:
Range("D4:F10").Select Selection.Borders(xlDiagon Selection.Bor ders(xlDiagonalDown).Line alDown).LineStyle Style = xlNone Selection.Borders(xlDiagon Selection.Bor ders(xlDiagonalUp).LineStyle alUp).LineStyle = xlNone With Selection.Bo Selection.Borders(xlEdg rders(xlEdgeLeft) eLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Bo Selection.Borders(xlEdg rders(xlEdgeTop) eTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Bo Selection.Borders(xlEdg rders(xlEdgeBottom) eBottom)
Page 14 of 38
.LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Bo Selection.Borders(xlEdg rders(xlEdgeRight) eRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Bo Selection.Borders(xlInsid rders(xlInsideVertical) eVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Bo Selection.Borders(xlInsid rders(xlInsideHorizontal) eHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With
You notice that this is a series of "With/End " With/End With" statements 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" "xlInsideHorizontal" and if you use this code on a single column of cells make sure that you remove the section about "xlInsideVertical". "xlInsideVertical". You can remove the lines of code about the "xlDiagonal "xlDiagonal..." ..." and any of the statement that you don't really need. Formatting is time consuming so slim down your code. VBA for Excel Lesson 9: Starting 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. I prefer using text boxes rather than VBA command buttons because they are much easier to maintain and allow much more creativity in the 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 the likes. A few note on Excel text boxes: I always keep the Excel drawing toolbar visible at the bottom of my screen
You create text boxes by a left click click on on the icon , let 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 menus are different.
Page 15 of 38
First you develop a macro in a module in the VBE. Then you click ob 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 have imported on the first sheet 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 macro by going to the menu "Tool/Macro/Ma "Tool/Macro/Macros.." cros.." then select the macro from the list and click "Run" .
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. Let's say for example that you have designed
Page 16 of 38
a macro that multiplies the content of a cell by 2. If "myMacros.xls" is open you can call this Excel macro from any cell in any other workbook workbook that is open. No need to copy 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. key. To do so go to "Tool/Macro/Macros.." "Tool/Macro/Macros.." then select a macro from the list. Click on "Options" and follow follow the instructions. A suggestion, suggestion, assign your macros macros to upper case keys ("Shift/A" instead of "Shift/a for example) to make sure that you don't 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 Security and Protection in Excel
Sometimes you send a workbook with macros to a colleague. colleague. If he can't get them to work it is probably because because his security setting is at "High" . Just tell him how to change his level by going to the menu bar "Tools/Macros/S "Tools/Macros/Security" ecurity" 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/VBAPro "Tools/VBAProject ject 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. VBA Lesson 11: VBA Code General Tips
-Always write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't capitalize any letters.... check your spelling. - If you want to enter the name Peter in a cell you will write: Range("A1").Value= Range("A1").Valu e= "Peter"
But if you want the name "Peter" (between quotes) you need to double the quotes write: Range("A1").Value= Range("A1").Valu e= ""Peter""
This tip applies to message boxes and userforms. Add comment to your VBA procedures to make them easier to understand. Any line of code that starts with an apostrophe is considered a comment and the font will be green in the code window like in:
Page 17 of 38
' In this section we do this and this You can also add a comment at the end of any line of code by adding a space at the end of the line then a comment preceded by an apostrophe like in: Range("A1").Value= Range("A1").Value= ""Peter"" ' Entering a first name between quotes - All VBA procedures (macros) start with Sub with a set of parentheses at the end Sub proWhateverName()
I always use the prefix "pro" at the beginning of a procedure and I use upper case letters at the beginning of a new word in the name of the procedure like in proWhateverName above or like in proAddData
- A VBA procedure always end with End Sub but you can always exit a procedure at any point with: Exit Sub
- All VBA sentences must be on a single line. When you need to write long sentences of code and you want to force a line break to make it easier to read you must add a space and an underscore at the end of each line. Here is an example of a single sentence broken into 3 lines: Range("A1:E9").Sort Key1:=Ran Range("A1:E9").Sort Key1:=Range("C2"), ge("C2"), Order1:=x Order1:=xlAscending, lAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers VBA Lesson 12: VBA for Excel for the Application
Application is a VBA object, IT IS EXCEL. For example: Application.Quit
will close Excel all together. ScreenUpdating
When you don't want to see your screen follow the actions of your VBA procedure (macro), you start and end your code with the following sentences: Application.ScreenUpdating = False
Then at the end: Application.ScreenUpdating Application.Scr eenUpdating = True DisplayAlerts
When you don't want Excel to ask you things like "A file already exists....." or "Do you wan to save this file..." you will use the following line of code at the beginning of your VBA procedure. Application.DisplayAlerts Application.Disp layAlerts = False
Then at the end: Application.DisplayAlerts Application.Disp layAlerts = True
You can also use the DisplayAlerts method anywhere within your macro. If for example you don't want to see a specific alert but you want to see the other ones use the False just before one line and the True just after. NOTE: If you use the False argument Excel will never save any workbook that you close. So if you want to save a workbook before closing it you will need: Activeworkbook.Save CutCopyMode
After each Copy/Paste or Copy/PasteSp Copy/PasteSpecial ecial operation, you should empty the clipboard with the following line of code to make sure that the computer memory doesn't overload. ActiveSheet.Paste Application.CutCopyMode=False
Page 18 of 38
VBA Lesson 13: VBA for Excel for the Workbooks
To develop a VBA procedure that is triggered by an event relating to the workbook (when you open it, when you save it...) see the VBA lesson on events. ThisWorkbook ThisWorkbook is the workbook within which your VBA procedure runs. So if you write: ThisWorkbook.Save
The workbook within which your VBA procedure ( macro) runs will be saved. If you want to close the workbook within which your VBA procedure ( macro) runs without saving it you will write these two lines of code: ThisWorkbook.Saved=True ThisWorkbook.Close Workbooks and Windows
When you work with two workbooks you will move from on to the other with: ThisWorkbook.Activate Windows("theOtherWorkbo Windows("the OtherWorkbookName.xls okName.xls").Activate ").Activate Killing a Workbook
When you have saved a workbook on your disk and you want to remove it you can use the KILL statement: Kill " C:\myFile.xls"
Note: You cannot kill any workbook that is opened including ThisWorkbook VBA Lesson 14: VBA for Excel for the Worksheets
To develop a VBA procedure that is triggered by an event relating to the worksheet (when you select it, when you leave it...) see the VBA lesson on events . Sheets
You access a worksheet named "Balance" with: Sheets("Balance").Select
Note that the word "Sheets" is plural and never forget the quotes within the parenthesis You cannot select a sheet that is hidden so you will need to write: Sheets("Balance").Visible= True Sheets("Balance").Visible= Sheets("Balance").Select
and then if you want to hide the sheet again: Sheets("Balance").Visible= Sheets("Balan ce").Visible= False
The name of a sheet must not have more than 31 characters and cannot include certain special characters like ? : \ / [ ] . If you don't don't respect these rules rules your procedure will crash. The following following lines of code will generate an error message: Sheets("Sheet1").Name= Sheets("Shee t1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces Sheets("Sheet1").Name= Sheets("Shee t1").Name= "Balance: Introduction Introduction" " because of the special character : Sheets("Sheet1").Name= Sheets("Shee t1").Name= "" because the name cannot be blank You cannot go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named "Results" you cannot write: Sheets("Results").Range("A1").Select
You must take two steps: Sheets("Results").Select Range("A1").Select
Page 19 of 38
Dynamic Charting with VBA for Excel
These images present a dynamic chart from the workbook "vba-example-dynamic-chart.xls". "vba-example-dynamic-chart.xls". The first chart shows the sales for all the t he products and all the cities and the second chart shows sales of selected products in selected cities with a few clicks on the right buttons. Discover the very easy way to do this in the workbook "vba-example-dynamic-chart.xls" "vba-example-dynamic-chart.xls" one of the 25 spreadsheets in the downloadable tutorial on macros for Excel
The same chart presenting sales of selected products and selected cities (Note that the text on the active button is blue and it is black for the others)
Page 20 of 38
VBA Lesson 15: Moving around the Worksheet in VBA for Excel
A lot of VBA beginners start their career using Cells. For example: Cells(1,1).Select is the same thing as Range("A1").Select and Cells(11,31).Select is the same as Range("AE11").Select. I strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AE is column 31. The only time that you will use Cells is when you want to select all the cells of a worksheet like in: Cells.Select
To select all cells and then to empty all cells of values or formulas you will use: Cells.ClearContents
To select a single cell you will write: Range("A1").Select
To select a set of contiguous cells you will use the colon and write: Range("A1:G5").Select
To select a set of non contiguous cells you will use the comma and write: Range("A1,A5,B4").Select
To select a set of non contiguous cells and a range you will use both the colon and the comma: Range("A1,A5,B4:B8").Select Offset
The Offset property is the one that you will use the most with Range to move around the sheet. It is the very important property that allows you to move right, left, up and down. To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select To move one cell down from the selected cell: ActiveCell.Offset(1,0).Select
As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need: Range("A1").Offset(5,6).Select
Here is a piece of code that you will use very often. It selects a cell and 4 more to the right to be copied/pasted copied/pasted somewhere else: Range(ActiveCell,ActiveCell.Offse Range(ActiveC ell,ActiveCell.Offset(0,4)).Select t(0,4)).Select Notice the comma after the first ActiveCell and the double closing parentheses before the Select . VBA Lesson 16: Message Boxes (MsgBox) in VBA for Excel
In VBA for Excel the message box (MsgBox) is the primary tool to interact with the user. You can use it to inform, alert or ask the user (with a Yes/No message box) if he is sure that he wants a certain critical procedure to run (deleting things). The code in VBA for Excel to generate the following basic message box is: MsgBox "Thank you"
Page 21 of 38
If you want you want part of your message to be between quotes you need to double the quotes as in:
The line of code would be: MsgBox "The result is in cell ""A1"""
If you don't want to show the cell's address but its value as in:
The line of code would be: MsgBox "The result is " & Range("A1").Value
Don't forget the space after "is". If you have greater (25,897) number and you want to show the comma you will need to use the function "Format". See the Downloadable Tutorial. Input Boxes
You need to declare (create) a variable to receive the answer to a VbYesNo messsage box or an input box. For the input box you will write: varAnswer = InputBox("For how many years?", "Duration")
and for the message box you will write varAnswer = MsgBox("Do you want to continue?", vbYesNo, vbYesNo, "Alert")
See more in the tutorial.
Learn more on message and input boxes: - If you have numbers greater than 1,000 (25,897) and you want to show the comma you will need
to use the function "Format". - Learn about VBYesNo, VbYesNoCancel, VbAlerts,VbExclamation VbAlerts,VbExclamation and other message boxes
Page 22 of 38
VBA Lesson 17: VBA Excel to work with Databases
To really get the most out of VBA working with databases you must master these functionalities in Excel. Visit the website on Excel and study the sections on databases and database functionalities. When you work in an Excel database you must first make sure that all filters are off. To this end you will start your procedure with these two "If" statements. First select any cell within the database. Range("A3").Select If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter End If If ActiveSheet.FilterMode ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData End If
Knowing that a database in a set of connected rows and columns you can select it all with: Range("A3").Select Selection.CurrentRegion.Select
Once this is done, you can count the number of rows (records) and the number of columns (fields) and store these values in variables or in other cells for future reference with the following code: varNbRows=Selection.Rows.Count varNbColumns=Selection varNbColumn s=Selection.Columns.Count .Columns.Count Range("A1").Value=Selection.Rows.Count Range("A1").Value=Selection .Rows.Count Range("B1").Value=Selection Range("B1").Valu e=Selection.Columns.Count .Columns.Count
In fact the number of records is the number of rows minus one (the title row) and here is the code: varNbRecords=Selectio varNbRecor ds=Selection.Rows.Coun n.Rows.Countt - 1
I never write the code for filtering a set of data I use the macro recorder and then modify the code.
Page 23 of 38
VBA Lesson 18: VBA for Excel to Manage Errors
After you have written a macro and VBE has helped you correct most the errors...after you have tested you macro there might still be error messages generated and you or the user will end up seeing this window:
This window is no problem if you or close colleagues are the only users of the workbook. But in other rare cases where you distribute your workbooks to many users you will probably prefer to have a more useful error message. Here is the basic way to handle errors. A VBA procedure usually runs from Sub to End Sub. In a VBA procedure that includes an error handler it will run from Sub to Exit Sub but if there is an error happening during the execution it will jump to a special address where the error handler resides . So to add error handling capabilities capabilities to any of your VBA procedures you follow these steps: 1- Add an Exit Sub right before End Sub 2- Between these two lines add an address ending with a colon ( addJump: for example) 3- Make room between the address line and End Sub to enter your code handling the error. 4- At the beginning of your code enter On Error GoTo addJump 5- As in the example below the error handler can be a message box giving your coordinates to the user: MsgBox "An error has occurred, call Peter at 1 613-749-4695 " Example 1: Sub proTestErrorHandler() On Error GoTo addJump Workbooks.Open "xxxxxx" Exit Sub addJump: MsgBox "An error has occurred, call Peter at 1 613-749-4695 " End Sub
Copy/Paste the examples in a module of your own and run them. As the workbook xxxxxx can't be found you will see a message box saying An error has occurred, call Peter at 1 613-749-4695 . VBA Lesson 19: VBA for Excel Variables
A variable is an object that you create create and in which you can store text, text, dates, numbers or almost anything else. Why should you use variable? The first good reason is to make your code dynamic, to avoid hard coding some values.
Page 24 of 38
Hard Coding vs Dynamic Coding
You are hard coding when you write: Workbooks.Open "MyFile.xls"
You are dynamically coding when when you enter the name of the file in a cell (A1) of your excel sheet and you write. varWorkbook=Range("A1").Value Workbooks.Open varWorkbook
At this point you or the user can change the value in cell A1 and open any workbook. You will create variables to count the number of rows, store the result in a variable and then do something as many time as there are rows. In a new workbook enter values in cells A1 to A10 then Copy/Paste the following following procedure in a module and try it (Go to "Tools/Macro/Macros" select "proTest" and click on "Run"): Sub proTest() Dim varCounte varCounter r Dim varNbRows Range("A1").select varNbRows=Selection.CurrentRegion.Rows.Count For varCounter = 1 to varNbRows Selection.Value=Selection.Value*2 Selection.Offset(1,0).select Next End Sub
Enter numbers in cells A1 to A:1000 or A1 to whatever row and try it again I use variables in 99% of my procedures and you will do the same to reduce the number of hard coded values. Maintenance becomes so much simpler. The Variable of the type VARIANT
As I was writing above you can store almost anything in a variable. You can even store an entire sheet with all its data. And why would you do that? When you have very large sets of data using formulas can become a problem because the calculation time becomes unbearable. The solution is to send the sheet into a variable execute the calculations within the variable and bring back the results. One of the Resource Workbooks in the Downloadable Tutorial shows you an example where 1,000,000 cells are calculated in less than 2 seconds. VBA Lesson 20: VBA for Excel Statements
A lot of visitors ask me how they can delete the entire lines when a certain cell is empty. First enter xxx where you want the loop to stop. Select the cell at the top of the column and run this macro. Sub proDelete() Do Until Selection.Value = "xxx" If Selection.Value Selection.Value = "" Then Selection.EntireRow.Delete Else Selection.Offset(1, Selection.Offse t(1, 0).Select
Page 25 of 38
End If Loop Range("A1").Select End Sub Exiting a Loop
If in the loop above you want the loop to stop when it finds the value 99 you can add this line of code within the loop: If Selection.Value = 99 Then Exit Do
Exit allows you to get out of almost anything like: Exit Sub Exit For Exit Do VBA Lesson 22: External Data and SQL in VBA for Excel
SQL is the language used to extract data from a database through a query. Do you need to learn SQL....the answer is NO. Here is a basic piece of code to connect to an Access database and extract data using SQL. Detailed explanations follow the code and you can download the database for FREE to test the procedure: Sub proSQLQuery1() Dim varConne varConnection ction Dim varSQL Range("A1).CurrentRegion.Clea Range("A1).Curr entRegion.ClearContents rContents varConnection = "ODBC;DBQ=C varConnection "ODBC;DBQ=C:\test.mdb;Driver= :\test.mdb;Driver={Driver {Driver do Microsoft Access (*.mdb)}" varSQL = "SELECT tbDataSumproduct.Month, tbDataSumproduct.Month, tbDataSumpro tbDataSumproduct.Product, duct.Product, tbDataSumproduct.City tbDataSumprod uct.City FROM tbDataSumprod tbDataSumproduct" uct" With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=Range("A1")) .CommandText = varSQL .Name = "Query-39008" .Refresh BackgroundQuery:=False End With End Sub
In the first two lines I create two variables . I will later store the connection to the database database in varConnection and I will store the SQL sentence in varSQL. I then clear the old data with: Range("A1).Cu Range("A1).CurrentRegion.Cle rrentRegion.ClearContents arContents If you connect to an Access database use the varConnection above changing "C:\test.mdb" for the path and name of you database. You can also use the Macro Recorder while connectint to an external data source with Excel. You will find varConnection for SQLServer, text files and other Excel workbooks in the Downloadable Tutorial. For the varSQL the best way is to make friends with your Database Manager (DBA) who will write them for you. If he is a good friend he will even develop a stored procedure procedure (that runs more rapidly because it runs on the main server). If you don't have a DBA or cannot make friends with him/her you can use the macro recorder again. You can also use Access as SQL writer.
Page 26 of 38
The 5 lines starting with With and ending with End With are the lines that you will use in ALL your procedures just changing the "Name" of the query. Click here to download (FOR FREE) the small Access database . Save it on your "C" drive.
Copy/Paste the VBA procedure above in the visual Basic Editor of a new workbook and try it. Make sure that the line starting with " varSQL= " is on a single line without any break. I have been using this very procedure for all the projects where I have to get data from an outside source. I modify the connection, I modify the SQL sentence, I modify the name and that is it. With Excel 2007 I add two extra lines not to have to enable the links when I open the workbook and to remove the new "Table" functionalities that I don't use for now and that give me problems with my SUMPRODUCT functions. Don't forget to change the name of the ListObjects. ActiveSheet.ListObjects("Query-39008 ActiveSheet.ListObjects("Qu ery-39008").Unlink ").Unlink ActiveSheet.ListObjects("Qu ActiveSheet.L istObjects("Query-39008 ery-39008").Unlist ").Unlist Copy/Pasting SQL Sentences
If you try to copy/paste an SQL sentence sentence from Access or any Query Analyser the result is a mess Sees how easy it gets when you use NotePad to prepare the copy/paste step in "vba-sql- 1.xls" one of the 25 Resource Spreadsheets ( Excel Excel VBA Download ) Dynamic Criteria
In the procedure above the criteria for the month "January" and for the product "Skates" are hard coded. You would like to use a drop-down list in the Excel workbook to select a criteria and execute the query with the selected value. VBA Lesson 21: VBA for Excel Functions
Three topics in this lesson: - using Excel functions within macros, - using VBA functions within macros, - creating new Excel functions with VBA. Excel Functions
Some of the functions that you find in Excel are available through macros in this form: Range ("C1").Value= Application.Wo Application.WorksheetFu rksheetFunction.Sum(Ran nction.Sum(Range("A1:A32")) ge("A1:A32"))
this sentence sums the values of cell A1 to A32 and stores the total in cell C1. VBA Functions
Here are two VBA functions that you will will use within your Excel macros: LCase, UCase
The "If" statements are case sensitive. When you test a string of characters and you don't know if the user will enter upper case or lower case letters, use the LCase or UCase functions within your "If" statement so that however the user enters his answer the statement will work. If LCase(Sele LCase(Selection.Value)= ction.Value)= "yes" then... then...
or If UCase(Selection.Value)= "YES" then... then... New Excel Functions
You can create new functions in Excel. For example the function created by the code below will simply multiply the value of a refered cell by 2.
Page 27 of 38
Function fctDouble(varIn fctDouble(varInput) put) fctDouble = varInput * 2 End Function
Once this code is in a module in your workbook you access the new function the same way that you access access the other functions in Excel Excel by clicking on on the icon function function on the tool bar bar the menu bar "Insert/Function "Insert/Functions". s".
or from
Add the above new function to a new workbook. Enter 6 in cell A1 and insert the new function in cell B1 (in (in the dialog box select the "User Defined" category and select the new function ("fctDouble") and enter A1 as varInput. The result is 12. If you need a new function that will transform $49.95 into "Forty nine dollars and ninety five cents" see how it is done in the spreadshe spreadsheet et "vba-template-numbers-letters.xls" "vba-template-numbers-letters.xls" one of the 25 Resource Spreadsheets VBA Lesson 23: Working with other Programs in VBA for Excel API
API stands for Application Programming Programming Interface and consists of a collection of functions that provide programmatic programmatic access to the features of the operating system (Windows). When you use API's within VBA for Excel not only do you control Excel but all other parts of Windows. Discover a few API's (CopyFile, DeleteFile and others) in the To delete a file from a disk you can also use the Kill statement that is not an API. Kill "C:\" & "myWorkbook.xls" Working with other Microsoft programs using VBA within Excel
Within Excel you can open another program and even act within it using VBA. For example here is a short macro that opens Word, then a new document to copy/paste the content of 2 cells from Excel to word and save the word document in the same directory as the workbook in which the macro runs: Sub proWord() Dim varDoc As Object Set varDoc = CreateOb CreateObject("Word.Applica ject("Word.Application") tion") varDoc.Visible = True varDoc.Visible Sheets("Sheet1").Range("A1:b1").Copy varDoc.documents.Add varDoc.Selection.Paste varDoc.activedocumen varDoc.activ edocument.SaveAs t.SaveAs ThisWorkbook.Path ThisWorkbook.Path & "/" & "testWord.do "testWord.doc" c" varDoc.documents.Close varDoc.Quit End Sub
Notice that you use VBA for Word within the object varDoc. If you don't know VBA for Word remember that there is also a Macro Recorder in Word. The object varDoc can be visible or you can work within it without bringing it on screen with: varDoc.Visible varDoc.Visibl e = False
So from Excel you can work with Windows (API's) and with Word, Access, Project, NotePad, Power Point, etc.
Page 28 of 38
VBA Lesson 24: Forms (Userforms) in VBA for Excel
When the message box or the input box are not sufficient anymore to communicate with the user you need to start developing userforms. The form or userForm is also known as a GUI (Graphical User Interface). The form is used to require values, parameters parameters and information from the user to feed the VBA procedure. Different basic controls can be added to the userform they are called: Label, TextBox, ComboBox , ListBox, CheckBox , OptionButton, Frame, CommandButton, SpinButton and Image . You can find all kinds of other controls on the Internet but the problem is that if you send your workbook to other people and the new control is not installed on their computer it wont work. This problem might even occur with the calendar. To learn more about all the controls see lessons 26 to 33. Creating a userForm in VBA for Excel
In lesson 3 you have learned how to add userforms to your workbooks from the project window of the Visual Basic Editor and this is what you see over the code window the userform itself and the toolbox:
You can close the toolbox by clicking on its " X" and call it back by clicking on the toolbox icon on the toolbar Testing the Userform
Anytime that you want to see your userform (finished product or work in progress) at work or when you want to test it select the userform by clicking in ay part of it that is empty (and (and not one of the controls), click on the "Run" button on the toolbar. toolbar. Your userform will show with Excel in the background and and you can play with it. To return to t o the VB Editor just click on the " X" of the userform. Adding Controls
The toolbox offers you 15 controls. They are (starting with the most used): Label, TextBox, CommandButton, ComboBox, ListBox, CheckBox, Frame, OptionButton and other ones that you will not use often the ToggleButton, ToggleButton, TabStrip, MultiPage, ScrollBar, Image and RefEdit. You can find and download all kinds of controls from the Internet but remember that if you use one of these controls and the person who uses your workbook hasn't installed it on his own computer it will not work. To add controls to the form you left click on whatever control that you want in the Toolbox. You then go to the userform, click and hold the left button of your mouse and stretch the control to the right dimension dimension..
Page 29 of 38
Once all your controls are on the form you left click on each of them and you can change the properties of the selected control in the properties window. Each type of control has tens of properties but only a few ones are important. You will discover these properties for each of the types of controls in lesson 26 to 33. To add many instances of the same control you double click on it in the toolbox and you do the stretching part on the useform as many times as you want. BUT don't forget to go back to the toolbox and click on the control one more time to deactivate the "multiple controls" functionality. WARNING: if you want to modify the properties of a control before multiplying multiplying it you will add a single copy of the control, you will modify its properties in the properties window and then you will use the copy/paste functionality by right clicking on it. Notice that when you do that the caption of the control (what is written on it) and its name (in the properties window) are not the same. Don't get confused when you start working with them. Tab Order
Once you have added the controls here is an important feature. The user can use the mouse to move from one control to the other but he should also be able to move from one control to the other by entering a value in one and clicking "Enter"or "Tab" and the focus will be set on the next control where he is supposed to enter a value not on a label. More importantly when the user enters a value in the final control you ant the focus to be on the right command button (Submit) and not on another one like "Close Form". To make sure that the user moves from one control to the next one in a set order you need to set the tab order. To do so, right click on the form itself and select the "Tab Order" item. Follow the instructions. The first control in the list list will be the one that is active (flashing cursor within) within) when the form is activated. Bring the controls that are not to be used by the user (labels) at the end of the list. For the controls that are not to be used you can also set the "TabStop" property of the individual control to "False" in the properties window. Managing Controls
You can move the controls by clicking on them holding and moving them around. You can resize them by selecting them and using the different handles around them. You can copy or cut them by right clicking on them and choosing the right menu item. Once you have added your controls you might might want to align a few of them or resize a few so that they are all the same size. To do so you first need to select many controls at the same time. To do so left click on the form near one of the controls that you want to select. Hold and drag drawing a frame that includes many controls.
When you let go of the button all the controls that are touched by the frame are selected.
Page 30 of 38
Right click on any of the selected controls and this contextual menu appears: appears:
You can then align the controls (7th menu item) or make them the same size (8th menu item). Discover the userform properties and the VBA code to work with it in lesson 25 so that among other things when you activate the userform all the list boxes and combo boxes get initialized. VBA Lesson 25: Userforms Properties and VBA Code The UserForm Properties
As you have seen in lesson 1 on the Visual Basic Editor you double click on the userform's name in the Project window and its properties appear appear in the Properties window:
Page 31 of 38
In the Properties window of of the VBE you MUST change the name name "(Name)" of the form, its caption (the name in the blue band at the top of the UserForm) and you can also modify the default setting of any of the 32 other properties of the form. When you name a form always use the prefix "frm" like in "frmDatabase" and be as descriptive as you can be so that your code will be easy to read. Always use one or more upper case letters in the name. When you write "frmdatabase.show" in lower case letters Excel will capitalize some letters "frmDatabase. Show" letting you know that the name is spelled correctly. The caption is what your users will se at the top of the userform. Be as informative as possible and complete the information with a label if necessary.
Else than the Name and Caption there are just a few properties that you might want to modify. You can select a different color for the background with the property "BackColor" . By default the userform appears in the center of the screen. If you want it to show somewhere else set the "Start" property to "0-Manual" and use the "Top" and "Left" properties to set a new position. The Code Opening and Closing the Userform
The first thing to do is to create code to call your your userform. Here is a basic line of code doing so: frmCity.Show
See lesson 9 on Events to learn how to assign macros to a text box or other button that you place on the sheet. The line of code to close the userform is: frmCity.Hide or Me.Hide "Me" being the general name of the active form The "Hide" sentence is usually part of the code of a command button on the form. A user clicks on a "GO" , "SEND, "SUBMIT" or "CANCEL" button and and part of what must happen happen is that the userform disappears from the screen. VBA Lesson 26: The Labels in VBA for Excel
In the toolbox the label has this icon . The label is a passive control meaning that the user never really acts on it. It is there to inform the user and to label other controls like text boxes, combo boxes or list boxes. Properties
The other interesting properties of the label are:
Page 32 of 38
- TabStop: To make the control invisible for the "Tab" and "Enter" keys (see Tab Order ) set this property to "False" . - WordWrap: If you want to write more than one line of text in a label set this property to "True" . Code
There is not much coding developed for the labels although there are 8 events related to the label. For example there is an event named "MouseMove" "MouseMove" . If you develop code within this event it is executed when the mouse moves over the label. If the code is the following: MsgBox "Don't forget to..."
a message box will appear when the user moves the mouse over the label. You can stack many many labels one over the other and make their "Visible" property to "False" . You can then make any of the label visible from an event related to another control. For example if a user chooses a certain value in a combo box a certain label appears. Labels are useful to create help files for other userforms like in the example below where the user has clicked on the "Help" button of the "Pricing Tool" to see the userform "Help on Pricing Tool" appear:
VBA Lesson 27: The Text Boxes in VBA for Excel
In the toolbox the text box has this icon
.
The text box is the simplest control to require an entry from the user. The user types something in it and this value can then be used in your VBA procedure. You will usually add a label to accompany the text box.
Page 33 of 38
For most controls including the VBA for Excel text box there are general properties that allow you to set the font, the color of the font, the color of the background, the type of background, background, the type of border and other design features. Using the 3 windows in the Visual Basic Editor you will see the following properties properties in the "Property" window window when the text box is selected. Properties
The other interesting interesting properties properties of the text boxes boxes are: - WordWrap to be able to write more that one line on a button, - ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button, - Enabled and Visible are properties that you can change programmatically to disable or render invisible a command button following a previous selection in another control of the userform, - TabIndex is a property that you change through the "Tab Order" functionality as shown in the UserForms section. - MaxLength to limit the number of characters entered by the user, - Value or Text which is the text show in the text box when the userform is activated ("Enter your Name" for example) To ask users to submit a password to run certain macros develop a userform with a text box and a command button. In the text box you can modify the " PasswordChar " property so that when the user enters the password nobody around can read it. Use an asterisk, an ampersand or any other character in it. Code
The most important ting to remember is that a text box is what its name says it carries text. So if you want to send a numerical value from a text box to a cell you must use the "Value" thing: Range("A1").Value=tbxInput.Value
When you want to create calculated text boxes you must use the "Val()" " Val()" thing:: tbxInputTotal= Val(tbxInput1) + Val(tbxInput2)
In "vba-form-spin-button.xls" (one of the 25 downloadable spreadsheets) spreadsheets ) you will discover a pricing tool. You will learn how to format text boxes boxes to show currencies and percentages percentages and how how to create easy help files for small tools like this pricing tool. Also discover the VBA code to disallow the use of the "X" to close the form.
VBA Lesson 28: The Command Buttons in VBA for Excel
In the toolbox the command button has this icon control and there is always VBA code behind it.
. The command button is a very active
The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go" , "Run" , "Submit" , "Cancel" , etc.
Page 34 of 38
Properties
The other interest interesting ing properties of the command button are: - WordWrap to be able to write more that one line on a button, - ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button, For advanced users there are the: - Enabled and Visible properties that you can change programmatically to disable or render invisible a command button following a previous selection in another control of the userform. Code
Name your command button before developing your code. VBA uses the name of the command button when it creates lines of code related to events. So if you don't name your command button VBA will create the private sub:: Private Sub CommandButto CommandButton1_Click() n1_Click()
as if you name the command Button "cmbSubmit" "cmbSubmit" for example the private sub will start with: Private Sub cmbSubmit_Cli cmbSubmit_Click() ck()
If you name your command buttons buttons after private subs have been created they won't work anymore. A very simple VBA procedure for the command button would look like this: Private Sub cmbSubmit_Cli cmbSubmit_Click() ck() Sheets("Code").Range("F1").Va Sheets("Code ").Range("F1").Value lue = cbxInput.Va cbxInput.Value lue frmPassword.Hide End Sub
The content of the combo box "cbxInput" is entered in cell "F1" of the sheet "Code" and the form (frmPassport) is closed. VBA Lesson 29: The Combo Boxes in VBA for Excel Before we begin on the Combo Box
The difference between a combo boxes and the list boxes is that the combo box is a drop-down list and the user can submit a single value from the drop-down list. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values. Combo Box
List Box
If you are looking for a drop-down list (also called pull-down lists) to use on a regular worksheet see the much easier and user friendly Excel drop-down lists in the website on Excel. When you double click on the combo box in the Visual Basic Editor you will see all its properties in the Prope Properti rties es wind window ow .
Page 35 of 38
No programming is needed needed to submit the list of values that will be offered to the user within the combo box. Look for the RowSource property. The RowSource Property:
The values that should appear in the drop-down list of the combo box are submitted in the RowSource property. For example if the value of the RowSource property is Balance!A1:A12 The values residing in cell A1 to A12 of the t he sheet named Balance will be offered as choices to the user who clicks on the small arrow of the combo box. The rules to submit the RowSource property is the name of the sheet where the list resides followed by an exclamation point (!), the address of the first cell, a colon and the address of the last cell. IMPORTANT NOTE: if there is a space or a special character within the name of the sheet where the list resides you must surround it with simple quotes like in 'New Balance'!A1:A1 Balance'!A1:A12 2. Cascading Combo Boxes
You want the second combo box to offer a choice of values that differs based on the first combo box. We call it cascading combo boxes.
VBA Lesson 30: The List Boxes in VBA for Excel Before we begin on the List Box
The difference between a combo boxes and the list boxes is that the combo box is a drop-down list and the user can submit a single value from the drop-down list. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values. Combo Box
List Box
If you are looking for a drop-down list to use on a regular worksheet see the much easier and user user friendly Excel drop-down list. In the toolbox the list box has this icon
.
Page 36 of 38
Properties
- RowSource The values that should appear in the drop-down list of the combo box are submitted in the RowSource property. For example Sheet1!A1:A12 Sheet1!A1:A12 will feed the list with the values residing in cells A1 to A12 of the sheet with the Caption "Sheet1" . The rules to submit the t he RowSource property is the caption of the sheet where the list resides followed f ollowed by an exclamation mark (!), the address of the first cell, a colon and the address if the last cell. IMPORTANT NOTE: if there is a space or a special character in the caption of the sheet where the list resides you must surround it with simple quotes like in 'This sheet'!A1:A12. sheet'!A1:A12. - MultiSelect is set to 1 if you want the user to be able to select many many values from the list. - Height The number of values shown in the list will depend on the height of the list box. You can set the height here or on the userform itself by stretching it. If the number of values in your RowSource is greater than what can be shown in the list box a scroll bar is added automatically. - Text should contain the value shown in the combo box when the userform is activated (Select a City, for example). - ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the combo box. - ColumnCount is the number of columns of values that you want shown in the list box. For example if you want to show part number and part name in the list you will submit a RowSource like Sheet1!A1:B12 with the part numbers in column A and the part names in column B - ColumnWidth is the width of all the columns shown in the drop-down list of the combo box. - BoundColumn is the column from which the value is drawn for the final value of the combo box. For example if the part number is in column A of the RowSource and the part name is in column B of the RowSource when the user select a value only column A or column B will become the final value of the combo box . So if you set the value of BoundColumn to 1 the part number becomes the final value. If you set BoundColumn to 2 the part number becomes the final value. VBA Lesson 31: Option Buttons, Check Boxes and Frames
In the toolbox the option button has this icon this one
, the check box has this one
and, the frame
.
You don't need to add a label to accompany the check box or the option button because they come with their own. The check boxes and the option buttons are both used to offer the user a choice. The main difference between between check boxes and option option buttons is that if you have have 5 of each on a form a user can check all 5 check boxes but can only select one of the option buttons. If you want to create two sets of option buttons read below below on frames and option buttons. If you don't want to use frames to create groups of option buttons you will need to use the "GroupName" property of the option buttons. All option buttons with the same GroupName work together. Properties
- WordWrap to be able to write more that one line in the caption, - ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions instructions about the option button or the check box. - Enabled and Visible are properties that you can change programmatically to disable or render invisible an option button or a check box following a previous selection in another control of the userform. Frames
Frames are also a passive control. Frames are used to improve the layout of the userform. You can use them around a group of controls that have something in common.
Page 37 of 38
Frames become more important to manage option buttons. If you have two sets of option buttons on a userform and you don't place them within a frame they all work together and you can choose only one. If you put each set within a frame you can choose one in each set. When you move a frame all its controls move with it. VBA Lesson 32: Spin Buttons Spin Button
In the toolbox the spin button has this icon
.
You can ask a user to enter a value directly in a text box but you can make things a little fancier by using a text box and a spin button. The spin button is not really used by itself. Because the spin button doesn't show its value it is usually used with a text box. The text box shows a number and by clicking on the arrows of the spin button the value in the text box is increased (or decreased) by 1, or 5 or 10...by whatever value that is set within the properties of the spin button. Properties
The other interesting interesting properties of the spin buttons buttons are: - Min is the minimum value of the spin button. It can be negative - Max is the minimum value of the spin button. It can be negative - Small is the value of the change when the user clicks on the arrows - Large is the value of the change when the user clicks on the scroll bar of the spin button In "vba-form-spin-button.xls" (one of the 25 downloadable spreadsheets) you will discover a pricing tool. The user selects a percentage of increase (or decrease) and a new price is calculated. The step by step instructions will show you how to program the relation between the spin button and the percentage text box. You will also learn how to format text boxes to show currencies and percentages and how to create easy help files for small tools like this pricing tool. Also discover the VBA code to disallow the use of the "X" to close the form.
VBA Lesson 33: Image Controls Image Control
There is a control in the toolbox called "Image". Within this control you can show all kinds of pictures. You set an image control on a userform and you submit a picture in the property "Picture". The picture becomes part of the control and userform. Fitting the Picture
The first thing that you want to do is to fit the picture in the image control to make the size of the control adapt to the size of the picture.
Page 38 of 38
When you are in the Visual Basic Editor and you single click on an image control a frame appears around it with 8 stretchers (picture below). If you double click on the middle stretcher (when a two tips arrow shows) of the right side or on the middle one at the bottom or on the bottom right corner stretcher the image control will adapt to the size of the image. Double clicking anywhere else will take you to the VBA code and will not adapt the control size to the picture size.
PictureSizeMode PictureSizeMode Property
Another interesting property of the image control is the PictureSizeMode. If the property is set to the default value 0-frmPictureSizeModeClip 0-frmPictureSizeModeClip the control size can be changed without the picture size being modified. So you can see only part of the picture or there can be a background behind it which color you can change at will. If the property is set to the 1-frmPictureSizeModeStretch 1-frmPictureSizeModeStretch the picture is resized as the control is. The image fills the control. If the property is set to the 3-frmPictureSizeModeZoom 3-frmPictureSizeModeZoom the picture is resized as the control is but the picture and background are present. See how you can do that with all the step by step programming instructions in " vba-form-imagecontrol.xls" one of the 25 downloadable spreadsheets . Coupled with a combo box you can develop an interesting selector using pictures to make the choices easy (image below).
