Hyper-disambiguated Excel VBA Programming | Grant Gamble | Skillshare

Hyper-disambiguated Excel VBA Programming

Grant Gamble

Hyper-disambiguated Excel VBA Programming

Grant Gamble

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
40 Lessons (5h 57m)
    • 1. 1.1. Course introduction

      4:47
    • 2. 1.2. Excel VBA overview

      1:40
    • 3. 1.3. The Developer Tab

      2:52
    • 4. 1.4. Macro security

      5:48
    • 5. 1.5. The Visual Basic Editor and Project Explorer

      2:02
    • 6. 1.6. Excel objects and event code

      7:59
    • 7. 1.7. Writing a macro in the VBE

      3:57
    • 8. 1.8. Using the Immediate window

      3:11
    • 9. 1.9. Saving a macro-enabled workbook

      1:30
    • 10. 1.10. The correct way to use the macro recorder

      12:50
    • 11. 1.11. Analysing a recorded macro

      6:58
    • 12. 1.12. Improving a recorded macro

      11:59
    • 13. 1.13. Assigning a macro to a button

      12:52
    • 14. 1.14. Testing a macro on another worksheet

      5:54
    • 15. 2.1. The Excel Object Model

      12:07
    • 16. 2.2. VBA Classes and Functions

      11:28
    • 17. 2.3. Properties and Methods

      12:54
    • 18. 2.4. Data Variables

      13:27
    • 19. 2.5. Object Variables

      19:25
    • 20. 2.6. Option Explicit

      5:25
    • 21. 2.7. Declaring and Using Constants

      5:49
    • 22. 2.8. Using MsgBox for Output

      10:11
    • 23. 2.9. Using MsgBox for Input

      9:30
    • 24. 2.10. Using VBA InputBox

      6:25
    • 25. 2.11. Using Application MsgBox

      6:19
    • 26. 3.1. If Statements

      11:36
    • 27. 3.2. If ... Else Statements

      13:18
    • 28. 3.3. Select ... Case

      9:19
    • 29. 3.4. For ... Next Loops

      9:32
    • 30. 3.5. For Each ... Next Loops

      6:46
    • 31. 3.6. Do ... While Loops

      13:56
    • 32. 3.7. Do ... Until Loops

      7:27
    • 33. 3.8. Creating Arrays

      10:16
    • 34. 3.9. Dynamic Arrays

      13:25
    • 35. 3.10. Utilizing Arrays

      11:05
    • 36. 4.1. Sub Procedures and Module-level Variables

      10:53
    • 37. 4.2. Static Variables

      5:29
    • 38. 4.3. Passing Parameters

      11:29
    • 39. 4.4. Creatig and Calling Functions

      7:48
    • 40. 4.5. Creating User-defined Functions

      17:04
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

153

Students

--

Projects

About This Class

The video tutorials in this course will show you how to automate Microsoft Excel tasks and operations. It is aimed particularly at Excel users, without much programming experience, who struggle to remember the syntax and structure of the VBA programming language. During the course, we will use a style of programming which emphasizes the significance of each line of code that you write. This means that when you revisit the code, the meaning of each line is apparent from the syntax, rather than becoming more and more unfamiliar. The course also demonstrates how to get the maximum benefit from IntelliSense, Microsoft's code completion feature. Whether you are looking to enhance your career prospects, save your company money or increase your productivity, this course will provide your with all the knowledge you need to get started. You can download all of the materials used in the lectures, so that you can follow along. (Please, remember to unZIP the downloaded files.)

Meet Your Teacher

Teacher Profile Image

Grant Gamble

Teacher

Grant Gamble is an experienced IT trainer, developer, consultant and author able to deliver a wide range of training courses. He has a vast experience of delivering public and on-site IT training content at different skill levels, to groups of varying sizes.

