VBA Starter Training Course - Online PC Learning
VBA Starter Training Course
Visual Basics for Applications for Beginners Page 2Copyright 2013 Trevor Easton Online PC LearningAuthor Trevor EastonAll rights reserved. No part of the contents of this book may be reproduced ortransmitted in any form or by any means without the written permission of TrevorEaston.For support related to this book, email Online PC Learning Support attrevor easton@onlinepclearning.comThe example companies, organizations, products, domain names, emailaddresses, logos, people, places, and events depicted herein are fictitious. Noassociation with any real company, organization, product, domain name, emailaddress, logo, person, place, or event is intended or should be inferred.This book expresses the author’s views and opinions. The information containedin this book is provided without any express, statutory, or implied warranties.Trevor Easton nor Online PC Learning will be held liable for any damages causedor alleged to be caused either directly or indirectly by this book.2 PageCopyright 2013 Trevor Easton Online PC Learning
Visual Basics for Applications for Beginners Page 3Visual Basics for Applications for BeginnersForeword 9Why I have written this information9How to use this e-book9Companion Excel Workbook ResourceResource Workbook ContentsChapter 1: What can you do with VBA?111113OVERVIEW13Run a simple test14Reduce staff costs15The benefits15Chapter 2: Understanding the Visual Basic Editor17OVERVIEW17Basic VBE Components17VBE Project Explorer18Saving the Workbook20The Properties Window21Understanding Modules22Procedures Window23Chapter 3: What is the VBA object based language27OVERVIEW27Let’s go Camping27Understanding Objects27Object Properties28Is there Method in your Methods29What are Events?30Chapter 4: Recording your first macro33OVERVIEW33Where Do I Start?33What can I record?34The Developer Tab34Allowing Macros in your Workbooks34Record our first macro36Run the macro from the VBA editor373 PageCopyright 2013 Trevor Easton Online PC Learning
Visual Basics for Applications for Beginners Page 4Test your new skills at recording macrosChapter 5: Working with VBA (learning to speak the language)3941OVERVIEW41Adding a Module41Adding a Userform43Code Keywords44Entering code45Relative and absolute references48Line-continuous character48Stepping through the code48Chapter 6: How to use Variables in VBA53OVERVIEW53Naming a variable54Why use variables?55Declaring variables55What is a data type?56What is the scope of a variable?57Chapter 7: IF Statement and Decision Modelling59OVERVIEW59Decision Modelling59If – Then Constructs (True)60Single If-Then61Multiple If-Then constructs61If–Then End If62Multiple If - Then - End If62If – Else (True, False)63If – Then - ElseIf – Else65Nested If - Then - Else66If – And –Then66If – Or – Then67If – Not – Then68IIf Function68Chapter 8: Select Case Constructs71OVERVIEW71Benefits of the Select Case function724 PageCopyright 2013 Trevor Easton Online PC Learning
Visual Basics for Applications for Beginners Page 5Syntax72Select Case - (Single condition)74Select Case - (Multiple conditions)74Select Case – Else75Multiple Conditions (Comma separator)75Multiple Conditions Between (To)76Combining Multiple Conditions (To Separator)76Using Operators with Select Case77Select case and Text Conditions77Option Compare Text78Combining Numbers and Text Conditions79Combining Select Case (Magic combinations)79Chapter 9: Excel VBA Message Boxes81OVERVIEW81Let your applications speak for themselves81Microsoft Excel’s use for message boxes81Simple VBA message box82Constants available to the message box function87How it all works behind the scenes89Multiple lined Message Boxes89Message Boxes Examples90Chapter 10: Understanding the Input box construct95OVERVIEW95Why use an Input Box96Input Function97Input Method97Type argument98A Simple Input Box98VBA Examples99Create a User Login99Collect employee age102BMI Calculator104Working with Ranges (Input Method for Objects)106Chapter 11: Understanding VBA loops by Examples107OVERVIEW107Do Loop1085 PageCopyright 2013 Trevor Easton Online PC Learning
Visual Basics for Applications for Beginners Page 6Do Until Loop109Do While Loop110For Loop111For Each Loop115Nesting Loops117Chapter 12: Basic Error Handling121OVERVIEW121Why Use Error Handling?121How to Configure Error Settings122How to Reset Errors122Three Types of VBA Errors123When to use error handling125What is the VBA Debug?125Understanding the On Error Statement126VBA Resume Statement Explained131Chapter 13: Build your First Userform Database135What is a Userform?135Userform Project136Insert a Userform138Userform Properties138Toolbox139Adding Controls140Writing the VBA Code145Writing our code146Set up the Database Worksheet150Test the userform with data151Setting up The Interface151Running the advanced filter152Chapter 14: Developing your first Application155How do we develop bullet proof Excel Applications?155Where do I start?155Great Idea – Bad Design156I’m going to share with you my recipe for success.156In the line of fire1566 PageCopyright 2013 Trevor Easton Online PC Learning
Visual Basics for Applications for Beginners Page 7In Conclusion162Appendix - Macros in this eBook163Message Box Macros163Variable Scope163If Function Macros164Select Case Macros168Message Box Macros172Input Box Macros175Loops Macros180My Tax Receipt Macros1927 PageCopyright 2013 Trevor Easton Online PC Learning
VBA for Beginners: Features Page 8VBA for Beginners: Features190 Pages: Step by Step TrainingWritten In language the beginner can understandThis learning guide has been written for the VBA beginner. I have tried tobreak all of the learning down to the basic building blocks required tounderstand Visual Basic for Applications.More than 130 full colour illustrationsTo add to an easy learning experience I have added over 130 full colourillustrations, many with notes and diagrams to highlight the necessary stepsrequired to learn effectively.MacrosAfter you have read this publication you will be returning to find code to usein your future applications. For this reason I have added all of the keymacros at the end of the publication. They are listed under the function theybelong too. This will save a lot of time when you return to the publication.Five Projects to completeIn harmony with the spirit of Online PC Learning, that project based learningis a fast and effective learning path. I have included projects throughout theeBook. This will give you the chance to practice your skills as you go.1. User Login2. My Age Calculator3. BMI Calculator4. My Tax ReceiptsCompanion Resource WorkbookAlong with this eBook you will receive a companion Excel file that is set up todemonstrate many of the chapters within.8 PageCopyright 2013 Trevor Easton Online PC Learning
Foreword Page 9ForewordWhy I have written this informationThe chapters in this book and the accompanying files will enable you toquickly grasp the basics of VBA in Microsoft Excel. There is a lot ofpublished information on the web that deals with the learning Visual Basicfor applications. Often a newcomer becomes lost with the complexity andrandomness of the information provided.Not everybody learns the same way. Some people like learning byrout from books and in many situations that may be necessary. Butthat’s not me. I am a visual learner and I learn by associating tasks.We will be learning by examplesI have presented the chapters in such a way that each chapter will set thebasis for the next chapter so that you can progressively learn the visual basicsfor Applications.Based on my own learning experience and the questions I asked on OnlineP See Learning, these chapters should cover all that is necessary to get youup and running and developing some awesome VBA code.There are illustrations throughout the chapters to demonstrate all the keypoints that are presented.How to use this e-bookGo through the book chapter at a time. If you feel you fully understand all theconcepts in each chapter then move to the next chapter.Open the companion Excel workbook resource file that is provided and viewand run the code that are provided as examples and test it out in yourcomputer.You will remember concepts more easily if you use them in a file rather thanjust read how to go about achieving the desired result.Best wishes with your learning endeavorsgÜxäÉÜ XtáàÉÇ9 PageCopyright 2013 Trevor Easton Online PC Learning
Companion Excel Workbook Resource Page 11Companion Excel Workbook ResourceAlong with this eBook you will receive a companion Excel file that is set up todemonstrate many of the chapters within.Resource Workbook ContentsooooooInterface If FunctionCase FunctionInput box function My Age Calculator BMI CalculatorLoopsError HandlingUserforms My Tax ReceiptsAs you can see above there are also 3 completed applications.11 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 1:What can you do with VBA? Page 13Chapter 1: What can you do with VBA?OVERVIEW Automating business processesObtain accurate data analysisReducing labor costsIncrease job satisfactionAlmost anything that you can do manually in Excel you can do with VBA andin a fraction of the time normally taken. When the code is written or recordedit can be executed time and time again in a number of ways.You can grab objects, workbooks, worksheets, ranges charts and shapesand do almost anything with them in a fraction of a second.VBA is an object based language and VBAMacro or procedure is simply a set ofinstructions to manipulate objects.In fact you can do more with VBA procedures than you can do manually inmany instances. Some tasks can only be accomplished with code.VBA is an object based language and VBA Macro or procedure is simply aset of instructions to manipulate objects. Format a worksheetAdd data validationCreate a chartActivate a pivot tableFilter large data ranges with multiple criteriaAnd lots moreSpeed up processesTasks that would normally take a very long time to do can be accomplishedin just a few seconds.13 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 1:What can you do with VBA? Page 14Run a simple testFor many years now, to demonstrate the power of Excel 2003 I havehighlighted all of the cells on a worksheet by clicking in the box diagonally tothe left and above cell A1 on a spreadsheet. Then type a number and pressCtrl Shift Enter. This would insert the number into the approximately 16million cells on the worksheet.Execution time varies with computers but the job is done in about 3 to 4seconds. The point that I would make is that if you were to type a numberinto 1 cell at a rate of 1 per second and did that 24 hours a day, 7 days ofthe week, it would take about 180 days to complete the task.We just did that in 3 seconds!From 2007 onwards the columns and rows increased to 1048576 rows and16384 columns which is equal to 17,179,869,184 cells on each sheet. If youtried to do that, there would not be the available resources necessary toaccomplish the task.If you were to highlight just the first 20 columns which is 20,971,520 cellsand then add a number the way I have described it would take about 6seconds for the 20 million cells to be filled.Here is the code to do that. Don’t save your work if run this experiment asthe file will be about 55 mb in size.Sub AddNumber()Columns("A:T") "8"End SubCan VBA save you time? The answer is yes, and lots of it.Analyze dataNow let’s look at a more practical example.As an example let’s say you have a large data set with 30 columns asheaders and 5000 rows deep and you want to filter that data based on somemultiple criteria. That is easy enough to do with the advanced filter that Exceloffers. The problem is every time you want a result you need to set up thefilter to run with the new data and criteria.With a small piece of code, that laborious task is no longer necessary, youjust click a button and the filter is automatically set for you. Then add asimple variable and the criteria can be changed depending on the optionselected by the user.14 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 1:What can you do with VBA? Page 15Reduce staff costsRepetitive tasksAnything that you do in Excel on a regular basis is a good candidate for aVBA procedure.Two real examplesExample 1A hospital with 20 wards has staff in each ward working four hours a week toput together a list of all the staff working on each day and each shift. Theythen go to their roster and copy that information to another spreadsheet.To do this with a VBA procedure takes less than 3 seconds and the sameprogram is used for all 20 groups. Time saved per week is 80 hours atapproximately 30 per hour which saves approximately 2400 per week.Example 2Every morning an activity report needs to be run and sent to the director.This is a repetitive task that takes one hour. Imagine starting your day withthat ahead of you. This process can be automated with VBA instructions andperformed with just a couple of clicks.The benefits1. Better job satisfaction2. Accurate data3. Labor savingsIf you are paying wages time is money and anything that can save time andeliminate human error is good business practice.15 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 1:What can you do with VBA? Page 1616 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 17Chapter 2: Understanding the Visual Basic EditorOVERVIEWIn this chapter we will take a tour of the Visual Basic Editor. I will run youthrough the standard setup and some of the key options you will need tounderstand. VBE Project ExplorerChanging the project nameProtecting the codeSaving the WorkbookThe Properties WindowDocking and UndockingUnderstanding ModulesModules are filing cabinetsNaming the ModuleProcedures WindowIntellisenseAdding comments to your codeVBE Immediate WindowQuick testBasic VBE ComponentsSHORTCUT FOR VBE: Alt F11To get started let’s open the Visual Basic Editor. (The Visual basic editor is aprogram that comes with Microsoft Office)Open the VBE by holding down the Alt F11key and enable these windows inyour editor. Click on the View tab and select the windows that you to appearin the editor.For a basic setup you should have the Project Explorer / Properties Windowand the Intermediate Window visible as you work. These options areavailable from the View tab on the menu bar of the Visual Basic Editor asillustrated below.17 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 18VBE Project ExplorerSHORTCUT FOR THE PROJECT EXPLORER: Ctrl RAs you look at the Visual Basic Editor you will notice that the name given toeach workbook by the Visual Basic Editor is “Project-VBA Project”.Below the name in the Project Explorerthe hierarchy or tree with all of theobjects for each project exist. If youcannot see the Project Explorer thenclick on the View tab at the top of theVBE and click on Project Explorer.When you single click on each object ormodule you select it. You can then rightclick to view the shortcut menu andchoose from the options. Optionsinclude opening the properties orviewing the code or deleting the object.If you double click an object you will be taken to the code for the procedurein the VBA Code Editor.Changing the project nameIt is good practice to change the name of the Project to more accuratelyreflect what that project does. If you have several workbooks open this willhelp you to clearly identify each project.To change the project name:1. Right click on the VBA Project18 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 192. Choose the General tab3. Add your new name in the box for Project Name4. Click OKProtecting the codeIf you do not want your precious code altered or perhaps you want to keep itsecret, then you should protect the VBA part of your application.1.2.3.4.5.6.Right click on the VBA project titleSelect the VBA Project PropertiesTick the box next to Lock project for viewingAdd your password and confirm it in the text boxes providedClick SaveClose and save your workbookWhen you next go to the VBA Editor you will need to supply tour passwordto open it so do not forget what the password was.Note:Protection in Excel Workbooks is not hard to crack. So in essence you willbe protecting your code from the honest and less experienced Excel user.19 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 20Saving the WorkbookIn Office 2007, Microsoft changed its file structure for workbooks from abinary (.xls) to a special (Microsoft) .xml format. That is why the file structurechanged from (.xls ) to (.xlsx). The x indicating the xml format.It is necessary to save your file to a .xlsm (Macro enabled) file if you areusing 2007 plus versions. You will be prompted (as shown below) with awarning and if you continue without choosing .xlsm all of your code will bedeleted from the workbook.DO NOT CLICK “YES” or all will be lost.Follow the steps listed below and your work will be fine and safely stored.Here are the steps: File / Save AsSave as type .xlsmFile name “Add Appropriate”Location Where you want to store the fileSave This will execute your options20 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 21The Properties WindowSHORTCUT FOR THE PROPERTIES WINDOW: F4Objects have properties. In fact all objects have properties. What we meanby properties is that the object can have a name or a color or a size and soon. In essence it is a setting for the object.Think of it as descriptive, like an adjective in English. Properties available toan object vary depending on the object selected.You will need to have the properties window readily available. It is possibleto float this window if that is preferable to your development style. Ipersonally like to have the properties window somewhere on the screen as Iam writing code. It can be floating or docked. This illustration below showsthe floating Project Explorer and the Propertied Window. To float them clickon the top of the window and drag the window to where you want. Noticehere we have Sheet2 selected and the relevant properties are displayed forthat object.Docking and Floating WindowsDocking means, locking the window into its home position in the Visual BasicEditor. To undock (float) the window, hold down the left mouse button on the21 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 22title and drag the window to the location of your choice. The main thing isthat you can get to it quickly.To dock the window to the left hand pane, first maximize the VBE editor thendrag the window to the left hand side of the screen.Understanding ModulesModules are the home for procedures. You can add multiple procedures /macros to a single module. Just as you can have multiple rooms in a home.Modules are filing cabinets (Modular filing cabinet)Modules are like filing cabinets for your procedures. A filing cabinetorganizes your paperwork. Open a draw and select the tab for “Rates” andyou should expect of find all of the rate bills. Our Modules serve the samepurpose. When you open the VBE you should quickly be able to locate yourspecific procedure by the name of the module.Naming the ModuleYou should name a Module to represent the contents that it contains. This isdone in the properties window for that module. Double click on the Moduleand then change the name in the properties window.As an example:If you put all of the procedures22 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 23that filter ranges into a Module, you could call that Module “MyFilters” orsomething descriptively similar. This is like a tab at the top of the Modularfiling cabinet. Don’t put unrelated macros in a named module.So in summary a Module can contain multiple procedures. It is a good ideato keep them manageable by categorizing the procedures and limiting thenumber within each Module.Procedures WindowThe procedures window or code editor is where you add, view and edit theVBA code. When you record a macro, the code will appear in this window.Here is an example of a simple procedure to toggle the sheet tabs visibly.Sub is short for “Sub Procedure”.IntelliSenseA great feature that is often overlooked is IntelliSense. IntelliSense appearswhen you type a period (Dot) to separate the levels of the object members inthat hierarchy. This will give you a list of members associated with theobject. Double click on the list item to insert it into the code for thatprocedure.This is a list of methods and properties available to the object. If you cannotfind it in the list then it is not available to the object.23 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 24If this feature does not appear to be working for you then go to Tools /Options / Auto List Members to activate IntelliSense.Adding comments to your codeMacros can be very long and can be associated with other procedures. It isa good idea to add comment to your code so that others who view and edit itcan understand what is happening. For me, it is so I can remember what thecode is doing each time I visit it. It is very good practice to add commentsthroughout your procedures and name them intuitively.There are two ways to do this. The most common is to use an apostrophe (‘)in front of your comment. For example: ‘here is my commentThe second method and now almost extinct is the (Rem) statement. Put thestatement in front of you comment e.g.Rem here is my commentWhen you do this you will notice that that line of code will change color to letyou know it is not part of the executable code.VBE Immediate WindowSHORTCUT FOR THE IMMEDIATE WINDOW: Ctrl GThis window is very useful in testing small parts of the code as you aredeveloping the procedure.You will be able to use this window with single statements. Simply type inyour statement and press enter. As the name suggests, it will execute thecode immediately.Quick TestHere is an example. In cell A5 type the value 11, then in the Immediatewindow add a question mark followed by Range("A5") 10. The (?) will printthe answer in the Immediate window. Put the curser inside the code and24 P a g eCopyright 2013 Trevor Easton Online PC Learning
Chapter 2: Understanding the Visual Basic Editor Page 25press enter and you will see True appear in the next line in the immediatewindow. Because the number 11 is greater than the number 10.? Range("A5") 1025 P a g eCopyright 2013 Trevor Easton Online PC Learning
VBA Resume Statement Explained 131 Chapter 13: Build your First Userform Database 135 What is a Userform? 135 Userform Project 136 Insert a Userform 138 Userform Properties 138 Toolbox 139 Adding Controls 140 Writing the VBA Code 145 W
Updated to include preliminary information on the VBA language from the pre-release version of VBA 7. 3/15/2010 1.0 Major Updated to include information on the VBA language as of VBA 7. 3/15/2012 1.01 Major Updated to include information on the VBA language as of VBA
13.2. Excel and VBA Implementation 248 APPENDIX A VBA Programming 255 A.1 Introduction 255 A.2 A Brief History of VBA 255 A.3 Essential Excel Elements for VBA 256 A.3.1 Excel Cell Reference 257 A.3.2 Excel Defined Names 261 A.3.3 Excel Worksheet Functions 264 A.4 The VBA Development Enviro
VBA4-Using Cell Formulas in VBA Page 3 of 7 O:\COURSES\VBA\Online VBA\VBA4-Using Cell Formulas in VBA.docx 8. While ActiveCell.Value "" is the way to loop as long as there is a value in the current cell. You also might use an offset: While ActiveCell.Offset(0,-1).Value "" will test the column to the left of the current column.
We can use VBA in all office versions right from MS-Office 97 to MS-Office 2013 and also with any of the latest versions available. Among VBA, Excel VBA is the most popular one and the reason for using VBA is that we can build very powerful tools in MS Excel using linear programming. Application of VBA
Programming: VBA in MS Office – An Introduction 3 IT Learning Programme 1.4. What is VBA? VBA is a high-level programming language that sits behind the Microsoft Office suite of applications. It is made available, through the built-in VBA Editor in each applicable application, to the end user to create code that can be executed within
begin using VBA and writing a simple macro. You access VBA through Inventor using the Macro Visual Basic Editor command in the Tools menu, or by pressing Alt-F11. Once the VBA environment is open, the first thing I recommend you do is change some of the VBA settings. In the VBA environment run the Options command from the Tools menu. Change the
3 Introduction 5 Life Skills 8 Discussion Starter 1 “Diversity” 9 Discussion Starter 2 “The Man and the Eagle” 10 Discussion Starter 3 “Color Blind” 11 Discussion Starter 4 “Crayons” 12 Discussion Starter 5 “The Crayon Box That Talked” 14 Discussion Starter 6 “If All the Trees Were Oaks” 15 Discussion Starter 7 “The Black Balloon”
Python figures out the variable types on its own. Monday, October 19, 2009. Basic Datatypes Integers (default for numbers) z 5 / 2 # Answer is 2, integer division. Floats x 3.456 Strings Can use “” or ‘’ to specify. “abc” ‘abc’ (Same thing.) Unmatched can occur within the string. “matt’s” Use triple double-quotes for multi-line strings or .