His UK company G Com Solutions Limited provide IT training courses and consultancy to a wide range of UK and international clients. His speciality is running week-long, intensive training workshops on topics like Microsoft Power BI, VBA, web development and Adobe Creative Suite automation.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. 1.1. Course introduction: Hello. I'm drawn. Gamble on Welcome to hyper different Regulated X l e d a. I know you're thinking I don't watch. What's he on about? Well, let me explain. I've been running Excel deviate courses for a long time on I find that if someone does programming regularly Web programming sp .net, whatever, then BB a falls into place in the Atlantic. Very insolent. But those people remain Lee Excel people, and you don't do much weight programming. Find it much harder to stay on top of B B A and how selects. I've even found that people often after dealing, trained if they hadn't done program for a long time, they start to forget the even the very basics. So the idea of this course is to install that Excel users fully understand the BB, a code that writing Andrea is a number of techniques on his course to make sure this happens. One of the key things we do on this course is to write the code in such a way that it becomes super clear what each line of code needs on This is what I mean by hype, reddish and regulation way. Don't just write the bare minimum to make sure, because works we didn't deliberately trying to make each time code is clear and self explanatory. It's possible things often means that you end up writing longer lines of code that must be gay program. It's, but the benefit is that it's much easier to see the function of these wires macro on. This becomes particularly useful BB eight program and isn't necessarily something you could regularly. So what course well, the course will cover all of the basic elements that you typically work with. When Postmates X up. We'll start with excellent stuff. This is reverted, the application object. I will write in code, which gets information from Excel, such a version being used on whether the users on a PC or a Mac look at the correct way of changing a user's excel seconds when you need to, and then how to restore the original value, so keep them happy. Then we'll move on to look at weapon with files and works. Obviously, when I work with his clothes, it's just a file, and then you open it becomes a workbook, so we'll learn how to create and delete files and folders how to process all the violence in a given folder. And then we'll talk about open in creating and deleting workbooks. How to check whether a workbook is open on how to check whether a local cake exists in unexpected locations. Levels roll down into worksheets, created them, deleting them, copying them, moving them have also discussed the most efficient ways of referring to worksheets, and also very fine for worksheet exists before you try and do something with it. We'll also look at the contrast between worksheets and charts sheets. They've got quite a lot in common separate tasking in a workbook, and they're also part of what's called the sheets collection. So we'll talk about when you should process them separately and when you should process them together will also discuss the contrast between crazy standalone pantries verses in Betty Charge. And, of course, we'll get plenty of practice on working with Star Ranges. Homing in on specific days arranges copy and data ranges between worksheets, modifying the values in cells and, of course, entering formulas themselves. Using 58. Also make sure you get plenty of practice on cold recognition, and it's very important, of course, comes with a code recognition template. You based your code into the templates and then just get through line by line. They control that you understand what each line of code. So if you're looking for an excel Phoebe A cools, which doesn't just ask you to learn things. Tariff fashion that gives you a chance to really open the coding right and know exactly what it's doing then. Hyper dish and video range in Excel BB A. Program him if exactly the course you're looking for. 2. 1.2. Excel VBA overview: in this course will be covering all the basics that you'll need to actually start right in your own Mac Rose and will focus on all of the components that you'll need to start program it. So let's just have a look at what some of these components are. So, first of all, we have the Excel Object model. This, basically is the programmatic equivalent off the Excel interface. So the top of the tree we have excel itself. We have workbooks, workbook, sheets, etcetera in order to program in V. B. A. You need to understand this object mortal will obviously be going over it in great detail. V. B. A itself provides a certain amount of the code that you're right Teoh create your applications and so will also be focusing on the syntax that's used, which is made available by visual basic itself. So to begin with, we'll start by making this developer tab visible. And then we look at the macro recorder, how to record your own Mac Rose and then haven't recorded a macro will look it going into visual basic individual basic editor and actually modifying your code 3. 1.3. The Developer Tab: As with all areas off like yourself, take cell the features related to the B A R. All arranged on the same tab. This is the general theme in all the Microsoft Office programs at Home Tab. We have all the popular features that uses 10 to require frequently in page layout. We have everything related to preparing a document for print. In view. We have all the options related to heart documents a displayed on screen and so forth. So there's a special tab called the Developer Tab, which is used for everything related to VB, a Mac rose. However, unlike the other tabs, the developer tab isn't initially displayed when Excel is installed in order to make it visible, you simply need to go to the file menu into his options. Ben. Here you'll see a series of Excel options arranged in categories which are displayed on the left. You need to go to the customize ribbon category, and then here on the right hand side, you'll see a section labeled Main Tabs. And at the bottom we have Developer, which is object simply clicking the track box to activate it. When you look OK, we'll have an extra tab at the end of the river called Developer. Most of the options on this tab related to V. B A. There is one section at the end here, which relates to working with XML. And then there's another section here for working with Adan's. But the options in the code group and the control group are all related to V B A. So, for example, if we click on the very first Laker labeled Visual Basic, this launch is what's called the visual basic editor. Let me just close that to come out of it. And then here we got any macros already recorded when we click will see a list of all the macros we can let run them. And again, here we can click to record a macro, and here we can click to specify our macro security settings. So we'll be discussing all of these features in the upcoming videos. But that's step one, making the developer tab visible so that you can access all of excels hit VB, a features 4. 1.4. Macro security: any file format, which supports execute herbal code, presents a security risk. The Users and Excel workbooks, which contained Mac Rose, fall into this category. For this reason, whenever you open a file that contains a macro, Excel will initially display a security warning. However, as a developer, you'll want to be able to switch this option off when working with your own projects. Let's look at how this is done. Let's go into the training folder and inside 01 getting started. Let's have no zero for macro security. You'll notice that it has the file extension dot XLs em as opposed to dot XLs X, which is the normal file extension for Excel files. And this indicates that either it already contains a macro, or at least it has the potential for containment macros. When I double click to open it, Excel displays a security warning indicating that Macron's have been disabled and it's up to me to determine whether I want to override that and enable the content in this file. I can vouch for the fact that there is no malicious code within our training folder, So how can we tell Excel not to display messages when we opened files within that folder. For the moment, let's close the file and then in the developer tab on the left hand side, you'll see we have the option. Macro security. When we click on this, this brings up excels. Trust center settings. These settings are not isolated. They simply part of excels. Normal Excel options. Clicking on macro security on the developer tab is the quickest way to get to trust center settings. But let me just show you the other way if we go across the file and options. This is where we have all of excels standard settings. And to get to the trust center settings, we click on trust Center, the very last category on the left and then on the right hand side, this button trust center settings brings up the window the dialogue that we were looking at just a moment ago so you can access the settings using whichever method happens to be the most convenient for you in trust center settings. The simplest thing you can do, perhaps, is to enable all Mac rose. But as you can see here, this is not recommended because you then don't have any protection at all. Excel will never warn you when you open a file containing a macro. So what's better is to use this feature on the left here called trusted Locations. When we click on this, we have the facility toe. Add as many locations as we want, so I'm going to add our training fold on location. In my case, that's on the desktop to our trusted locations. To do this, click on add new location and then hit the browse button and navigate to the required location. So I'll scroll down to the XlV, be a level one folder and then double click to open it. Then click OK before clicking. OK to confirm this addition, it's very useful to switch on the option. Sub folders of this location are also trusted. So everything inside the specified folder and all of its sub folders can now be opened without Excel displaying the security wardens. So now we click OK, and here we can see the new item added to our trusted locations, and I'll click OK, Ken and the third time to finish. Let's put it to the test so I'll open that same file in 01 getting started. I'm opening zero for macro security. Hello, Grant Gamble, please to meet you. I am a security risk and R V B. A code is executed and provided you have speakers on. Do you have your volume turned up? You should have heard a message which simply says hello. Followed by your user name followed by I am a security risk. And if I go into developer Tab, click on visual Basic Here, you can see the code that is actually doing this. If you don't see this code, by the way, simply double click on the icon marked this workbook within 04 Macro security. And this is an example of code which executes automatically when a workbook is open. And, of course, we'll be discussing these techniques in detail during the course. For the moment, I'm just gonna close. So the purpose of this video is just to show you how you can tell excel not to display warning messages when you're working with your own VB a projects 5. 1.5. The Visual Basic Editor and Project Explorer: the visual basic editor is a mini application, which is built into the main Microsoft Office products. Unlike the products themselves, however, the visual basic editor hasn't changed for many releases. It still has a fairly old school look to it to access the visual basic editor in the developer tab, Click on the visual basic button when you hover over the button. Excel also reminds you of the keyboard shortcut, which is old F 11. As you can see, the visual basic editor doesn't have a ribbon. Instead, it has the pre 2007 menus, tool bars and floated windows. Let's begin by just closing all these floating window. So if you're following along, just close down any windows that you have open. And if you think back to Excel 2003 where do floated windows and tool bars live? They live under the view menu just to get a view. You'll see here we have two bars, and here we have all our floated windows. The first window that we should look at is the Project Explorer. This is the key window, which gives you access to VB, a code. A project is basically a container for VB, a code V B. A visual basic for applications is the program in language, which is used to automate the bike Result office suite, and it could be placed in several different locations. The Project Explorer gives you access to all those locations. 6. 1.6. Excel objects and event code: the first location in which code could be inserted. He's into what it turned Microsoft Excel objects and you'll see in every project. This folder is always there, and inside the folder you'll see icons, which represent both the workbook and all the sheets in the workbook. So at this point in time, I have, ah, workbook that contains a single sheet. If I had 10 sheets, I would have 10 icons. One icon represented each sheet and to place code, which controls that sheet or which controls the workbook as a whole, I simply double click on the appropriate icon. So if I double click on sheet one, a code window appears. And if I double click on this workbook, another code window appears under the window menu. The various code windows are displayed. I can choose a code window to bring it to the front. If you've come across the shortcut, control tad to switch between all of the different windows that you have open in Excel. This also works with code windows in allows you to switch between all the different code Windows Best disclosed these two and goes down for the moment. Controlled double used a short cut if you like to use that method code that's placed inside Excel objects is what's called event code. That is to say that the code executes automatically when a certain event takes place within that object. So in the case of Sheet one, let me just double click to bring up the sheet one code window to write code based on an event. I go to what's called the object drop down menu, and here I choose the object in question, which in this case, is the worksheet. This then causes Excel to automatically create a sub procedure, some procedure, simply a chunk of code. Anything that I type between the opening and closing lines of the sub procedure will be executed when the default event takes place. And the default event for worksheets is whenever the new selection is made. So if you switch from cell a one to sell, be one that will trigger whatever code is written within this sub procedure, that's the default event. Whatever the right hand side in the procedure drop down. We have access to other events against which we can code so we can write code, which will be triggered automatically whenever any of these events take place within a worksheet. For the moment, I'm just going to stay with the default sub procedure, and I'm going to write a very simple line of code using the function message box. So here I'm just going to type MSG Box. That's the name of the function space and then little type of message. Since the messages text, it needs to go double quotes. So here's my message. So any time within sheet one that the selection changes up will pop my message box. I can now switch back to excel. And if I click on a new cell, up comes by annoying message. If I use the arrows on my keyboard again because this election is still changing upcoming my message. Let's Pressel 10 11 to go back to the code window and let's delete this annoying piece of code. The same applies to the workbook itself. Let's close the sheet window. Double click on this workbook to open up the workbook code window. So we followed the same procedure from the first drop down, the object dropped down, which is the object which in this case is the workbook and Excel creates the defaults of procedure. This time, the default event is the opening of the workbook. Let's say that we're interested in writing some code, which will execute whenever someone attempts to print the workbook. So what we do this time it's big up to the procedure dropped out on the right, and we find the print event procedure. You'll notice that it has a special name. It's before print rather than simply print whenever the word before occurs with a sub procedure. This indicates that the event itself could be canceled. So here's our new sub procedure. We no longer need the open one, so I'm just going to highlight it. Deleted. So any code that I enter here will be triggered whenever the user attempts to print the workbook. You'll notice I use the term attempt to, because whenever a sub procedure has the word before, then, this implies that you can actually block the event. You can stop them from doing that particular action. So this case, we can stop them from print in the workbook. Let's say that our company has a policy of only print in a document when it's absolutely necessary, and in the case of this document, it's not considered absolutely necessary that it's printed. So we want to do two things. We want to display a message to say. This document is not meant to be printed saving as a PdF instead, and we then want to block the print command. So first of all, the message be seen Message box and Mystery Box and then in quotes, all simply put a quick message and then to block the print command. I use this special parameter, which is called Cancel, and I simply set cancel to true and that will stop the print event from actually happening . So cancel equals true, and that should do the trick. So let's test it. I'm going to go back to Excel old F 11 if you want to use the keyboard. And I suppose we should put something in our work season the moment it's completely black. So it's got something in it, and we go to file on Prince. When I click on the print button, up comes my message on the print event is blocked. Let's go back to visual basic and again, let's delete our example code 7. 1.7. Writing a macro in the VBE: as well as place in code within the Excel objects. You could also create Mac Rose on macros have placed in a special container called a module . So to create a model, I get insert body. I can also right click in the project and choose insert model. From there and up comes another code window, which relates to module one. Close it down to bring it back up again. I simply double click on about one. So you've seen to code windows. We've seen the product explore on the left here on. Then, when you open an object, we have a code window, which enables us to place code. Inside that object, we go to the view menu. Let's bring up the properties window. Let me just resize it so that we can see both the properties on the Project Explorer. Basically, this will give you properties related to the object you click on. So if I click on sheet one, I see properties related to that. This workbook lemon, I click on the module that's just a single property, which is the name of the module. So let me just cool it. Hello World and press enter and that renames the module, so the properties window basically allows you to change the properties of whatever you select. It's most frequent use is when you work in with user forms, and these are special dialogue windows, which you can customize and where you can put things like pop up menus. Check boxes not dissimilar to a form that you might create on a website. So when you're working with user forms, used the properties window all the time. When you're working with other objects, you use it less frequently. Okay, so we've got Project Explorer code window on properties window and to create a macro, which, if you remember, is what models of four modules are the container for Mac Rose. To create a macro, we can either just type it or we could go to insert procedure, enter name for our macron, so I'm going to use the same name again. No space is allowed for macron names, and then the type will be sub and the scope will be public. Private supper stages are used where you want to have a chunk of code that's used by other Mac Rose. To create a macro, you create a public sub procedure What we click OK, Excel puts in the necessary code and we could obviously simply have type this code ourselves. So here we're gonna use our message box again and to run the code, we simply make sure that the cursor is in the necessary sub procedure and we can click on the run button which is displayed on the main toolbar. You can also run a macro from the workbook itself in the developer tab. Click on back roads on the left here, and then you'll see a list of all your back rows. So hello, World is the name of the model dot and then hello World is also the name of the macro. When I click on run, I get the same result. 8. 1.8. Using the Immediate window: and let's just have a look. At one final window, I go to the view menu. Andi, bring up the immediate window. The immediate window is used to execute a line of code. If I just copy this line and paste in the immediate window when I present, it runs that line of code. It's also very good for cuisine. Excel to just find out little bits of code that you might need when you're right in Macron's or when you're coded in general. So, for example, let's say I want to use a background Phil. Then I want to find out the actual color code that I should use. If I want to turn a cell that specific to query exile and return a result which you can then use, use a question mark in the immediate window. Andi, Obviously, you'll need to know some V B A in order to know what type, but this is the whole purpose of the course, so you'll be learning lots of VB as we go through. But for the moment, I'll just tell you the code that you need to type, which will be active Cell don interior don't color and them. In my presence, I get a color code, which I can then use, And if I want to actually change your cell, I can just copy that line. Onda se equals 192 So whatever my active cell is, when I presenter, it will change to that color. So if I click on this cell here when they present, because there's no question Mark, it's now going to actually execute that line of code. It's been like, go back to my active cell. It's changed that cover. The immediate window has a number of functions. It's also used quite a lot of debugging, so we'll keep coming back to it as we go through the course. But for the moment is to show you that it could be used to execute a single line of code, either for discovering elements that you could menus in your code in or simply to test the line of code just to double check that it's actually producing the result that you need. I can't just gonna clear this out and close it down. So that was the immediate window. Then we've got the code window, and here we've got the Project Explorer and the Properties Window. They're all available under the view menu 9. 1.9. Saving a macro-enabled workbook: when your individual basic entity will notice that you have a slave book command, so there's no way of saving the code as a separate entity. The code is simply part of the workbook, and when you click on Save, it will always save the workbook. In the case of VB A. Remember that you always need to specify that your Excel workbook is macro enabled, so you'll be saving it as not Excel sm rather than Doc XLs. Thanks apart from that, it's very much business as usual. So I'm just gonna go ahead and save this on my desktop. Okay, so there you have it. That's a quick intro to the visual basic editor, the B B E on dime. Not going to go through all of the features at this point because, as we write, our code will be encounter in these features in the appropriate context. But, as you can see has a slightly different interface to the modern office applications, and you can just flick between the two environments, either by president all 10 11 or by clicking on visual basic 10. 1.10. The correct way to use the macro recorder: The back room recorder is a very useful built in facility, which allows you to generate code not by writing it yourself but by simply starting a recorder and performing a series of steps as you before steps. Excel generates the equivalent Phoebe a code for you, even if you are an experienced programmer. The recorder is a useful facility, but what it's not is a substitute for write in code. The code generated by the macro recorder can be used in your solutions, but not as is, you always need to modify the code to make it more flexible and more suitable your own applications. In this video, we'll focus on the best and most efficient way of using the macro recorder. Most books and tutorials will tell you that to use the macro recorder, you simply start recorded before the series of steps and then stopped recording. This isn't really good advice for beginners, because when you'll learn in V B A. What you mean the interested in is understanding how the code works. And in the case of Macros, which piece of code does what? So my recommendation is that while you learn in B B, a When using the macro recorder, you should make use of a facility called a comment. Comments are descriptive non execute herbal lines, which have found in all programming languages, in which he used to explain how the code works. What's useful is that while you're recording a macro, you could actually insert comments prior to execute in each step. That's the technique that we're going to use in this example. So let's begin by looking at the finished product in the XlV Be a Level one training folder . And in the first sub folder getting started, it's open up 10 Macro recorder finished so that we can look at the finished application that we're going to build using the macro recorder. It's a very simple example, which enables the user to click on any column heading and have the data sorted by that column heading. You'll also notice that the column by which we've currently got the data sorted is highlighted just to emphasize how the data is currently sorted. And the way that the solution works is that we've placed a series of invisible buttons on top of the column headings. If I write big money, you'll see the next highlights it for me. It also gives me the right click menu to get rid of the right click menu. I simply press escape, and I've got one of these on each of my columns, so that's right. Click on the 2nd 1 Press Escape. You'll notice that in each case, they're not full with. That's a small cap to the right of each of the Buttner's. I've done this so that the user can still go into data and spiritually filtering half the filthy buttons workers Normally, whereas if we had our invisible buttons, covering the entire head in the filter mechanism wouldn't work properly and naturally. What we've done is to attach a macro to each of these bottoms. So when I right click, if I look at a sign macro, you'll see that a macro has been assigned to each of the bottoms. So that's how the finished solution will work. Let's now close this deal. You don't need to save and let's open the start version. So in the training folder, number 10 macro recorder begin, and that's exactly the same file. But with no back rows and no hidden mountains surgery called a macro weaker across the developer and click on the record Makary. But Excel also conveniently has a button in the bottom left, which you can access that does the same thing a dialog boxes then display but asks you for a name for your macro. And no spaces are allowed in the name you can just use alphanumeric characters on Underscores. So let's call it column become sort. And as I've said, if you find the names difficult to read and you prefer, you can just place an underscore like so just to separate the words in this case, I'm gonna lose the underscores into sleeve it like so then you specify where the macro is gonna be stored. If it's an important macro that you'll be using lots of times in lots of workbooks, you can save it in what's called the personal macro workbook, and it will then be permanently accessible within your copy of Excel. In this example, we just want to save it in this workbook only. So which is this workbook, and then click OK, And then, as I said, what we really want to do is to describe and doc event each of the steps that were performing. So my recommendation is before you do anything going to the visual basic editor, either by president all 7 11 or by clicking on the visual basic button. And here you'll find that Excel has created a module. Sir, in the Models folder, you'll see it's created module one. Let me double click on module one. Up comes the code window. We can see our blank macron. So there's the name we typed in, and it puts in a few comments. As I've said, a comment is a non execute herbal line of code. It's just there for the reference of the programmer and for anyone who's looking at the code in V B. A comments are created simply by starting the line with an apostrophe, so you'll see that all these lines which have been automatically inserted, start with an apostrophe. Since we don't need them, we can delete them, and we can put in our own comment. So the first step that we go to before is to sort the data. Obviously, this is a generic step, which will be performed regardless of which color person clicks on. During this course, I'll be put in all my comments in upper case letters. That's purely the convention that I'm gonna be using so that comets stand out. As you're looking at this green, you can immediately see which lines of comments by the fact that they're all in uppercase. So I'm going to lightly sort data. That's our first step. That's it. Now we go back to excellent. Before that step, we compress all day every level. I'm just gonna do it this slow way and move across to the data tab. Click on the custom sort button doesn't really matter which column I sought by because this will be the generic step. Very good about this, but I'll choose the first column, which is the last thing I'm like, OK and Excel before is the sort. Let's not go back into code, and we can see the cud that Excel has generated for that step. So all of these lines of code relate to the step that I've just performed, which is to sort my data. The next thing I'd like to do is to highlight the column by which the data is currently sorted by changing the background fill of that call him only but he find to do this. I first of all have to remove any existing background, Phil. Otherwise, as the user clicks on different columns, all the columns will eventually have that background film. So the next step is to remove the background Phil from all the data cells. Again, I'm gonna put a comment in to that effect. So type an apostrophe. We live background filled data cells by data cells, of course. I mean, all cells exclude in the head in cells, so starting from road to so it's fine. I could leave my cursory in that position. I don't need to create a new blank line moved back to excel. Remember, all to 11 if you want to use a short cut and then I'm going to select ALS the data. I'm going to do this using the keyboard, so impressing control home, which takes me to sell a one that might press the down arrow to move to sell me too. So those two steps control home followed by the down arrow well guaranteed that I'm in cell a two. That's the first data cell. Then to select all the way to the right, I hold out control shift in press the right arrow and finally to select all the way to the bottom. I hold down control shift impressed the down arrow. I've now got older data self selected and to remove the background. Phil. I just go to home and shoes feel color. No, Phil, let's move into the visual basic editor to see the code that's been generated. So there you can see Excel has faithfully reproduced on my steps, starting with a two expending all the way to the right, extending along the way to the bottom and then flying the background filled. It especially find the final step I want to record is to apply a background Phil only to the column by which we currently sorted on. All I really want to do is to generate some generic code, which I can lend customize to make inflexible because always I don't know what to call him , uh, users going to click on. So as far as recording, I'm going to use column pay. But that's not significant. I could use any of the six columns that contain data, so come in can say fly background, Phil to sort column and then finally get back to it. So and before so I'm going to use calling A is my sort code services of recording. So again, I just want the data cells. So I'm gonna use control home to go to a one, press the down arrow to go to a to and this time I just need control shift down to select all the way to the bottom. They're like a cross to home and specify the background feel that I want to use. So I'm gonna use that very light blue accent one and that's my final step. So I can now stop recorded either by using this convenient button, which appears whenever you started to record in the bottom left. Or, of course, I can also go to the developer tab and there are gonna stop recording. But when I go back to visual basic, they're the three chance of code that I've generated that by inserting comments, it's much easier to see which bit of code does what. So just bear that in mind. If you are using the macro recorder, make sure that priety before we need step, you just going to the code window and you put a comment on after you've recorded a back room. You'll then have neatly labelled sections that tell you what each bit of code does. So that's it for recording the macro. It's not usable. This one of the things about the macro recorder. It doesn't generate usable codes. You still have to modify. So this is just the first step. In the next video, we'll look at modifying the code that's been recorded and generated for us so that it becomes more flexible. 11. 1.11. Analysing a recorded macro: having recorded a macro, your next step is usually to try and make sense of all of the lines of code that the macro recorder has generated for you in the training folder. 01 Getting Started. It's not number 11 analyzing a recorded macro, and this workbook simply contains the macro, which I recorded in the previous video. So when we go to developer Visual Basic, if you open up the Models folder, you'll find Module one. And when you open up that you'll see the code that was generated as I recorded that macron . So in this example, we want to just look at these lines of code and think back to this steps that we performed in order to generate that one. Nice way of doing this is to have a split screen where we have the code on the right on the Work street on the left. So I'm going to close down thes two windows, the product explorer on the properties window, so that my covered window now occupies all of the available space. And then I'm going to anchor my code window on the right by just picking up the window, dragging it to the right. And then I'll anchor the worksheet on the left by just picking up the worksheet window, dragging it all the way to the left. So I'm going to make the right window wider at the left window of its mullah. So the first thing we did was to sort the data. So back in Excel. Let's go to the data tab because I've reduced the with of my window. Everything is kind of squished up, so I need sort and filter sort. So here's the sort dialog and let's see if we can compare the code to what is actually going on in the salt window. So the first line ends with don't clear, and this basically is a line which clears the parameters. I didn't actually record this line. It's just intelligent behavior on the part of Excel. If there are existent criteria in the sort window, simply add into the May give unpredictable results, so it's best to clear the existed criteria before starting. That's what that first line will do that the second line, which spills over onto several lines. By the way, um, if I just go back into the code window and highlight this section here. Space underscore, which you'll see on several of the lines, is the technique that's used where you want to split a very long line onto two or more lines. So on that long line, we, first of all, specify which column we go to sort by. So in the interface, we actually choose the name of the column, but in the code you'll see it actually puts the cell reference off the sort column. So it goes from a to all the way to the bottom of column A at least the bottom of the data in colony. And then the next parameter is equivalent to this one, the sort on and you can see we have Excel sort on values, which is the equivalent of choosing values from this drop down. Then the next parameter is the order, which is, of course, eight is at Excel ascended. Then we move on to the actual sort itself. So all of this really is the ad level and then specifying what's in each of those three drop downs. Then we move on to the sort itself, First of all the range. Secondly, whether the data has headers Yes, my data has had its So that check boxes equivalent to dot header Yes, By the way, this structure with and end with you'll see very often when used a macro recorder on what it means, basically, is that all the lines between the within the end with are effectively preceded by this piece of code. So we've got an identify for the work streets. This is how the expenses work Street that you can see here is being identified in VB a code . And then we have the function or method dot sort so sort in the active worksheet using these parameters. So all of these lines that actually begin with a dot are effectively preceded by this statement, and it saves the statement from being repeatedly evaluated. And it saves us from type in the statement. Lots of times. Let's just bring this sort when they're back up. So we have the fact that my data has headers. Then the match case. We confined by going into options, and here we can see the case sensitive. It's switched off, so that case equals false orientation is top to bottom. The sort method is the only thing we won't see Excel always puts that in when you use the recorder only refers to Chinese characters, so that was the sort that we did. Then we moved on to select the entire sheet by first of all, going to sell eight to. And then I typed control shift, right arrow. See here in the coat Excel, too, right? And then I tied to control. Shift down, Arrow Exel down. Finally, I removed the background. Phil and Excel. None is something that's immediately recognizable to achieve that, that I highlighted just the sort column. And again I used control home down Arrow to start on Sally, too. And then I pressed Control Shift Down Arrow to select the entire column all the way to the bottom. Again, we've got the don't end excel down syntax as a result of fat. And finally, I applied blue Light Blue Phil to that selection. So you'll see. There's quite a lot that's immediately recognizable on which we can identify as corresponding to the steps that we performed when recording the macro 12. 1.12. Improving a recorded macro: Once you started to make sense of the code generated by the macro recorder, you almost certainly want to modify that code and make it a bit more suitable for your particular applications. So in our training folder, we go into zero won't get it started, and that's open up. Number 12. Improve it recorded background. What we go to visual basic. In the Modules folder, you'll find Model one. How many double click To open it, you'll see the familiar code, which is simply the code that I recorded a couple of videos back by using the macro recorder the areas. But you're what to change a typically those which result from the fact that Excel has been very specific in creating the code. When you're recording a macro, inevitably, in order to change a certain selection of cells, you have to physically select that range of cells. Otherwise, Excel has no way of knowing which sells you want to modify. So the end result is Excel records references to very specific cells in this example, we can see here, but we have a reference to a specific work feet. The Expenses worksheet, which is the worksheet that contains will our data. But this means that if we want to place this code in a workbook that didn't have an expenses work Street, the code would immediately break. Similarly, when we come to specify the column to sort by, we have a very specific range of cells being referred to. If we try and run the code from a worksheet that has a different number of rows or columns , it's not going to work. And again here we're always sort in. By Colin A. We want to be able to sort by whichever column the user specifies, so we'll need to make visible, flexible again here when we come to specify the sort range we really want. The sort range to be all the cells that contain data. No, a specific range of cells starting from a one is fine, but we don't necessarily want to end on column F on. We don't want to necessarily stop on rose 6601. So let's begin by addressing the problem off a specific worksheet reference. What I'm going to do is to replace this reference with a more general one, and the syntax I'm going to use it's simply the word active sheet. This means whatever sheet the users currently working on. Basically, if you're clicking on buttons, this means that this seat that contains the data must be the active sheet. Otherwise, you wouldn't be able to interact with it now. Obviously, it takes experience to know that that's the correct syntax. But for the moment, we're just gonna do this on a need to know basis on then. Obviously, as you go through the course, you're going to become more and more familiar with this type of syntax. So great to copy and use the replace command. As in most Microsoft environments, a shortcut for replaces control H. Alternatively, you'll find it in the edit menu, and I'm going to paste into the find What box? So that's what we want to replace. Had we replace it with active sheets and I think, replace all and excel fines three occurrences. That's one problem sold. We can now use this on any work feet, and we won't have a problem next. Let's address the problem off these specific ranges. What will deal with in this video is the fact that we're stopping on a specific range so we don't just want to stop on a specific wrote. We want to go all the way to the bottom and similarly here. We don't want to stop on column if we want to go all the way to the right and then all the way to the bottom. Now, fortunately, we've actually got code that does that. If you remember, when I recorded the code, I pressed control shift right to give it all the way to the right and then control sit down to go all the way to the bottom. And similarly, here again, I started on a to press control shift down to go all the way to the bottom so we can use that same technique here on Dhere to make sure that we're not stopping on a specific road or in this case, we don't stop it on a specific color. But before we try and replicate this technique, let's address another problem. The fact that were selected the cells before applying a pattern to the When you're recording a back row, you have no choice but to do this. But when you're right in V B A, it's unnecessary and it's undesirable. because it slows down your code. So what we could do in V B A is to get rid of all these select statements and simply leave a reference which specifies the cells that we want to change. So let's start from the top. The first thing is with select him a to and then were selected all the way to the bottom, starting from a to so where we have the word selection since on the previous line we've selected a to selection really means a to. So all we have to do is to copy the statement range eight to and where we have selection on the following line. We can replace it with range eight to This means that this line is now obsolete and we can get rid of it to select the line of code. By the way, he used the same technique that you do in Microsoft Word. Position the cursor just to the left of the line until you haven't arrow pointing to the right as opposed to this one, which does something completely different. So it's gonna be pointed to the right. Many can either click once to select a line or click and drag to select successive lines. So here I'm just gonna click on this line and then press delete. The next change I'm gonna make requires a bit of familiarity with V B A. And that, basically, is to chain these two statements. So there's my control shift, right, selected all the way to the right, and then there's my control ship down. She likes it all the way to the bottom at what I can do is to chain these two statements so they have one after the other so I can get rid of all of this code so that I am saying Go all the way to the right, handling gold the way to the bottom because I've just got rid of one line. I've ended up with an extra close bracket that doesn't make any sense anymore, so we'll have to delete that before I proceed. When I made away from the line, I quit his double check minutes. Good, because if it weren't it would turn red. And then the final thing I can do is what I did. First of all, this statement refers to a series of cells, In other words, starting from May to going all the way to the right and all the way to the bottom of the data, having referred to that range of cells and then select it and it does something to this election. So what I can do is I can select thes statement that refers to the cells and replace the word selection with that statement. This means that the line that does the selection can now be deleted. And I made a slight error here because I have range. Then I've got a statement, and then I closed my range at this point. So the range statement ends here. It actually needs to end after the excel down so that the range picks up. Everything started from May to going all the way to the right away, down, So that close bracket that I took out was the wrong one. This is the one that I really need to take out. So I'm just gonna get rid of this one. And what a close bracket at the end here. So I got my outer range and then inside it, I have all of this. So that's corrected this one on what I can do is to copy this statement, and then when I'm doing my sorts, here's my range statement for doing this sort. Instead of having this very specific range, I can now paste the statement that I used earlier. The only difference, of course, is that I don't want to start from a to I want to start from the first cell a one. So I changed my twos to a once. So during this obviously takes a bit of experience at this stage. All I want to do is to point out changes that you make to your code the fact that you don't have to select elements. You can't just refer to them, and your code will work much faster. Obviously, as we go through, the code will have plenty of practice on working with these intricacies. So let's now do the same thing down here. We're working with selection, having selected range A to so we could simply replace the word selection with rain J two. That means that this line could be deleted and then finally we have a statement which refers to all the cells starting from a too old way to the bottom. We then select the cells referred to do something with that selection. So all we need to do here is to copy thes statement that makes that cell reference and replace the word selection with that statement. This means that this line can now be related. So now we have a statement which can replace this range reference to specific cells, starting on a to an end in on a specific road so we can replace it again. We go inside the range statement. We can't be everything inside that starting on a two and ended on whatever is currently the last cell containing data. So we copy that and replace this very specific reference with that more flexible statement . So we've made some improvements here, and as I said, the only thing that remains is the fact that we're always referring to Call him a when we sought. But when we change the background of the sort column, so we'll address that in the next video, we'll be attacked on macros. Two buttons 13. 1.13. Assigning a macro to a button: The final thing we want to do is to attach our macro to a series of buttons, and what will need to do is to modify the macro so that the button that you click on determines the column by which we sought. So let's go into our 01 getting started folder and no number 13 assigning the macro to a button. And this is basically the modified version of our back row that we did in the last video. So when we go in, here's our code with all the changes that we've made. So what we want to do now is to create a series of buttons. So I'm just gonna switch on filter mode. So we've got our filled arrows. We're gonna make sure that the buttons are just to the left of those arrows in Excel. Anything could be a button. So I'm just gonna go into shapes and choose a rectangle as the bottom. So if you're designing solutions and you've got really attractive PNG buttons on from your website, you could just use those as buttons in Excel. Anything you like in Excel could be a button, so I'll just draw the 1st 1 and then I'll copy this for all the others when paste in its useful, if you click in the soul into which you want to paste and then when you paste excel pastes within that cell and then it's just gonna be a case of resize in the mall. So this what needs to be significantly bigger on this one needs to be significantly smaller . And we just modifying the height of a couple of these so I'll just, uh, resized them. And then the next thing we want to do is to name each of the buttons. Name in a button is very similar to name in a range of Selves, but you have a bit more flexibility in that the name can contain spaces, so you'll see that the default name here is rectangle one with this face. So what I'm gonna do is just to call them, call him a or just c o. L will be enough column B and so forth. That way, if we have ah, Work Street, that has a lot of columns that we end up using two letters and, uh, that will still work. So coal with a space at a and then very important, you have to press enter, Otherwise the name doesn't register. I'm just gonna copy the first part of the names pasted in each time. So that's B and, uh see And, uh, tea and, uh, E and, uh and finally f and this double check that the name civil stuck because it's very easy when you name in these for it not to register, So they're they're all named on DA. The next thing I'll do while they're nice and easy to select, is to attach the macro because basically their law have the same macro attached to them. But the name is what's going to distinguish them. So to select them all having clicked on the first, I hold out shift click on each of the others that, like, right click on any of them Jews assigned backroom. Yes, our macro. We click on that like Okay, so they now will have the same macro assigned to them. We can now make them invisible while they still highlighted all this right click and choose format object. And then I set the Phil transparency 200% on the line. Transparency 200%. So they completely transparent and they're still there. When I click on them, they will run the macro. And of course, at the moment they are always sort in by column A because that's what our macro stipulates . So the next step is to go into the developer tab and remedy this. So what we want to do is to substitute that letter A with the letter that the user has clicked on the letter of the column the users clicked on. And to do this, we're going to use a variable will be discussing variables in the next section of this series, but for the moment, let me just explain what a variable is. It's very much like a cell reference in an Excel formula. So, for example, if in an Excel formula we have equals si tu minus D to see to is really a way of saying whatever value is in cell C two minus whatever value is in cell D to we don't really care what the value is, we just specify its location. Variables achieve something very similar. You name your variable amend Accel assigns of space in memory and associates that name with it. You can then place anything that you need to refer to in your code inside that variable, and you can manipulate it just as you do with cell references. So let's look at how you declare a variable. Just gonna put a comment into that effect. But then we'll look at how you assign a value to a variable, so to declare a variable used the key word dim, which is short the dimension, and then you create a name for the variable. It's very useful if the name that you create gives a clue as to the type of data that the variable will contain. In this case are Variable is gonna work with text and text in V B. A is referred to as a string. So for that reason, I'm gonna prefix the name with str. And then since the variable is meant to specify the column, I'll call it str Column. And then the final thing I need to do is to specify for the purposes of V B A to specify the data type, and I do that with the keyword as and as we said, it's a straight. So we now have this variable available on Dhere. We want to assign a value to it, and the value that we want to assign is the name of the bottom that they click on. So whatever name is associated with that button, we want to end up in our variable or, to be more specific, we want the letter at the end of the name to end up in our variable. We don't really need this part to assign a value to a variable. You put the optional keyword, let followed by the name of the variable str khalib equal to, and you then put a statement which generates the specific type of data the variable is meant to contain. So we need to put a statement which will produce or refer to the name of the bottom that the users clicked off. And there's a special statement in V B A, which does that application, not caller. So that statement will pick up the entire name, and then we can use VB, a function which is very similar to a function found in Excel. It's the mid function. You may well have used it in Excel. It extracts a string from within another string, so this is we know application dot caller. This will produce the entire name as in cold. Be, for example, we want to extract the be part. So that started from character. Five, isn't it? So we could see CEO. Well, that's three, then the spaces character for and the letter starts at character. Five. If it's ah, large worksheet. So you have two letters in the column reference. Then we want to pick up both of them, but we want to start on character. Five. Very easy to do with the bid function. So first of all, we put made at the start open brackets and then Excel displays a tool tip that tells us what arguments the mid function needs. So festival needs a string, which we've already provided. That's application dot caller, so we put a cover. Then it needs to know where within that string to start extracted characters. And as we've said, we start from character. Five. You'll notice that there are square brackets around the third parameter, which is the lens or the number of characters to be extracted, and that means that it's an optional parameter. If we have met it, which we will do, Then Excel simply extracts all the characters from that position until the end of this drink, and that's exactly what we wanted to do. So if we have got a large work street and we're using two letters to refer to the column, Excel will extract those of so. Having executed that line, SDR column will now contain the appropriate letter on the final step is to replace a with SD. I'll call him this technique, which is referred to his concoct A nation, works in exactly the same way in Excel worksheets. So if you've used text formulas, you may well have come across this technique. We delete the A and we prefix that quoted String with str column and we link the two components with an ampersand shift seven, which is the concoct innate operator. So saying whatever letter or letters have been extracted into str column, follow them with a to that that will produce the appropriate starting color. So I need that several war time, so I'm just gonna copy it. We need it here, of course, and then further down, when we change in the background color of a cell, we also need it here and finally here. So by using that variable populated it with the appropriate column letter or letters, we can then prefix the two, which is our starting point road to with the appropriate letter. In other words, the letter that corresponds to the column. The user has actually tricked it. So let's test that this works. Okay, I'm gonna save my changes have been just click on each of the columns. So first name, branch date, amount category, and then last name, which was our original, and they will work fine. So that concludes our little mini application. It's now working. Okay. In final video, we'll see whether our code is flexible enough to enable us to take this solution and use it on a completely different worksheet. 14. 1.14. Testing a macro on another worksheet: So we've tested that our macro works in the sheet in which it was originally recorded. What we're gonna do in this final video is to test that it works in a completely different worksheets. So we go across to our training folder. Zero wouldn't get it started. Let's first of all, openly finished one, which is the one that we finished in the last exercise. And then at the same time, let's open the one called Test. As you can see, this is a slightly different worksheet. It doesn't have as many rose, but it has got more columns. It's got one extra columns, and of course, the columns will have different names. So the first thing we want to do is to copy the macro across on. This is very easy to do. We goes a developer. Visual basic has closed down the windows to avoid confusion here, we conceded to project. So here's the project for the finished one, and he has the project for the test. One finished. One has a Models folder with model one in it, and, of course, the test one has no models to copy the model. We simply drag it from one project to the next is easy. Is that so? This now has its multiple fold up his model, one without code. That's easy. The next thing we want to do is to copy the buttons across. So I'm going to right click on the first every time you write. Like of course, the pop up menu appears. Remember, you can press escape if you want to get rid of it, and then I'm gonna hold down shift. And if I now go to the end, one on, then just right, click on each of them with the shift key held out, So I can now copy them. Switch to my test sheet on a paste, so they're going to be very difficult to work with while they're invisible. So with them selected, I'm going to right click to use format object and just remove the transparency. So I make the 0% transparent to make them more visible again so I can see them all. So this is obviously there are six of them in the original. This one obviously belongs to call F. I'm going to right click it, livid over to call a breath on, then resize it. And at this point, I can just go to data and bring up filter it. So I know what size debate them. And just remember, each time it's right click to select the then escape to get rid of that baby. So this one is for called E here. I've got little resized, a couple of liver, a bit big, but that's funny. So I'm gonna copy this one clicking column G paste resize it and, of course, rename it So that becomes coal Space G. And then remember to press center so that it registers. So the final thing is to make sure I've got the correct macron assigned, which is the macro from this workbook, not the macro from the original. So to avoid confusion, I'm gonna close the originals go across the finished close that one down. Now I'm going to hold down the shift key on just right click on each of these Teoh selectable. Then with the ball selected, I can choose a sign back row. Now I just delete what was there before. Click on the macro this workbook and click OK, then the final thing is to make them invisible again. I still have my format shape task paid open so I could just increase the transparency once more 200 to make them all invisible. And that should be it. So now I can just click on each of these columns. Let's just track the mall and you can see that it works fine. Let's just go down to the bottom and make sure that the Phil doesn't extend beyond the bottom. So that's it. So we have got a macro, which is flexible enough to be used on any worksheet. We just need the patience to copy the macro across a copy of the buttons onto the column headings and then just resize them to match the columns in that particular worksheet. So that completes this section, where we have focused mainly on using the macro recorder to generate code on men. Teoh look. A tweak in that code to make it more useful and more flexible in the next section will dive into VB a proper Andi. Start discussing some of the key elements that you need in order to be able to write your own VB a code from scratch 15. 2.1. The Excel Object Model: in this section, we're going to be discussing some of the key elements which go together to make up VB eight code, and we'll start with something which is quite an important concept. An object is a collection of code which enables you as a programmer to replicate the kind of interaction with excel that you could make a zey use of. So as a user, your first interaction is with Excel itself. And here we might get a file and choose options. You've got access to excels, general settings. So you're not working on a specific workbook. You're just set in parameters which will apply throughout your work within Excel in the Excel object model. The application object represents excel itself. Next, we have workbooks, so I can go to an existing workbook here. I'm going to go into the second section, the second folder within our training folder zero to key elements of V B A code. And then I will open up 01 the Excel object model. So now I have a workbook open. And of course, I could also create new workbooks, so file new and a blank workbook. So I now have to work books open Within Excel in the Excel Object model. The Workbooks Collection. It's a workbooks that you have open within excel. When I want to do any work in excel, I need to focus on a specific workbook. So I go from the workbooks collection to the workbook object. So within the workbooks collection, I focus on a particular workbook, and then within that workbook, I have a series of other objects available to me. Probably the most important of these objects is the worksheets collection, and that's the worksheets that I have within the workbook. So this example Workbook just has to work sheets as well as the worksheets collection has also the sheets collection. As you know as well as worksheets, you can have chart sheets. So if I right click and choose, insert and then choose a chart, I now have a perfectly meaning less chart sheet. But just to illustrate that as well as worksheets, you can't have child sheets. So the sheets collection includes both worksheets and charges, whereas, of course, the worksheets collection includes worksheets only. If I need to do some data entry, I'm going to focus on a particular worksheet. So within the sheets or worksheets collection. I now focus on a particular work feet within the worksheet. I then have an infinity of cell ranges, every available combination, and each of these possible combinations is usually referred to as a range object. Once we've homed in on a particular range within the worksheets, we were typically want to do something to them. We might want to read a value from a cell. We might want to change the format on a range of cells and so forth. All we might want to just Enderson data in a single cell. So here I might type in a quantity and then press tab to move along to the description and so forth. The entry that you put into a cell or cells is referred to as the value. So let's just retrace our steps. We start with the application, which is excel itself. We look inside the collection of workbooks which are currently open. We home in on a particular workbook, and then within that workbook, we examine the worksheets collection. We home in on a particular worksheet. We navigate to a particular range in this case, a single cell, and we then modify the value within that cell. So let's now delete this quantity. Let's now have a look at the VB a syntax for doing this programmatically. So we get across to develop, click on visual basic or simply press old F 11. I'm also going to close down the unwanted blank workbook that I created. So now I only have one project open, which makes life a little less confusing and also going to delete the charge sheet, which I didn't really need. So let's just right click and delete, so the first thing we'll need in order to create a macro is a module insert module. I'm just going to rename it just to remind you that you can Let's just call it object model pay six. Now I need the macro itself, and I can either just type the code or I can click in the code window and go to insert procedure. And let's call the procedure. Insert data allergy. Probably remember. No spaces are allowed, so there's my blank macro and let's now think about navigation. So we had application, and then the next step is to get toothy workbooks collection and specify the workbook that we want to work on we then type of dot So the dot can be viewed in this context as a drill down character. So you're saying look inside, excel and then find one of the objects inside it. In this case, we're off to work books, then in brackets weaken, type the name of the workbook. So the workbook has this long name here. However, we do have an alternative. We can also say application dot and you'll see that one of the properties one of the elements inside the application object is this workbook Andi. This syntax is used to refer to the workbook which contains the macro itself. So this is a lot more convenient for our purposes. As you type, you'll see that excel displays all of the elements that are members off the object that you're currently working on. So the dot automatically brings up all of the possible syntax that you can use at this point in time, in a very similar fashion to when your typing functions a very similar fashion to when you're typing functions within a formula. Once excel displays the one that you want, you simply press the tab key to enter it into your code. So we've now specified a workbook within this workbook. We now want to drill down inside the worksheets collection and home in on the correct work feet, so the worksheets that we're interested in is called invoice form. It's also the first feet within the workbook so we can do two things. We festival mentioned a collection, and to do that, we put dot worksheets on again. Just press tab. But Excel displays it in the list and then in parentheses. Weaken do one of two things because it's the first worksheet weaken type A one. Obviously, this could change very easily. If someone were to insert an extra worksheet in front of it, it would become worksheet to. So perhaps a better approach is to use the name invoice form on because it's text, it has to go in double quotes. Having specified the worksheet, we now want to drill down and specify the range, and the range we're interested in is sell a 15. So to specify, they sell. We use the same tax range brackets, quotes a 15 and then finally we want to change the value within that cell and the syntax for doing this is not value equals and then the value that you want to appear in the cell. If it's numeric, you can simply put it without quotes. If it's text, then obviously it needs to go inside quotes. So let's say we want the number to which will be the quantity, so that will give us the quantity and then the description. As you can see his be 15 here. We've got three months cells, so the unit price becomes E 15 as opposed to see 15. So back in our code, we can now simply copy this. Onda changed the letter so this will become a B on this will become an E. The description will be text, so it has to go in double quotes and then our price. And there we have it. So that's a very brief introduction to what's called dot syntax on. This is the syntax that you use to navigate your way through the Excel object model. Let's just test our code may control that the cursory somewhere within the macro, we just click on the run button, and then when we get back to the worksheet, we can see that we've got our three values entered. And of course, the formula then works out the result. So that's an intro to the first part of the puzzle, which is the Excel object model. In the next video, we'll take our first look at the Phoebe, a equivalent on the differences. Of course, there's no interface, which gives you access to be be A. It is purely a programmatic model. Where is obviously with Excel you have visual you, I that you can actually work with, so that's coming up next. 16. 2.2. VBA Classes and Functions: In this video, we're going to move from looking at the Excel Object model to our first look at the VB, a object model unlike the Excel Object model, which represents something that we're familiar with and with which we interact as we use Excel, the V B a object model is hidden. It resides in memory, and essentially it consists of a series of functions which are provided by the V B, a language which use a programmer couldn't use as you need them. The function that will be looking at in this video is the input box function. This enables the user to enter a value which I'll macro can then utilize in some way. So in our training folder, in the second sub folder, 02 Key Elements of V B. A code let's open up zero to V B A classes and functions and then in the develop its out. Let's click on visual Basic. This is pretty much where we left off in the last video. So here's the macro that we wrote At the moment, the macro enters three fixed values. It enters a quantity, a specific description and a specific unit price so What we want to do is to use Vehbi eh to allow the user to enter those three values. When you will learn in V B A. His mother is going straight to the help menu within the visual basic editor, you have access to something called the Object browser. To get to this, you get a view of Jews Object browser and here we have what you might call a dictionary of the V B A language. It's divided into several different libraries, so you can choose a library. Yeah, I could choose, for example, Excel. The Object browser then displays a Siris of classes, and the class is essentially a body off code, which is available to use a programmer. Each class represents a specific area of excel. So, for example, here we have a class called access title, and this, of course, relates to the chart object. When you create a chart, you could optionally attach a title to any of the axes within the chart, and the Axis title object contains all the functionality for that particular aspect of excel. So if we switch across to the V B A library, we see all the classes which are available as part of the V B A language itself, and the part that we're interested in is interaction. When we click on the interaction class, we see a series of functions on the right hand side which of the functions that are associating with this class. And here's the input box function that will be using to use this function in our code. We could use a similar pattern to the one that we saw in the last video. Instead of starting with application, which represents Excel, we start with V B A. Then a dot been the class, which is interaction, then another dot and then input box. However, you'll never actually see this syntax in any code, and the reason is that input box, as with most of the functions within V B. A, belongs to a special class called Global's. In any element which belongs to that class doesn't need to be qualified. In other words, you don't have to specify where it falls within the hierarchy within the V B, a object hierarchy, so you can simply say input box with no prefix. However, while learning Vehbi eh, using the longhand syntax is an excellent practice because it makes your code self documented and just reminds you where this function comes from, where it belongs within the V B, a hierarchy that's now closed down the object browser. And let's look at modifying our code so that instead of having these fixed values, we can have values entered by the user. But before we do that, let's modify the code on the left hand side where we referring to the worksheet so that our code could be a bit more flexible at the moment. We're referring to the tap name if for some reason, the user decides to change that tap name. So let's say the user decides. Well, actually, it's obvious that this is a form we don't really need the word form. Let's just call it invoice. This, of course, will break out code because the worksheet that were specifying has been renamed, so there is no longer a worksheet by that name within the workbook. So if I run the code, this is something that you'll see very often here. We have a standard error message subscript out of range. The sub script is the thing inside the brackets and It's saying that you specify the sub script which doesn't exist. Invoice form does not exist. V B A provides an excellent mechanism for avoiding this possibility. In the Project Explorer, you'll notice that the object represents in each of the sheets, but in your workbook, I have two names. The name in parentheses is the tap name, which is the one that I've just changed. But in addition to this, you have what's called a code lee. And this is a name that you can use in VB A. To refer to that object. And this means that if the tab named gets changed, the code name will remain the same. Therefore, your code will still work rather than leaving the default names sheet one and she to. It's usually best to come up with something more meaningful, and it's also good practice to use a prefix, a consistent prefix which will make it easy when you read in your code toe, identify wherever you're use in the code name of one of your worksheets. So I'm gonna be using the prefix S h. So I've got to change the name using the properties window. And, of course, if that's not visible. You simply go to view into his properties window to change the name of sheet one. I just click on the object and in the name box on getting site S H invoice press enter and then move onto the next one she to. And here I'll change the name to S H customers and Price Center. So to refer to its worksheet, I can simply use the word as H invoice. And I'm just gonna copy that and then replace beast Two statements as well. And we'll be using this technique throughout these videos wherever we want to refer to. Work sheets will always come up with a code name for each of the sheets that we want to manipulate and then use that code name within our code. So let's now turn our attention to input box. So, as I said, I'm going to use the longhand method which you'll never see, and we're doing this purely to be self documented. So when you read this code as someone new to Vehbi, eh, it's just going to give you a clue as to where the code belongs within the scheme of things . So I type v b a dot that I can see all of the classes, all of the V b A classes. I want interaction, so I'm type in i n t e. And when interaction comes up, I just press the tab key. Then don't I end up comes input box on one's input box is selected. I just pressed tap to insert it. I don't need to put parentheses, and in the parentheses, I put the arguments that I want to use. You'll notice that the arguments that are displayed all have square brackets with the exception of prompt, and this means that prompt is the only one that is obligatory. All of the others are optional. So for the moment, prompt YSL will put the best practice when put in parameters for your functions is to put the name of each parameter followed by colon equals. So here are quite prompt colon equals and then the promptings of pores of string. So it goes in double quotes on Let's just say, enter a quantity on close parentheses. So, of course, the next two lines will be almost identical. Soldiers copy on paste for the next two lines on, then, here I'll change quantity to description, and here I'll change it to unit Price. So now let's test our code. I'll go back to excel, and I'll just run the macro by clicking on macros in the develop attack, selecting the only macro that we've got and lichen run outcomes. My input box with the prompt that I specified, enter quantity. So they started my lance for a quantity of three. Click OK inter description office chairs and enter a unit price off 85.75 and then in Go my three Parameters. So that's our first look at use in the V B. A object model. It consists mainly of a series of very useful functions, and as I've said, you can enter these functions without a prefix. But just to make our code a little bit mawr self document in. And just to remind you where these functions be long, we're going to write them longhand so you could recognize the syntax that is part of the Excel Object model, which will start with application, not something on DA since access part of Phoebe A itself, which, of course, will start with Phoebe a dot 17. 2.3. Properties and Methods: In this video, we'll continue our discussion of objects by talking about the two types of syntax that you normally use with both Excel and VB A objects, and these are methods on properties. So let's go into our train in folder and in 02 key elements of VB. Eight code will now open 03 properties and methods, and this is just a blank workbook. But it is macro enabled, As you can see by the dot XLs M file extension on, when you go to developer Visual Basic, you'll find that there is single module and it's called M properties and methods, So just double click to open that. And here we've got a Siri's off comments. So these are the steps that will be used in in the macro that will be writing just a practice working with these two types of syntax, properties and methods. Basically, a property is an attribute that you can set or any excel object, for example, if we take the application object, which, of course, stands for Excel itself when we go to file and choose options. Most of these settings are available as properties off the application object, so for example, if I were to change the user name from Grant Gamble to G Gamble. What have effectively said is application, not user name equals G gamble. So if I click OK and get back to visual Basic, if I go to the View menu and bring up the immediate window, remember, you can type a line of code and executed straight from the immediate window without having to create a macro. Why type Application dots Outcome. All the members off the application object members are the syntax, which can be used with application and that will consist primarily off methods and properties. And, of course, it's gonna be user name. Once it comes up, I press tab equals. This is obviously a string, so it has to go in double quotes, and I'm gonna change it back to Grant Gamble on when I press enter. If I look in my options, then you can see it's been changed back to Grant Gamble. So that's an example Off property. Another simple one is the active sheet, so there's only one sheet here. If I double click on it and call it data and then present, I will have changed active sheet dot name. Suddenly, if I go back to visual basic and type in active feet, don't name Cools have that strange? It's a main when I press enter. When I get back to excel there you can see it has been changed. So these are all examples off properties. Let's see what that looks like in the object browser. So view Object browser. Make sure that you're in the Excel Library, and let's start with the application. When I click on application. On the right hand side, you'll see members of application, which, as I said, primarily means properties and methods. So first of all, we changed user name. So when I scroll down there it is on my summary at the bottom tells me that this is a string. Hence it had to go in double quotes. Then we went to the worksheet object because obviously active sheet, it's essentially a worksheet object, and we changed the name property. And as you can see, this is also a strength moving on to methods. A method is basically an action that you perform on a particular object. For example, if we working with the workbooks collection that is to say all of the workbooks, which you've currently got open in Excel we could open or add a workbook. So if I go to the file menu, choose new blank workbook. I've now added a workbook to the workbooks collection, and when I close that workbook, I then removed it from the workbooks collection. So to do the same thing in visual basic again, I'm going to use the immediate window. I can say application. But what books don't add as we can see the argument that it takes its optional. It's in square brackets, and that's to specify a template to be used with a new workbook. And obviously that's admitted. Then we simply use the default template, which is what I do. But I press enter. You'll see. I now have a new workbook. Five. In this case, I've activated it. So if I now go back and type in application, don't active. What book? Don't close When I percent workbook. Five now disappears from the workbooks collection, so let's now close the immediate window and let's go back to our code window. Remember, the shortcut for this is control tab and let's just switch back to our code. So what we want to do is to create a macro, which does all of these steps. So we begin by inserting our macro. To do that, make sure you click in the code window and choose insert procedure. If you've accidentally clicked over into the project window, for example, you'll see the procedure is great out. So it makes sure your cursor is somewhere in the code window and then just insert procedure and will type. The name creates no space report and it's a sub and it's going his public. We click OK, and of course you can just type this yourself. You don't need to use insert procedure. So now we want all of these steps performed within the macro. So to highlight all the steps, just move the cursor just to the left of the text until it changes to a narrow point in right click and hold, and then just drag down to select all those lines, move on to the selection and then drag it into our macro. So now we can practice working with objects methods on properties, so the first thing we want to do is to change the number of sheets that Excel gives you when you create a new workbook to one so that when we create on the workbook, we know we'll only have one sheet. So this set in just reminding where that lives is file options. And it's just here when credit new workbooks include this many seats. So this is obviously an excel set in. Therefore it's going to be in the application object. So as we said, this is gonna be application docked. And in fact, the setting is called sheets and new Workbook. When it comes up, we can just press tab and then we said it toe one by put in equals one. So this is obviously a property, which we've just changed. And of course it has to be numeric. Next, we want to create a new workbook. So again, I'm going to start with application because this is an excel operation. So we drill down into the workbooks collection and then put Don't add. Now we want to drill down inside that new workbook on rename the only worksheet it contains because we created the World Book, it will be the active workbook at this point in time, so inside the application object. We can use active workbook, then drill down inside it to all the work seats. And of course, if we just say work, sweet one, then we'll pick it up. It's the one and only worksheet in the workbook. And then the property were interested in changing is the name unless the school it data. So now we'll pretend that we're going to do a lot of operations, which could potentially be very slow. And for that reason we want to set the calculation mode to manual. I've put the word normal here. I meant to put manual. So if I could change that to manual and of course, we're back with Excel settings here, this is application again, and it's just calculation when I type equals. This is an example Off Excel Constance. So it's not a case of entering a string. This is basically entering one of three possible values, and they just equate to options within Excel settings. So here I choose manual, and just to remind you where that lives, if I go to file options formulas, enter the three options that we've just been looking at on leave. I switched it to manual. So let's cancel. Go back to visual Basic. The build report is the step that we won't be performing. Let's just say this consists of Ah, whole Siris of different operations, so we'll pretend that we've done all those operations. We now want to tell the user that the report is complete. So now we'll be using VB A as opposed to application V B A interaction don't message box. And here you'll see we've got one parameter, which is obligatory on all of the others are optional. They're all displayed in square brackets. So best practice is to actually put the name over the parameter, followed by colon equals and then the appropriate type of data, which, for a message box, is obviously going to be a strength. Hence it has to go in quotes. I'm going to say your report. It's complete space, close quotes and then use ampersand to concur. Katyn ate the person's name. So here we switch across to excel, which is application. Don't use the name. And if you want an exclamation mark after that, we could just put ampersand exclamation mark in quotes. Then, finally, we were going to set the calculation mode back to automatic, so application don't calculation equals Excel. Automatic. Okay, so let's test on a macro. Make sure that the cursory somewhere within the macro and take on run there's only workbook . It's got a single sheet, which has been renamed to Data Onda. We pretending that our report is complete. And as you can see, it's picked up my name from the Excel settings. So let's just click. OK, we can close down. Book six as we go through the course, will be working with properties and methods, so you'll very soon get very used to them. But that's just a quick introduction to those two key elements of V B a syntax. 18. 2.4. Data Variables: variables play a pivotal role in VB, a programming and indeed in all programming languages to have a look at how they work. That's kind of the training folder and 02 key elements of E. B. A. Let's open zero full data variables. So first of all, what is a variable? Variables are very similar to sell references. If we look at this formula, it refers to sell a 15 and then multiplies it by self E 50 so it multiplying quantity by unit price with the formula refers to a 15. A 15 effectively is a variable. We don't know what value will be in selling 50. We don't know what value will be in cell E 50 but whatever values air in those cells will be multiplied together, and this is how variables were. The only difference, of course, is that variables live purely in memory. They're not part of any worksheet, and also you define the name of the variable when defined in variable names. A good practice is to use a prefix which will indicate the type of data that you plan to put inside that variable. And here we can see the made data types that XlV be a works with. First we have string, which, of course, is for text Alfa numeric values. Then we have a couple of data types related to whole numbers. Inter GIA is suitable for smaller whole numbers up to about 32,000 on long in Didja, which is called Simply Long, is suitable for larger whole numbers up to a couple of 1,000,000,000. Then we have currency, which is used for monetary values and a couple of data types suitable for manipulating very large riel numbers containing decimals single and double double, of course, being the larger of the two. Then we have a 1,000,000,000 which can only hold two values true or false date, which is used to hold dates and times. And then finally we have the default data type, which is a variant. So if you were to declare a variable but not specify its data type, it would default variant. And when you use this type, Excel basically tries to guess the data type based on the nature off the data. Any time that you'll need to declare a variable as variant is where you'll be using techniques that will produce unknown Data times. But most of the time you'll want to explicitly specify the data. Type on best practices to use a prefix, which indicates the type of data that your variable will hold. So I haven't had a look at these data types. Let's now go across to visual basic and look at how we actually declare variables so you'll find we have a module already created. It's called M data Variables on. We've also got a blank macro called variable Basics. So let's look at a few techniques. First of all, whenever you're gonna use a variable, it's always best to declare the variable. And to do that, use the keyword dim, which is short dimension, and then you come up with a name feel variable. So what we'll be doing in this short macro is will be first of all, greeting the user, and we'll grab their name from the system. So let's create a variable called str user and to specify that it's a string variable str user isn't really enough of a clue that's purely for our reference. It doesn't tell Excel that it's a strict variable to do that used the key word as and As soon as I hit the space bar, I see a very long list, which includes not just data types but also all the different Excel objects to which I could store a reference in a variable. So variables have these dual purpose of storing data as well. A story references to excel objects. So here I just type str for string and went String appears I compressed tab to insert it, that I'm going to compose a message for the user, which again is a string. So str message As spring, after displaying the message to the user, we're going to ask them for their age. And this, of course, is a whole number. So int age as interject images go up to 32,000. So that should be enough for most people's age. And finally, we want to flatter the user. So whatever age they enter, we'll take 75% of that. Agents say, Really, you don't look a day over that figure. So for that reason, let's create in flattery as in teacher. So that's how you declare variables dim name of variable as an data time. Let's now look it, assigning a value to a variable also called populated a variable. So I'm just gonna tab this in. We could also tap in the dim statements if I highlight all these lines in press tab but that just taps them in. This is just for readability. So to populate a variable used the key word. Let if the variable contains data and set. If the variable contains of reference to an excel object on, we'll be looking at object variables in the next video. So here we're obviously dealing with data. So it's a let variable name str user in this case equal followed by any statements which evaluates to the correct data type. And of course, this time we want to string its application. Don't use the name evaluates to a string. Let me just remind you where that lives file options on whatever values in there at his application. Don't use the name application of course means excel. So now we want to assign a value to SDO message again. It's data. Let s t o message equal. So let's say hello Space news The am percent character for can cast a nation So hello! Str user exclamation mark How are you today on then we could use the speech object to actually say sdo message to the user. And here I'm dismissing a 10% soldiers needed 10% after STL users well, to speak to the user, we used application, don't speech and then don't speak. The first thing, of course, is the text text Kotal unequal str message in this case and that's what we need to say on this occasion. We run our macro. It should pick up the Excel user name of Grant Gamble and say hello to me. Hello, Grant Gamble. How are you today? That's fine. So far, so good. So now we want to move on to grab in the person's age. So let me put a new message into str message. Let's test your message equal. By the way, how old are you? And then speak STL message. So I'm simply going to copy this line and I'll do that by holding down the control key and drag in the line to a new location. So str message Haven't Bean updated? When I used this statement, it's obviously going to speak the new version of STL message. But what? I also want to do is to display a box for the user to type in their age. I don't necessarily want the spoken message to have to finish before the dialogue box appears. So to make that ham I'm going to use the second parameter, which is you can see, is optional. It's in square brackets called Speak a Sink. I think it's short for a synchronous. That means that the speech will happen simultaneously with whatever comes next. So, in other words, the message doesn't have to be spoken in its entirety before the dialogue box appears. So for that, I just put speak a sink true. And then immediately after, that's I can now say, Let I NT age equal. And then I'm gonna put the longhand v b a dot interaction don't input box and then in parentheses. I'll need a prompt, which, of course, will simply be STL message. So you got the same thing in the dialogue as is being spoken to the user. So now we've populated all our variables. So in the using variable section, what's calculates their flattery age so we can say Let's into flattery equal in age multiplied by north 0.75 on. What we can also do is to force this calculation to return an integer value into our interview variable and there, special Siris of conversion functions which are available in V B A. And I'm gonna put this longhand just a za self documenting feature so that when you look at this again, you know, it's part of V B a. So alleviate our conversion. That's the class in which this is found on. See int is the function which will convert our calculation on force it to return an insurgent value. So now we've got in the flattery in place, we can compose our final message. So this is gonna be let str message equal really ampersand into age. I m per send years old exclamation mark You don't look a day over space ampersand in flattery ampersand, exclamation mark. So there's Armitage and then finally will speak the message to them. And this time we don't really want anything else to happen in concurrently. So I'm going to use this style without the sink equals true. So basically copy that on paste it in here. Okay, so let's test it. Hello, Grant. Gamble, How are you today? by the way, How old are you? Okay. I have 100 years old, really. 100 years old. You don't look a day over 75 and there it is. That's our first look at variables. We'll be using them throughout the course in all sorts of ways. So by the end of the course, you'll be extremely used to them. And in the next video, we'll move on to look at how you use variables to store references to excel objects. 19. 2.5. Object Variables: in the last video, we looked at story data in variables. In this video, we'll move on to look at story references to excel objects in our variables. What we're going to do is to write a simple macro that looks inside this folder. Call Sales, opens up the file called Sales and then filters all the products in the range called Brain Stuff. So we want to do the equivalent of going to data auto filter and then select in the brains . Arrange only, and then we want to copy all of this filtered data into the file cold brain stuff. And let's say we want to delete the existing worksheet, create a new one and then paste the information into that new work fleet. So let's just close these down and then let's open the file containing our macro. And this is 05 object variables, and what will be doing is will be creating references to the workbook, worksheet and range objects in both of those workbooks. This is slightly overkill because we're not really doing a lot of operations, and that's really where using variables becomes useful. But we doing this just that you can get some practice cotton crossed, using variables for data with using variables for excel objects to start references to the objects that you're going to manipulate. So let's have a look at our empty macro. We go to develop, Open up the visual basic editor here, you will see, we've got the skeleton of the macro that we're going to create, so we'll start by declaring our variables. Well, then, uh, open up the source workbook. Which, of course, is this one sales will filter the data like I've just shown. You will specify the source range, which will, of course, be the filter data. Well, then open up the brain stir workbook, copy the data, and then close the original sales workbook, leaving the brains to open so that we can see that our operation has been successful. So, as I said, we're going to go into overkill mode on the creation of variables. We're gonna dio six in total workbook worksheet range. And we doing that for both the source workbook and the target workbook. Declaring the variables is the same as declaring data variables used the key word dim, followed by the name of your own creation, preferably using a prefix to make your code that much more readable. So starting with the workbook that's gonna be WK b source. And then we put the keyword as, and in this case, the data type becomes an Excel workbook. Let me do the same for worksheets. Dumpty Chaos source as well up seats and the range are N g. Source as range a nice feature that you can use within the V B A. It's to replace within a selection. So if I now want to do three equivalents for the target workbook, I can select these three lines and copy them by simply holding down the control key and drag in the selection below the original. Then I select the copied lines. And if I now use control H or edit replace, I can simply say in the find what box source and in the replace with Box Target. And then this very useful option selected text is already highlighted for me. I therefore, justly from requires all and it replaces three occurrences. Let's then do one final variable to specify the path because obviously when we come to open these workbooks, we have to specify where they're located, and we'll use a relative pass. Basically, we'll take the location in which the workbook containing the macro is saved, the folder that contains that workbook. And then we'll work forward from air into the sales folder and look for to work books with the names that we know those workbooks will have. So this, of course, will be a string very long, so dim. Str path as strength. So before we can open up the source workbook, we need to populate str path to populate or to assign a value to a data variable. We used the optional keyword let in the name of the variable an equal, and we then use a statement which produces a path. So to specify the folder that contains the current workbook or rather, the workbook that contains this macro. We say application not this workbook stop path and then we want to add to that which we do by using ampersand shift seven, the concatenation operator. So I m percent and then double quotes and then we'll need to put a backslash to continue without path sales, which is the name of the sub folder, another backslash, and finally, the file name, which again was sales The X l s X closed quotes. So we're taking the folder in which the current in which this workbook it's saved on we're adding on the rest of the past to get the full passed to the file you want to open. When we opened the file, we simultaneously put a reference to it inside wk b source. However, when you assign a value to an object variable, you must use the keyword set rather than let. And unlike the keyword, let set is not optional. You have to include it, and this is why I'd recommend when you're learning Levy A. But you always use the let so we have a bit more parity, so you can always think. Do I need a let or do I need a set? So Set wk b source equal to application. Workbooks don't open now. This, of course, is a method. It's an action, but because it's not the only statement on this line, it's actually part of a statement. We have to use parentheses. So if the only statements on the line was application, not work, it's not open. You would need a space at this point runs and parentheses, but because it's only part of the line, you do need to have parentheses, So the only parameter that will need it's the file name on the convention that will be used in is to put the name of the parameter colon equals and then the value. Which, of course, in this case is str path string that we've just composed on the bridge is light. So let's save our changes and test style macro, and we can indeed see that sales is being opened. So far, so good. So the next step is to filter out data, and to do this will need to drill down into the work feet that contains the data and then specify the range that will be Filter it. We can either say the first worksheet because we know it's the only one in the workbook or we could refer to it by name. So inside sales we have a sheet also called sales, so we can either refer to it as worksheets one or work feats sales. Again, we're talking about an Excel object, so the keyword is set as a poster. Let wks source equal to and of course, Millikan Inside wk b source at all the worksheets and then in parentheses. We specify which one, and I'm gonna do it by name, which means that I have to use double quotes. So that's the worksheet we're interested in. Now. The range set are N g source equal to this time we're looking inside wks source, and we simply want the used range, which is to say, all of the cells that contain data. Now that we've drilled down into the range, we can filter it, and that's very easy. Our energy source. Auto filter. And this time, because the method is the only statements on the line, we don't need parentheses. We just hit the space bar and go straight into our parameters. So first of all, we specify the field, and this is done numerically so numerically. Which column do you want to filter by? If I open up sales yet again, Nice thing you can do and counting columns in Excel. It's just a drag across the column headings and keep the mouse held down and you'll notice that a little tool tip appears. Which tells you how many columns you've currently got selected So, as you can see, I want the product range and thats column G, which is the seventh column so you can see at the end of the tool tip times seven C seven columns. So that's the one we want to filter by. Back into our code on field becomes seven. When you have multiple parameters, a nicely you can do is to put them on separate lines. Just makes them easier to read. To split the line. Use the Continuation Operator, which is space, followed by an underscore. Then I can use Tab and Space bar tow line up the cursor on the next parameter that I need is the criteria criteria. What colon equals and this is a string criteria. So it goes in quotes and it's the word brain stuff. So that's my filter operation on the filter data is my source range. That's the range that I want to could be, So I can now go into the next section and simply repeat this line. Set our energy source equal to W ker source. Stop used range because, of course, the used range has changed to the filtered range. Now I want to open the target workbook. So of course I'm gonna use to very similar lines. So again, highlight these two lines, hold down the control key and then just drag into the open target workbook section. So here I'll simply change the word sales to brain stuff. Of course, the folder is exactly the same, so that doesn't need to be changed. And then the variable that I'm using is of course, wk b target, I suppose, to w k b source. So that's opened up the target workbook Now to copy the data. I've already specified the source range. I simply need to, especially for the target range. And what I want to do effectively is to create a new worksheet. Let's say we want to relay in the worksheet brain stuff. Amend. Delete the existing worksheet. So to do this, I'll set wks targets equal to W K. Be targets. Worksheets don't add. As you can see, the method is not the only thing on the line. Therefore, if I want to specify the location within the workbook off my new work, street only parentheses and the two parameters that enabled me to do that are before and after, So I'm gonna use that before Method Colon equals. When I specify which worksheet within the W K B target. I need to place this new one before And of course it's worksheet one. So I simply say, W k b target dot worksheets brackets one. So my new works, it will go before the existed worksheet. Once I've done that, I can delete the existing worksheet which will, of course, at this point in time have become worksheet to when you attempt to delete an excel worksheet. Excel will just double check that you want to delete it on display a little dialogue box s so we can hide that dialogue box simply by using the application dot display alerts property. So we say application not display a Let's equals false. Then we delete our worksheets so it's gonna be wk be targets Don't work sheets, too, that we delete and then we simply reactivate our display. Let's so I'm just using controlled drag to copy that line and the changing false True. So now we've specified the worksheet into which we want to copy the data, so we're ready to specify the range where the data is gonna be copied and to do the copy itself. So to specify the range, we say set RMG target equal to PKS Targets don't range rockets a one because, of course, you only need to specify the top left cell into which you're pasted. Now, when you come to copy data using B B A as long as he specified both the source and the target, you don't need to do a copy followed by a paste. You can actually do the copy on a single line, and Excel never goes into that special copy mode with the flash in border. It just transfers the data cleanly and quickly. So wherever possible. This is the method that you use to move data from one location to another in your back, rose. So you start with the source range, which we've got held in our our energy source variable and then used the copy method dot copy. The method that we're defining is the only statement on the line, so we don't need parentheses. We just into space, followed by the destination parameter. Tolan equals and of course, RMG targets. And that's it. Just a couple of final steps to clean things up. Let's rename the Work Street and then also fit the columns so that all the data becomes visible. So to rename, we've got it held in our variable wks target dot name equals brain stuff Then to also fits , we just use wks Target don't used range the columns But also if it and that's it, then the final thing we do is to close our source workbook. The source workbook is of course w k b source. Come and say Doc Close, but we've made changes to it, which we don't want saved. So we've got a method which is the own me statements on the line so we don't need parentheses. We just need a space on. We used the safe changes parameter colon equals false. So it closes the workbook, abandons the changes that we've made on. Then we're in business. One final thing we might do is just to put a message box up. And I'm gonna do this long planned B b a dot interaction just to remind you where it lives . Mm Street box space prompt colon equals operation completes. Okay. So that we have ah, macron. Let's save our changes and let's test it so operations complete. There's our brain stuff and we've gotta work. Sequel, brain stuff era along the breaks, the products and we've closed our sales workbook without safe in the changes. So I'm gonna do the same with brain stuff. Let's just close on. Let's say Don't save. So we've gone slightly overboard on the creation off object variables, but I just wanted to give you a chance to contrast that. Use of variables for storing data using this optional keyword. Let with the use of variables restoring references to excel objects where you have to use the obligatory keyword set. 20. 2.6. Option Explicit: in this video, we're going to look at a very useful VB, a statement which relates to the use of variables and that is option explicit. So to see why this is so important, let's go into our 02 key elements of V B a code sub folder and then open up 06 Option explicit. When you go into the visual basic editor, you'll see that we have the same module that we used in the last video and inside it we have the macro that we wrote it in the last video. Now you may have noticed that the top of each of the modules that we've inserted we've automatically had this statement option explicit. I won't miss me, basically, is that variable declaration is required. So in other words, before using any of these variables, So, for example, str path. Before using that, I will have had to declare it and to show you why this is so important. Let's take a very simple scenario where we make a typo, as we put it in the name of a variable. So down here, when we come to close our workbook use in W K B sore stock close. Let's say we have accidentally typed wbk. So it's not a particularly conspicuous era, something that you could easily miss because we have option explicit at the top of our model. When I try to run this code, I'm presented with an error message informing me that this variable hasn't been defined and I can immediately see our. This is because there's a typo and I enter the correct name. Now let's look at what will happen if we omit option explicit. So to come out of brake mode, I click on the reset button on. I could obviously delete option explicit, but what you can also do when you're testing your code is you can simply comment out one or more lines, and you can do this manually by inserted an apostrophe yourself. But there's also this useful button at the top here called Comment Block, and this allows you to just select one or more lines and click to turn them into comments. And next to it, you have a NCAA meant block, which convert it back into a line of code. So I'm gonna comment out option explicit and let's see what the difference is By the way, I should tell you that this is the edit toolbar. So if it's an all on your screen, you just need to go to view two bars and choose edit. That's where it lives. Okay, so now we've deactivated option explicit. We're not going to be warmed if we make an error when type in the name of a variable. So when I run my code, most of it works fine. But when it tries to close the workbook because of the typo, I get a message object required. And, of course, when you get these kinds of errors you may get sidetracked to, you may actually take it very literally. Start doing Web searches to see what's gone wrong when all the time. It's this very simple typo. So sometimes it's right on the your nose and you miss it. So again, let me hit the reset button to come out of brake mode, and I'll just close down. The files that have been there opened as a result of running that code, said the simple solution is always make sure that you have option explicit at the top of your model to save you typing it yourself. You could have it automatically inserted whenever you create a new module, so you'll notice that when I right click, insert module up comes option explicit at the top of each model. This is the default behavior, and it's the result of an option in the Tools menu tools options called require variable declaration. So if I were to deactivate this option when I inside in the model now, you'll notice that option explicit isn't inserted. What this means is that even if I declare my variables because option explicit is not there , if I make an error when type in the names are variable, I'm not going to be warmed. It will be up to me to locate the source of that era, so it's something very simple and definitely worth doing. Simply make sure that in tools options you have required variable declaration always switched on. And then every time you insert a module right at the top gives this very useful statement option explicit 21. 2.7. Declaring and Using Constants: in this video, we're going to look at Constance and contrast the use of Constance with the use of variables to see how constants work in our 02 key elements of V B. A code. Let's go into 07 declaring and news in Constance. When we go into the developer town on visual basic, we'll find the same model that we worked on in previous videos. Essentially, a constant is a variable value doesn't vary. It's a piece of data that you want to keep track off while your code is running. Value remains constant around, and within this code we've got a couple of candidates for Constance here. For example, when we're opening the source workbook, we have a literal string, which targets the source workbook. Rather than having this literal string. It's best practice to declare a constant, which contains that string and then refer to the constant throughout your code. This makes it much easier to maintain your code and modify these literal values. So to convert this into a constant, I would simply copy it and to declare a constant, you used the key word, Const. Then it's still a best practice to use the prefix, so I'm going to call it str since it's a string and the convention is to put the rest of the name all in uppercase letters just so you can distinguish Constance from variables when you look at your code, so I'll call this str source. Then you still say as string to specify the data type, then the second difference between a variable and a constant definition is that you must then declare the value of the constant. So here we would need to say, equals and then I can just paste in the string, but I copied. I can then replace the literal value with the name of my constant. Here's another candidate, although it short because it's a literal value. Making it into a constant makes your code easy to maintain, so I'll do the same thing. Copy it, declare a constant. This time we'll call the criteria and again well paced in the value and replace the literal value with the name of our constant. And then finally, let's do the same for the target workbook. And once again, let's replace the literal value so you can imagine is your code becomes more complex using this technique makes your code much easier to maintain. You haven't got a hunt for those literal values. It'll be declared along with all your other Constance. So in this example, if we want to use our code to find another range rather than the brains, the range, let's just go back into the folder where those two files reside. And I'm gonna take a copy of the Brain Stofile by holding now control and just dragging to copious. Then let's rename this with another range. So are fictitious. Company has arranged called Communion Do. And if we go now back to our code, we can change our criteria. And also the file name the target bile name. So by having my constance of the top here, it just makes it much easier to update these literal values and have my code do something slightly different. Soon it's just tested. And as you can see, it now creates a committee do file. The only thing I forgot to change waas the name or the worksheet. So I'm just gonna close this town on then here str Criteria can also be used. Ask the name of the Work Street so run it on here. We've got allow commended products on the worksheet now has the right name. Okay, So best advice always use constant definitions rather than literal values to ensure that your code is much easier to update at maintain. 22. 2.8. Using MsgBox for Output: the V B. A message box function is used both to convey information to the user as well as to obtain user feedback. In this video, we're looking out, put in information using the message box function, and in the next video, we'll move on to look at obtaining feedback from the user. So in our training folder, we're still working in 02 key elements of B B. A code. Let's now open up 08 using a message box for output. Let's go into the developer tab on Click on Visual Basic to Lourdes, the VB visual basic editor here on the left, you'll see that we don't currently have any modules, So let's remedy that by going to insert module. And I'm just gonna rename the module using the Properties window and let's call it em and its people. I'll put then over to the code window unless instead of macro my game to insert procedure on, let's call the macro basic in votes so that we can build on what was covered in the last video, namely Constance. Let's create Constance to hold the attributes of the message box that will be created, namely the title on the prompt. The first constant well, actually for the Bronx. And that's the text displayed within the bubble looks to use the keyword Const Str String, and we'll call it prompt. And then let's do another constant for the title Bento actually display the message box will use the longhand syntax, the B B A. Got contraction got message box, and I'd recommend that you keep doing this for a while just to remind yourself where message box fits within the grand scheme of things. So it's not part of Excel. It's part of Phoebe A on its part of the interaction class because the message box is the only statement on this line of code. We don't need brackets. We just need a space. Then we're again gonna follow best practice. This is not essential, but it's just very useful to do when you will learn in B b A. And subsequently we're gonna put the name off the parameters so prompt. And of course, our prompt is constant. That's your problem. Coma and under school, which is the continuation character. And then let's just tab across on a line, the second parameter that we're going to use under the first. So because we name in our parameters, we can put them in any order. So the 2nd 1 I'm going to put its title and there's a fool's is str cycle, and we'll put it that so another continuation character and the 3rd 1 we're going to put his buttons. This parameter gives you a lot of options. And it's this parameter that determines whether you're going to use the message box for output or input when using it for output. You're interested in defining the nature of the message. The message that we're displaying here is for informational purposes. So a good one to you for that is BB information. So let's look at the result on Here's the results of using the information we get the little information like on display Next message any more senators that we have a ping just to contrast that with the plain old I lock. If I delete this last parameter and run again, there's no beep and there's no icon. And now let's look a contract with a warning message. It will come macro, just paste it above the original. Of course, we have to change the name Let's call it a warning message. This time I want be on our title ever. Let's change the icon that we display within the diamond box, not just delayed the old one. In order to display the intelligence dropped down that we saw on here will use the exclamation. So women run again, we get beat, and this time we have a warning triangle to indicate that this is an error message. So as you concede, the buttons parameter provides the message box function with quite a lot of flexibility. This flexibility includes obtaining feedback from the user, which is what will cover in the next video. But before we leave this one just to point out as well that you can have quite a lot of information in the message box and you can display the information on multiple lines. Let's based another copy for original some procedure, and let's remain this one detailed in vote. So let's say we want to give some information to the user on options for technical support , so we'll change the title to technical support. And then let's say we want to have a three line message on the first line. We want to say There are three ways to have pain, technical support and then we want to leave a blank line and Tabin and put the first method . So to do that, we can just use the continuation character space under school and then just continue our string on the next lines. I'm just having a cross, then using ampersand to continue the string. And then we can use a couple of constance that the b A has which are new line on tab again , We're not just gonna put the constants. We can use the long hands in tax just to remind you where these constants live. So that part of BB A and they're in a class called Constance V B a dark Constance. And then when you press dot again up, they will come So we won't be the new line and we want to tab, which is also constant. So ampersand b b a dot constance don't be turned and in fact, we also want to have a blank lines only to new line characters. So I'm just going to copy that bit of code on a place today. So you've got new line new line of attack. The first method is to port ampersand. Then we want to have another blank line on Tabby. So just copy this. Use in control and drag. I just ignore the fact that that temporarily thinks I've made an error because innovation characters space come to school. Ban most of this like So when you get used to be be a syntax, obviously you can just put be the new line on BB town. But I recommend, uh, while you're learning, just continue using this long hands in tax. And also you will notice that when you use that syntax, you do get the benefit of intelligence. So when I put my dot in here, Your Majesty, at all the constants are listed for. So that's another benefit of using the long hand technique as a learning mechanism. So let's test it. And they're all three lines without new line characters and our tab character kicking in. It's quite a large tab. I always find it in dense quite considerably, so a lot of the time you might prefer just to use spaces. But just to point out, that character could be used within your message boxes. So that concludes our look at output in information using message box. In the next video, we'll move on to look it. Get in user feedback by a message box. 23. 2.9. Using MsgBox for Input: in this video, we'll move on to looking at using the message box function for user input. In the last video, we saw how it's used purely for output, but it's very flexible. It can also be used to input. So you know 02 key elements of B B. A code we're not against weapons 09 Using the message box function for input and then in the developer tab, we click on Visual Basic, and here you'll find we have a macro, which we wrote a few videos back, which simply filters using the specified cry interior and then copies the filtered data to a separate workbook. So what we want to do is before this subroutine runs, we want to check with the user as to whether the data is ready on. The user can either answer yes or no. So let's look at how that stuff. First of all, you'll notice that I've changed the public sub to private son, and the result of that is that it's a bhangra mackerel. So if I were to get back to excel and look at my macron's, you'll see it's no longer listed. What this means is that this subroutine is only now of them. It's not people for Macron's to call you. Now write a macro. Our macro can call this subroutine, but the subroutine can't we run as a macro in its own right? I'm going to insert a macro by using this procedure, and let's simply call it main, and I'm going to move it to the top of the pile, like selecting it on dragging it to that location. So what we want to do here is to display a message box and then capture the result whether the user clicks on the yes button or the no button. So let's just have a look at the syntax involved by going into view Object browser. And here's our message box. It's in the V B, a library interaction message box, and when you look at the description, you'll see it has the parameters, some of which we've been using. And then at the end, we have this keyword, as followed by VB message box result, and this tells us that this function returns value off the type Fyvie message box result, and this basically is a collection of Constance. So if I click on this link. It actually shows me what the possible values are. So these are the members off VB message pops results, and they basically indicate which button the user has clicked on. So we'll be using the VB. Yes, no time. Hence the two possible results are baby No on, baby. Yes. So the first thing we'll do is to declare a variable off the VB message box result type so dim on, Let's call it VB a result as baby message box results. So now when we come to display the message box, we need to capture the result. Hence we say, Let's be be a results equal. And this is one of those occasions where we could ignore the intelligence suggestions. None of them are what we're after. What we really want to do is to display a message box, so we used B B A thought interaction don't message box, and then we'll need parentheses because the message box is not the only statement on the line and then the prompt parameter and will then use the buttons parameter to specify that it's a BB Yes, no style message box. So buttons coal on. He calls me be? Yes, no. So without that parameter, we wouldn't have the two buttons that we need to get the correct when the used in order to test the result will need an if statement and really discussing payments until the next section are quite straightforward. And I think a lot of people will have encountered them as excel functions the excel a function. It's very similar. In fact, if anything, it thinks here the basic structure is we have the key word. If then we have a test and then the key word then then we specify statements that we want to execute. It is true. Then we have the key word else followed by the statements to be executed. If the test is false, Onda, we then close with the end. If with the end if statement so very straightforward. So test basically is gonna be off vb a result and we're testing to see whether it equals yes and of course, having defined the type when I type unequal sign, it gives me all the possible values So I can just double click on B B. Yes, true statements will basically, if the data is ready, I just want to run or call my copy data, said procedure. To do that, you can literally just put the name of the sub procedure, and that will cause it to run the sub procedure. At that point, best practices always to use the keyword calls, even though it's optional. It just gives you more of a clue when your scan in your code, where calls to some procedures have taken place if the data isn't ready, will display an error message and stop the macro. So this is gonna be another message box. No need for parentheses, because the message box is the only statement on the line. Just a space and then into a prompt and again will use the buttons parameter on. This time will have be explanation. That's it. So that's our macro. Let's test it and first of all, will click on. No Data is not ready. There's our message box kicking in. Now let's test it again with the yes button. So when we click, yes, it goes ahead and runs the macro and this outcome in do report created for us. Let's just close that one down. You'll come across a lot of code where the evaluation off message box result is done using numerical values. But I recommend that there, when you're doing this, especially as a beginner, you should always use VB message box results as your data type. It just makes life a lot easier, because here you haven't got a worry about, but in numerical values by express controlled space bar, then it's always going to show me every possible value as a very easily recognizable constant. That's a quick look at how you can use message box for input. What you can't do. Of course, my message box is too type a value, but that you need input box. And there are two flavors, one supplied by Phoebe A, which is quite basic, and a more advanced variety, which is supplied by the application object. And we'll be looking at these two variations over the next two videos 24. 2.10. Using VBA InputBox: in the next two videos, we're going to look at the two variations of the input box function, which you have available to you as a programmer. The first is the V B, a input box, which will look at in this video. And then there's a slightly more advanced version, which is application dot input box, and we'll look at that in the next video. So in 02 key elements of B B A code, let's open up number 10 use in the BB, a input box, and then let's go across to the visual basic editor. You'll find we've got a single module, and when you go inside it, we have the copy data macro that we created in an earlier video, and what we want to do now is instead of having a constant to determine the criteria that is being used, we want the user to supply the criteria. So the first thing we'll do is to get rid off the STL target constant, and we'll just use str cry interior to determine which workbook is open on the criteria that he used for Children. I'm just going to delete this line and then just for clarity. Since this will no longer be a constant, it will become a variable. Let's just change the case. So using upper case is the convention to distinguish Constance from variables. So I'm just gonna do, find and replace it. It's replace and we'll just put this in a normal case and now we need to convert it into a variable. So Const becomes dim and, of course equals Come in. Do now becomes obsolete, not permitted because variables can't be declared and assign value at the same time. And then let's just under a comment here that we need the user to supply a value for str criteria. So having deleted str target, we now need to replace it with str criteria. So when we come to open the target workbook were taken the location off the workbook that contains this macro by using dark past and then we tagging on what waas a constant. So what will now need to do is to just build this as a string which includes our str criteria variable. So just to remind you of the path structure, here's the work with the contains out macro, so dot path means that folder that contains this workbook. Then we tag on a backslash going to the sales folder tag on STR Criteria because that will contain the actual name of the range and then finally tag on dot XLs X. But while extension. So here when we come to open target workbook, we have application not this work up the past and then after the n percent, we can now puts festival the backslash Ben sales another backslash close the string ampersand str criteria and finally and percent don't x X, so that will ensure that we open the correct workbook to copy the filtered data into. And of course, STR criteria is already in place. So all we now need to do is to populate str criteria by an input box. So to do this, it's gonna be let because it's the data variable str criteria equal and then the input box . We use this part of the d A. It's a member of the interaction class and because the input box function is not the only statement on the line, will then need parentheses. We put our prompt colon equals. Then we might put a title. And then let's specify a default value, and that's all we need. So let's test our code. There's our default. I'll replace it. And when I click OK, up comes I'll come in. Do workbook with the filtered data. Let's close it down and let's test it again. And this time leave the default value and there's our brain stuff. Report. So that's the V B. A version off the input box function, and the key thing to remember is this limitation that it has. It always returns a string value, so it's fine, quick and easy to use for a lot of interaction with the user. But if you're interested in getting the user to input numeric values, then application dot input box, which will look at in the next video, is a much better choice. 25. 2.11. Using Application MsgBox: in this video, we'll take a look at application dot input box and we'll examine the parameter, which makes it slightly more sophisticated than the V B A variation. So now 02 folder. Let's go into the last File 11 using application dot input box, and then it's going to visual basic. What we want to do here is to create a new version off a macro that we did a few videos back where we asked the user to enter values and then populated three cells within this work seat. So we put a quantity in a 15 the description and be 15 and then a price in E 15. So if I use an application on input box, we can just check that the user enters a numeric value in quantity at a unit price, just to remind you as well that we've named our sheets so as well as the tab name. We actually have a code name which our baby a code could refer to, so it's inside of macron. Insert procedure. Let's just call it entered data so we can start with estate invoice don't range 15 equals, and this time it's application input box, and when the parameters appear, you'll see that they're very similar to the V B. A input box. We've got the problems. You have a title. You can set a default value. But the important difference is we've got this final parameter here called type. And this is what enables us to specify the data type that the input box will accept. And if the user then enters a different time, an error message is automatically generated. So let's go into the object browser and have a look at these Intacs. They will go across to excel for this into the application object a man scroll down to input box, which it forces a function have been selected an object you press F one or click on the help button. You'll be taken to the only and help and given a description off that object. So we scroll down. We've got a description of our parameters, which are pretty much the same as the BB A parameters. But then the one we're interested in is tight on when we scroll down here, it gives us the different options that you can use. The two that were interested in, uh, number and text in a later video would have a look at what? Using input box to grab a cell reference from the user, which is also quite useful. So as you can see, if we specify type one that makes our input box numeric and it would specify type two that makes it a string input box. So he turned into our code. We'll start with the prompt a man that's that in the title and finally the type. And of course we put one to make it numeric, and then we can simply copy this line. So I'm holding down Control Dragon. One of the annoying things is that when you do that, it doesn't leave the highlight in place. So if you want to copy again, you gotta manually highlight the data again and then control drag to copy. Of course, this becomes a description on the type here is to then we have the unit price, which again is one. And finally, let's change our cell references to be Andi. So, as I said, the main benefit of this application, not input box, is the built in data validation. So let's put it to the test and let me type t w o instead of putting the number two. And when I click OK, you will see it has an aromatic automatically entered form. So that just reminds you what I need to do. So I'll put it to and my description and again, it doesn't like text and I'm forced to put a number. So there are my three values. So any time you're entering numeric values, always use applications of input box in preference to the V B a version. 26. 3.1. If Statements: in this section will be looking at VB a control structures Control structures allow you to group multiple lines of code in useful ways. The two main types of control structure uh, condition ALS and loops condition ALS allow you to execute several lines of code based on the results of logical tests. Loops allow you to carry out multiple lines of code repeatedly, and they're very useful for process in multiple objects. So, for example, every filing and folder or every worksheet in a workbook to get the ball rolling in this section, let's start by having the look of, if statements, so it 03 BB A control statements. Let's open up one if statements and then let's go into the visual basic editor you will find we've got a single model M if statements and inside it is a macro that we've seen a couple of times, which asks the user to supply criteria and then uses that criteria to filter some data and copy it to another workbook. So the thing we want to do here to practice using if is to make our code run both on Windows and on Macintosh versions of Excel at the moment, it will only run on Windows machines. That's because the Mac operating system doesn't recognize back slashes in file paths. Instead, it uses Colon as the separator. So if we want this to run both on Mac and Windows, we'll need to ensure that we check to see the version and then use a file path, which is appropriate for that operated system. So since we've used a constant to tack on the end of the file path, we obviously come into half code, which tries to change that value. So what will do instead is to create two constants one for Windows, which is one we've already got and then once a Mac. So I'm just going to select this line, hold down my control key and drag the line to copy it. And then let's rename the copy. Str source. Mac under school back for readability. And then we'll change this to a format that the Mac will understand. So we simply put coal on instead of backslash. So now when we come to open the source workbook and we build our file paths instead of just taking on str source will need to test to see which operating system is being used on either tag on str source or str source. Mac. So to make this line conditional, that's right. But inside an if statement and I'm just going to tap the line in, just press the tab key to move it in just for readability. So the structure off an if statement is. If Test Onda test is any logical test in other words, one which can only evaluated true or false, then this line will be executed. If the test is true, we don't have the key word else and then another line which executes If the test is false, so it's obviously gonna be a very similar soul. Just control drag to copy that line and to close the if statement we have to use and if so , in a simple if statement, those of the or keyword. So we've got if then else and and if so, let's say we're gonna test to see whether the operating system is back so clearly if it is Mac, we will want str sore smack instead of str source. I'll just replace that with str source. Mac. Now we need to come up with a test which verifies that the Macintosh and to do the test, I'm going to use application adult operating system. This is a property of the application object, which returns a string. The string doesn't consist purely of Windows or Macintosh, but it will stomach with either Windows or Macintosh. So my test is simply going to be does application or operating system. Start with the word Macintosh to test whether it starts with the word I'm going to use the left function, which you've probably come across in Excel formulas. As usual, I'm going to write it longhand, so v b a. And it's in the strings close, not left when you open parentheses after type in a function name. Normally, Excel will display a tool tip, showing you the names of the parameters and the order in which they go. I think it's because the word Ben follows the open parentheses. So if I just closed my parentheses and then move back on, then let's just put the opening Prince sees back in. You can see now it displays the two parameters, and again, I'm gonna go into overkill mode and actually put the named parameters. So first of all string colon equals. And this is where I'll need my application dot operating system. This is the string that I'll be operating on and checking to see whether it starts with Macintosh application, not operating system coma. And then the second is the lens or number of characters that you want to extract starting from the left. So length, of course, is going to be the number of characters in the word Macintosh, which is nine. Okay, so what I'm gonna say is, if all of that is Macintosh, so now I simply type equals packing and it cools. Being text has to go in double quotes, so that's the first work. But the source workbook and we've ensured that str path will contain the appropriate constant for the operating system in use. Now we can simply copy that if statement on, use it for the target workbook which we opened down here and again, I'm just going to wrap this line inside out. If statement so the test will be exactly the same. Just paste that in, and then here we would have our else. Then I can copy the line on close key, and if so, because we test in to see whether it's a Macintosh. This is one that will need to change. So we need to Trade is the style of that to the style of a Macintosh file path. And we do that by putting Colon in these two positions. So that's it. So we've insured were attempting to open a workbook we have built in a file path which the operating system could understand. So now we can just transfer this code onto a Macintosh and check that it runs. So save it and then we've across to a Mac. So it's first of all, have a look at the original before we made our changes. So in this version, you can see we only have str source is our constant and we use in back slashes in the file parts. Of course, when we come down to open our source workbook because we're tacking on str source, which is the windows file paths, it's gonna break run off exchange of criteria to come in do and immediately it chokes. You see, it starts with a vowel. Buster has Coghlan's, but then we're trying to tack on str source, which is our constant that has thes backsplashes, and it's the backslash is that cause in it? So if I click on De Buck, it climbed. What's the problem line? We can see that it's STL source, and those backslash is basically that causes the problem. Well, let's look at the finished version. So on this version, we're going out to constants the Windows version, which uses backslash. Let me about the man fashion, which uses the colon better, which is what operating system demands. Then, when we come to open Astle's workbook, we do in our test to see whether the operating system is Mac and we use an STL source back if it is and STL so smacking it isn't then for the tongue at work book, you can see that we're doing the same test. We're using the colon version for Mac and the Backslash version for Windows. So let's now run and again I'm gonna change the criteria to come in do this time it opens up the sales workbook. That's the filtering and copies, the data and our operation complete successfully. So let's just get across to excel on here. So come in, do workbook Onda, we gold Commander Data and the file worksheet rather is called community as well. So this is an example of using the simple statement you're catering basically for two possible outcomes. If it truly is a woman Boots. And if the test is false, you take another in the next video, we'll move on to look at the if else, if version off the if statement, which allows you to cater more possible outcomes. 27. 3.2. If ... Else Statements: in this video, we'll look at the if else if structure and the advantage that this offers over the simple statement that we saw in the last video is that you can cater for multiple eventualities. So now 03 of the drain in folder Let's open up zero to if else if and then in the developing sound. Let's just click on visual Basic and in the Project Explorer, we can see our models. We have a single multiple called em if else I'm inside that we have this skeleton off the macro that we're going to write. And the purpose of this macro will be to detect the version off Microsoft Excel, which is on the user's machine, and we'll be using the if else, if structure to test for all the different versions that we're interested in. Cater in four And those versions are going to be on Windows Excel 2010 2013 and 2016 and on Macintosh Excel 2011 and 2060. Let's look at the two pieces of syntax that will be using in addition, of course, to the if statement. So in view, let's choose object browser going to the Excel library and look at the application objects and the two properties that were interested in first of all, operating system. And it cause this is a string, so this will either contain the word windows or the word Macintosh, and then the other is version. And although this is basically numeric, you'll see it's actually treated as a string. So if we want to examine it as a number will have to convert it into a number because the property returns a strength. So we begin by declaring the variables that we want to use. So first of all, we want to store the name of the version as in Excel 2010. And this, of course, will need to be a string variable so we can say dim str version string. Then we want to convert this into an integer value. So let's also dim onion t version as interject and to test the operating system. Let's have our first boolean variable so that we can test to see whether the operating system is Windows, and if it is, we'll set out 1,000,000,000 value to true demon bln wind as Julian When working with Excel formulas, you've probably come across the find and search functions. These enable you to check whether one string occurs within another. The equivalent in V B A is the in string function i n str So here we can use it to test whether the word windows occurs within applications operating system and we can populate our bln win Boolean variable accordingly. So the syntax for this is let BLM win equal and then we can use our V b A in string function. So it's inside V B A. It's in the strings class and it's i n str So the first parameter is where do we want to start? And we want to start from character one. Then we specify the string inside of which we want to start searching. So that's our stream one parameter and this is where we need applications are operating system and then finally we specify what we're looking for. Within that string, string two parameter will be the word windows. This function produces a number which represents the character position of string to within string one. So as long as this number is greater than zero, we know that string to has been found in science during one. It's our test needs to finish with greater than zero. So now we have a test which will populate our 1,000,000,000 variable with either true or false. Next, let's convert the version to interject. So here we're going to populate I NT version are into variable because it's a data variable . We're going to use the optional keyword. Let so let's inversion equal and then we'll need a B B, a conversion function, which is C int and the thing we convert in his application, not version. So we now have the two pieces of information we need in order to detect the version of Excel that's being used so we can now get into our if statement. So we'll start with 2010 on Windows. So the test here is going to be. If inversion is equal to 14 which is the version or 2010. And also Yellen win is equal to true. Then we can populate str version with Excel 2010 the STL version equals, and of course, it's a strength. Excel 2010 and I haven't put the let this time, but we'll let that pass. So let str version is also fine And of course, what we now want to do is to test for all the other versions. I'm going to copy these two lines by selecting them, holding down control and then just dragging them below the original. But of course, I can only use the if statement once it's on the second test, I need else it. So now I'm testing for 2013 and this is version 15. And, of course, bln. When will still be true? Let's now copy this for our 2016 test. So here we test Introversion 16. So I forgot to change this to 2013. So that's 2013 on then. This one is 2016 so those around three Windows versions let's now do the same for the two Mac versions. So I'll just keep copying my else if and then modifying the copy control drag. So 2011 for Macintosh will be the integer 14. But of course, bln win will be false. So excel for Mac 2011. And then let's just copy that one small on this will be 2016 which will be the integer 15. So we've tested for all the versions that we're interested in evaluating. If none of the above are found, then we can assume that it's a pre 2010 version of Excel. So a final part of the statement will be the else. And this will say steel version equals, Let's say, pre 2010 Excel version. And then we closed our and if and our if statement is complete. And to complete the macro, let's just display a message box with the version that has been detected. The B B A. Don't interaction don't message box with the prompt best your version and will tag on the word used in ampersand 57 detected. And that completes our macro. Let's save the changes. Andi. I'm currently using Excel 2016. As you can see. No, in fact, I'm not least in Excel 2013. I do apologize. Um, that's fine. So we go back then it should detect basically that I'm using Excel 2030. So let's run on their Indeed, you can see Excel 2013 detected. So I'm now just going to test it on the other two versions, the other two in those versions and then on the two Macintosh versions. So here we are in Excel 2016. And here's our macaroni. So we should now activate. This section is being true. That works fine. Next 2016 has been detected. Let's switch across to 2010. So this is 2010. Here's our macro. Let's see if this section about statement now kicks in. That works fine. Excel 2010 has been detective, so the three Windows versions are being detected correctly. Let's just do the same for our Mac versions. So here we are in Excel 2011 for Mac. Mr Smith, across the visual basic editor. So here, expecting the version 14 on Windows. False to kick it. And that's just click on run and then we can see Excel for Mac, 2011 has been detected That works Fine. Excel 2016 for Mac. When we go into the visual basic editor, it's gonna be a case of where is everything. As you can see in the visual basic editor, everything is missing, basically. But here's our code, and this time I expect in the last section to kick in. Here's the play button run program, and it does detect Excel with Mac 2016. So that's how the if and else if structure works in the next video, we're going to move on to look at select case, which on the face of it seems very similar to if announce it. But the main thing to remember when you're using, if then else if is that with each of your tests you have a slightly different 1,000,000,000 statement says You could see each time we've got a different combination of version and BLN win equals true. So, in other words, are tests are mutually exclusive. We never test into the same thing twice for obvious reasons. And then we have a catchall. So none of the above true. Then the else section kicks in. So this is the main benefit of using if then else if you contest the different possibilities. Eight of protests that you make is unique within the context of the statement. 28. 3.3. Select ... Case: in this video, we'll look at the Select Case statement. On the face of it. It's very similar to the if an else if structure that we saw in the last video. But its main benefit is its simplicity. Let's look at how it works in the training folder in 03 v. B A. Control statements, Let's open up 03 Select case. And here we've got a single worksheet called Welcome. And what we want to happen is that every time you open this workbook, the current date is this blade. But we'll also include on Ordina, LOL. Today's date, for example, is the eighth of July, so we actually want the th to be calculated automatically and inserted into cell a one. So when we're going to develop our visual basic, we're not going to create a model because we're not writing a bathroom. This is event programming. This is something that we want to happen automatically. And when that's the case, you use these objects excel objects. They're always there, one for each sheet on one for the workbook itself. So what we'll do is to double click on this workbook and then we type out code into the code window. Best practice for creating the sun procedure. It's simply to choose workbook from the drop down menu in the top left annual, then presented with an automatically generated some procedure on the default event. The opening of the workbook is in fact, the one that we want, and let's begin by defining the variables that will need. So the first thing we want to track is the day of the week. And that, of course, is an integer. So let's declare an integer variable dim in today as into job. And then we want to work out the appropriate orginal for that day. And this, of course, is a piece of text two letters. So let's dim str ordinary a string. So those are the two variables that will need and the steps that we want to perform. Let's just put some comments in for that. So step one, extracting the day from the date inside our into day variable. This is going to be let because it's a data variable and today equal, and then we're obviously gonna have to look inside V B A to find the appropriate function. So we start with b b a. Then we look inside the date time class, and here we'll find Okay, this is a function, and it simply requires a date. And of course, the date we're interested in is today's date, and we're going to look inside the same class. So bebe a big time. And this time we simply warms date, and that will give us today's date. So we're extracting the day portion. Oh, the current date. So now on to our select case structure. So you say select case and then nine times out of 10 you'll put the name of a variable whose content you want to evaluate. And in this case, of course, it's game day, and then you think of all the possible values on what you want to do. If that's the case, So what we want to do is to populate str orginal with the appropriate orginal based on the day. So let's start with S t the case one that will be S T. And you can specify several possible values on one line simply by putting a comma separated list. So 1 21 and 31 would all require Esty is the ordinary So the next line you simply put what you want to do. If that's the case and what we want to do is to populate str orginal with the string s t. And as always, I'm just gonna copy and paste because it gets very similar from here. So hold down control and drag the selection below the original. Now let's do nd so two on 22 will require nd and then finally rd So our d the third and the 23rd And for any other number, we simply want to put th there's a special way of specifying every other possibility and that his case else so none of the above the truth do the following Let s steal older. No, you call th and to close a Selectric. A statement used the syntax and select. So obviously we could have used an if else if structure for this. But you can see how neat and concise this particular way of doing it becomes on. The reason is, of course, we're just evaluating one variable. And then we've got all these possible outcomes for that one variable. So this is where select cases more appropriate than if else it if an else if is great when you're testing for different things each time. But if you're just evaluating the same statement or the same variable than select cases, much more efficient said the final thing we want to do is to output the date using the old , you know, into a cell, a one. So because we're gonna work with this sheet which is called welcome Best practices to give it a name, a code name, as it's referred to. And you do that in the property sheet just here on I'm just going to use the prefix s age, so shh, welcome. And then we can refer to that sheet by that name in our code. So we simply say that states welcome don't rains and it's a one. It goes in close thought, value equals. So the first thing we want is in today and percent. Then we want the orginal str orginal and then we want to tag on the rest of the date. So to do this, we can use the format dollar function on just to show you where this lives, we're going to view object browser, so it's obviously part of V b A as opposed to excel itself, and it's in the strings class. So here it is. Let's formats and former dollar. They do the same thing that the advantage of format dollar is. It returns a strength. If you've used the text function in Microsoft, excel in formulas and format. Dollar is very similar, so we can finish this off by saying VB a doctor strings I thought formats Golan. A man in parentheses. We say what we want to format, which, of course, is the date. So that's the V B. A state time state coma. And then in quotes, we put our format so we'll need a space. Then the month, which is four EMS for the full name of the month than another space and then four wise for a four digit. Yeah, and that's it. So we've can caffeinated together everything we need to compose a date that contains the appropriate orginal on. Because we put in this inside this workbook in the workbook, open some procedure. It will kick in automatically whenever you open this workbook. So let's test it out. We simply need to close stave, and then we can close the workbook and reopen it, and then goes out eight, which is the eighth of July 29. 3.4. For ... Next Loops: loop instructors enable programmers to execute a block of code repeatedly and in VB, a particularly useful the process in all objects within a collection. We'll begin our look at loops with the for next loop. So 03 BB a control statements. Let's open up 04 for next loops. And then it's going to the visual basic editor by clicking on the visual basic bump. And here we can start buying inside in the module. Let's rename it I m full next, and then it's licking the code window on the right here on insert a procedure i e. A macron, demonstrating out four innings. Lose work. Let's create a short macro called interest and will use the speech facility to speak an intro to the user. So the first thing you need to do is to define a counter. And there's a bit of a tradition in all programming languages that you call the counter I. So you'll find this in a lot of code that you come across where encounter is being used. I, which obviously is short. The indigent is the name of your counter, so let's stay with that tradition, and we have dim I as integer. So obviously a counter is a whole number, so it's either gonna be integer, or if you're dealing with big numbers, then long then the basic structure of the loop is four. Counter equals start value to end value, and it's closed with next counter Say, in other words, you repeat the name of the counter after the word next to indicate which of your loops is terminated on that line. And then, in between those two, you could have as many statements as you like. And to make best use of the for next loop, you'll normally utilize the counter in some way within the code block. So the example that we're going to use is going to use the counter I because that's the counter variable we've just declared. And the stone value will be one, and the end value will be three. Then too close eyes the name of the counter. So next counter obviously should be next. I on this just to remind you so that we know which loop is terminating on that line because it's possible you could have a one loop inside another. So are we going to do is happily feculent, don't speak, Don't speak. Text is going to be simply I so well here, our robotic voice saying 123 Then of course it stops. And then after it stopped, we can say application, not speech, not speak text it is. So let's test it. Want to three. Hit it. You can also loop in reverse. So let's copy this code and just modify the copy so that we have a countdown. As in 54321 we have lift off. So to do this, we use five as thes start value on day one as the end in value. And then we'll just change this to we have lift off and let's also rename it. However, when I'm running, we have lift off. It goes straight into this line on the loop is missed out completely. The reason for this is that in order to get from the start value to the end value, B B A will waste at a one by default. So what we have to do is to override that default and tell it toe add minus one, because otherwise it's never going to get from 5 to 1 and the key word for doing that is step, so we put out step to minus one. Then it starts at five AGS minus one, which takes it down to four and so forth. So when we now run is fine for three to one, we have lift off. So now that we've had a look at the mechanics of the for next loop, let's create a macro, which does something a bit more useful. Let's say that we want to have a macro that creates a workbook that has numbered sheets as you know, the default names for worksheets in Excel Our sheet one sheet to etcetera. Let's say that we want a macro that will create a workbook and then rename the sheets purely with numbers. So just 1234 and you might do this if you, uh, need workbooks with a lot of work feeds on, do you want to just make the names as short as possible so that the tabs all visible and easy to navigate? So the safe, a bit of type, and I'm going to start my copy in this macro just to remind if you hold down the control key and then dragged the selected text to a new location. It will copy. So we'll call this one numeric street names, and then we'll take out the unwanted lines so the lower limit will stay as one, as in worksheet one. But we need to calculate the upper limit. We don't know how many sheets the workbook will contain. This depends on the user's settings. So let's declare another variable didn't indices as in Georgia. And as you probably know, the limits are. One is the minimum and 255 is the maximum, and we'll also need a variable toe hold, a reference to the workbook that were created. So then W k b new that's what book. So we create a new workbook and put a reference to it within wk be new. So because this is an object variable, we used the keyword set bkb new equal to application. What books don't bad. Having created a new workbook, we can now populate our into sheets variable because this is a data variable use let instead of set, even though, as you know it's optional. You cannot make it so let's intercedes bqool don't bkb new don't work sheets don't count. So now we can take in sheets as a limit. So we leap in from sheet one two. She last if you like, and within the loop, we simply want to change the name parameter. So that's going to be lovely. Katy. New work fleets and in brackets we can use our counter because this will obviously be changing each time this line executes. And then we simply say, Don't name equals I. So the name of the sheet is the value of the counter and nothing else. So before I test it, I'm gonna go into my Excel settings for my own options, and I'm going to change this value. So let's make it 12. And then that's running on Macro America Street names run, and there's my new workbook and, as you can see, has 12 sheets which have these very short numeric names. So that's a quick run down off how to use the for next loop on. As you can see, its main benefit is that within the loop, you can utilize the counter variable in lots of different ways in the next video. Well, contrast this with the four each next loop whether focuses simply on Lupin through all the collections, within an object on where you don't particularly need to use a numerical counter. 30. 3.5. For Each ... Next Loops: In the last video, we saw a computer for next loop to look through all of the objects in the collection and how you could use a numerical counter to your advantage. If however, all you need to do is to process all of the objects in the collection, and you don't need to have this numerical counter at your disposal. There is a better and more convenient way, and this is the for each loop. You know, 03 folder That's no open up 05 all each next. And let's go into the visual basic developer and create a macro. That being said, I want you on, Let's rename it, I m pull each next on. Then let's just click in the toad Window rights and create a macro by using insert procedure. Let's call it sales workbook or just dumped U K B Open. And what we want to do with this macro is to find out whether a workbook that's required by the macro is currently open. So what we'll be doing, it's to look through all of the open workbooks and Steve weather, but they get the matches, the name of the workbook that our macro needs toe have open. So with this technique we won't need a numerical counter variable. And of course, the object in question is a workbook. So let's take that. Very then, as we lived through, each of our workbooks will want to test to see if it's the one we're after. So let's have a boot to put the results of that test. And then finally, let's create a constant into which we can plate the name of the world. Look, we need to be open rather than code in the string later on. And although it's not strictly required, let's set bln open toe false. This is the default value in any case, but it was just emphasize that BLN open as the value false before the loop starts. And then after the Liu Ben's, we contested to see whether it still contains the value forced. So now let's have a look at our Lupin structure, so let's put in the generic syntax first. So, of course, in our example, the object variable is W K B. Each on the collection. Do you think the world to be open an extra and that is all we need to talk to instigate its structure will automatically place each of the open workbooks into our variable. And then within our four each next loop, you can examine that workbook just whether it's name matches our constant. So here, of course, we'll need an if statement. So if it does, then we set our Boolean variable to truth. And then there's no point in continuing with the loop because we found what we're looking for. So to exit loop, you can use the statement exits four and then we closed the if statement. So that concludes our loot. And by the end of the loop BLN, Open will either still contain false because the workbook hasn't been found or if the workbook is found. If there is a match on the name, then it will have been changed to truth. So after the loop, we can now have an if statement to test the value inside bln open. So we'll say it equals false. Then we want to put up a message box to say we can't find the file that we need and then for the else on we close out its statement. So now we contest down macron at the moment. The required workbook isn't open. So when we run up comes the message workbook not open. Let's no go back into our folder on Open the workbook. So here it is, sales that XLs X and let's run the macro one small. And, of course, there's a typo, If you spotted it well done. So that doesn't say XLs X. I'll just change that over and it finds the sales workbook, and we're good to go. Let's just close it down for good measure on now it should say no. Found again, but not open cannot continue. So that's a simple example of where you'd use for each next, as opposed to just the ball. Next, where all you want to do is to loop through all the objects in the collection. You don't want to make up. You don't want to do it in reverse. You don't want to do every other one, and you don't need any merit counter. So and that's the case. This is by far the best structures, Peters 31. 3.6. Do ... While Loops: in a do while loop, a series of statements have performed conditionally. While the condition is true, the statements have performed. As soon as the condition ceases to be true, the loop stops to see how it works. Let's go into 03 BB a control statements and open up 062 Wild loops on What we want to do here is to have a macro, which produces a unique list of product range names. But to make life more interesting, we don't know where the product range column is going to be. So the approach we're going to take it's too start in a one. Andi. While there's data in the cell, we'll just keep moving right. A person would find itself with no data. Then our loop will stop. We'll stop moving right as we move right will test the value in each cell. And when we find the string that we're looking for, which is product range, then we'll stop. But we did find product range will exit loop prematurely. If we do get all the way to the right, then that indicates that we haven't found the column that we're looking for. Let's plant a visual basic and create a macro, so we'll insert module. I'll just rename it I m product ranges. And since we're going to be manipulated in this worksheet, it's a good idea to give it a code name. I'm gonna call it estate Sales, and then it's uncertain how micro inside Procedure Let's go Live product range list. Let's begin by declaring the constants and variables that will need. So I first opened the constant pain. The string I will unfold in other words, then will define a range variable which will start it a one and then just keep moving to the right, and then we'll have a 1,000,000,000 variable so that if we find the string that we're looking for, we can set out to prove if the column that we're looking for is found will then create a workbook and then will create a range variable, which will point to the first column of that new workbook so that we can copy across the data and remove duplicates. So the first operation we want to perform is to search, starting from anyone for the column heading, which is str range. So we begin by point in RMG sales to sell a one of our worksheet and then before we start looping, will ensure that BLN found is set to false explicitly. So now we come to the loop and the do while loop has a very simple structure. So we have the keywords to while and then we have a boolean test, one which has to produce the result. True or false? So here are test will simply be whether there is some data within the current cell on. To test this, we can use the BB. A information function is empty, so we obviously don't want it to be empty. So we used the negation keyword. Not so while not and then we're looking inside b b A gun information dot is empty on what we want to test is, of course, RMG sales felt value. So that's the shell of our do while loop. And the first thing you should put inside any loop of this kind where it's a conditional loop is a statement. What will stop it from being an endless loop says you get into the habit, perhaps explicitly have just put in a comment in and in this case, the thing that's going to stop this from being an endless loop is the fact that we're moving one cell to the right. If we stayed stuck on the same cell, the league would never end. So to move one place to the rights, we just reset our energy sales and we reset it relative to its spectral equal foreign girls . It was the upset property which has two directions vertical horizontal. So verdict. We want zero. We don't move down. It's all on horizontally, positive one so that we can move on staples. So that's a very potent line within the loop that will stop it being an endless loop. Then, before we move to the right, we want to test the value in the cell to see whether it matches str range. And if it does, we set our 1,000,000,000 value to true and we stopped looping. You can do that using exits do. By using this statement, it means that orangey sales will remain stuck on the correct column so this line won't kick in because we actually exit in the loop as soon as we find what we're looking for. So once the live completes, we can then have an if statement to see whether we did in fact find what we were looking for. So we tested in BLN found am oldest say required column, not found that limits. We want to copy it and removed implicates. So this will be the else part of our if statement. So we'll need a new workbook. And of course, we've got a variable for that purpose. W cable news And then we point rnd new to the first column of that workbook. Probably come across this syntax when using formulas in Excel. This means the entire column. It's when we paced the data into It's can strike the primitive, of course, the debate, and you don't need todo even count before you do one line copy. Another thing We copy, of course, isn't simply RMG scales, because if you remember, our energy sales is point into a single cell. So it is our energy sales doctor entire column, but Poppy on then we specify destination. Which, of course, is our energy new then the relieved implicates statements is very straightforward. That's are in the new don't remain duplicates, and the two planets has been eating festival and of course, there is only one column, so we can just say Call him one on whether our data has headers that you'll see we have three possible options. These are Excel, Constance. And, of course, do you have a headache. And we've done the whole column. So it's Excel? Yes, and then finally, let's display a message to say the operation is complete and then we close out a statement with and if so, let's save our changes and do some testing on there. You can see it creates, Ah, new workbook forests on. We've got operation Complete message. So the next thing we contest is to move this column to another location something and just paste is after. And then when we run, there should be no change. You can see, not still looked before we leave this topic. Just to point out variations that you'll encounter is because do while loop also, while we're so we dispense with do on. Then there's this made up word, which is while end and it's contracted to wend so you'll find while wend loops, which virtually the same thing. The reason that the do loop is better is this nice facility that you have to be ableto exit the do loop just with one line exit. Do if you want to do the same thing in a while, Loop in a while. Wend loop. What you have to do is to actually put a label on men, go to that labels. It's a big gets a bit untidy. I'll just show you how it's done so well. Comment that out and then we'll put a command. Go to that school, our label output, then to put a label in B B A. If you type the label that puts and put a cola that then becomes a named region, which you can men branched it on. It's not really recommended. It gets very untidy. So let's just test that it works on. You can see it does. But as I said, um, go twos are bad news, so don't use them. So I'm gonna undo all of that and put it back to the way it waas and I'll keep undid. So there we are. We back without do while and loop on. Then, just before we go. Just one other variation. The fact that this test can actually be done either before the steps inside the loop all after, so I can actually drag this to the bottom here, and it will still work. The important difference between the two positions is that if you have the test after the loop, as opposed to after they do, then all of these steps will be performed at least once. They guaranteed to be performed before the test kicks in, and I'll show you where this might be useful. In this circumstance. Let's go back to the Work Street and insert a blank column. So if we run the code now, of course, the test is performed right at the start, so it finds the empty cell straight away, and none of this actually happens. So when we run the code, you'll see it says the required column hasn't been found. But if we want to place this test at the end, this means that this line will execute before the test kicks in on. Therefore, it will find some data, and it will eventually find the cell that it's looking for. So that is the other reason why I would recommend always used do while loops because you've got those two really useful facilities. You can exit the loop prematurely using exit Do on. You've got the choice or where to position the test, and you can just do it strategically, depending on the circumstances that you expect to find. 32. 3.7. Do ... Until Loops: in V B A. The do until loop achieves exactly the same objective is the do wildly. It's simply another way of phrasing exactly the same test. Let's have a look at how it works by going into 03 on, then 07 Do until so here. We've got exactly the same workbook that we had when discussing. Do while, and if we're going to visual basic, you'll see you got the same macron. So here's the do wild section and what we were saying with this test with Basically, let's start on a one. And while we don't have an empty cell, keep moving to the right as far as testing to make this into a do until we simply replace while not with. Until so now we're saying, Start on a one and keep moving right until you find the blank cell. And as you can see, this is exactly the same thing. So when we run the macro, we get exactly the same result, and there's no real preference to use in either approach. Simply use whichever one you feel seems more logical. So let's now go in and right and you until macro just so that we can practice that syntax a bit further. So we'll inserted your model and I'll rename it. I m do until and then we'll come insert a macro into the code window. Inside, procedure on the example we're going to use is to obtain an email from the user. So what we want to do here is to display and input box. Andi forced the user to enter their email address. We might do this if we had a macro that was running in the context off a company only scenario. So only members of a particular company would be authorized to use this macro. And we know that everyone in the company has an email address on every email address ends with exactly the same domain name, so we could start by entering the domain name as a constant. Then it's greater variable into which we can grab the person's email. So now we can go straight into our do until loop. The syntax is due until tests. I would close it with loops and for a test, we can use the string phone to test whether the string being entered and the other main and let's say that we anticipate a minimum of two characters before up there in strings. It is in the strings class. It's gonna be BB eight strings strength. And then our first we can write everything longhand Starks I kind of want. And then we have string one that string inside of which we're looking. And of course, that steel email. And then finally we have screamed suit. The string was set in full. And this is of course, str domain. So the in string function will return a number indicating the current position of the string that we're certain ball Onda. Let's wait toe have a two characters before that string. A decision will be three now. Normally, whenever you do a conditional Lou, you always make sure that you don't end up with an endless loop. But the patient is pretty much what we want, because the user has the ability to get out of the endless do simply by supplying the correct type of email so you can go straight on to display main our dialogue box out input box. We're populating str email with what is the V b? A input box, which is in interaction that would prevent his deeds because the input box function is not the experiment on the life promo mix. Venice. Have a title, Um, and finally, let's have a default value, which can simply be str domain. So that concludes our loop. And then after the loop, we can be confident that STL email contains an email address. So let's end by saying and then it's tested. So as long as I fail to enter about the amount address, this just isn't my screen and it goes a citizen. Put in an email address, comes the message box. So just to reiterate that, do while and do until something how you phrase your test. So here, if I decided to change this to do, while I would simply have to change my test just to inverted. So instead of saying greater than or equal to three, I could say do while less than three on when we test it, we should get exactly the same result so it stays on screen until you enter your email 33. 3.8. Creating Arrays: an array is a special type of structure, which enables you to stall several related items in a single variable to see how raise work , let's go into 03 baby and control statements and then open up was, you know, eight. Creating a race and we'll go across the developer and into the visual basic editor. And what we're doing this example is to create a macro, which represents the beginning of an application where we want to validate the user based on that block in name. So we'll capture the log in name of the user and then compare it to a series of possible. So let's begin by its module. And let's rename this and then let window insert a procedure that's cool and user check that. I think it's a blessed him. That's Logan, that string. And let's say that when we write in the air, we know that there are five users that we want to be able to actually use this application . So there are Five Valley use the names now we could create by separate Constance, but one of the valley get names in each one. Expect fst or Estella unequal. This is where a raise come do it. But syntax for defining an array variable is very similar to define in an ordinary variable . The first differences that you normally start the name within A. Just to remind yourself that it is an array variable. You still specify the type of data that you want to place inside the variable. So obviously we're still talking strings, so a SDO and then move use valid to identify this particular variable. The second difference is that you then specify in parentheses the scope off the variable. That's to say how many values you want, the variable to be able to hold, and the best way to do that is to put the upper and lower limits. So if I want to have five values in my array, biological thing to say is 15 lower limit one upper limit five. So those are the only two differences. The first is optional. The flanks of using an A at the start of the name, and the second is obligatory, so in brackets you specify the limits. There are other ways of specifying the limit, but this normally is the best practice. But the upper and lower limits explicitly, and you know exactly where you are when he comes to process the values in the rain and then the third thing is not unique to raise. It's exactly what you do. For every variable you specify the data type. And of course, we're still talking string because the values in the array will be identified like a numeric index. You can look through the values in the array, so the next thing will define is a counter variable. And as we lived through when we find a match, we'll use a boolean variable to indicate that we have successfully validated the user. So the a ring that we're working with here is really a constant array, that there's no such thing as an array constant. You can use const men go ahead and define Honore with multiple values. So what we would do at this point is to populate the manually. Just to say Item one is first person. Item two is the second log in name and so forth. So obviously this is a data variable. So I'm gonna use let's s your valid and then in parentheses, specified the index and then we just do that for more times. We've populated all Sipes books within it. A gray area. The next step is to grab your booking. It's tied STL looking Let's as loving equal levy a contraction environment. And then in parentheses, we put the particular element that we're looking for, which is user name. I'm putting this in upper case, but remember that VB is not case sensitive. So now we've grabbed their years a name we want to compare with each of these. What? Ok is that this is obviously using you, Mary. And then l always gonna use I as the counter. So before I you could say, for I equals 1 to 5 because we know that there's a limit but the best bit When it is two years to special array functions l bound and you bound. And obviously the l bound functional calculate the lowest value and the U bound function the highest value. So if these were to change, your syntax would simply pick up the new values. So I won't. L bound s t o valid. You bound It's a valid and then inside the loop will conduct a test and will use the I variable to pick up each of these values. So str valid and then in parentheses replaced the I. So this means that as the loop runs the I will be substituted or a number between the lower and upper limits as the loop continually repeats. So if str valid I equals str log in then obviously a match has been found and if that's the case, we change bln okay to truth And then there's no point in evaluating the other elements so we can exit the loop using the same tax exit full. Then we end our if statement and then we end our for next loop Repeat in the name of the counter just for clarity so that we know which loop is being terminated after all, Lou Ben's bln. Okay, well, either contain false because we haven't found a match Horrible contained. True because we have found a match so we can now test the value of BLN. Okay, Andi, for the moment will just display the appropriate message using the message box function on . Then the els. Will this be a similar message? This whole down control in drag and then we close our structure with end. If so, I am currently Levine is Grant Gamble. So when I run, I get my welcome to the application. If my name wasn't somewhere that balance strange that then I get the alternative. You are not authorized to use this application. So this is using what? In which you know in advance All the people need sports way. There will be occasions where you don't know this on. And this is what I think you're right on. We'll look at how these work in the next video. 34. 3.9. Dynamic Arrays: In the last video, we looked at how you can create an array, which cases for unknown number of values. Sometimes, however, you're not sure how Maney values your rain needs to cater for. And in this scenario, you use what's called a dynamic array. One who size can vary depending on the circumstances. Let's look at how dynamic and raise work in the training folder in 03 BB A Control Statements. Let's open up 09 dynamic arrays and let's straight away go across the developer visual, basic and instead of module. And we'll just rename this and dynamic arrays. And what we'll do in this example is to create a metro, which will email the report to one or more email address is entered by the user. Since we don't know how many emails that user will enter, we'll use a dynamic array to capture all of the emails entered. So let's begin. By creating our macro, we activate the code window on use, insert procedure, and let's simply call it E mail reports. So let's begin by creating all of the variables that will need starting with the report itself. So, first of all, I'm gonna create str path. This will be the location off the report that's gonna be emailed. The command will be using to email. The report is workbook dot send mail. This particular command can be used on a closed workbook. So for that reason, I'm gonna dim wk b reports As what book? Next. We'll need a variable to capture each of the e mails entered by the user and then to store multiple emails will need an array. So this will be a str e mails and then to declare a dynamic array, you simply put open and close parentheses after the name of the array with nothing between them. So in other words, you're not specifying the limits of the array, but we will specify the type which, of course, it's string. And then finally, I'm gonna have a counter to count. The number of emails on this will assist us when we come to loop through the dynamic ray and also to change the dimension of the dynamic rate as the progresses and obviously into emails. His interject would also be fine. And now we need to create a do loop which will force the user toe either enter an email or click the cancel button and because we want to display a dialogue at least once, will have the style of Do Loop, which puts the test at the end after the loop, as opposed to after the do So. This is going to look like base. Do several lines of code. Then, after the loop, we put out test on. Basically, we're interested in testing STL email. Whenever the user clicks on the cancel button, STL email will return null strength. If the user enters an email that, obviously str email will not be blank, it will contain a strength so our test can simply be so. As long as this test is true, we basically want to display an input box and capture the results off the input box inside our str email variable. So this is going to be a let's SDR female equal CD eight on interaction they put locks on. We then need parentheses because we have two statements on the line and our obligatory parameter is prompt. And then let's enter a default value. I'm just going to use a fictitious company called Steady Smart. So we'll say at Smart Kid Okay, close brackets after this input box has been displayed, str email will either contain the null string if they click the cancel bomb or it will contain whatever email they've typed. So we can now test the value off str email. If they click. Cancel. We obviously just want to end the whole process so we can simply say exits, dude, And remember, this is one of the benefits of using the do loop. It does have an exit option. If the user has impressed the cancel button, then we need to capture whatever they've entered into our array variable. But before we do that, let's add one to our counter. Then we need to increase the capacity of our dynamic array by one slot. And here's the syntax for doing that. The reading keyword obviously relates to the initial din keyword, and it's used with dynamic arrays to change the bounds to change the number of slots. But in the array, the key word preserve is also required because without it, each time you redeem your array, all the contents will be wiped, so preserve means preserve the existing contents and expand the capacity then to expand the capacity. We start with a low limits of one, and we increased to the upper limit as specified by our counter. So every time the user adds an email address, the counter goes up by one on the capacity off. Our dynamic array also goes up by one. And then finally, we need to actually add the value to the dynamic array. So here we're simply using LNG emails as the index off our dynamic array, and this will ensure that we're always targeting the last element in the array, the one that's been created as a result of expanding the size of the array. So we put str email into the last slot of the array each time. So this is what's happened in inside our loop. And then when the user clicks the cancel button, we contest the value of LNG emails. If LNG emails is zero, this means that the user canceled straightaway without type in any email addresses, so we can simply say you fell into emails equals zero. Then we can display an error message. Otherwise we can go ahead and send the report. If you're sending a report via email, it's usually best not to use a macro enabled Excel workbook. So what I'm gonna do is just to quickly create a new file unordinary file. I'm going to save it in this same folder and give it exactly the same name as this file. And of course, the anything they will be will be different. Is the file extension at the end? So this could be our reports, Which means that all we need to do inside str path, which is the location of the report, is to have exactly the same path as the current file which we can pick up by using application dots this workbook, but with the file extension Doc XLs sex instead of dot x l s m. So we can let s deal equal complication for this. What book? Not full name on that will pick up both the path and the name and then we can simply wrap our replace command around it. So that's Phoebe A don't strings don't replace on. This is the expression that were working inside the fine string dot Exe Dallas em on the replace Don't. Thanks. Thanks. And then we opened the workbook. So this time we're using sets on the final name will simply be That's your pulse. And then to actually email the report. We have this useful method or function called send mail on. We specify our recipients, which of course, will be everyone captured in str Females. This parameter can either be a string or an array of strings, and then we'll just put a subject that's terminates our If statements on we could also just display a concluding message to say your report has been emailed. So that's our code. Let's just save let's put it to the test. So first of allow cancel. And sure enough, we have operation cancelled by using no email sent. Now let's try entering some email addresses. So I've entered three email addresses. No, I'll cancel. So I have Microsoft Outlook set up is the default email client. This message appears I could probably get rid of it in the outlook settings, but let's not get into that right now. Onda. Of course, if I were to click on deny, it would generate an error message and the program would crash. And similarly, if you don't have an email client set up also, you'll get an error message so we've got a real sort of bare bones piece of code here is not by any means, production ready, say, for the moment, I'll just click on allow and it goes ahead and sends the emails and gives me my okay message. So, as I said, if you are doing something like send an email the only a lot more code than this on once we've discussed error handling, well, actually come back and revisit this on Make it a bit more robust. But for the moment, that basically is how you work with dynamic race. This is how you define them, and then you've got this very useful statement or redefining them and change in the dimension changing the number of slots that the array actually has. 35. 3.10. Utilizing Arrays: in this video, we'll get some more practice on working with the Rays. I will have a look at a useful technique that generated BB eight code within an Excel worksheet using ordinary Excel formulas. So in the 03 Debate Control Statements sub folder, let's go into the last file utilizing a raise. And when you open this, you'll see that what we have is a series of country names with the capital city. What we're gonna do in this example is to create a quiz, which displays the question along the lines of what is the capital off China and then allows the user to respond once the user has answered all the questions will just say you have scored X out of a possible why. So we will need to correspondent a raise one to hold all the countries and one toe hold all the capital cities on the technique I want to show you is how we can produce the code that levy a code to populate these two array variables using Excel formulas. So the first thing I'm going to do is to just put numbers. These will be the index numbers for our array variables. And then here I'm just gonna put a formula to populate the array for countries. And here I'll put another formula to generate the code or capital cities. So as with all formulas, we type equals. And let's say we're going to call our array a str countries. This is a strength. So in an excel formula it has to go in close a str countries. Then only the opening parenthesis followed by the number. So to do that, I simply closed my strength and used the concatenation operator and percent and then click on C two. So that gives me the number inside parentheses. Now close my parentheses. So it's back into string. That's the clothes in parenthesis equals And then I need quotes. I'm already inside quotes. So how do I specify in an excel formula that I want quotes in sight? Quotes on The answer is basically you double up. So two quotes means I want to have a quotation. I want to have double quotation marks inside my strength. And then, of course I gotta close quotes. So that's three double quotes together. Then we need another and percent and then we need the name of the country, which is a two. And then we finished off with the clothes in quotes. So that's open quotation mark double quotations and then the closing quotation mark. So let's press enter and that's the pattern Will have. So the name of the array, the index, which is one, equals men inside double quotes the strength so I can just copy that down and then do pretty much the same thing for the Capitals. So I'm just in a copy of this formula. Paste it here on, make the necessary changes. So this now becomes Capitals. Si two is the same. That's our number, but a two needs to become B two. There is a nasty capitals waiting for Beijing like and then just called me that down. So that's an example of how you can generate BB, a code using Excel formulas. So let's now go in and right now Macron and I'll call this model M Capital Quiz. And let's call the macro itself. Also, Capital Quiz and, as always, will begin by the Konar variables, beginning without to array variables. And for this example, I'm only gonna put the 1st 3 just for speed so that's one, 23 spring, and then we do the same thing for Capitals. Then we'll need a variable to hold each of the answers provided by the user. We'll also need the integer or long interject to hold the schools and finally leave the counter, which will enable us to loop through the arrays. So now we need the code which will populate these two race and as I've said for speed and then again to use the 1st 3 values so we can go back to the work feet on, just copy the 1st 3 and then we can do the same four capitals, and I'm just gonna press the tab. Key to Tab. Those three lanes in so many quiz will simply consist of a four next loop. So for I equals. And as you probably remember, when Lupin through an array, it's always best to use L bound and you bound. And, of course, it doesn't matter which of the two variables I use in this context. I'm still going to get the same numbers generated from 1234 If I make any changes up here, then l bound a new valuable pick up those new numbers. So now we want to display the quiz question and capture the answer inside our str honza variable. So the countable insure that we display each of the country names. Uh, at the end of our question, because this is a question. Let's stand just tag on the question and then we want to check to see whether the answer captured inside str answer is the same as the corresponding slot within str Capitals as specified by I And on this line, of course, underst missing the clothes in parenthesis. Just put that in. So to test the answer, we, of course, use in a statement we had one to the school and of course we won't need an else if they given incorrect answer, we simply do nothing. After the entire loop has finished, we can then display a message box telling them how well they've done. So you have scored and then we tag on LNG school out of a possible and then to calculate the total number of questions. I've just taken the value of I but remember when you get to the end of a ny loop, I was actually one more in the upper limit. So for that reason, if you're gonna use I because the loop has terminated, you'll need to subtract one. So now we can test our quiz. And I'll just warn you that if you are doing this type of thing, these comparisons a k sensitive. So if the user were to type in the correct answer, but in the wrong case, they would get one added to their school. So simple way of correcting this is to use l case or you case lower case or upper case to convert both of the things being compared to exactly the same case. I'm going to use the longhand as usual TV Adolf Springs L case. And there's a new case would work flying as well on close brackets. And then I'm just gonna put exactly the same thing on the other side. That way, both items being compared and in exactly the same case. So let's test our code. And here, for some reason I've just got into singular instead of plural. So that's just reset on correct that too cool and try again. Lower case Beijing and, uh, the Case deli And so low case Washington D c enter. He's got three out of a possible three, so you can see that the I minus one has given us the correct number. So we have a very rudimentary quiz. But the main thing I wanted to show you in this video is how easy it is to generate BB a code using very simple Excel formulas. 36. 4.1. Sub Procedures and Module-level Variables: in this section, we'll discuss the use of modular code as you write my grows, and as you Macron's become more complex, you'll find that they have more and more lines of code. And rather than having a single sub procedure with hundreds or even thousands of lines of code, it's far better to subdivide your code into separate modules. And there are two types of modules, some procedures and functions, which will discuss in this section. Let's begin by talking about sub procedures. So in our training folder, let's go into 04 procedures and functions and I'm not 01 Some procedures, module level variables. Then let's go into the visual basic editor and you'll see that we've got a single model, which contains one of the macron's that we roads earlier. Now, if this macro or more complex, what we would typically do is tow. Have each of these sections as a separate sub procedure. A natural will be doing in this video just against them. Practice on how to work with separate sub procedures, So the first thing will address is the question of variable scope. This basically means from where, within your code of variables content is gonna be accessed at the moment. All about variables that contained within this sub preceded this macro call copy data. So their scope is currently local. In other words, local to this specific sub procedure. If there are any other sub procedures within this mortal, they wouldn't have access to any of these variables. So the first thing we want to do is to take all of these variables and changed their scope to model level. That's very easy to do. It's simply a question of position. If they're positioned inside a sub procedure, their local to that sub procedure, if their position outside, holes up procedures that will make them module level so I can simply cut them and then taste them here simply by move in. The midst of this position means that if I have several sub procedures within this mortal, all of them will have access to exactly the same variables. Although position is the only essential requirement or declaring a module level variable, it's also best practice to use the keyword private as opposed to them. Them is perfectly legal. It works fine, but by using the keyword private, you're emphasizing that these variables have a scope which is module wide. So I'm just going to use finding replace by highlight the variables control age replace and I'm replacing key work with the key Would private on replace all within the selected text. We can also use the private key word when declaring Constance, naturally, the word private count replace Const So it's used in addition, So we would simply have private cost as opposed to simply cost another convention. Worth following is to indicate the scope of the variable within your name and convention. And typically what you would do is to proceed all module level variables with the letter M so M w K b source, for example. So I'm just gonna use finding replace to achieve that. Let's start by having W k replaced with m wk. I'm gonna make sure switch on match case for this. I don't think that's gonna mess anything up so replaceable that takes care of workbooks and worksheets. Then we have our Engy. So our injury becomes m g. Replace all and finally we have strings on this time. It is very important that we have the match case switched on because those you got words like strings. Um, on here again, we've got the parameter name string, which we wouldn't want changed. So Match case will take care of that I replace all. So now all our variables which have the model levels, go on our prefixed with the letter M. And at this point, it's definitely worth testing to see whether our code still works. So yes, it does. So we haven't broken anything. Let's just close that, Uh what book? Let's now turn to are some procedures. So we'll say that this current sub procedure, the macro itself will be the main sub procedure from which will call all of the others. So I'm just gonna change the name to Maine on then the first, some procedure that will modular rise will be this section. Somebody cut this and has paced. It is a separate sub procedure, so I can obviously type it, but I'm just gonna use insert procedure and the weight to distinguish between a macro. Some procedure on a sub procedure which is called from within the macro is the scope. So it becomes private, and I'm gonna call this one, use the input, and then paste the code of my copy have in place this code in a separate sub procedure is that important that I call it from the main sub procedure. So let's just get with name on here. I can insert a cool standards if I simply paste the name that's sufficient to cause the sub procedure to run the clarity. It's useful to put in the keyword call just to emphasize that you are calling a separate sub procedure on that line. Then let's do the same with the second section Open source workbook. So again, I'll cut away code crazy new private sub procedure. Call this open scores bkb on paste in the copy code. Then we need to call this new sub procedure and paste. Then let's have just two more sections, so all of this could be our processing. We'll call this process data again. We have a private's a procedure process, states, uh, we placed in all that code was tapped. This in Teoh make it a bit more readable. Do the same here on indeed, here This is cool. And then finally, let's have finalization on Let's cool finalization so that complete so model organization on. Although it's slightly overkill because obviously this is quite a short piece of code. It hopefully just gives you a taste of how you structure a typical application. So as you have more functionality, you have basically separate blocks of code separate sub procedures for each section of that functionality. Another thing you may find useful is just to change the mode to procedure view. This basically means that you just work on one procedure at a time and in the top right here from the procedure dropped down, you can switch between different procedures. I'll just go back to, uh, all more your view. And of course, I should just test that it still works. So this is now the only macro that we have within this model. Just to prove that if we go back to excel on Mac Rose, you'll see that only Main is listed all of the private. Some procedures are treated back rows. So let's run main do this time, and we still get the same results 37. 4.2. Static Variables: In this video, we look of variables that have a static scope, static variables, the equivalent to session variables found in Web development to see how they work in the 04 Procedures and Functions Sub folder. It's open up zero to static variables. And then let's go into the visual basic editor and to illustrate the use of static variables, will write a macro, which asked the user for his or her name. But if the user then stops the macron and runs it again, we don't want to ask the user for their name once more. We want to retrieve the name that was entered the first time, and this is a classic example of where you would use a static variable. So let's insert module. I'm just going to rename it M Static Variables and then let's insert a macro. And then let's define a couple of variables. First of all, I want to hold the use his name and secondly, one to keep track of how many times the maker has been run. When the program runs. If str name is blank, we'll ask the user to enter his or her name. Then we'll add 12 hour run counts. I'm agreed. The user and I forgot to put in the prompt for the last one. So was gonna stay without convention on fixed that on this time will simply say hello and then in Canton ate the name of the user. So as it stands, every time we run this macro STL name will be blank. I would have to ask the user for their name. Let's just test that. Let's just put some brackets in here. But of course, if I run it again, it's for gotten everything that I entered before, And I'm back with the micro having been run a single time. So to convert these variables into static variables, you have two choices. The first is to replace the dim key bird with the keywords static, and the second is to place the keyword static in front of the some procedure definition. This will make a lot variables within the sub procedure static. So here I'm just going to use static twice. So both of these now static variables. So I'll run the macron from Excel. I entered my name once. Macron has been run a single time. And then, when I run it again. It remembers my name and it also remembers how many times the macro is being run. So every time I run it, that figure will go by one that my name will stay inside the static str named variable. So as you can see, it's very simple to implement this technique and just remind you of the two ways off doing so. I'm just gonna undo both of those and place to keep work static in front of the sub procedure definition. So we started from scratch once more my into my name. And then each time I run the macro, it remembers my name and that's 12 The number of times of the macro is being run, and that's it. So that's how static variables were used in V B A. 38. 4.3. Passing Parameters: we've already seen how we can make our code more modular by having a macro, which calls supporting that some procedures. One way of making the sub procedures more flexible is to define parameters for them. This means that each time you call that some procedure, you can have it do something slightly different. And the more parameters you define, the more flexible the sub procedure becomes. Let's look at how this works in the 04 Procedures and Functions folder that's now going to 03 passing parameters and here will define the classic structure whereby we have one main macro that caused a series of sub procedures. So let's call that module I m passing parameters, and then that's insert on Main macro. And now let's create three subordinate on private sub procedures. So from our macro, we now need to cool our three sub procedures. And although the keyword call is optional, it's useful to put it in each time, so each of these sections were typically have lots of lines of code. But let's say that the end of each section we have a verbal confirmation. Use an application, don't speak so at the end of each of these supper sieges will want to call the verbal confirmation procedure, So let's now define that again. It's private on. What we can now do is to define a parameter variable, which will determine the message that's actually spoken. This is very similar to use in the didn't statement, but its X place in the parentheses that follow the sub procedures name. Obviously, it will be a string because it will be a piece of text that's read by application, not speech. So let's to school it best your message as a string. Once you define a parameter, every time you call that some procedure, you need to supply a parameter off the defined time with the call to the procedure. So here I can say cool verbal confirmation. But then I also have to specify str message and you'll notice that the intelligence reminds me what type of data needs to be supplied for this parameter. So I'm just going to say initialization complete, and then I'll just, uh, copy that. So we have one side procedure which is now being passed a different message each time it's called. So now inside our verbal confirmation, we can use. Str message So when we were on our macro, each of the sub procedures that's called will in turn call verbal combination with a different message each time. Initialization Complete processing, complete finalization complete. A very useful feature of parameters is that they can be optional when you define your parameters. You can define optional parameters simply by using the keyword optional. However, all optional parameters must follow the obligatory parameters, so str message We want always to be there. It's not optional, so it has to come first. But then we can have a second optional parameter on. What I'm going to use in this example is to say, Would you like to hear the message again? So this will be a Boolean and BLN repeat as Julian, and when you're specifying optional parameters, you can also specify a default value. So when the call is made to the sub procedure, if no value supplied for that parameter, the default value will be used. And to do that, you simply say, equals because I've defined in his 1,000,000,000 true and false to displayed automatically . So I'll say that false is the default. So what I can now do is to have a do loop structure which offers the user the chance to hear the message again. It's a fight, but the do as the first part of the statement This means that everything inside the loop will occur at least once and then the test can follow the loop rather than following. To do so to do the Boolean test, let's have a message box with yes, no buttons on what I always do when I'm using that structure is to define a BB variable. So dim, baby. Um, sir, as Bebe message box results unless initialize it to contain the B No. And then in our test which follows the keyword loop, we can say, while bebe answer is equal to baby Yes. And of course, at this point in time, it never can equal Yes. But what we can now say is, if our BLN repeat variable is true, will display a message box with yes and no bombs when you're test in a 1,000,000,000 as well as saying, if my billions in this case 1,000,000,000 repeats equals truth, you can simply say if my 1,000,000,000 so in this case, if bln repeat and it evaluates to exactly the same results. And what we want to do here is to display a message box and then capture the result inside VB answer. Let me be Answer equal v b a dot interaction message box with a prompt off. Would you like to hear this message again? So now we've given the user a mechanism for changing vb answer from VD? No to be be Yes. So if they click the yes bottom, the the answer will then contain baby Yes, And at this point, the loop will repeat on application, not speech will occur once more. So since in our three function calls we're only using the obligatory argument We're not supplying the optional argument. The default will kick in 1,000,000,000 equals false. Hence, this line will always proved to be false and the message box one won't be displayed. So let's just test that initialization complete processing complete finalization complete. And now let's try adding in that second optional parameter. So I'm going to do this on the initialization. Cool. And I'm saying that true. So they're now gonna have the option to hear the initialization complete a second time. Initialization complete Onda. Of course it says, Okay, finalization complete. Because what I forgot to do was to set the all important parameter. I've only got a prompt in here. I haven't got the buttons parameter to specify VBS No. So I haven't provided the mechanism or capturing VBS. So to change that, I can just put a comma continuation. Maybe on the next line. Hand used buttons. Bebe. Yes. No. So we've now got a mechanism to click on the yes button which will give us BBS inside BB answer. So let's try that again. Initialization complete. Yes, I would like to hear the message again. Initialization complete. So it's just gonna keep asking me that until eventually actually on no processing complete final invention complete when it goes on to call the next to. So that's a look at how you use parameters on this mechanism of using optional parameters. Just provides an added mechanism for giving you the maximum amount of flexibility when you're calling the sub procedure 39. 4.4. Creatig and Calling Functions: in this video, we'll take our first look at functions. Like some procedures, functions could be used to make your code more modular, but they offer one distinct advantage. A function could return a value. Let's go into 04 procedures and functions and then zero for again created and call in functions. When we look in the visual basic editor, you'll see that we have some code, which would wrote in an earlier video. What we're going to do here is to make the coat a bit more robust mindset in a function. As the code stands, we asked the user to supply the criteria that they want to use to create our report. But then the code assumes that there is a match in workbook, which has the same name as the criteria entered by the user. And then we look in the sales folder for a file off that name. So, of course, what this means is that when we run the code, if they use a types the name of a range, but which we don't have a workbook, then it crashes because it's attempting to open a non existent workbook. So this is the line that can't be executed because basically, MSD, our path contains a string which doesn't exist in the specified folder. Let's click on resets to come out of brake mode on What we need our function to do is before we attempt to open the workbook. We want the function to verify that the workbook exists, so functions are defined in exactly the same way. Sub procedures. I'll just put it at the end here. This time we need the time to be function, and again it's going to be private, and that's called a function file exists on this occasion. We won't need to define any parameters if we were to define them. They go inside the parentheses, as is the case with some procedures. But what we do need to define is the return value, the type of data which dysfunction will return when called. And we do that in exactly the same way as we define the type of variable we used to keep, what, as and then specify the data time. So I'm going to make this 1,000,000,000 so this particular function can only return true or false. Now let's move the code that actually builds the past to the file that we're attempting to open into our function. And here's the code. Just before we open the workbook, we composed a path to that workbook, so I'm just going to drag this into our function. So what this, if statement does is it takes the criteria entered by the user inside mst our criteria. And then it uses that to create a file path to a workbook which it assumes will be in the sales older. So what we now need to do is after the of statement, we need to test whether or not that file actually exists. And to do this we can use a V be a function called de i o as in directory. So we simply say, if v b a stop file system don't deal. Yeah, and we then specify the path that we're checking which causes mst our path. If this path does not exist, the de ir function will return the null string so we can simply say equals V B a Constance vb no string then. And what we don't want to do is to say our function produces false because it's a 1,000,000,000 and to do that you simply repeat the name of the function. So we say file exists equals false. Naturally, we can then say else filing this equals true, so that completes the definition of our function. So the key elements are that we specifying the return time, the type of data that the function will produce and then to actually return a value. We used the name of the function and specify the value that we wanted to return because it's 1,000,000,000. It will either return false or true, depending on whether on what we find the file we're looking for. So back in our main function, we can now call file exists. So these three sub procedures should only run if the file does exist so we can place them inside an if statement. So we would say it's filings this and we can either say equals true, then who? We can simply say if a file exists on it evaluates to exactly the same results. So file exists, then it's located all of this else. We really need to display an error message and stop the macro, so our function cool is nestled neatly inside. An if statement when that function call takes place. All of this code will run. But the key thing is that at the end, file exists will either return false or true. And then it's gonna be evaluated in this if statement if it evaluates to true, these three sub procedures will be called if it evaluates to false, then the error messages displayed instead. So that's just save our code and then tested. So if I type of string bill I know doesn't exist when I click OK, you'll see our error message kicks in required work, but not found cannot continue. 40. 4.5. Creating User-defined Functions: In the last video, we saw how you could create a function and then call it from a macro. However, functions can also be used within Excel worksheets. That's what we'll cover in this video in the 04 folder. Let's now go into 05 Creating and user defined function. If you're an experienced Excel user, you're probably familiar with the B look up function I won't will be doing in this video is creating our own variation of the V look up function, which has a pro rata feature. In this example. The look up is being used to calculate the annual bonus paid to sales people, and if we take the first person Alexander Williams, you'll see that he's entitled to a bonus payments of 1.25%. He falls in this bracket between the quarter of a 1,000,000 half a 1,000,000 but he's much closer to the half a 1,000,000. So if a pro rata system were in place instead of getting 1.25 because he's fallen short off the half a 1,000,000 required for 2.5% he'd be getting a pro rata figure. So because his sales close to the upper limit of the band in which he falls, so his bonus rates will also increase. So on our pro rata system, you get a bonus of about 2.4%. So that's kind of visual basic. And let's begin my incessant module. I will call it M User defined from. And now we need to insert a public function. I will call it be, look up pro rata. Let's begin by defining our variables. And at the same time I'll describe the technique that we're going to use to come up with the answer. Our first step will be to identify the upper and lower limits that apply to each person, said Look at the person sales, and then we want to find out which band they fall into. So the first person, as you can see Onley look up column, falls into the band between quarter of a 1,000,000 half a 1,000,000. And then, on the Return column, the column that contains the answer. He falls into the 1.25 to 2.5% bracket and of course, for this function, both the input values on the output values are numeric. So hence will use the double data type for all of these variables. Next, we'll calculate the look up range, and to do this will simply subtract the lower limits from the upper limit. And we'll do this both for the look up column. But first column. I'll look up table on the return column in this case, the second column of our look up table. These two figures will tell us the input and output bands within which we need to calculate the final pro rata figure. What we now need to do is to calculate where within these bands the figure actually falls on. The first step in doing this is to calculate the difference between the sales figure that we're looking up on the lower limits. So in this case, there'll be quite a big difference because this person sales figure is close to the upper limit. So we'll call this one DB l look up, def. Then, once we have these figures in place, we can work out our pro grants a factor, and to do this will take the difference between the look up value on the lower limit. A man defined that by the look up range. So if the person sales figure falls bang in the middle, we'd end up with no 0.5. In this example, this person sales figure is very close to the upper limit. So we end up with a figure of 0.9374 and finally will need a range variable so that we can loop through the range specified by the user. Let's not turn our attention to the other type of variables will make the parameter variables. So we compare our V look up with the built in V. Look up what we've got here. I can just click on the inside function button to bring up the function with it. You'll see that we've got a parameter called look up value. Then we've got the table of Ray and then we've got the column in. Next time we will need all three of these. We won't need the final optional parameter, but what we'll do is we'll use exactly the same terminology that Excel uses. And then finally, we need to specify the type of data being returned, which in this case is going to be a double excels, built envy. Look up can, of course, return text values. But for our function, text doesn't make any sense. The point of pro rata is to calculate a numerical output value, so our function only needs to think about returning a double. So our first task is to locate which banned matches to overcome value. So the use is going to specify the table the look up table. By doing one of three things they can drag across just the figures they could drink across the entire table, including heading. Or they can do what most people do and dragged across the column headings. So the strategy we're going to take is to start on the top left of the specified range. And then before we proceed, we'll just need to calculate whether we're in a cell that contains a number. And if we're not weaken, then just move down one cell, which will take us to the first numerical cell within the range. So when we set orangey look up, we need to look inside our table array parameter on that has been defined as arranged. So since I pressed dots, the intelligence gives me all of the members which apply to the range object on what we're interested in is the cells within the range. And to get to the top left, we say rolling next one column, index one. So that's our starting point. Then we can use a one line if statement to say, if they sell did not contain a number. Let's just move down one self so that takes us to the first numerical cell. And then we want to keep moving down until we come to a cell, which either contains a value. It is greater than the look up. Values in this case will actually stop on half a 1,000,000. Or if this person had actually sold moving a 1,000,000 we would be taken right off the edge of the table. So you also have to say, Oh, we come to a blank cell. So either of those two, we either come to assault. It contains a number greater than the look up value, or we come to a blank cell. So I'm going to use a do while loop for this, and we simply want to move down again. So after this code executes, we're either going to be on a soul within the table in the first column of the table. If the users sales figures exceeded, the maximum value will be on that blank room. So let's deal with the blank row first, because that's the simplest. If they're on the blank road, they simply get the highest rate. So I just basically need to use the offset property to move up one cell and write one cell . Then that's the value that the function will return such a return of value. We used the name of our function, and we set that equal to the value wandering turn. And of course, we'll meet and else and then an end. If so, in the else scenario we're on one of these cells on were then interested in populating our four variables to calculate the upper and lower limits of both the Look up column and return Colin. So I'm just going to copy this section. Ondas modify the code. So first we tap them in and change all of the let's all of the Dimmesdale. Let's on. We just specify what each of them is going to be. So the upper look up will simply be orangey look upto value, and then each of the others will simply get offset. So this one we need to go up one minus 10 and then to get to the return column. So if the first person this will be our RMG, look up. So to get to the upper return limits, we simply need to go right one and then for the lower we get up one and write one, So this one will be 01 on this one will be minus 11 Next we calculate our ranges and that's simply the difference between the upper and lower limits. On the other difference will need is the difference between the look up value on the lower limit, and this will tell us what the pro rata will need to be. So here we could see this person is very close to the upper limit. So we take his figure and subtract the lower limit from it. So here we using the look up, valued specified by the user on we subtracting de bl lower look up. So once we've got all these calculations in place, we're home and dry. We can calculate the pro rata figure and then from that we can calculate the final value returned by the function. Did you calculate the pro rata? We simply need to divide the look up def by the range that it forms part off. And then finally, to get in function to return a value. We used the name of the function like we did here in the positive part of the statement what will need to do? It's festival to multiply D B l return range, buyout program figure, and then add that value to the lower limit of our return section. So for this first person you remember, he falls very close to the upper limit of his bracket. So he had a high d bl pro rata of no 0.9 something on. What we then do is we multiply that pro rata by the difference between these two that gives us the programs a factor which we then add to his lower limits. So instead of just get in the lower limit, which be look up, gives him he gets that lower limit bigger plus the pro grant or amount within the band that he falls into. So let's save and test our code and to test our code. We can simply delete the original look up and he says, I run so he's inside function, and then your functions, ones that you define will be under a special category gold user defined. And there it is. You look up program. What mean set the function, All the parameters that we've defined. It is blade in exactly the same way as for built in functions. So I look at value will be too table array of there's gonna use the column methods. I'm drinking across the two column headings and pulls the column index. It's called him, too. America is so his bonus rate instead of Bean 1.25 it would have bean with you. Look up is now closer to the upper limit on his 2.5. I just double click to copy that down for everyone, and we could do one final test if I put a 1,000,000 in front of his figure. He now gets the upper limits of 7.5, so let's just don't do so. That's an example of where you might use a user defined function where you want to calculate a value within the body of the worksheet on. There isn't an Excel built in function that will do exactly what you need