Microsoft Word VBA Macro Programming | Grant Gamble | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Microsoft Word VBA Macro Programming

teacher avatar Grant Gamble

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

60 Lessons (11h 11m)
    • 1. 1.1: Welcome to Microsoft Word VBA Macro Programming

    • 2. 1.2: What's Involved in Learning VBA

    • 3. 1.3: Getting Set up

    • 4. 1.4: The Developer Tab

    • 5. 1.5: Recording a Macro

    • 6. 1.6: Adding Macros to the Quick Access Toolbar

    • 7. 1.7: Assigning a Keyboard Shortcut to a Macro

    • 8. 1.8: Editing a Recorded Macro

    • 9. 1.9 Benefits and Limitations of the Macro Recorder

    • 10. 2.1: The Project Explorer

    • 11. 2.2: Other Windows in the VBE

    • 12. 2.3: Macros and Sub-Procedures

    • 13. 2.4: Getting Help

    • 14. 2.5: VBE Options

    • 15. 2.6: Project Properties

    • 16. 3.1: Declaring and Populating Variables

    • 17. 3.2: Data Types Part 1

    • 18. 3.2: Data Types Part 2

    • 19. 3.3: String Variables

    • 20. 3.4: Number Variables

    • 21. 3.5: Date Variables

    • 22. 3.6: Constants

    • 23. 3.7: Object Variables

    • 24. 4.1: Sub-routines

    • 25. 4.2: Functions

    • 26. 4.3: Using Arguments

    • 27. 4.4: Optional Arguments

    • 28. 4.5: Variable Scope

    • 29. 5.1: If Statements

    • 30. 5.2: Select Case Statements

    • 31. 5.3: For ... Next Loops Part 1

    • 32. 5.3: For ... Next Loops Part 2

    • 33. 5.4: Do Loops

    • 34. 5.5: While ... Wend Loops

    • 35. 5.6: Array Variables Part 1

    • 36. 5.6: Array Variables Part 2

    • 37. 5.7: Dynamic Arrays

    • 38. 6.1: The Word Object Model

    • 39. 6.2: Object Methods

    • 40. 6.3: Object Properties

    • 41. 6.4: Referring to Objects

    • 42. 6.5: Selecting Objects

    • 43. 6.6: Testing if and Object Exists

    • 44. 6.7: Removing Objects

    • 45. 7.1: Events Overview

    • 46. 7.2: Document Events Part 1

    • 47. 7.2: Document Events Part 2

    • 48. 7.3: Using Built-in Dialogs

    • 49. 7.4: Creating a User Form Part 1

    • 50. 7.4: Creating a User Form Part 2

    • 51. 7.5: Event Handlers Part 1

    • 52. 7.5: Event Handlers Part 2

    • 53. 7.6: Data Validation

    • 54. 8.1: Implementing Error Handling

    • 55. 8.2: VBA Error Trapping Settings

    • 56. 8.3: On Error Resume Next

    • 57. 8.4: The Err Object

    • 58. 8.5: Monitoring Output

    • 59. 8.6: Stepping Through Code

    • 60. 8.7: Using Watches

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

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.





About This Class

The video tutorials in this course are designed to give you all the ammunition you'll need to start creating your own custom macros to automate Microsoft Word tasks and operations. Word users learn VBA macro programming for a number of reasons: some people are looking to enhance their career prospects; some are looking to save themselves time; others are aiming to save their company money and increase productivity.

Whatever your reasons for considering learning more about Word VBA macros, this course aims to equip you with everything you need to get started.

This course was last updated on November 2015 with the addition of a module aimed at providing further practice by guiding the user, step-by-step, through the creation of an interactive Word VBA solution.

Meet Your Teacher

Teacher Profile Image

Grant Gamble


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?
  • 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.

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.


1. 1.1: Welcome to Microsoft Word VBA Macro Programming: Hello and welcome to Gamble Computer Solutions Word VD, a macro program. Video tutorials. In this course designed to give you a worthy ammunition, you'll need to start creating your own custom Macron's to automate Microsoft Word operations. Would users decide to learn maybe a programming for a variety of reasons? I want to learn word VB a programming so I could make myself totally indispensable to my company. I want to learn word. Deviate for Jeremy to enhance my career. I don't have a record macros, but I can't write that grows from scratch. I want to learn word BB A programming save money. We've had some bad grocery for our company automate document creation, but trouble waas that for every single modification we've gotta paint outside company, I wanna learn word BB, a programming toe automate one very time consuming operation that I have to do at the end of each month. Whatever your reasons for wanting to learn BBMak Rose, this course aims to equip you with everything you need to know to get started. We'll begin with a brief look at the macro recorder. We'll discuss its benefits and limitations, but we'll very soon move into words visual, basic editor and begin right in our own Mac Rose. We look at the use of variables both to keep track of data and to store references to Microsoft objects such as documents, paragraphs, tables. Anything which you may need to manipulate with your code will discuss how to make your solutions. Markkula, by writing and calling separate sub procedures and functions way learn how to use conditional logic to enable your code to branch off in different directions, depending on the circumstances it encounters on how to use the loops to carry out batch processing, such as Before Miniseries of Steps on Every Filing a Given folder. Once we've looked at these essential Phoebe A techniques will move on to an examination of the word object. The programmatic representation of all the various elements which are manipulated by Microsoft. Word will discuss the Sin Jackson programming techniques used to target manipulates Microsoft word objects. Next we look a T gent programming and learn how to write interactive code, which is triggered automatically when certain actions performed by the user, both within word documents and in custom dialogues called user forms. We'll talk about debunk in your code and how to use the tools available to track down there is when they have crept in. We'll also look a writing code, which anticipates possible errors and handles them when they occur. In short, this calls will equip you with everything you need to know to confidently start right in VB a Mac rose, which automate procedures inward and which will enhance your own profile as a word user and a word developer. Let's get started. 2. 1.2: What's Involved in Learning VBA: In this video, I will briefly discuss what is involved in learning Microsoft Word. VB A programming. What skills do you need to have in place before you can comfortably write your own macros from scratch? There are basically four elements that you need to have in place. The first of these is not going to be provided by this course. It's assumed that you already have this knowledge. You need to know Microsoft Word very well. In order to be able to automated, you need to have an intermediate or advanced knowledge of Microsoft Word. You need to be familiar with beaches such as sections, headers, footers, the distinction between paragraph format in character formatting, how tables are created and modified. It's also useful if you're familiar with advanced features such as content controls. Without this knowledge, it's going to be difficult for you to understand the significance of some of the code that you're writing as well as this. You may find yourself right in Macron's to perform steps in Microsoft Word, which can be done perfectly well without the assistance of macros. The second skill you'll need is a knowledge of programming. If you already have experience of programming. This is a distinct advantage because this is one thing you won't have to learn. However, this course does not assume that you have any programming experience whatsoever. So when we introduced new features, new programming features, we will discuss them in depth to make everything clear for you. Next, you'll need the knowledge, specifically visual basic for applications. This is the program in language, which Microsoft used to automate all of the Microsoft Office suite. And again, if you have knowledge of visual basic and have used it in other contexts, this is a distinct advantage, because again, this is one thing you won't have to learn. The final part of the puzzle is that you need to become familiar with the word object model , and this is the programmatic representation of all of the various features within Microsoft Word. Now, Microsoft Word has been around for a long time, and it has over the years become extremely powerful, and it has a vast array of features. So the bulk of what you'll need to learn in order to program word is the syntax which represents this vast array of beaches. So the object model is as huge as word itself. The good news is you don't have to learn every single aspect, just as no user uses every single featuring word. When you write programs, you'll be doing a limited and specific series of tasks. They tend to involve objects such as documents, ranges of text, paragraphs, characters. So you'll find that you can achieve an awful lot in the wearable automation simply by using a few features. But do bear in mind that the object model is a very huge beast, and it does take a while to become familiar with the syntax involved in its use. Learning word. VB a. Programming it Bowles gaining mastery of four elements. Microsoft Word itself, programming in general, Visual basic for applications for the D A and the word object model. Learning the word object model is the largest, most challenging and most important part of this endeavor. 3. 1.3: Getting Set up: in this tutorial will be download in and install in all of the exercise files that will need to complete. The various practical tutorials during the course will also be installing a global template or added inside, which will save all the macros that we do during the tutorials place. In all, our code in this global container will make it easy for us to access. Run and test our code from any word document. To download the exercise files, switch across to the download section at the top here. This is a ZIP file, which contains all of the documents that will need for the course sensibly. Click the download it, and if your browser lets you, then choose the location, so I'm going to choose the guest op. So here's the file that's been downloaded and it's a zip file. Civil needs one. Zip it. Remember that you don't need any special software to unzip a file on Windows. You can simply choose right click open with Windows. Explorer just made this across, and you can then use copy and paste. Or you can simply drag the folder out of this window into another location. Mr. Striking out holes in the desktop. And, of course, this doesn't move it from the Zip file. It simply copies it to the given location. That's dumb, Andi, if you want to, you could then delete the ZIP file. It's no longer required. Here are our extracted files. Now let's look at setting up the ad in which will be using throughout the course to store all the macros we create inside the train In Folder, you'll find two files with the file extension D O T M and these other word templates, their global templates or Adan's. And what we'll do is we'll save all of our code in the because these files a templates when you want to edit them, you can't simply double click if I double click on a template. Work gives me an untitled document based on that template rather than open in the template itself. If I want to open the template itself, I have to right click and she was open. Or of course, I could use file open and admit that way. But the key thing is, is you could see this is not an untitled version. This is the document itself, so we'll be referring to this document throughout the course and saving most of the microbes that we create in this one document in order to test the Mac Rose Weaken set this up is a global template. You have to be inside word to be able to do this, but the template itself doesn't have to be open. So just to prove that I'm going to close it than to set it up is a global template. I go to file options, then in the word options dialog on the left, click on the add ins category. Next, choose manage, word added and click Go. We want to have our fire listed in this section, Global Templates and Adan's. So we click on the add button. Word takes us to the templates folder. We simply ignore this location and let's go across to the desktop. Open up the Exercises folder, and we can now install out templates. The first file, the one that will be using currently has no Mac rose in it. It simply has containers waiting for us to create the macros if you want to, you can also add in this 2nd 1 which has the completed version of all the macros, so you could add both of them and then activate whichever one you want to work with by clicking on these checked boxes. So for the moment, let's leave them both activated and click OK, and let's quick word. Now when we go back into a word, I just want to show you that those templates won't be activated. If you click on options, add IDs. Word Adan's you'll see they're listed, but they've become deactivated, and this is the normal behavior inward. And this is probably the most convenient solution, because after all these macro czar the training, they're not macron's that you're actually using in your own work. If, however, you would like to have this ad in permanently activated, word does enable you to do this. Any template, which is saved in the startup folder, will automatically be activated by any launch word. Let's just cancel to find the location of the startup folder, simply go to file options and then click on the advanced category. Scroll down and right at the bottom. You'll find file locations, and at the bottom again, we have the startup location, so we click on modify this takes us to the default because I haven't changed it, and I could man just click at the top here and copy this address. Let's close and cancel And then back in Windows Explorer, I could paste this in press enter and that takes me to the startup folder. No, I can move back and forth between these two locations, and if I want to, I could now copy these files into the start up location. Macro saved in an activated added, are available to all documents opened inward. The macro enabled, adding called GCS word VB. A train in dot d o T. M will be used as the container for the Mac Rose. We will create in these tutorials to activate an ad in use the command file or office button word options. Adan's Manage Word, Adan's Global Templates and Adan's 4. 1.4: The Developer Tab: the developer tap contains all of the options that you'll need to start working with. Word B b A. However, when you first install word develop, attack is not visible on the ribbon. In this video, we'll look at how to make the development tab visible. Will also discuss how you can modify your macro security settings so that word doesn't give you unnecessary warning messages every time you open macro enabled files, which you yourself a development to make the developer time visible inward. 2010 in 2013 which is what I'm used in here, you go to the file menu and choose options in the word options dialog. Click on the customized ribbon category on the left, then in the list of tabs on the right, scroll down to find developer and simply click in the checkbooks to activate it. When you click OK, you'll see that you have an extra tab on the end of the ribbon called Developer Inward 2007 . Go to the office button in the top left of your screen and choose word options here in the popular category. Activate the option show, develop it out in ribbon and click. OK, There are six groups of controls on the developer tab, but it's the 1st 1 The code group that were mainly interested in clicking on the visual basic button brings up the visual basic editor or VB, and it's here that you'll write macros and edit them. We'll start working in a visual basic editor from Section two onwards, so you will soon become very familiar with it. The code group also contains controls for playing Mac Rose recording and pours in them and also for changing macro security settings will discuss recording and playing Micro's later in this section. For the moment, let's talk a bit more about macro security. Let's go into the Exercises folder and into 01 getting started and then into project. And here you'll find a couple of macro enabled files. Double click on the 2nd 1 Recording a macro end. When we opened the file, word displays a security warning message. These messages are quite useful for files that you yourself haven't created, but if it's your own file, it's clearly unnecessary to be given these warning messages. So we go across to the developer tab. Let's click on Macro Security and see if we can stop word from display in these messages when we opened files which were perfectly happy to work with, the simplest thing you can do is enable macros, but this is not recommended, and it's probably not a good idea because it means that you never get warned, even in those circumstances where it might be useful. A better solution is to go into trusted locations on the left and add the subdirectories in which you're saving your files to your trusted locations. So let's do that now. We simply click on add new location. Browse. I've got my exercises folder on my desktop, so I click on desktop and scroll down to word Phoebe a macro programming and then click OK , Very importantly, we need to switch on sub folders of this location or also trusted. Let me click OK, click OK again and let's put it to the test So quick word and just go back into the exact same file. And this time, as you can see, I'm not given the warning message. Let's close the file, and I'm just going to copy the same file, go back to my desktop and just temporarily paste a copy at that location, So this location is obviously outside the trusted location I've just specified when I go back in, here's my error message again, which is just a zit should be. That's just close word once more, and let's delete this file. The developer tab is not visible on the word ribbon. By default, it has to be made visible. Using the word options, dialog, the controls and the code group of the developer tab allow you to record, run and edit macros. Add in your VB A development folders to trusted locations provides a safe method of overriding words. Warning messages on macro enabled files. 5. 1.5: Recording a Macro: Yeah, in this video. Well, look at recording a macro and then edit in the code generated by the macro recorder. Before you record a macro, it's always worth thinking whether you actually need one. For example, there's very little point in recording a macro, which does nothing but format your text. You can simply create a style. Similarly, there's no point in creating a macro, which does nothing but inserts, um, text on the page. You can go to insert water text, and you can save a selection to the auto text gallery and just insert your text whenever you need it from the auto text gallery again. You wouldn't create a macro, which sends the same document out to several people because, as you know, word has a mail merge feature. So the need for macros is really where you have something that's very labor intensive and consists of several operations. So we're going to take a very simple example of this. Let's go into the Exercises folder and into Section one getting started, and here you'll find a document called Recording a Macro. In this example, we're going to take the scenario where you're creating a publication but you received documents from lots of different sources, and some of these documents have typographic conventions, which you don't want in your publication. So to look at a few examples, let's switch on hidden characters. And first of all, you'll notice that after every paragraph, we've got an extra return. And this is a habit that a lot of people have, which tends to create too much space in a publication. Another form of doubling up is after its sentence. We've got two spaces instead of one, and again that's not really necessary. It tends to draw attention to itself in the final publication, and then a final example of something you might want to clean up is to hyphens instead of the EM dash character. So we want to record a macro, which could get rid of all those typographical quirks in one hit. Now let's go back to the main train in Folder, and if you remember from the last video, we're going to be saving all our code in this, adding, because Adan's are essentially templates inward, you can't double click to open it, you right click and choose open. Then to record the macro, we go across the developer and click on the record macro button. We'll leave the default name for the moment and later I'll show you how you can change that . And then we'll specify that we want to save it in the template. It actually says documents based on the template. But of course, we're going to use our template is and adding essentially rather than a template on which other documents are based. When we click OK, we go into recording mode and you'll notice that the cursor changes. And if you click with the left or right mouse, nothing happens. So when you recording the macro or cursor movement has to be done with the keyboard, um selection has to be done with the keyboard and so forth. So now we can go across to the home tab and use the replace command or, of course, speaker type control age. So let's deal with the problem of two returns after every paragraph. If we expand by clicking on the more button at the bottom. Here we have the special pop up menu and right at the top paragraph mark. So this code stands for a paragraph Markle Carriage return So we're looking for two of those in succession, and wherever we find that, we want to replace it with a single paragraph mark. That's our first replace all. Now let's deal with two spaces. After a sentence, sentences can end with full stop close in parenthesis. Question mark exclamation mark in a neat way of catering for all these different possibilities would be to use a regular expression. I don't know if you've come across regular expressions, but since they were little complicated, I don't want to get sidetracked for a multi doing, which is recording that Cruz. So we're going to do it in the most simple manner, which is to do four separate replace operations. So, first of all, we'll start with full stop all period, as they say in the States. Two spaces replaced with full stop single space. Replace all next we'll have close in parenthesis. Two spaces closing parenthesis, single space. Replace all question marks based space question mark one space and finally will do. Exclamation mark. Two spaces, exclamation mark one space. Replace all The fact that we're not finding anything is irrelevant. All we want to do is to record the operation and the results we get will depend on the document that's active. When we run the macro, the final change we want to make is an em dash. We want to hyphens to be replaced with an em dash. Now there is a short cut for doing this, which we could use its control, Ault and hyphen. And it's got to be the hyphen on the numeric keypad. Not on the main area of the keyboard, however, let's pretend, but we've for gotten a short cut, so we need to actually copy an em dash and paste it into the fine and replace. If we did that, we would actually record those steps so would have some extra editing to do before our macro is usable. However, you probably noticed that there's a nice feature. Let's just cancel this on the developer tab, which allows you to pause the recording so you can pause it for as long as you like. Go away and do something else. Maybe someone's asking you to do some work in excel, so you switch across. Then when you're ready. If you could remember where you got to, you just hit the resume record a button and you're back in business. So to get our em dash, let's say we're going to insert symbol, more symbols and special characters, and here it is at the top, so we'll just insert it close and put it on the clipboard. We can't do a right click, so it's control. See, or X would be better because we don't really want it. So I'm just going to do it by going to home and hit in the cut icon. Now, before we go any further, we go to develop resume recorder, and now we can do our final replace operation. So it's two hyphens in the find what and paste our M dash into the replace with box. Replace all, click OK, and that's our final operations that we can now stop recording. So we've got the macro recorded in our adding we can now save those changes, closed the adding, and let's go back into Section one getting started and open the document to what we want to apply the macro. You'll probably remember from the last video that unless you save and add in in the Starter folder, you actually have to activate it each time you launch word. So we go to file options at its word, Adan's and here's the one we're after. So we activate that. If for any reason yours is not in the list, simply click on add, navigate your way to the train in Folder, The Exercises folder, and then just choose GCS Word. Levy a training dot D o t m. Now that that's activated, when we go to developer and look at the available Mac Rose by clicking on the microbes Barton in all active templates and documents, you'll see Macro one. If you remember, that's the default name, and we accepted it without changing it. And then we run. And then very quickly we've got everything cleaned up. So single space, after all, sentences here, we've got out em dash, and we only have one return after each paragraph. So let's close this document on. We won't save the changes in case we need to test our macro again. Let's move back into the main exercises folder and look at edit in the macro we've created . So we need to right click and open and to edit macros in the developer tab, you click on the visual basic button. Mac rose a store in modules these various modules will be using to store the macron's that will be created throughout this course above it. We have new Mac Rose and this is the module that was automatically created by the macro recorder. So if we double quick it, here's the code for macro one, which is the macro that we recorded. Now we're not gonna analyze the code. You will get plenty of opportunity to analyze VB a code and to actually look at how it all works. So for the moment, all we want to do is to move this code into our model and to get rid of the new Macron's model that was automatically generated civil selected, all and issues copy. Move across to the model for this section. Section one, just double click and then we'll paste in our code. We can now get rid of new Macron's by right. Click in and choose in. Remove normally when you're deleting something, if you see a warning message, it's almost always. Are you sure you want to delete? You click Yes, and it goes ahead and elites This message is different. What? It's actually saying is, Is this an important model on? Do you therefore want to export it before removing it? Of course, it's not an important module. You don't want to export it. So we ignore the default. Yes, let me click. No, the final thing will do is to rename our macro. The rules are name in your macro bar that you can't new spaces. You can use letters, numbers and under school, but you can't start with a number. The convention that will be using its festival. Which section we in? So we're in Section 01 Which video tutorial is this It 05 and then which macro? It's our first, so we'll call it a. Then we can put a short description. And let's call this one clean up text because we're not allowed to use spaces. Capitalizing the first letter in each word makes the name easy to read, and that's it. So we could now save our template, and we can refer back to that code anytime we need it. When recording macros left and right mouse clicks are not allowed, you can pause and resume macro recording at any time. Macron's that you record a placed in a module called Numac rose in the document or template that you specify 6. 1.6: Adding Macros to the Quick Access Toolbar: in this video, we'll look at how you can add a button to the quick access toolbar, which launches and runs a macro when clicked. We'll also look at how you can customize the appearance of the body and also the tool tip, which appears when you hover over it. The macro that we want to use is the one we recorded in the last video, and if you remember, we saved it in our adding the ad in In, which will be saving all the macros that we create on this course. Because the Adan is in our Exercises folder and not in the words startup folder, the macro as it contains no automatically available because the ad in itself is not automatically activated. To activate it, we have to go to file or office button on 2000 and seven. Choose options, Maddin's Manage Word, Adan's and then finally in global templates and Adan's. You'll see GCS word VB a training, and we can simply activate it. And, of course, if it's not listed, simply click on, add and then browse for this file. Now that this is activated, we can go up to the quick access toolbar and on the right hand side, we have the customized quick access toolbar dropped out, and from this we choose more commands by default. It lists popular commands. Family Simply change this to Mac Rose, and here's our macro, with its very long name, which consists of both The module, which is called Eso, won getting started. Then there's a dot and then we have the name of the macro, which we can see on the tool tip. So we highlights it. Click on add. It's added, and it has that default icon and that default very long name. Luckily, we can click on modify to customize both of those. So at the top here we can choose any of these icons. Since this is a document, let's look for a document icon. And here we've got a very good one. Writing in bolt on a page and then the display name weaken certainly shorten. So let's delete everything apart from the name itself. And it might be a good idea to put the word macro at the start so that we can distinguish between those icons that run other commands and those which will run Mac Rose. We don't have to keep this cryptic name anymore so we can put spaces in to make it more readable. And that's it. We click OK, and our button is now added to the toolbar. When we hover over it, we get out. Tilt it. Best test that it still works. So we open up the file that were used in the last video to test it inside, 01 getting started. And then it's called 0102 recorded and macro and we click to run the macro and everything works Fine. Now, if you are using this technique on your own, Mac Rose, it only makes sense to a sign Buttons. But those macros which you know are going to be permanently available. So in our case, Al Macro is saved in a template which we activate when we want to test our Mac Rose. So clearly, if we haven't activated, the ad in the macro won't be available and would run. It's although we've done this for illustrative purposes, just to show you how the technique works. Yeah, in a real life situation, it only makes sense to use this technique if you know that the macro is permanently available. So let's just quit word for the moment. We won't say there are changes to this test file, and then we'll go straight back into it. But of course, the differences. Our Adan is not currently activated. So when we click on the button, no, we'll get an error message because the macro cannot be found. So it's If you are doing this at work, then ah, reserve this technique for those macros, which you know the user will always have access to. Perhaps because they saved in the normal template. Well, because the template that they're in will be saved in the words startup folder and will therefore automatically load when you launch word. And I'll just finished by reminding you how you can verify the location of the word startup folder. It's file options, office button word options on 2007. Then it's advanced. Scroll to the bottom and file locations. And then here you got started so you can kick on, modify on, perhaps just copy that location, and then any templates that has saved in there will automatically be activated so you won't have to do the Adan's activation, which we have to do for our training added. To add a button to the quick Access toolbar, which runs a macro, choose more commands from the customized Quick Access Toolbar menu. Both the icon and name displayed on the Q 80 can be customized. Only Macros, which have permanently available to the user, should be assigned a button on the Q 80. 7. 1.7: Assigning a Keyboard Shortcut to a Macro: in this video, we'll look at how you assign a keyboard shortcut to a macro. Now. Naturally, word has a lot of built in shortcuts itself. So it's important that when you assign your own keyboard shortcuts, you avoid clashing with words many built in shortcuts. The macro to which will be assigning a short cut is the one that we recorded two videos ago . So we'll start by activating the template or added that contains it now. Windows 2007. You got to the quick access toolbar and choose more commands. Then, at the bottom of the screen, click on the customized button next to keyboard shortcuts on Windows 2010. In 2013 go to the file menu Jews Options and then click on customized ribbon. At the bottom of the dialogue. You'll see keyboard shortcuts with the same customized button in the top left of the customized keyboard. Dialogue in the category section. Scroll down to find back Rose. Then on the right, you'll see our macro listed next week in the box marked Press New short Cut key. And of course, what we want to do here is to come up with a shortcut, which we know is available. Let's do something that we know is not available. So, for example, control, See? Yes, you can see it tells us this is a sign to edit copy. So let's delete this. A simple way of avoiding these classes is to use old in combination with two letters, so this may not be as fast. A keyboard shortcut is some of the built in ones, but it's still useful, and it does avoid the possibility of clash in with the built in shortcuts. So our macro is called cleanup text. We could therefore use Walt. See you. So we hold down the old key and instead of press Inches, one letter, press two, the key thing that we're looking for is that it's currently unassigned. Once we're happy with that, we click on the assigned button on that short cut, then gets listed as the short cut associate ID with our macro That's close. Click OK, and then let's test it. So let's go into the getting started folder and use the file that we've used before. So now we press old see you and this runs out macro to assign a keyboard shortcut to a macro inward 2007. Choose more commands from the customized Quick Access Toolbar menu in where 2010 in 2013 which is file options, customize ribbon to avoid clashes with other keyboard commands. It is a good idea to use a short cut. It combines the old key with two key strokes. 8. 1.8: Editing a Recorded Macro: in this video, we'll look at edit in code generated by the macro recorder. When you record a macron, this code often includes many lines, which a completely relevant for your purposes. Let's go into the Exercises folder and at the bottom. Let's open up The adding in which we saved are recorded macro, so we just need to right click and choose open. Then let's go across to the developer tab and click on Visual Basic to launch the visual basic editor in the model folder. We opened the first module section 01 getting started. So here's the macro we recorded. Let's try and relate it to the operations that we actually perform. I'll just move this over to the right, move back to a word and then bring up the replace dialog. Let's make that over here. You can also just close the Project Explorer to bring it back. You just go to view into his project Explorer, by the way. So we start at the top here we have selection, not find not clear formatting and then selection, not find a replacement clear formatted. If we try and relate this to the dialogue box clearly what we're dealing with here is this option the ability to tell work that when we search, we only want to find certain things if they're in a given bond paragraph style, etcetera. So clearly, if any of that format in is in place on the user's machine, then that could have an impact on the results of our search. So these two lines are key to make. Ensure that any existing format in commands don't interfere with our search results. Then, of course, we've got the texts that were certain for which goes in that box, the text that we're replacing it with which goes in that box we that have some options which relate more to find in. They're doing a global replace. So forward and wrap really way you're doing Ah, replace all. Don't make any difference to the outcome, so these two can be deleted, have been cleared, the format in. We don't need this case. Whole words, wild cards, etcetera. None of these are really relevant to what we want to do, so we can in back delete all of them. So the only three commands which are relevant Ah, what's the text be searching for What are we replacing it with? And this line, which actually does the operation, which is equivalent to actually clicking on the replaceable button. Let's just close that down on. We could bring back the project, explore in case we need it so we can go through on weaken, delete everything from this point forward each of the operations that we recorded. So that's made our code considerably shorter. Another feature of this code is a very conspicuous form of repetition. We have the line with selection, not find and end with repeated for each section. So instead of having a separate block reach of al commands, we could actually have everything in one block, which perhaps will end here. Another thing you may notice is that selection, not find, is repeated here. And what that means is that if we take these two words out, this line can also be part of the with end with structure. Okay, so what we need to do is to lose all of the end with and then to lose selection, not find on this line, and I'll just tap across to line it up with the others. So let's just leave a blank line between each section, so that was our first find and replace operation. So here I'm just going to dilate this. That leaves the plane. Klein. And then this is the line that actually does the replacement. That's the equivalent of clicking on the replace all button. So if I delete all of that and just have it across, I accidentally deleted the full stop. So put that back in. So that's the second operation. Here's the third there's the next. So each time which is deleted this tab across leave it blank line and the next one, This is where we if you remember, we paused the recording. And then obviously, when we went back in, ah, we recorded. Or rather, the macro recorder generated this line, which it did up here. So clearly it's just a repetition of that first clear format in command. So these two lines can go unwanted with statement is our final end with, and obviously we can't delete this last one. What we can do is to select it. The tips for selecting the whole line is very similar to a word itself. Move the cursor over to the left hand side but not too far so that it's pointing that way and then just click to select the whole line, and then we can move into the selection. Just drag it down. Then here we delete those two unwanted words and just have it across. So all of this now is within one with end with structure on the final thing. What did you say? You is commenting your code. So this is what a comment looks like. Any line that starts with an apostrophe. So the macro recorder has put some rather unnecessary comments in this all this delete those on. Let's just comment each of our sections. So what are we doing here? We're removing a blank line between paragraphs and then from here, we, ah, remove in extra space above the sentence and that, uh, relates to all of those. Then the final thing we're doing, it's down here, replaced in two hyphens with, and so these comments will just remind us exactly what our code is doing. Obviously, in the upcoming tutorials will be covering all of this code in detail, but hopefully that's just giving you a quick flavor of how we can go into a recorded macro on make changes to it, which just make it easier to work with. So let's just save our changes and to test it on, let's go into the getting started older, open up recording a macro and then activates the Adan options at INS Word. Andi activate. And if you still got this button, you can just click on it to run the macro, just in case you haven't still got it, I'll remind you of the other way. We go to Developer Micro's. We could pick it up from there and run, and you'll see it's still in good shape, so it still works. Fine. Recorded macros can be shortened by deleting lines of code, which were relevant to the operation you actually want to perform at In comments to clarify the purpose of different sections of code is useful both for your own reference and to assist others in understanding the purpose of your Mac. Rose 9. 1.9 Benefits and Limitations of the Macro Recorder: in this video will have a brief discussion of the benefits and limitations of using the macro recorder. Basically, macro recorder is a great way to check syntax that you're unsure. You simply head the recorder and look at the code that's been generated. What it's not so good at is giving you an example of how to actually write a usable, inefficient macro. So let's take an example of a macro, or perhaps a fragment of a larger macro that we might use the recorder to generate. Let's say that in this section we're interested in having the macro recorder create a table for us. And let's say we want to form at the table and two headings in the first row and perhaps, um, placeholder text in the second row. So as we seen before to record a macro, we go to developer record Macro, we're not going to save this, so let's just save it in on untitled document and to insert our table, we go to insert table and let's just do one row. So let's say four columns and then insert our headings, so I'm going to take a real estate example, so this will be room type. And then let's say we want to merge these two cells to have a wider cell for description because we can't use the mouse. I'm going to hold down the shift key and press the right arrow twice to select both of those cells granted merged cells. I'll just goto layout and click on mode cells. Then I'm in the correct cell to type description on time along, and let's say that this is dimensions. Then we press tab and put in some placeholder text to tell the user what goes into this cell. So e g bathroom, a J large main bathroom, nearly fitted dimensions and for dimensions. Let's say I'm working in feet in interest and I want to enter feet as an apostrophe and inches as double quotes. Now, of course, if we have the auto correct switched on, then when we typed these characters, we'll get basically curly quotation marks on da curly apostrophes. So to avoid that, let's go into file options, prove it also correct options and just go across to auto format as you type and to switch off the option straight quotes with smart quotes. And now we can typing out I mentioned. So I'm going to say 15 feet six inches by 14 feet, six inches. So we have basically gone into the options for the specific purpose of getting rid of smart quotes. But as you'll see shortly, the macro recorder will insert a lot more code that we actually asked for. The final thing we wanted to do was to form at the table. So I'm going to go across the design and just choose one of these formats and we're done. So developer stopped recording. Let's then move across to the visual basic editor and in models. As always, the recorder has called the module Numac Rose. So let's see what's going on. Our first command is, too, at the table. Then here we apply the default style. We didn't actually ask for this, but because word actually formats the new table, it also in the code generates that default format in. Then we've got ourselves type in the text, and here we can see one of the first bad habits. If you like that, the macro recorder will actually get you into. If you were to get into the habit of basing your Mac rose on recorded code. It uses the selection object constantly, so it's not a very good tutor in terms of getting you used to the variety off objects and elements that you can have inward. This, for example, is all to do with tables, but there isn't very much that tells you it's to do it tables. Basically, it's simply saying selection, selection, selection and it doesn't matter what you work with. It always selects the object that you work with and then uses the selection object to actually manipulate that element. The second problem is that when we went into options, we were interested in one thing only this line. Now it's very useful if you want to find out what the syntax is to actually get rid of smart quotes, to do exactly what we did, record yourself switching on smart quotes or in this case, which in them off. But in actual fact, what happens is lots of commands which are completely relevant for our purposes also end up in the code generated by the recorder. So it's still very useful in order to grab this Intacs that you want and take it into one of your own Mac Rose. What it's not so useful for is to give you code that you can actually use as the starting point for your own Mac Rose. So it's a bad idea to actually record code and say, Well, this is my macro. I'm going to start with this and then I'm going to try and make it into something usable. It's much better to treat the macro recorder as a way of generating syntax, which on some occasions can be quite useful, in which you can then grab and put into your own macros. Yes, we could tidy this up. We could delay tal the elements which are irrelevant for our purposes. Surfer. It's all bull. All of these options set is that we don't want Is that all of these. We can get rid off all of those we can get rid off, and since we now just got one line, we don't really eat the with, so that can go. That's all we need in terms of options. And we could also get rid of TheStreet. I'll the default style that we've got up here because in terms of styling or were interested in, is this pelant, where we actually apply great table for accent five, that blue style that we actually wanted. So, yes, you can tidy things up. But for the reasons I've mentioned is not really a good starting point for your own macros , and you'll find that as you try and scale up the macro, the performance is really quite poor because the recorder constantly relies on selecting elements, activating them, and then work in on their those active or selected elements. As you'll see when we start writing our own macros, there are far better ways of structure in your code so that as you scale up and start tackling larger projects, your code is efficient and the performance is acceptable. The macro recorder is a useful tool for verifying unfamiliar scent tax, which may be buried within the word off Jake model. Unfortunately, the code generated by the macro recorder is not a good base from which to start creating your own. Macron's 10. 2.1: The Project Explorer: Like all of the Microsoft Office programs, Microsoft Word contains an application called a Visual Basic Editor, which is used for editing VB. A code in this section will cover all the basics of working in the visual basic editor environment within the Phoebe. The first window that you need to get used to is the Project Explorer, and this is the launch pad for all your VB a code in activities. And that's what we'll be talking about in this video. To access the visual basic editor, click on the developer tab and then either click on the visual Basic button or press old 11 . The keyboard shortcut Visual Basic Editor is an old style Microsoft application. It has menus, two bars and floating windows and, as was traditional with this style of application to make the various windows in tool bars visible, you go to the view menu. So here we can see the various windows, and here we can see the different tool bars, and you can also right click on any toolbar and choose the name of the toolbar to hide or show it. The Project Explorer is probably the first port of call when you go into the visual basic editor because it's from here that you can launch all of the containers for your VB, a code it is not visible. You simply go to view and shoes Project Explorer or use the shortcut control are within the Project Explorer window. You'll see a project correspondent, every document or template that's currently opening word, and you'll probably see an extra project related to the normal template, which is associated with the application. A project is simply a container, a code. So when you think of projects, just think of the term code project. It's also worth pointing out that the project is always there. It doesn't imply that the rest code associated with that particular document every single word documents when you enter the visual basic editor will have a project already associate it with it. So it's both the way of access inexistent code, which has been associated with a particular document or template and also creating the code in the first place. Although most of the code that will be creating in this course will relate to Mac Rose, there are, in fact, four different ways of adding VB, a code to a document or template to help us explore these. Let's just open a document that's been prepared. So I'm just going to close the VD for the moment and minimize word, and that's going to the Exercises folder. We're now in Section two, and inside this you'll find a file called 0 to 01 project. Examples. You'll notice that it has a d o T m file extension on this is a macro enabled template to open a template so you can edit it. You can't simply double click because that action will create a blank document based on the template. Instead, simply right, click and choose open inside this document. Let's just zoom out. You'll see we've got a diagram, which shows you the different types of container inside, which you can place your maybe a code. So we'll be focusing mainly on modules, which is where you can actually create Mac rose. But in addition to this is, you'll see we have word objects, which basically just contains this document, and this is an object referring to the document itself. Then we have user forms, class modules. Let's now go back into the visual basic editor on here, we can see examples off those four different types of container and we'll just go through them in order. So I'm going to close down all the various code window so we can start from scratch. Okay, So in this project, with prepared examples of each of the four different code containers, the 1st 1 in a folder labeled Microsoft Word Objects contains this document and this basically is the programmatic representation off the document in question the document to which this project relates. So obviously, in this case, there is a visual representation which you access by simply going back to word. And now you're working with the object itself. But from a programmatic point of view, this object basically can contain code which relates to the document. To access this code, you double click on the icon. You can also right click and choose view code. The code that you place inside this object is not a macro. It's basically an event handler. This is a subroutine which will execute when a given event occurs on the events which relate specifically to documents. Ah, opening and closing. This particular sub procedure called document new relates only to templates, and it's triggered every time a new document is created, which is based on that template. So here, basically, we can see chemistry books, which is a function that's used to display a message for the user, and it's simply a reminder. Please remember to save your work frequently, and then this parameter specifies that it's a dialog box that has an exclamation mark. Ah, in other words, we kind of shout into the user so not recommended. If you are doing this at work, let's just test it. Simple clothes and instead of right clicking and choosing open well now double click. This gives us a blank document based on the template, which in turn triggers our code on outcomes are MSG Box. There's the exclamation mark inventions, so we click. OK, we'll get rid of this, and then we'll go back in by right clicking and choosing open. So we once again editing the template itself. Now let's go back to develop a visual basic. I'm just going Teoh, go back to this document by double clicking on the icon. That then is an example off an event handler, which basically will take place when a new document is created based on that template. Next, we have user forms. So again I can just double click to show you they use a form, basically, is a customised dialogue inside it. You can place all of these different types of control. So it's quite a nice way of getting your code your macro to be a lot more flexible by creating a user interface, which allows the user to make choices and to tweak the way in which your code runs to enter parameters which your macro can then used to make decisions. So it's quite a powerful feature, and we will be covering user forms in detail to add kowtow, use of form you simply double thick on the control to which you want to add that functionality. So here, clearly, we want things to happen when the user has finished, enter in their name and then clicks on the continuum. So for our code, it would double click on continue. We would then automatically be given a sub procedure which uses the name of the control in this case has been named BT and continued, and the action in this case it's a click. Okay, moving on. We have modules, and this is where we'll put most of the code that we do in this course, because this is where you actually create Mac Rose. Or as we saw when we used in the recorder. This is where macro code is automatically generated by the recorder. So if we couple click on Model one, which is the only model we have here, you'll see it contains a single macron. We can tell it's a macro by the use of the word suburb, and the macro is called show form. Inside it. That's just to sing the line, and this name, you'll probably noticed marries up with the name of our form. So we have to prefix FRM for full and then the form is called Sign in and essentially you'll realize DOT show is used to display the form so we can run it from here and up comes on full. I understand to my name and click continue, So I don't think we need to say anything mawr on models, because this is where we'll be spending most of our time creating various sub procedures within modules, so that just leaves then class modules. This is basically an advanced feature, and it's slightly beyond the scope of this introductory VB a title. But I will explain what class modules are. They're basically containers for object oriented code. Object oriented code refers to a style of programming, which is suitable for large scale applications, whether it's a significant amount of code, usually collaboration between different programmers. Given the size of the project, there is a lot of scope for errors creeping in and due to code that belongs to one area of an application, interfering with the functionality of code related to a completely different area. Object oriented programming provides a mechanism for divide in code into isolated compartments. Andi basically structure in your code in the form of black boxes, if you like, and this is done by defining classes. Each class relates to a specific area of the application and is effectively a blueprint containing all the code related to that particular area. So we double click to look inside it. You'll see we have a bare bones class relating to the user on it contains basically three properties or attributes of the user relating to the user name, and there are three types of user name one is you could simply ask the user, Enter your name. So here we've got name entered. Then there's the word name, and the system is your windows log on name. So basically, this class shows a fragment of code that you might use if you were creating a class which kept track off user activity. Okay, so there we have it. Those are the four types of object into which you can place your VB a code. There is one other folder that you may see Inside Project called Reference. And although it's not a container for code, I should just explain what the reference folder is full from the code. In points of view, it basically provides a mechanism right in references two documents or templates that contain code. Say, for example, let's just close this down and safe and then just go back into the Exercises folder. If you're not still where and in zero to, you'll see that we have here a back row enabled documents. It's not a template this time called Ref. Let's just go into this. So is currently a blank document. When we go to developer visual Basic, you'll see it contains a reference to normal because it was based on that template. Let's say that we want to create a reference to the template that we've just been working in. We go back to developer and on the right hand side, click on document Template. Here we can click on Attach, work our way into the Exercises folder Minds on the Destiny Section zero to and here's our project Example Macro enable template. So we double click to open this click. OK, so now when we get back to the visual basic editor you'll see in our reference folder, we have a reference to that file. As a consequence of that reference, we now have the project off that file available. So here are the four containers that we've just been discussing, and as it stands, we can actually modify all of those from within the current document. So by creating that reference, we actually now have access to all the code in objects within that file. So I could come in here, for example, and perhaps adding a button. So as you can probably guess, we can duplicate a button by copying and paste in, and you can also duplicate by holding down the control key and just dragging. So I'm just gonna tidy that up, making it smaller, maintaining a caption on it, too canceled. Then I'm going to rename it. So here's my properties window. It's not visible. It's just view properties window, and the name is the very first parameter. We're going to use the prefix b TN cancel answer. And then I want to put some code on it, which will simply close the window. So I double click on a sub procedures automatically generated using the name of my control , PT and cancel and the Event click, which is exactly the event that I want. So here I can simply say, unload, followed by the name of our form and to test it. I'm just going to go back to a word, Mac Rose and run the show for Macron that brings up with full and then I can cancel. So those changes of automatically been made to the document itself. And if I were now too quick word, it's gonna ask me two things. Do I want to save the changes to ref, which is the documents I've currently got open so I'll say yes. But it's also going to ask me, Do I want to save my changes to project examples? And again I will say yes. A project is simply the container for all code associated with a document or template. Within each project. VB a code may be implemented in four ways by creating macros within a module. My writing event handlers in the this document object my writing event handlers for user form controls and by creating class modules. And remember any document which contains VB a code must be saved is a macro naval documents . 11. 2.2: Other Windows in the VBE: having had a look at the role of the Project Explorer window. In this video, we'll have a look at some of the other available windows in the visual basic editor. Let's go into the Exercises folder, an intersection to and let's once more at it, the template that we used in the last video. So just right click and choose open and then developer and visual basic to enter the visual basic editor. And this time, let's start with a clean slate. Let's just close down every single window. So we've established that the Project Explorer is usually the starting point. So we bring that up and we condense. See all of the objects, which are available for attaching code to each of the documents that you currently call open. The next type of window that you probably will encounter is a code window, and there are a number of ways of opening code windows. The simplest is to double click on one of these objects, and that brings up the code window. In the case of a form double, click in simply opens the form, so to get to the code in one hit, you can right click and choose view code. And, of course, in the view menu, you also have code. The short cut. As you can see there is F seven. Each code window that you open doesn't close any of the existing code windows. They simply accumulate on the right here. If we look at the bottom of the window menu, all of the currently available code windows could be accessed, and you may well have come across the short cut for switching windows in other programs. It's controlled and tab, so you can see that as I press, control and tab, the appropriate object correspondent of the currently active window is also highlighted. We'll obviously be doing a lot of work in the code window, so you'll become very used to using it during this course moving down the View menu. The next window we have is the object browser, and this is a built in reference facility, which enables you to check the syntax for all the VB, a code that you may be thinking of writing. The code is divided into different libraries. So, for example, if you're doing something which relates to VB A itself rather than a particular program, within the Microsoft Office Suite U two's Vehbi eh? So here you'll find syntax, which is common on which could be used when, right in programs using Vehbi eh? In Microsoft Word or Excel or Power Point. So, for example, when we're dealing with the document object will discuss the file system object. And this is where you would come if you wanted to check the syntax to see, for example, how to change the current directory past the command that does it. Current directory. C U R D I r herder When you want to work with word and of course, you look in the word library, and here you'll see all of the objects which relate to Microsoft Word. So, for example, if you're working with a document object, you can scroll down and check the available syntax. So if you want to say the document, you will go down. Of course, to say that, as will save us to, is it now is on here. You'll get an idea of all of this in tax. We'll talk more about the object browser in Section six, the word object model. You'll notice that the Object browser opens in the same areas, the code windows and indeed it's displayed at the bottom of the window menu, and you can switch to and from it when it's open, using control in time. The next section in the View menu relates to debug in, so we have immediate locals. Watch and call. Stack will discuss these windows in detail in Section eight, debug in and error handling. For the moment, I just want to mention that the immediate window is a bit more versatile, as well as its role in debug in which will come to later. It can also be used to execute a single line of code. This makes it a very useful tool, both for very fine or check in syntax, and also just for carrying out a single action use in code. I'll give you a very simple example. Let's go back to Microsoft Word. Just using this icon here or old 11 and then in the file or office button. Let's choose options or word options on in the user name section. You'll see I've got my name Grant Gamble. As a user, I have the ability to change this as well. Let's see what it is so it's a property of the application object, the application object being, of course, Microsoft Word, which is read, write. And this is exactly how it works in code. So in the immediate window, if I want to read that property, I would use a question mark and then application docks and no prizes for guessing it's going to be using name when I press enter, it shows me the user name because it's a read write property. I can also change it. So to do that, I delete the question Mark I simply a sign a string because obviously this is a string parameter. I assign a string to it because it's a string. It has to go in quotes. So let's say just grant. When I press enter, that line is then executed. So if I go back into word options, you'll see the change has been made programmatically. Let's just put it back to what it waas so as well as its role in debug in which will come to later. Just remember that that facility exists. You can just execute a line of code to quickly check and set in, or to check a parameter and it very often saves you having to create a macro for that purpose or to use the recorder as we've done before. Then we have the project explore, which we've seen and the properties window. So this basically allows you to choose to change attributes of the selected object. So if we click on this document, we have a lot of feedback basically relating to the settings off this current document. But it's mainly with user forms that you'll be used in properties. So whatever control I click on, I can see a number of properties relating to that control like a neither view them as an alphabetical list. Or I can switch across to the categorized tab where the various attributes are grouped under headings, and you have the ability here to collapse the headings, which makes it much easier to sort of navigate and find the attribute that you're looking for as well as this. With years of forms, you can choose the control from this drop down as well as obviously clicking under control on the use of warm itself, and it just switches across. So if I wanted to change the name of my label for example, the reason that the name hasn't been changed is because it's not going to be manipulated programmatically so it's name is irrelevant. But if it were to be given the name, it would probably begin with L. B L for clarity, using a three letter prefix to indicate that it's a label Andi name because that's what it relates to with models and class modules. The only thing that you really change is the name of the module. So let's say that this is going to be called main. We then got a couple of options, which relates specifically to use the form. So if I highlight the user form, they become available. On one is toolbox, which we saw briefly, which allows you to act controls to the form. And then there's tab order. And that's the order in which elements are highlighted as the user presses the tab key when the form is visible on this form. Because the cancel button was created after the continue button, it's last in the tab order. So to prove that if we run the form and I just press the tab key, you'll see it goes throb the textbooks to continue and then to counsel, which is not a big deal, but to make things neat and tidy. If we bring up tab order, and if I simply click on Bt and cancel and move up, it's now in a more logical left to right top to bottom order. So when we run from this tax books, we go the council and then tap again takes us to continue. So that's a look at the various windows. And as I said, we will cover the debugging windows when we come to the debug in section in detail. Then we've got four tool buzz the standard tour bars, the one which is currently visible. And if you're a word user who has been using one for some time, then these tool bars will be very familiar from previous versions of the Microsoft Office suite. So we've got things like obviously, cut copy paste on do, um, play to run code on indeed to run a years of four. And then we've got some icons here for making the some of the windows visible so they duplicate the functions that we've seen in the View menu. The Edit in Toolbar has options, which refer specifically to code, so soon as I switched to a code window, then they will become visible. I will discuss these options, will use them as we go through the course and we start type in code, will discuss them as we need them will discuss the tools on the debugging tool. But when we come to the debug in section and the final toolbar is for user forms, so I'll just switch back to the user form and it's got a few options relating to structuring and formatting your controls. So, for example, here I've got the ability. If I wanted to group these together or to change the alignment, so if they weren't a line, I can tow line the bottom edges, and I could make them the same with so whichever one I selected first, I can't remember, but that will become the basis of the common with, and then when I choose that they will become that with. So based on the results, we can assume that I dragged from right to left when I selected it, and the continue button was selected first, and they both the same with as Theory Journal with on the continued up Let's just don't do so. Very straightforward. So that's a quick look at the windows and tool bars that you have available. I haven't looked at these interface elements in the next video will move on to look at write in code code Windows Air used to create and edit code inside modules. News of forms that this document object and class modules, in addition to its role in debug in the immediate window, could be used to execute a line of the V A code. The Object browser is a built in reference, which details this intact structure of the B A on the word object model. The properties window is most frequently used when a sign in attributes to controls on a news a form. There are four tool bars available in the visual basic editor debug, edit standard and use of forms 12. 2.3: Macros and Sub-Procedures: having had a look at the visual basic editor and some of the tools it has available, let's now move on to actually write in a macro macron is essentially a subroutine, so we'll be discussing the different likes of subroutine that you can create within the module. When creating the macro solution. It's almost inevitable that you won't want to put all of your code in one single some procedure. A more typical approach is to put all the code relating to one stage of your operation into a separate some procedure and then toe have one main, some procedure which brings it all together. So let's say that we're about to create a macro, and we have sat down and designed this various steps that we want to perform, and we've broken them down into three main areas. Initialization processing and finalization. So let's create a diagram that shows the functionality that we want Town macro toe. I'm just going to use insert smart, aren't hierarchy. Let's just go with the plane organization chart so we can dilate this box so this would be our main, some procedure, which will be the macro itself. Then we'd have a sub procedure for initialization, another one for processing and another one to finalization. And then, as I said, this one would be the macro. So let's give it a an outline and making the outline thinker. Okay, so that's the structure that we're aiming for. Let's now look at how we will write code that reflects that structure that's going to the Exercises folder. And if you remember from Section one, we're going to put most of our Mac rose into this Adan called GCS word VB a training and open and adding just right click and choose open. Then let's go to develop out visual Basic and in the Models folder. Let's open up the second model right in Macron's in VB. So the first sub procedure that we want to create will be our main sub procedure, which will be the macro to define a macro, you use the optional keyword public bullet by sub, followed by the name of the macro, and we're going to use the prefix section zero to this is video tutorial. 03 macro A. That's a prefix, and then we'll call it main with my press enter. The program completes the sub structure for me by putting in the answer the readability and then gonna press the tab key on on the editing toolbar, I'll just show you which one is edited. This one is the standard toolbar, and this is edited on the editing tool, but you have actually got an invent button. But as you can see in brackets that the shortcut tab is much more convenient on the other way out Dent his shift from time. I now want to call my subordinate. Some procedure is. And to do that I use the optional keyword call followed by the name of the sub procedure. So I'm gonna full of the same name in convention. 020 p on initialization. That was our first step. Then we had processing on finalisation. So those are the three subordinate some procedures which my main macro will call. And now, of course, we need to define these. Let's be lazy and just copy this code. Paste it here we can then use out dent, which is shift tab to pop it back there. And we just replace the word cool with private sub. And if I then just paste that over the word cool. We don't have the skeleton off our three sub procedures. So that's the key difference between a macro on a sub procedure, which is called by a macron. The macro itself uses public sub and the some procedures, which is called use private sudden by default. Within a module. Some procedures are public, so if this is admitted, it becomes a public some procedure anyway. Let's just put some code in there, so we've actually got something happening. So again are tres tab on. Just use MSG Box and in quotes initialization complete. Okay, lets just copy that Kings of the other subs. This will then be processing and organization. So there's our basic structure. Let's save our changes and move back to Word, either by closing the Baby E or all 10 11 and on the developer toolbar. Let's get cold, Mac Rose. Here's the macro that we wrote as our project at the end of the last section. And here's the macro we've just written and, of course, maybe run. It just cools. There's three. Some prestige is and displays our chemistry books. Message Macro is a public sub procedure placed inside a model private sub prestigious placed in the module can only be accessed by other sub procedures in that same module to run one sub procedure from within. Another, use the optional keyword cool. Pull it by the name of the some procedure. 13. 2.4: Getting Help: When you're creating your word macro solutions, you're obviously going to get stuck from time to time. In this video, we'll discuss some of the strategies that you could use to find help on Microsoft Word. The topics. There are five main tools that you have at your disposal when you're getting help. Onward Lee B. A. First of all, we have the Internet, so Web searches. I'm not going to say very much about this. It's such a part of our lives nowadays that if you're stuck on something, you go to the Web and you type in precisely what you're stuck on. The more precise, the better, and you get some help. So if you're writing a macro from scratch and you're not quite sure how to start, you could basically do a search on exactly what you're trying to achieve with the back row , and you'll find people who have had similar requirements and look at some of the solutions that they found again. If you're stuck on a particular problem, an error message perhaps keeps cropping up. Every time you try and run a macro, you type in the exact error message that you'll get in do a Web search on that and look at solutions that have been suggested for people who had the same problem. Then, of course, we've got the built in help reference. Next, we have a facility called the Object Browser. This is access from the visual basic editor and gives you a structured presentation off all of the elements, which constitute word Vehbi, eh? Next we have the macro recorder, and this provides an excellent method off Generating Co. Just to get you started so that you can actually see what things are called. One of the problems of using the help reference or using the object browser as we'll see in a moment, is basically that it's quite difficult sometimes. Didn't know what the lanes of the elements that you're trying to manipulate. What's it actually called? I can't look it up because I didn't want it school. So this is where the back Rory Cup record it becomes by useful. And then finally, we have a nice feature called intelli Sense. This is something that's built into the visual basic editor, which anticipates what you're about to do and displays in context help, which you can then use instead of having to type everything himself, Rely purely on your memory, so Web searches. I'm not going to say anything more about. As I said, It's basically something that is inherent to everything we to in this world now. Ah, so let's move on to the help reference. And of course, we're not talking about the general word help which we've got here. Um, so I was closed that down. We specifically want word vb a help. So we need to go into developer visual basic or, of course, press old F 11 the keyboard shortcut on from here. We either choose help. Microsoft Visual Basic for applications help. Well, we click here or, of course, repressed F one by default. The help will appear online. If you spent a lot of time off line when your code in you might find it convenient to download the word 2013 db a reference and then we click on download on activate theme specific VB, a reference that we want to download, assuming you're happy to use the online reference. As I said, the main problem that people find is where do I start? What are we're actually looking for First of all, obviously start with word 2013 development reference, and he's got three sections. Concepts. How do I and reference the concepts contained topics on that useful things that people often do at the sort of introductory and intermediate level. Let's say, for example, you want some help or format in text. You might start off by reading this section on the pliant format into text so as you go through, it will give you a brief description of each technique and then give you some sample code, which economically copy and uses a starting point for your own code. Let's just go back to the development reference. Then we got How do I? This is a similar Siris of topics, but they are more advanced says You can see they are referred to things like content controls, building blocks and connected to databases. And then thirdly, we have the reference itself, and because it is comprehensive, obviously it has on awful lot of things that you will never actually need or use in your coded. At stake is an example. The front object. Let's say you're working with character formatted, so here you will see you have three ways of proceeding, you can look at every piece of syntax, which is fond members. Here, you've got links to everything that could be done with the foreign objects or, if you want to focus either on properties which are essentially attributes that you can set . And if we go back to the word interface in the home tab. Obviously, the found object equates to the front group on the ribbon. All of the character formatting attributes that we might said, such as the font color, the forms name the phone size. All of those are essentially properties. So when we click here, everything that we see is equivalent to one of those options. If we now just go back to the forms, object, by contrast, the methods, our actions that you can perform on the front. And, as you can see, that's not a huge amount of them grow and shrink, probably the two most frequently used in this list and, of course, their equivalent to click in on these two elements. So a method, basically is an action that you can perform on the object. So if you think about it, if we compare, grow and shrink to size. Size is a specific attributes. Grow is an action. It's a similar thing to the contrast of saying that the child is four feet six inches tall . That would be basically a property of the child, as opposed to saying the child has grown by three inches since I last saw her. Okay, so that's Ah, quick overview of using the reference from cold, if you like. In other words, you just jump in and you navigate through. This method tends to be the hardest. Because, obviously, as we've said, the main problem is, where do I start? What are things called? What am I looking for? So let's just close that. Let's now have a look at the object browser. So again, developer visual basic and it's under the view menu. It's structured in exactly the same way as the help reference that we just saw, so we would start by choosing word as the library. Then we have the same west that we saw so we could go down to funds as we did the help reference, and here we can see all of the members off fund. So here's the grow method. You can see that methods have this green icon, whereas property have this old index card icon with the hand holding the index card so we can see here. If you want to make something bold, it's gonna be the front object on the bold property off the front object. So what the Object browser shows you? Basically, it's simply structure. It shows you where a property is or where a method is in the context of what's called the word object model. In other words, every piece of syntax, which it's legitimate. Inward. Vehbi, eh? What are the V B A equivalents to each of the elements within word. So as you work in the word interface, using all the options on the different ribbons, the Object browser basically tells you what the equivalent vb A's to each of these commands that we have in the word ribbon. But what it doesn't give you a course is much help. So having isolated whereabouts within the structure of V B. A A particular elements lives. If you want, we'll help on it. You're then press F one or click on this help. It has to be this help button, as opposed to this one. If you click on this one, even if you have something highlighted in the Object browser, you're always going to end up on the starting screen that we saw earlier. So if you want to get contextual help on the currently selected elements, you either press at one or you click on this icon sighs you can see with now on a description of the font, not bald property on DWI can see here basically that we need to put any expression that refers to a formed object, and then we can set it to true false or W d undefined. I say we can set it because here the help reference confirms that it's a read write property. In other words, that property which weaken both check to see what it currently is and modify. Then further down. We've got a quick example off using the fund stop bold property. Okay, so that's the Object browser. And as I said with both of those, both the help reference in the Object browser. The key issue is if you're starting to write Vehbi, eh? What am I looking for? What are things called? So this is where the macro recorder becomes so useful. Whatever you're trying to do with your VB a code inward, you can actually record yourself performing that step most of the time. This won't actually write the back row for you, but what it will give you is code that you can use as a starting point. So let's take a couple of examples or using the macro recorder. Let's say that we want to find out how to, as part of our macro, how to delete the third qallab of the currently selected table. So let's just insert a quick table, give it a few rows and columns. Say this many. Let's just put some text in so you know which call him is which. So I'm just gonna put one, 234 So now we simply go to the developer tab and we record a back room. We click on record macron. We won't bother giving in the name, which is using it as a help feature, and we won't save it in the normal template for the same reason we'll just save it in this current document. So now we can't do any clicking while you're recording the macro you can click on a cell, for example. So what we need to do is to make sure that we're in column three. And the logical way to do that is to use the keyboard shortcut. We're going to the first column. A man just used the tap key twice to go to the third column. And, of course, if you then use the delete column, come on, you must then delete the third column. So the keyboard shortcut for going to the first column in a table, its old home. It doesn't really matter which row of the table I happened to be on. I now know that I am in the first column, so now I just press the tab key twice. This means I now must be in the third column on any road. I dont goto layout all the options in the ribbon or find while you're recording America. It's just the click in within the document that's not permitted, and I go to delete two's daily columns back interest of the third row. That's all I wanted to check, so I go to developer and stop recording. I could go to visual basic and retrieve the macro when you record a macro model is created , it's usually called new back rose. We double click to open it, and there's our code. So here is where I pressed Walt home. Then I press the tab once and twice, and finally I chose to delete the current collar so I can now copy these lines. Typically, I'd sort of take them out. Andi, slot them into the macro that I'm actually created. Let's look at another example of how you can use the macro recorded to check syntax. Let's I want to do some work on format in and page set up, and I really want to get ah, handle on some of the since accidental need to use to prepare a report and make sure that it's formatted in the right way. Let's again go into developer and recorder macro in the current document. Now let's go back to the home tab, and rather than chosen individual components, the most trick you can use is wherever there's a dialogue available, you simply record yourself open in the dialogue and clicking. OK, so if we want to know everything about fonts, you just click on the launch button here, which brings up the front dialogue. Don't need to change anything. We just click. OK, let's do the same for paragraph. Click on The Lordship Barton up comes the paragraph. Dialogue. We click. OK, then we wanted to do some work on page layout. So we go across the that again. We've got the laundry Barton, which brings up the paid set up dialogue we click okay with without making any changes. Then we stopped recorded. Now, when we go back to visual basic, you'll see we've now got macro. To added, it was the first on the recorded in his back road to and look what it's done, so we didn't actually choose anything. But simply by open in the dialogue and clicking, OK, all the want properties are now available, so whatever it is we're trying to do, we can find it in this list on try and get a handle on how it works. Similarly, we scroll further down. We've got all of the paragraph formats in and the down still everything to do with page set up. So that technique is very useful just to sort of get a whole bunch of code that you can then analyze, as well as simply looking at the code. You've now got a context, but you can use as a starting point when you use in the help reference and when you use in the object browser. So let's look at how you can go from code to help reference or from code to object browser in context. First of all, let's say you want to just verify how vb a handles for names. Is this a special piece of text or can I put anything I like? How do I know what to put? So basically, you highlight these in tactical elements question in this case, by doing that, I'm I light in dot form stopped name because this structure, as we'll see later, basically saves me type in selection, not fond at the start of each line. So it's equivalent to have in selection dot for dot name selection dot for dot size etcetera. So when I select dot name, I'm effectively selected form stop made. If I want the help reference at this point, I can simply press F one. I can't think of anything I can do in the men used to get to the right place as we've seen , these will take me to the start in page rather than the context that I'm after. So I press F one on the keyboard and up comes to help specifically on the full name property. It tells me here it's read, write, which means I can check it and also modify it. But it's a strange so that this means it can be anything. So, having verified that, I could say Go along to the home tab, choose a particular forms and simply copy the name of the font as a string. Then I can go back to the visual basic editor pace that inside the quotation marks because I now know that this is simply a strain. I know that that's gonna work, that that line of code will change the current selection to back, formed that Mike and do exactly the same thing with the object browser. Let's I just wanted to get an idea of the structure of where the font dot name actually fits into the general scheme of things. Again. I select dot for dot name and instead of President one. What you do is to right click in twos definition, and this is also a good way of checking whether something exists. If you type something and you're not sure whether it's legit, right, click it and choose definition. And if you get an error, you know that it's not the JIT. So when I choose that up comes the object browser in context, the font object is selected. All the members of displayed and the name property is highlighted. Okay, so as you can see, the macro recorder is a really useful way or actually just getting yourself started and getting to know what things are called. What the various components within Microsoft Word or called when you're automating those particular elements. And then the final thing I wanted to say as a help feature Waas intelli sense. This kicks in basically when you're right in your own back roads. So let's say we want to create a back row between these two. For example, Mrs Cool it test and let's say that we want to use selection, not forms, as we did in the macro that we've just recorded. So if I start typing myself with selection thoughts, can you see that as soon as I type the dot. All of the members of selection are displayed, so I know that whatever it is, I type has to be something in that list. And as soon as I start typing it so f o is all I need to type to get font to be highlighted as soon as it is highlighted by then press the tab key. This boat saves me time. And, of course, with longer syntax, it's going to save me typos. Then let's say we want to do exactly what we did in very recording. We want to change the fault name. So again I typed dot and what I'm seeing now is everything to do with funds. All of the members of the fault object. Just as I saw a moment ago, all of the members of this election object. So we said it was dot name again. I've just typed and a and then press tab equals. Then we know it goes in quotes, and I think it's still on the clipboard. I have a copy anything since not space that in close quotes, let's have a look at another example of intelli sense. So let's I want to change the color of the phone. So I type in dark color and I can see from the list that it's gonna be color index. So I press the tab key. Just lock that in and watch what happens when nights I've equals. You'll notice that I get a list of options, and these basically are called in Yuma rations, which is equivalent to making a choice, usually in one of the dialogue boxes. All of these integrations start with W. D. So let's I want Dark Blue. It was gonna be W d dark blue And again, I can look these up so occasionally when you take the equal sign, nothing comes up. So you have to do it from memory. But to check that you've got it right, you can just highlight it. I'm going to definition if it exists, the object explorers displayed, and you can check all of the different options again to find them in the list when you're in the word library. As I said, they will start with W. D. And then it's just a case of guessing what they're called. So these ones are called WD color index, and you'll find there are a lot of these in the word interface in terms of what they quit, too. If we go back into the home tab, look at paragraph, for example, and take alignment. So here I've got centered right and justified. Wherever you've got choices like this in the word interface, it never uses simply strings that's too much room for error. It uses thes W. D in Yuma rations. Onda entirely sense provides a great way of checking the So if I go back in, for example, let's say we want to send to the text mail. We'll just do another with blood, which is a technique very favored by the macro recorder. Let's leave it blank line as well. So this will be with selection got paragraph formats. And as you can see, I've just type p a r than I press tab. And with then if I move back up inside dots, a l alignment of it comes and I press tab. And then again, when I suppress equals here these items they all start with W. D. And as soon as I start typing center happens to be the 1st 1 So I just pressed tab to pop it in, so you'll find that using intelli sense is a great way as your type in your code of getting interactive in context. Help, uh, would saves you keystrokes and saves you typos. And as we go through the course, I also mentioned ways in which you can maximize the occasions on which this intelligence will actually kick in. Just save you having to rely on the old memory all the time. Okay, so that's about it for this tutorial. That's an overview off the five different techniques that your use as you're trying to get help to speed up the process off, creating your back rows and automated Microsoft Word. The macro recorder is an excellent tool for identifying the names of word elements, which you want to manipulate with your BB a code to get help on a particular object, property or method from the code window. I like the appropriate syntax element and press F one to navigate to a particular object, property or method or to a word W d in, um oration. Highlight the appropriate syntax element in the code window and then press have one to get help on a particular object, property or method from the object explorer. Navigate to the appropriate method or property that President one 14. 2.5: VBE Options: in this video, we'll look at how you can modify the way in which the visual basic editor works by customizing the settings in the Tools Options Dialog Box in the Exercises folder and in the Section two sub folder, Let's Open 05 VB Options a Man. Let's go into the visual basic editor, and here you'll find we've got some code in a module just named Body or one, which is really taken from some of the exercises that we've completed in previous tutorials and will use this code as we discussed the way in which you could customise the operation of the visual basic editor. Given that the visual basic editor uses the old school Microsoft interface found in Office 2003 and earlier, you won't be surprised to learn that in order to customize the way in which the program functions, you're going to tools choose options. Because, of course, this is the way in which Microsoft Office programs traditionally worked the options. Dialog contains Four Tabs editor, and it's a format General and daqian. The options in the editor tab mainly relate to the help that you receive as your type in your code in the visual basic editor. The very first option is auto syntax. Check on this, I think, is really something of an overkill option, which alerts you every time you make an era. I'll give you an example of why I call it overkill. Let's say that we're about to types and code at the end of this subroutine. Let's eight an if statement, Um, I don't think we have discussed if statements yet, but that's beside the point. Eso I start my statement with the key word if and then I think of something else that I need to track, something I may have missed out. So I want to leave the if statement incomplete and just go and deal with that other situation. So I move up the page, and as soon as I leave the line up comes this rather going pop up to tell me I've made an error and of course I know made an error because I haven't finished the line. So I click OK, and then, from then on, I'm fine. So my preferred set in is to simply deactivate waters in tax track. Let's just look at the difference, so I start my F statement decided to do something else. And when I leave the line, I don't get the annoying pop up message. However, you'll notice that the incomplete and therefore incorrect line does go red so I can tell that I made an era even without it, sort of yelling at me. So it's sort of subtly reminds me that I've made an error rather than shouting at me, so I'd advise you to leave that switched off. Then we've got require variable declarations. Variables are a very important part of V B A. And in fact, the next section is devoted entirely to working with variables. And the very first video deals or includes a description off this feature require variable declaration so you'll learn all about that in the next section. The next three options determine the intel ascents, or auto completion options within individual basic editor. And these are a big time saver as your edit in your code. So let me just show you how these work festival alternate members. So if you remember the keyword members when we would discuss in the Object browser as we click on each class or objects on the left here on the right, we see the members off that object. In other words, all of the methods properties on, in some cases, events which are associated with that particular object. And this is what this option relates to. So if you enter a key word, which is descriptive off a particular object, when you follow that with adults, all of the members of that objects are displayed on. In other words, the list that's displayed tells you all of the legal syntax that you were allowed to put at that point in your code. So it's a very useful feature. Let's take an example. Let's say that at the end of this sub, so if you remember, this is just a dummy sub, which simply displays a message in each of the subroutines that have called from the main sovereignty. So let's say at the end of the final message, or perhaps at the end of each message, we want to add the user's name on the syntax for picking up the user name from the Excel interface is application dot user name, and you're probably familiar with the concept of concatenation where you string text elements together and you could do this when building string formulas in Excel. So what we do here, let's say we're doing it on all of them. Put a space or perhaps commerce space. And then, after closing the quotes, who then put ampersand application dots. And, of course, because the option to display members is activated up come all the members and no prizes for guessing that user name is what I then put perhaps like an unfinished that off with ampersand exclamation mark. So I could then copy that on Place it on each of these, say, when I run. My three messages now have my user name at the end of them. So if I were to switch off the option waterless members, it simply means that when I start type in application dot you'll see nothing happens. So I can't think of any reason why a someone learning visual basic you would want to deactivate that option. Let's just don't do my lost changes. Believe that, as is so that will remain switched on. And then we have auto quick info, and this is very useful when you're typing methods and functions. So, for example, we recorded this code But if we were typing it, let's Brie time that line, for example, not execute. So that's the auto list members kicking in. So I simply press tab when executed, highlighted to insert it. Then when I put a space, the auto quick info kicks in, and this basically tells me all of the parameters required by this particular method. As in the case of Excel functions, any parameter, which is enclosed in square brackets, is optional. So again a useful feature. It helps you to confirm the name of your parameters. And, of course, it's useful, as we'll see later to always name your parameters. Justus. The macro recorder did when we recorded this. So let's just undo those changes. The auto data sips feature is used in debug in your code when you're working in something called Break mode and we'll discuss this in Section eight, which is all about error handling and debugging, I'll just briefly mention what it is just to give you a taste of what's to come. We're trying to track down an error within this subroutine. We can put a break points on any line to do that you fling in this column here. And this means that whenever I run the macro, it will automatically going to break mode at this point, enabling me to execute the macro line by line and monitor how things are going at each point safe. I now run, you'll see it stops on this line. This line hasn't yet been executed, and then I can step into, which means basically execute each line one at a time. And as I do this, if I hover over various lines of code, the data tip pops up and actually tells me what that element contains. So you can see here. It's telling me Replacement, not text currently contains full stop space. And the reason for that is that the line that's currently highlighted is the line which will be executed when I next step into, rather than which has already been executed. In other words, this replacement dot text is the last one that was executed. When I step into again, then if I hover over replacement, not tax deals, yet now contains the close parenthesis space. So that's just a quick taster of what's to come. We'll discuss this in detail. Let's just come out of break boat and removed the break point. We'll discuss this in detail in Section eight on error handling on deep Again. So let's just get back to tools options. Then we have, ah, Windows settings. Dragon Drop texted it in very useful. It allows you to move lines or any code around simply by dragon. And, uh, it's It can also be used for copying. So, for example, if I wanted to change the order in which some of these were executed, let's say I want to do the question mark last. I can just select all of these lines of code and just drank them down, like so to copy. As you probably know from other environments, you hold down the control key, so if I select these, I've got control held down. As I drag, I now get a copy of those lines, and you do this very often. We've got similar lines of code just to get yourself started, and the trick is to hold his. Remember when you use that technique to modify the copy you've created, and of course, everyone always forgets to modify one bit. But such is human nature, and then we've got a couple of very simple, that useful options defaulted Full module view. Basically, I've got several subroutines in here. On full model of you is the default, which basically shows all of them simultaneously at the bottom. Here, I can go to procedure view Andi. At this point, I'm only seeing one subroutine at a time in the top here. I've got the procedure drop down menu, and I can choose a particular procedure to go to it. So if you find that as your macron's get longer, you prefer to stay in this procedure view where you're just looking at one procedure at a time, then obviously you can just go in here and switch off. Defaulted for Marty. Old view. If we credit new module, you'll see it's now defaulted to procedure view automatically. Let's just remove that model, and then we've got a very simple one here. The procedure separator. That's this line, which separates all the sub procedures, and it's usually a good idea. It makes that much easier to make sense off. If we switch it off, they simply disappear. In the editor formats. We can change the colors. Probably another good idea mess. You tend to use one machine exclusively all the time because every time you go to a new machine, you'll be faced with the default colors. So if you want to, you can click and specify what text looks like when it selected. So that's it's normal appearance. Do you want era text to be a color other than red and the break point text that we saw when we would discuss in there This option bought her data tips. Do you want the yellow highlight or do you want another highlight? So, as a general rule, you'll want to leave all of these using the default colors again, We have comment text as green so you can easily distinguish comments from ordinary code. He would text things like End with end sub, which are basically components of E B A, rather than names that you have created for your own variables or literal text and so forth on these going to dark blue. Okay. The other thing, of course, you conduce is to simply change the forms and size. So courier issues because it's a mono spaced fund, which makes it easier to get things toe line up. Um, and obviously if 10 if size, tennis and comfortable you may like to increase that size. Teoh. Make it easy to work with, then in the general Tab, we have two main types of options. We've got options relating to use of forms and working with those. And then we have options relating to the handling of errors. User forms. Air used for customized interface is very straightforward there, covered in detail in Section seven, that just to show you quickly, we can insert eight years of form and you'll notice that we have a grid on the user form. So as we create in various controls, such a strike zone pool your name. This is a label control, and then next to it, we might put a textbooks control to the user to enter their name. And as we work in with these controls, the grid makes it easy. It's a line them up so we can customize the grid space in, and we can just specify how tight we want agreed to be. So, for example, we might have 10 by 10 and you'll see we've now got a looser great let's just put that back to the default so again. I won't mention all of these options. I won't discuss these options at this point when we come to the section on debugging and error handling will have a look at them there. These last two options here quite straightforward. Show tool tips on collapse Project hides windows. Uh, when you hover over each of these, you'll see it displays a tool tip. And why would you not want that? So if we switched that off General show tool tips And this time, when we hover over the each of the tools, no tool tip appears. So I would imagine most of the time you'd want to leave. This switched on. Let's just reinstate show tool tips. And finally we have here collapse project hides, windows, and all this means is when I click to collapse a particular project. All of the windows related to that project disappeared, so I had a couple of windows open. As soon as I expand back, they come, and if I go to the window menu, I can see them here, and this is quite useful, especially where you've got several projects open several workbooks open, and obviously each workbook contains a project So you end up with lots of windows open as you close or collapse. Each of the project the windows related to that project will disappear. A commitment easy to find your way around. The final tab is simply docking. And this relates to the way in which you can position the various floating windows, which are available under the view menu so you can see by default. They're all DACA ble once, for example, bring up the project sort of the properties window and it stopped on the side here. So for some reason, I wanted to dock it somewhere else. I could just pick up, knew that now it's docked at the bottom. Now, if you wanted above the project, I could docket here and then made. It suits normal position below the project so that behaviour is normally fairly useful. You might want it to be switched off. Perhaps if you're working on dual monitors and you want to sort of keep different windows on different monitors. But as a general rule have in the dock in features by useful in makes it easier to keep your screen nice and tight and tidy. Okay, so those are the various options that you can set. As I said, there are a couple of them, but we haven't discussed. Just remind you require Variable Declaration will discuss in Section three, which is coming up the next section. Very first video. We'll talk about that, then Auto data tips on the options here all relate to error handling and debug in which the topics discussed in Section eight on them. I don't think we'll say any more about the form groups settings. That's fairly straightforward. So it relates to use of forms basically what you discussed in Section seven. The settings in the Tools Options Editor tab allow you to dictate which auto completion and help messages displayed. Apart from the annoying auto syntax check, it's best to leave all these options activated settings in the Tools Options Editor four Back to control the font size and color used for the different types of VD. A syntax. The Tools Options General Tab contains two types of settings. Options related to the editing of use of forms, which will be discussed in Section seven events, dialogues and user forms and options related to the wing, which VB a handles errors these will be discussed in Section eight. Debugging and error handling the settings in the Tools Options Doc in Tap Control, which floating windows could be docked to the sides of the visual basic editor. 15. 2.6: Project Properties: in this video. Well, look at Project properties. These settings allow you to customize the way in which products are displayed in the Object browser and also to lock project from viewing so that users could only see your code once they supplied the appropriate password in the Exercises folder. Let's go back into zero to and then 05 vb e options. And this is the file that we worked with in the last video. And here is everything. As I left it, I save my changes. There are two ways of specifying project properties. You can either right click on a project to his project properties from the right click menu . Or you can highlight the project. Go to tools. It's his product properties from there. The option that you see displayed in the menu depends on the name of the project, which is one of the things that the project properties allow you to change. So here the name of the normal project is actually normal. Hence, when I right click, I see the choice normal properties, and similarly, in the tools menu, it reads local properties. Whichever method you choose, the product properties dialog is displayed and it has two taps General and protection in the general town. You can enter a unique name for the project, and this is quite useful. If you typically work with several projects at a time, they may have a couple of Adan's a couple of templates, all at the same time. Having a unique name for each one becomes school useful. So what will be a descriptive name for this project? Well, we were using it to practice Lee B E Options. So let's use that is the name. No space is allowed so I could put it under school or nothing at all. They will call it VB Options, and then we can give it the description. Let's just put practice on set in be options. These options of the bottom relate into the help file, and conditional compilation arguments are fairly advanced. And unfortunately, we won't have time to delve into them. In this course, let me click OK, you'll see instead of the word project. We don't have V B E options and you'll notice in the properties window. We have yet another way of trading today as well as this. We go into the object browser in the library's drop down menu, you'll notice that Alavi be a options is displayed. It would have been displayed even if we'd left the default name Project in placed. But clearly giving it a descriptive name of our road makes it easy to recognize when we choose it, You'll notice that the description that we wrote is displayed in the description tab at the bottom of the object browser. So when I click on Module one, you'll see all of the subroutines that we've got are displayed. So when you've created a complete VB, a solution, this facility is point useful because it gives you a way off this monitoring exactly what you've developed and of navigating to specific parts of your solution. Let's just go back into tools you'll notice instead of project properties. We don't have VB options properties and let's look at the protection time and here we have the facility off lock in the project for view it. So if we switched that on and I'll just pop in a simple password, let's just take it 12345 In order to see this work in, I'll just need to closed the document cancels his clothes word for a moment, then just go back into the document. And this time you'll notice that the project is collapsed. Normally it opens in the state in which you left it. But obviously once it's been locked, it's always collapsed. So when I try to expand it, it asks me to supply the password. 12345 And only then will it allow me to expand. Explore the project. Okay, so those are the two tabs that you have available for set in project properties. And as you can see, both camps a fairly straightforward and they are quite useful as you start developing more projects and put it more more code inside the the general tab of the Project Properties Dialog allows you to name the project and provide a description, both of which it displayed when the project issued in the Object browser. The protection tab allows you to lock the project so that it can only be viewed after supplying the passport that you specify. 16. 3.1: Declaring and Populating Variables: variables are a facility found in all programming languages. They allow you to store information required by your programs and to refer back to that information and modify it at any time. In this section, we'll look at how variables are used in V B A and in this first video, well, look a declaring and populating variables in other words, place in data inside them. To get some practice on, working with variables will create a macro in the word BB. A train in template. Let's go into the training folder and to open the word BB a training templates. We right click and choose open. Then that's going to develop visual basic and into the Section three modules s 03 Variables and constants. Unless creating on macron, we'll use the prefix s 03 clearly one A, and we'll call it a variable basics in V. B. A. You can use variables distal two types of elements. First of all, regular data such as text numbers, dates, etcetera. But you can also use variables just all references to all of the word objects that you intend to manipulate. You can store references to documents, paragraphs, tables numbered lists bulleted lists. Anything that you plan to manipulate will do things well within your code. So what we'll do here is will practice declaring on populating both data variables and object variables. First of all, let's look at declaring variables to declare a variable. Allows the V B a compiler to know how much memory toe allocate floor that type of data. It also tells the compiler how the data is to be treated, whether it's to be treated like text well, like a numeric value well, like a date to declare a variable used. The key word did, as in dimension, and then you create a name for the variable. The restrictions are name in variables of the same as the restriction on name in modules. The name has to consist of letters and numbers and the underscore character, but it can't begin with a number, and obviously the name cannot contain any spaces. In the interest of clarity, it's useful to use a prefix with the name of variables to indicate the type of data that the variable contains. In our example, let's say that we're beginning a macro, which requires a new document to be created and we want to ask the user to enter a title which will then insert as the first paragraph within that new document. When the user enters the title Wiccan Stories in a variable, the title will obviously be text Onda VB, a data type which corresponds to text, is strained. A popular prefix to indicate that a variable is to contain a string is SDO, so we'll call this variable str title. Next we used the key would as and as soon as I type of space, you'll see that intelli sense displays all the different types of element which can be stored in a variable. The reason that this list is so long it's because it contains a lot of word objects as well as data types. So for this variable, we need strange having type str String is highlighted, so I just press the tab key to insert it. Declaring object variables is an identical procedure. I tied him again. I can use it prefixed. So now I'm creating a variable toe hold a reference to a document. Don't use the prefix DNC. Let's call with D O C. New as and as soon as I start to type document. It's highlighted, so I just press the tab key to insert it. Now let's look assigning values to our variables. First of all, will assign a value to our string variable. To do this, we used the key word let then the name of the variable and then the Assignment Operator, which is an equal sign on the right of the equal sign. We now have to put any statement which the compiler will evaluate as a string. So obviously the simplest thing we could do is to put a literal value, for example, and obviously this is a strength that goes in double quotes. This doesn't give us very much flexibility, however, so let's undo that. And instead we want the user to enter the title. Now we've encountered the MSG box function, which displays a message. Its partner in crime is the input bought sanction, which displays a dialogue containing a text field into which the user can enter some data. So it's like input box open brackets and then word promises. By displaying the parameters required by the input box function. You'll also notice, after all the parameters, the words as string. This indicates the type of data returned by the function. So, in other words, to function returns a string which consists of the data entered by the user in the text field. The only parameter that's required hence the only parameter which is not in square brackets is the prompt. The message, which is displayed above the text field instructing the user what we require them to enter . So we'll simply say, Please enter title. I haven't got the title. We now want to create a new document and put a reference to it in our document variable. To populate an object variable, you use the keyword set in contrast to let which is used or data variables. So it's set lock on you and again the assignment of razor equal, and we now have to put any statement which refers to a document. The statement that creates the document is such a statement. It returns a document object which consists of the document you have just created. So it's legitimate to place that statement after the Assignment Operator in V. B. A. To create a new object, you used the object collection to its that object belongs so in the case of a document. We need the documents collection on the function or method which actually creates the new object is add. Although we don't need any parameters, I'll put in the opening parenthesis so that we can have a look at the tool tip which is displayed for us. What I wanted to show you is that after all the parameters, the optional parameters in this case which we don't need, you'll see the words as document. This indicates that the ad method or function, returns a document object. This is why it's legitimate to use the statement in this position because of the rule is after the assignment. Operator, you have to come up with this statement which produces the type of data. The corresponds to that variable. So we have a document variable. We've declared it as document. Hence we must come up with a statement would refers to or returns the document object. So on this occasion we simply want to create a standard document based on the default normal template. So we don't need any parameters so we can just delete the parenthesis is the final thing we want to do then is to insert the title that the user has entered into our new document To refer to our new document, we now simply use the name of our variable. So it's doc new and then we can use the useful property content equals str titled and to make it into a paragraph. Let's tag on using the concatenation operator and percent the carriage return and there's a special V Be a constant, which stands for carriage return, which is V B C R. And that's it. So let's say and let's test our macron So up comes out input box. There's our prompt Please enter title. I'm just going to answer annual report when I click. OK, my new document is created annual reporters inserted. If I show hidden characters, you'll see I've got my return at the end. So let's just close that and let's save our changes. Unlike returns of the visual, basic editor just like to make a couple of points regarding the Declaration of Variables and the assignment of values. Two Variables festival, the contrast between let and set. So we use let when we assign a value to a data variable and we use set when we assign a value to object. Variable, however, whereas the set keyword has to be used, let is optional. So you'll probably never see this in any code that you see in in books or in code that new , uh, encounter on the Web, for example, because it's optional. No one bothers to use it. However, I will point out this contrast from time to time just to remind you that there is this distinction between the way your sign values data variables on the way that you assign values to object valuables. The second thing I'd like to point out is that variable declaration itself is an optional step. You don't have to do it, but it is highly recommended, and I'll give you an example of way. It's a very useful if you decide, as you should do that, you are going to declare all your variables. Then you should use this declaration option explicit at the top of every logical, and I'll give you an example. But, boy, it's useful to do this if I take out option explicit. The step of variable declaration is now optional, and one consequence of this is that if I make a typo when I enter the name of the variable . So let's say here, what's next to the are the tea is next to the are so let's type of tea instead of an hour. So mistyped the title. Let's run our macro one small and we click OK, and when I look at the outputs, I have my knee document. But the title that I answered is not in the new documents, so obviously it won't take along in such a simple example. It won't take me long to track down what the problem is. There are times where the fact that there is an error stays hidden. You don't even realize that there is an error there as a consequence of this simple mistake that you've made in miss type in the name of the variable. So let's look at what happens when we put back option explicit. As soon as we try to run the macro, it doesn't even compile, so it doesn't run. It tells us this variable hasn't been defined, and obviously it's not going to take us long now to know that we've simply mistyped the variable that we declared. You don't have to type option explicit yourself. You can have it automatically inserted at the top of every module on what determines this is the setting in tools. Options require variable declaration, so as long as that is switched on every time you create a new model. So let's just insert module. You'll notice that option Explicit goes in there, and it then means that you have enforced variable declaration. And as we've seen, it's very useful. Let's just remove this. So now that we've had a look at the basics of declaring variables and assigning values student in the next video, we'll move on to look more closely at the different types of data that you can store in variables. Although variable declaration is not essential in V B. A, it is good practice to use the option Expressive declaration. To make it required to declare a variable used, the keywords dim and as to assign a value to a data variable, use the optional keyword, let followed by the name of the variable, followed by any statement which evaluates to the correct data. Time to assign a value to an object. Variable used the obligatory keyword set, followed by the name of the variable, followed by any statement which evaluates to the correct type of object 17. 3.2: Data Types Part 1: although variable declaration is not compulsory in DB A, it's definitely recommended that you declare Alvar Ari a bles that you intend to use, as well as the type of data that you intend to use them for. Declaring the data type of a variable leads to clarity in your code and helps to avoid errors. In this video. We'll talk about the different data types that VB recognizes, so let's go into the Exercises folder and in the section three folder Variables and Constants. You'll find a file called data Types. So here we have a table, which just gives a breakdown. But the most common types of data that you store in variables in the top part of the table . We have primitive data such as text and numbers, and in the bottom section we have some of the most popular word objects that you tend to store references to in your variables, as you can imagine. Since word is essentially a word processor, the string data type is probably the most frequently used and in this course will be using the prefix str. Every time we declare a string variable. A string refers to an indeterminate number of characters, so any combination of the text that you see on this page could returned a string. Then we have whole numbers, integer and loan, and the difference here is the range. So the interview data type reverse to a fairly small range from minus approximately 32,002 positive 32,000 and some change and then the long goes from approximately minus two billion to approximately positive two billion. Then we have single and double. These are the floating point numbers, which allowed numbers after the decimal point and the main difference between them. They're both suitable for holding very large numbers. The main difference between them for most people is the accuracy that you require. So with single data type, you have six decimal places of accuracy with the double. You can have up to 14 numbers after the decimal point for monetary values with then got currency, so from minus 900 trillion up to positive 900 trillion or thereabouts. And it allows up to four numbers after the decimal point of accuracy. Although of course, monetary values only normally display two numbers after the decimal point. In order to achieve accurate calculations, the currency type allows up to four numbers after the decimal point. Then we have the date type, which is used for dates and times, and then we have 1,000,000,000. And if you haven't come across the 1,000,000,000 data type before, it basically stores one of two values. True or false if you don't declare variables. The default data type in V B A is called variant and are occasions where you will explicitly declare a variant. Basically, a variant is a rapper mechanism rather than a data type, which enables Vehbi eh to work out and keep track of the underlying data type and convert the values in the variable appropriately. So best practices not to use the variant unless the situation that you're working with the type of data you're working with is unknown or unpredictable. Stay away from the variant data type and always explicitly type your own variables by using the as keyword. Another thing to bear in mind when working with variants is that because VB A has more work to do, variants take more memory and also make programs slightly slower. We had a brief look in the last video that declaring variables for use with objects such as document and here we've got a run down of some of the other objects that you very frequently used in this way and store references to in your variables and just remind you when you're assigning values to variables, you used the key word let, which is optional when you're talking about data variables. So because it's optional, no one tends to use it, and he used the keyword set when assigning values to object. Variables. Set, however, is obligatory. In this section I will be using let just to remind you that let is as it were, your positions set and just to get you thinking about which of the two key words is appropriate that the variable that's being declared. So what we'll do now is just to write a macro that allows us to play with a couple of these different data types. I will store the macro in the word VB a train in template, but we don't have to open it directly. All we need to do is to attach it, attached the template to this document, and we can then access the modules in the template via this document, to do that, we go across the developer, click on document templates and then attach. Then we work our way to the Exercises folder, which in my case, is on the desktop. And here at the bottom you'll find the word VB a train in template once that's been attached. If we go across to the visual basic editor, you'll find that all of the modules in that template and now accessible and we can write Macron's directly into the template. So here's the micro, he wrote in the last video. Next they'll do another one, so it's best 030 to micro A. I will call it data types, so we'll begin by declaring a few variables. So let's say Tim str title Strange Tim int words as integer Tim LNG characters or cars as long them sng average a single on dim LNG average as double. And then let's define a couple of object variables so we'll have them d o. C. As documents. Daniel Tvl as table. So now let's populate these variables. So we'll say that str title will be the title at the top of this document, which we can pick up by looking at the first paragraph of the document. So for that reason, we'll need to populate our document Variable first, because it's an object we use set, do you see equal to And we could use active documents. Well, we could refer to the document by name to refer to the document. My name. We would say documents and then in brackets and quotes, we would put the name of the document. So I'm just gonna go back to the desktop, just copy the name of the document. And now we can look inside D, O. C or Doc because that variable name Mary first specifically to that document. So we all now use let's as a settle where it's optional. I'm gonna keep using it during this section just to highlight the contrast between let and set the str title equals stock, not paragraphs one which means the first paragraph, of course. And then the paragraph is an object. It's not actually a string and of course, we're populating a string variable, not an object variable. So you've got to drill down inside the paragraph, object to find the string which represents the text inside it on the way to do that is to say, Don't range, not texts. Then let's populate our words and characters. So we're still on data variables here. So it's gonna be let again into words, and we're looking inside, Doc. No words don't count on the dot count property is how you actually pick up the number of items in a collection. So we've got the words collection and not Count says how many there are. We do exactly the same four characters Let LNG cars the call Doc Doc characters count and now the average. So we've defined SNG average and LNG average really to highlight the difference in precision between single and double. So let SNG average equal LNG cars divided by into words, and that will effectively give us the average word length. Then I can just copy that line and change that to D. B. L. So you got the same calculation. But it will be interesting to look at the result that we end up in these two variables and then finally, let's populate our table. So you back now to a word object. So it's set tbl equal to Dr Tables one because, of course, we know there is only one table, unless just a couple of extra variables to actually hold a number of rows and columns. So dim int rose as Inter joe them in coals as indigent. So, having populated our table variable, we can now populate the two data variables that go with the table. So let int rose equal Tvl don't rose, not count flit into Cole's Bqool Tbl columns that counts, and that's pretty much it. 18. 3.2: Data Types Part 2: that we could write some more code to perhaps create a document and output all of this information. But instead of doing that, I'm going to show you another feature which will pick up on in the section that will come to later on debug in an era handling. And what we're going to do is to go into break mode, so we'll execute all the lines in our macro and then going to break mode. Brake mode is a method of Friesen code execution on one of the things that you can do in the visual basic editor when code execution is frozen is to just hover your mouse over the names of variables to see what values are currently in those variables. So rather than bothering to output them, we can just going to break mode. And then after all the lines have executed, we can then just examine the variables. So in order to ensure that we don't go into break mode before all our variables are populated, we need to put our break points on this line because the line that you put the break point on doesn't itself execute so that it says n sub I put in the break point on that line. The end sub line will not yet have been executed. And hence our program Al Macro will still be running to put a break point. And even if you're not aware of this, you may well have done it accidentally. You click on this power next to the appropriate line that dot then appears so. If you do do this accidentally, just click again to get rid of it. Okay, so we're gonna enter break mode on that just before end in El Bakraoui. Now we can run our macron, and here I've just realised I've made an error, so let's click. OK? When I declared my double, I used the wrong prefix. I used the prefix for long instead of the prefix a double. So apologies for that. I'm just gonna go back and correct that d b l. And then it's run again. So nothing happens as far as the interface is concerned. But each of our little variables now has a value inside it so we can hover over each one for the document object. It just shows you basically the name of the document. Identify it. Str title. If we can get over, that has be be a data types. And the reason that you don't see the close quotation marks is that it includes the return at the end of the paragraph. Then we've got a number of words is 275 number of characters 952 And here we got the single . So the diff the main difference between the single and the double is gonna be precision. So if you count the number of numbers after the decimal, you'll see it. Six. Where is with the D B L? We'll have 14. So that's the main difference between single and double. Then we've got the number of rows and the number of columns in our table in the upcoming videos. Within this section, we'll talk more about working with string variables, integer and long variables, single and double variables and date variables. However, before finish in this video, I just wanted to make a couple of points regard in the variant data type. So let's come out of brake mode. And to do this, we click on the reset button just here, and then we can click on this bullet point just to get rid of our break point. The variant dated Sybers, I said, is really a rapper. Data type on what you're doing by using the variant data type is instructing Vehbi eh to make the decisions regarding the type of data that a variable contains. By doing this, you're obviously leaving yourself open to the possibility of errors creeping in on unpredictable results. So it's always best to explicitly declare the correct data type to reach variable that you create on those rare occasions where you do have to use a variant, you declare it in the normal way, then vnt whatever. As variant, however, those occasions are fairly rare. So let's get rid of this. What you should perhaps be aware off at this stage, it's how you can accidentally end up with a variant data type. The first thing you could do, of course, is not toe have option explicit, So if option explicit is missing or deleted, you can then refer to a variable without having previously declare it. So we could say, for example, let I n t cells equal tbl doc Rose brackets worm, not cells. Don't count on this is now perfectly legitimate, and we can prove that by switching on break mode again and just running our code. Then if we hover over in cells, weaken, see? Yes, it has got full. But what's the data type? Basically, because we haven't declared it. The data type is variant. At the moment. There's not much risk risk of anything going wrong, But obviously, the key point is, variants are unpredictable. So that's the first way in which a variant can accidentally creeping. Let's again reset on switch off the break point. Let's reinstate option explicit at the top there and perhaps just lose this line. Another way of accidentally ended up with the variance is where you don't declare the type . So if I take off our string, I haven't broken any rules. Because all options explicit insists on is that you declare the variable. It doesnt insist that you declare the type so domestic title is fine. The fact that it has STRS the prefix is irrelevant. That's purely for my benefit. It doesn't have any impact on what BB A does to my variable, so that's the second way of ending up with a variant data type. Let's just don't do. Another way is where you'll declare in several variables of the same time. But you use the syntax, which seems logical but isn't let me explain. So so we had to string variables on to save time. We wanted to declare them on the same line. The correct way of doing that is to put comma second variable. So let's say it's going to be table text, so str table text as string. So that's the correct way of declare. In two or more variables. You just separate them of commerce, and you then repeat yourself the full syntax. But what seems logical, although it's not, would be to do this. So you say to yourself, Well, actually, they're all string variables. Why don't why do I need to say as string every time? So you take out all the air strains apart from the very last one, it takes up less space. It's less work on DIT compiles with no errors. However, what you actually end up with is one string variable, the one that has as string after its name. All the other variables on that line will be variant because there's no as, and there's no data type actually explicitly declared. So that's another way of ending up with the variant data time. That's just tidy this up that will get rid of this. Okay, so now that we've got an overview of how you work with different data types, when you declare variables in the upcoming videos, we'll look in more detail at working with the different data types working with strings, integers, longs, singles, doubles, dates, etcetera. So let's just close the visual basic editor and close this document. So word is now gonna ask us whether we want to save this document. Well, quick, yes, we do save. But it's also now gonna ask us, Do we want to save our changes to the template which we attached to this document? And of course, we do. So we click on Save Again. As well as declaring variables, it is important to declare the data type of every variable. Any variable, which is not declared for whose data type is not declared, becomes a variant. Variables of the variant data type, a slower to process, use more memory and can lead to erroneous results 19. 3.3: String Variables: in this video, we'll take a closer look at working with strict variables. Well, look at declaring fixed length as well as variable than strings, and we'll also look at a couple of VB a string functions. Let's go back into the Exercises folder that let's open up our word. VB a train in template, right click and open, and then let's go into the developer tab on visual Basic. If you're Section three, model is not still open. Just double click to open it up. That's scroll to the bottom and create a new macro. So we're now in 03 03 mackerel A. Let's call it strings. Let's now switch across to procedure view. Just a the bottom here. If you click on the procedure view icon in procedure view, you only see one macro at a time to access other micro's. Just go to the top right to the procedure. Drop down menu and you can switch back to any of the other micro's. Let's start by declaring a couple of variables. We've already seen how you can declare a variable, which will hold a string of an indeterminate length. You can also limit the length of a string When you declare it, let's take an example of both. Let's first have a variable into which weaken store the users first name so dim. Str first name as string So here there's no upper limit on the length of the string. Now let's say we want to ask the user to choose a user name and that it's gonna have a limit off 10 characters with no spaces so dim. Str user name has string and to limit the length, we simply use an asterisk followed by the number of characters. So asterisk 10. Let's now use the input box function to populate these two variables. So we're dealing here with data variables so the key word would be let equals input box and then in brackets will put the prompt Please enter your first lane and then let again full str use the name. Please choose a user name that's now insert a break point on the end sub line so all our code will execute and just before the end sub will go into break mode so we can have a look at the variables. So we run well. Enter grants, click OK and then as a user name. Let's enter Red Hippo and let's go over the limit. So hippopotamus. So what? We click OK if we look at str first name. No problem. It's what was entered when we look at str user name, it's chopped off what was entered and we've only got effectively the 1st 10 characters. Let's exit break mode by clicking on the reset button and let's remove the break point B B A has a number of functions designed to be used with strings. Let's just take a look at a couple of them and I'm sure we'll encounter several war during the training course. Let's say that we actually need tohave str user name completely not the case and with no spaces. So the statement we have so far gives us whatever value is entered by the user to remove spaces from whatever they answer, we can use the replace function, so we start our statement with replace because it's a function within a parentheses on the tool tip tells us that we need three obligatory parameters and then we've got a Siri's off optional parameters. The first parameter is exactly what we already have. That's to say the string inside of which will be searching so we can put a comma and move on to the next parameter find, which is the string that were searching for. And this is a space, obviously, because it's a string, it goes in quotes. The third parameter is what we're replacing it with and because we're replacing it with nothing but still with a string, we simply put quotation marks open and close, but with nothing between them and that ends our function. So so far we have a statement, which gives us whatever the user enters minus any spaces that they put it. We could now wrap a second function around what we've got so far to give us an uppercase version of this string. Again, the function goes at the start, and it's you case. And then we put the statement that we have so far in parentheses. After the name of the function, let's test the macron, so I'm going to reinstate our break point and run. Now let's see what we've got. So the space science it is removed. Everything goes to upper case, and we've got do hip hop will be working with strings throughout the course, so you will get plenty of practice on doing all the best. Ah, so let's leave it there for the moment and we'll move on to look at working with number variables, So I'm gonna click resets to come out of brake mode, remove my break point and then save out changes to declare string variable of indeterminate length. Use this in tax dim str variable as string to declare string variable whose length is fixed . Use this in tax dim STL variable as string star ex, where X is the number of characters allowed. B B A. Contains a number of functions for manipulating the contents of strings. 20. 3.4: Number Variables: in this video pulled its and more work on number variables will look at a couple of useful number functions. I've also looked at how VB A handles the distinction between raw numbers as used in calculations and formatted numbers, which have characters added to them, such as commerce, in order to enhance readability. Let's go into the word BB, a train in template, and we want the Section three model variables and Constants and let's add a couple of Micro's. The 1st 1 will do is a simple macro, which will convert a Farron night value to Centigrade and to give us a chance to practice on useful techniques. What will do is to display the result in a formatted fashion. So instead of simply displaying the raw number that the calculation returns will format it to make it more user friendly. And we'll put the degree symbol, Um, at the end of the the number. No, we compare V B A with Excel, so let me just go across to excel enter a random number, and I'm sure you're aware that in excel you conform at numbers to look exactly as you want them to, so I can choose currency and here I've got thousands separators and I brought two decimal places with, in my case, the pound sign. But the raw number that I entered is still stored by Excel. So Excel takes care of both the wrong number and the formatted number for you, Aaron v. B A. You have to take care of both parts. Both numbers yourself. So what we'll do for our temperature conversion example will have to number variables. They can use this single data type, but then we'll have to string variables which were equivalent to the but which will contain formatted versions off those two numbers. So let's create our macron s 030 full macron a and we'll call it tempt conversion. And then I'm going to switch across to my preferred procedure view. If you prefer for model of you, feel free to stay in that Do. Let's begin by declaring our variables so dim sng far, which will be the paranoid value as single. Then sng sent a single and then let's do the string equivalents. So then str far a string and then str sent a strength. Now let's populate the Fahrenheit variable by using the import box function. I'm gonna keep using the optional keyword. Let again just to draw your attention to the fact that these are data variables. They're not object variables, but sng, huh? He cool in four books. Isn't ah number in Charing Points? Then we've got to convert that to center. Great. So let's sng sent equals on the conversion as soon as I remember is you take off 32 so sng for minus study, sir. Then you multiply by five divided by nine. So now we've got the numerical value sorted out. We want to display the result, but use formatted number values. So this is where our string variables come in. So let str far a cool and to actually generate a formatted number, we can use the format function. This is a string function which can take a numerical input value and return a formatted version off that number which includes non numeric characters. So for meant, the expression will be sng far and then the format goes in quotes and it's done in the same way as excel custom formats. So let's put hash coma Hush hush 0.0 So these placeholders means that if there is a number in that position, it will be entered in the result. Otherwise, no zero will be inserted where zero means if there is a significant number at that point, it will go in. But if there's not, zero will be inserted in its place and similarly will have two decimal places and it will round 22 If there are more than two decimals in the results on, we can do the same thing for SDR sent. So we're gonna copy this line on the way I normally did is just move the cursor to the left click ones to select the whole line and control See followed by control V twice. Because the line is highlighted. The first control of the which is, of course, paste pastes over the selection so effectively nothing happens. So you then just repeat control V to get the second line. So this now becomes str sent and snd sent. So now that we have our formatted numerical values, we can output the But why don't we just add in the degree symbol since you've got words very convenient insert symbol at our disposal. I'm just gonna nip across to that insert symbol and more symbols. And then let's scroll up, Scroll down again and there's our degree. Sign me. Insert that now I'm gonna use cut. We don't really need to leave it here, So just cut it into the clipboard return and then we could paste it in here on in here. So now we can output these two variables in a message. Let's just put that on that blank line. Chemistry box. Presti off ampersand Karen Heights It cools and percent str sent 10% centigrade. What's test our code, But before doing that, it's always a good idea to save and then run. So let's see what 85.52 Fahrenheit is equivalent to 35.52 degrees Fahrenheit equals 29.73 Sent a great So see him in. We've got the right formula that looks fine Before we leave this example. There's a very useful function that you can use whenever you ask the user toe. Enter a numeric value and this is the valve function. Well, to show you why it's useful if we run the macro again on the user says I'm interested in 79 degrees as my input. When we click OK, we get a type mismatch error because of the presence of those textual characters That's just like on end. Now, as we go through the course, we'll learn more facilities for test in values that you're about to place in variables. But Vow is a very simple way of ignoring those textual characters. So if we wrap the malfunction around our input box like so, then when the user enters degrees, the valve function simply stops reading characters when it encounters something that's not numeric, so very often that will just make the input box function bit more robust. Okay, I'm going to switch back now. It's a full model of you on the reason I do this is so that I can be sure that the next macro we create goes exactly where I wanted to. So let's write our second macro, and what we're gonna do this time is to work with inter jizz. We're going to ask the user to enter their age, and then whatever age they enter, we will take off 20% and then display a message which says, Really, you don't look a day over whatever. So if they enter 50 as the age we take off 20% leaving us 40. So we display a message that says, Really, you don't look a day over 40. So let's call this one greet user, and then I'll just get across to procedure view. So we'll dinner variable to the user's name. Str name. Just string Ben for their age. Brianti age as integer, and then we'll have a separate variable to store the age that we want to display. So let's call that dimmer pence like that age as anti Gia. And then let's populate the 1st 2 variables. So let str name equal input books. Please answer your main. Then let's I n t age equal and then we'll use the valve function input box. By the way, empath and STL name 10%. How old are you? And then we closed both the input books from the vowel functions. So now we calculate in flatter age. So let's say in AIDS multiplied by no 0.8, and we display our message. So really, you don't look a day over I m percent flat age, So let's save and test. So who's running our macro. Let's say Barack Obama, By the way, Barack Obama, How old are you? 52. Really? You don't look a day over 42. So 42 is probably a bit too precise. It would probably be nicer if we could round the age to the nearest five. So 42 were drowned down to 40. 43 would round up to 45 so forth. Although we working with an integer we can still use the round function to do this. So what we do is we put the round function around the calculation that we've got so far. Then we divide by the number that we want to Round two, which is five. And then we multiply the whole thing by that same number high by five. And as if by magic, we end up with the original number rounded to the nearest five. So let's try it again. Onda 56. So that then rounds 2 45 And again, let's save our changes. The format function could be used to create a formatted string version of a numeric value. When asking the user to enter a numeric value, the valve function can be used to remove any trailing textual characters. The round function, as well as round in floating point numbers, could be used around to the nearest multiple of any whole number. 21. 3.5: Date Variables: in this video tutorial, we look at how VB a allows you to work with date variables. We'll also have a quick look at working with Boolean variables. So here we are once again the Section three model, and what we'll do in this tutorial is to create a macro which calculates the difference between the current date and a target date, as we did with the number variables. We'll also look at how you conform at dates to display exactly as you want them to, and we'll also be used in a 1,000,000,000 variable to keep track of whether or not our countdown has reached a critical stage. So 0305 k and we'll call it a date countdown. Let's just switch across to procedure view So we'll dem de a t target as dates and also str Target has string. This will store the formatted version of our date. We won't bother creating a variable for the current date itself because the function now, three letters, um does the job, so we don't really need a variable for that, but we will create one for the formatted current date, said S. T ah today as strange then we'll create an inter variable to store the number of days remaining. And finally our 1,000,000,000 bln critical ask trillion. We've looked at how you use literal strings and you enclose them in quotation marks. The equivalent for dates is the half symbol, so it's going to be let the 80 target equal and then the assemble and the date goes in in US format. So let's say the date is gonna be the first of April 2014. Well, them in the UK I would still put four stroke, one stroke Teoh one full. Now let's get a formatted version of this. So let str targets equal and this time we can use the function, format, date time and this is a string function. So the expression it's looking for is gonna be de a T targets and then the format is a VB constant. So let's go with the long date and then we go straight on to do the same for today's date. So str today equals format state time now is the function that we use and again is gonna be baby Long date. Now we want to calculate the difference between the two dates. So let's I n t diss on the function that actually does this calculation. His date def. First of all, you specify the interval as a strength. So we're interested in the days and then you put the two days so date one will be now and date too. DHC targets based on i n t d. If we can now calculate our 1,000,000,000 so let be a land critical equal. And then we put a test into this less than or equal to and let's say, 14 so that logical test will return either true or false. So now we have everything we need. We can compose a message. So let's say a mystery box and we'll start with the target date. So carnet dates ampersand str target the formatted version and then we'll put a carriage return which we can do by using the constant BBC are then ampersand on will then split the line and then just having a little. Then we got today's date ampersand str today undersigned BBC are can percents placed under school Then we won't days remaining ampersand. Certainly these we are to discern space underscore and finally, let's say status critical Empress owned Bln. Critical. That's a message. Let's not save out changes and test a macro. So we got the first of April is the target. Today is the 19th of March. 13 days remain in. Hence our Boolean value returns. True as well as using format date time, you can also use the format function that we saw in the previous video. This actually gives you more flexibility. So, for example, if we wanted to display the day of the week instead of full that day time, we could just use format and then here, instead of using the VB constant, we can now use this strength to get the day of the week. For example, we could use three or four days, so three ds will give you the abbreviated name of the day. Four days will give you the full name of the day. Then let's say we want a single D for the actual number, so no leading zero will be inserted for the first of April. Then we want April to be the full month. So that's four EMS and then we want to fold four digit year. So let's just copy that and do the same here, so we'll make it format instead of format, date time and just paste over our full meant. Let's compare the results. The first of April is going to be a Tuesday. Today's the Wednesday on again, Let's save our changes. Literal dates are normally enclosed in half symbols. In USA format, the day to day function is used to calculate the difference between two dates. In a given interval of time, the format date time function could be used to apply a preset format to a given date. The format function could be used to generate a custom formatted date. 22. 3.6: Constants: in this video, we'll look at the use of constant in B. B. A. We've seen how you can use variables to store values, which potentially contain several times during the execution of your macron. However, you very often need to keep track of values, which will always be the same as your code executes. These are the kinds of values which is storing Constance rather than in variables. So once again we're working in the Section three model off the word VB a train in the template and we go to create a macro, which illustrates the use off Constance. And we're taking the example off a message that's displayed in the error handling sections overall. Macro, which gives the technical support email address that should be contacted when the user has problems. Since this email address is going to be used several times, it's useful to define it as a constant so that if it needs to be changed, we only have to make the trained ones that will update wherever it's been referred to within our code. So let's create our macron and let's simply call it using constants. Look, I'll just switch across now to procedure view to declare a constant used. The key word constant as opposed to didn't. And it's also useful to make it easy for constants to be distinguished from variables. One technique is to use upper case letters for the name of the constant. We can still use the prefix, so str support the veil because everything is up. A case we need to have. A separator between words and the permitted separator is the underscore character. We then specify the type, just as we do, or we declare a variable. But what's different is that because this is a constant whose value will not change, we now have to say what that value is. So let's say support at some company. Don't come another example of where you would use a constant might be if you had a rate such as V A T value added tax that we use in Europe, which occasionally a subject to change. So if you're going to refer to it several times during your code again, you define it once is a constant rather than hard code in the actual rate in your lines of code. So let's take bettors are second example, Combs s N dri. That rate a single equals Notley, too, and we might also want to store a formatted version of this, which would be a string STL. That rate has strength equals 20%. Then let's say we're in the middle of our air handling section and we want to use an MSG box that includes a reference to these constants. Let's just split the line, So ampersand str support, you know, And then we'll put a couple of blank lines in spending line one small then we might say in parentheses. Hourly rates. From now we could say hourly rates of £50. But what would be better would be to have another concert. So Const. Sng away, right? A single. It cools 50. We could can Katyn eight hour only rate subject to They see that after sound you Let's split the line again snz our rates on your the full stop. So let's save and test our code. So our common messages and the values held in our constants are inserted. Let's just go back to full nodule view for a moment and refer back to the macro that we did in the last video This variable de 80 Target is a prime candidate, for definition is a constant because it doesn't actually change. It's not designed to change throughout code execution. So here, rather than having dim, we should really have constant. And then let's change the capitalizations and match. And then, of course, we have to define it so we can move equals, followed by the date. And then let's just delink this. Another candidate would be here with hard coded that figure of 14. It would be far better to define a constant, let's say was called int critical date or int critical days and set back to 14 and use the constant in our code rather than the literal valued. So let's do that as well. Send in critical the able ass as Inter jure equals 14 and then we can use that name instead of 14. Let's just make sure that we haven't broken the code by doing this. Everything still seems fine, but we've added a bit of clarity to the code that we had. So let's just say about changes. Constant should be used to store values which required by your macro, but which will not changed your in code execution. It is also useful to define literal values as Constance rather than embedding the literal is in your code. 23. 3.7: Object Variables: in this video, we'll take a closer look at the use of variables that story in references to word objects. We'll be talking about using variables to store references to the document. Object, the range object, the table object and the row objects. However, as you'll see throughout the course, variables could be used to store references toe any word object. Let's scroll down to the bottom of the Section three module, and we'll we'll do in this tutorial is to create a macro that creates a new document, then creates a new table in that document and adds headings to the first row of the table. And let's just call it object variables. Well, switch across to procedure view, and we'll begin by declaring the variables. So it's logical to start from the top of the hierarchy. So the first object that we be creating is the container for everything else, Which is the document so dim D. O. C. New, since it's a new document that will be created as documents next, we'll drill down inside our new document and access the range object. The range object can refer to 100 pages to a paragraph to a word to any range you specify Inside a blank document that has just been created, the range object refers to the insertion point to the blinking cursor instead of them. Our energy, new as range inside the range, will place a table object. So did Tvl, new as table, and in order to put some text inside, it will refer to the first row of that table. So let's dim row headings as road. So those there are four objects and it's obviously logical that will start at the top of the hierarchy with the object that contains all the others. So we start with the document object to populate an object variable. We use the keyword set as opposed to let which is optionally used for data variables so set folk knew equal to, and we then put any statement which refers to a document. In this case, we want the statement that creates one. So it's documents your God, that gives us our new document. We now want to populate our range object to point to the contents of this new document so we can say set Florentine you equal to dock new don't range because the document is blank. Are Engy new equates to the current insertion points within the documents about that insertion point. I mean, I want to create a table. So now it's said TV l knew equal to on what we can say is doc new dot tables don't add. And then in brackets, we specify three parameters. First of all, the range within that document where was like the new table to appear? And of course, this is where we put in our in Genoud. Secondly, the number of Rosie like, let's say to and thirdly, the number of columns so that will create our table. Andi by default it won't have any borders, so the table itself won't be visible. Let's say we prefer to be visible. A nice, simple way of doing this is to say, tbl new got borders, enable equals true. And now let's add headings to the first row of the table. First of all, we'll set our variable row headings equal to tbl. New don't rose brackets one. We can now target the text in each of the cells in that robe object. So road head ins Nope. Cells one no range, no text vehicles on Let's say name and we can then copy this line. So just move the cursor to the left edge of the line. One click to select the line control, see for copy control the four times 23 4 and then we change these two to three and four. So we have name, let's say date, amount, details, and that's it. So obviously, this is a fairly small sample off working with these objects, but in a typical macro, you would be doing the more manipulation with each of these objects. So having them in a variable is a very efficient way off, being able to manipulate them simply by using the name for this variable. Let's just test that this works so well. Save and run. Let's check out new documents so everything seems fine. We've got our table with four Collins and two runs just one final point before we leave this topic. And that's the technique for removing objects from memory. It's obviously not very important in this specific example, because it's a brief, but if you want to do some memory management and just free up memory by reset in variables that you no longer will be using. Here's the syntax for doing that. So let's say we've now finished without table. We want to remove it for a memory we would use Set tbl view. He cools nothing. So there nothing Keyword is reserved for use with objects. Variables. Let's just running again and obviously we get the same result. But from memory management point of view, that variable is no longer taking up any memory. Let's just save, and we'll finish this section by looking at the use of the with and with structure. Those of you who have done some B B A will probably be asking yourselves, Why didn't we actually use with an end with in this context? And that's the topic of our next video. Using variables to store references to word objects helps to make your code faster and more efficient. When a signing objects to a variable, remember to use the keyword set to remove an object from memory, use the syntax set my object equal to nothing 24. 4.1: Sub-routines: Hello and welcome to Section four Word BB. A programming in this section will discuss ways of making your code more modular and efficient by separating different sections of code into different sub presages and functions. In this first radio, we'll revisit a technique that we discussed briefly in Section two, whereby you can have one main sub procedure that calls a Siris of subordinate subroutines. In this example, we want to record a macro, but to save messing up our template, we'll use a blank document. Although the steps that will be performing in the macro will be performed in this blank document, we still want our code toe end up in our template. So the first thing we'll do is to attach the template to this new document. We go to developer, document template and attach, and then we go across to our exercises folder, which is on my desktop. And here's our template. When we go across the visual basic you'll see here. I've still got my Section three module open. I'll just closed that down on going to Section four, which, of course, is completely blank, apart from our option, explicit just to remind you why that comes up. Tool's options require variable declaration because that switched on every new model will have option explicit inserted. Let me also remind you of a second way of a tat in a template to a document, especially if it's a blank document. We find the template, and we simply double quick it instead of the usual right click and open. So here we've ended up the document one. But because it's based on the template when we go into visual basic again, we have access to our word VB, a training template. Let's close that for the moment and let's go ahead and record a macro. So what we're going to do is to take the example of a document that we need to create, which tracks the creation of a series of reports. Each report, Let's say, has ah, an author who is responsible for its creation, and the document that were created will give details of each report. Who is its author? When is it due by and what percentage of the document is currently complete? So, of course, we begin by starting the macro recorder and let's use our usual name in convention. So we're in Section 04 This is video 01 Necro A and we'll call it Main. We don't want to save it in the normal template. We want to saving in our word VB a training template which we can access by choosing this option. And let's begin by put in the head in that we want a top of the document. Let's call it report creation summary. And then let's make that line ahead in one. Then let's insert a blank line or two and insert a table, so I'm going to have life Collins and two rows. Next, let's form at the table. And finally, let's add some headings. That's our macro completed. So we go across the developer. Stop recording. Now let's go into visual basic to see what we've ended up with. When used the recorder, it creates a module called Numac Rose, and obviously we specified that we wanted it to go into our template. And here's our new macro. So we'll begin by copy and all of this into our Section four model, and we can then lose Numac Rose. So what will now do is to divide this code into separate sections and Obviously, in reality, you would be working with longer sections of code, but the principle would be the same. So this section relates to the creation of the document itself. So let's cut this section and paste it into a separate subroutine because they're subroutine will not be a macro in its own right. We used the key word private So 0401 B and we'll call it create documents. Then we can paste in our code and let's just modify it slightly. So at the moment, all of this will happen in the active document. Let's say we want it to happen in a new document. So the start of this subroutine, we can just add in the statement that creates a new document documents Not bad. So obviously, when you create a new document, it becomes the active document, and all of this will then happen within that document. Now, one thing we're not going to do in this example is to work with variables will put variables on a back burner for a couple of videos. The reason is when you're working with multiple modules, you have to address a separate issue, which is the question of variable scope in other words, the places from within each module from which each variable can actually be accessed. So we weren't work with variables for a couple of videos, and then we'll will address the topic later. Okay, let's go back to our code and look at the next section that we want to create. So here the table is being created on here. It's being formatted. So let's put both of those into one subroutine. Several cut all of these lines and create a second private son. So this is create table and paste. And then finally, we have this section where we actually enter the headings. Let's call that create headings and then we just need to cool. Each of these sub presages. You can get rid of all these comments. So here we just need to use the optional keyword call, and it's always useful to use it so it stands out, so you can immediately see where each subroutine or function is being called. Control the control, say, of course, And there we have it. So this is a classic set up whereby you have a main subroutine, and it then calls several subordinate some procedures. So the macro is the one whose name is simply preceded by sub, that everything that's preceded by private sub is only available within this model. Two other sub procedures in this case to the main. Some procedure you can, of course, test each of these independently. Whether or not it works will depend on what that particular section of code does. For example, one private sub may require certain things to be in place before that code actually makes any sense. So normally, it's only the the main. Some procedure in this set up that you actually test and run. So let's go ahead and run. And let's look at the document that's been created for us. So there we have documents to now we've got ahead in one, and we have our table so that since we work in time, we can say, Don't save on this occasion, we've lived dangerously. In other words, we haven't been saving our code. Um, on the normal circumstances, I would always advice, of course that you save everything you do. But on this occasion that's abandoned Document one. So, do you want to save your changes to document one. No, we don't. But then it's gonna ask us if you want to save our changes to the word VB, a training template, and this time we click on Save a Simple Way of Model a rise in your code, Mr Creator Macro, which then calls a Siris of subordinate subroutines when defining a subroutine designed to be called by other subroutines, precede its name with the keywords private sub to call. The subroutine used the optional keyword call, followed by the name of the Subroutine. 25. 4.2: Functions: in this video will discuss functions and look at how they differ from subroutines. As you'll see, the main difference is it functions can return a value that this could be very useful. We've already seen how you can use the built in functions within the V A, such as input box and how they can be used to populate your variables. But in this video will move on to look at how you can define your own functions. In the last video, we looked at how you could model. Arise your code by having separate subroutines, which you then call in addition to this model organization. If you use functions rather than subroutines, you have the advantage that each function can return a value. Let's look at an illustration of way that's so useful. Let's create a similar structure to the one that you used in the last video. So his own macron and we'll call it main ones more. And let's say that inside this we're going to call the function, and again it's going to create a document. Now let's define the function again. We use the keyword private, but instead of sub you say function, then let's paste in the name presente. However, now there's one additional thing that we could do. We can define the type of data which are function will return. So in this case, let's say that we want the function to return the document, which it actually creates to specify this data type after the parentheses, which follows the name of the function you put the ass keyword in exactly the same way is when you're declaring a variable followed by the data type. So for creating the document, let's use the code that we recorded before. So I'm gonna copy all of this and after our function has created a document, we now want to return a reference to that document. And the way that this is done in Baby A is very similar to the way in which you assign value to a variable. So because we've defined a function which returns at an object, a document object, we need to use the keyword set. We then use the name of the function, so I'll just copy that and faced it in equal to. And then we put a reference to the document because we've just created it. It's safe to say active document. So now we have a function that returns the value, which is the document that it's just created. We now have the option in the main module in the main subroutine, a macro of actually using that value that document. So here, instead of just a function call, we could also populate a variable. So let's define a variable. And then here we can actually populate that variable because it's an object, will you set equals and then we've got our function cool. However, when using this syntax, we could no longer use the keyword call. So we'll just remove that and then to test that this is working. Let's just use message box the documents contains. And then I say, Doc, you dot characters locked count ampersand characters. So now we can say about changes, and then let's test it. So here's our new documents, and it contains 24 characters. So, as you can see, the ability to return a value adds another that I mentioned to functions which subroutines don't have in the next video. We'll move on to look at how you can use parameters in subroutine and function calls on This adds even more flexibility and more power to the process of model organization. In addition to model, a rise in your code functions a capable of returning information into a variable used in the function. Call when defining the function. Precede its name with the keywords private function. To specify the data type returned by a function used the syntax as data type after the closing parentheses, which follow the function name. To specify the value returned by a function. Use this Intacs. Let my function equal data value The functions which return data values set my function equal to object name for functions which return objects. 26. 4.3: Using Arguments: In this video, we look at how you can make your subroutines and functions more flexible by defining arguments or parameters. Arguments enable you to have the same function, perform slightly differently based on the values which are passed during the function, call or subroutine goal. If we look at the subroutine calls that we've done so far, for example, this one which creates a document. However many times we call that subroutine, it's always going to do the same thing. It was always insert a title with that specific piece of text simply by defining an argument for this subroutine. We can have the same subroutine create a new document but place a different title inside it each time. In a similar way, this subroutine, when called, will always create a table, which has exactly two rows and five columns. If we define two parameters to control the number of rows and the number of columns, we can call the same sub routine but end up with a different table each time. Let's look at how this works. To save some time with copy this subroutine on dysfunction, just paste it below the originals and then rename. So this will be three, a three Day and three Bay and the element which we want to replace with an argument, is this title to define an argument and arguments are also known as parameters. Inside the parentheses which followed the name of the function or subroutine. You specify the name of your parameter all argument, and we can use the same name in conventions that we've been using for variables, which is essentially what arguments are. So this is obviously text str title as string, so it's exactly the same as declaring a variable with the exception that we don't use. The keyword did. And of course, this text is then replaced by str title. Now, when we call the function, we can specify. In fact, we must specify a string value which will end up inside this parameter or variable. So in parentheses we just paste in the title that we had before so you can see how this process works. If we needed to create several new documents, we could use this syntax repeatedly but enter a different title each time within the parentheses. Let's do the same for the creation of the table, so that's copy the subroutine that creates the table, which is here. I just rename that three. So the premises we want, ah, to replace that's fixed into a flat fixed in Georgia. So we'll call them in rows presented a and coals as Introgen. And then, of course, here we is intros on Dhere offend coals. So I'll just copy the name of the function, or rather, or rather, subroutine. And then here we can place came and paste, then in parentheses. After the name of the subroutine, you'll see that the program is actually prompting us to enter in Crosas, Inter Gia and in coals as indigenous. So we have to comma and four. And let's just test that we get the same results as before. So let's save our changes and then run out macro. So here we can see one of the dangers of using copy and paste. You always have to remember to make the necessary changes to what you paste. And, of course, what we've done here is we've left the old name so on returning a value, we've used the wrong name, so we just need Teoh reset and modify the name accordingly. That's run again. We can see that we get our new documents with the title on the table. So with a small amount of code that we have so far, we're not saving a huge amount of time. But this structure is very powerful because it means you can reuse the functions and the subroutines that your macro needs and get slightly different results by creating parameters to have the subroutine or function behave slightly differently, performed slightly different actions, so it makes your code even more reusable, even more flexible. Even more modular arguments, also known as parameters variables used to make subroutine calls more flexible to define arguments used this in tax argument. Name one is data type one argument named to his data Type two etcetera in parentheses after the name of the sub procedure. When calling a subroutine or function with defined arguments, a value of the appropriate data type must be supplied to reach arguments using this Intacs cool procedure name argument value. One comma argument valued two comma, etcetera 27. 4.4: Optional Arguments: In the last video, I mentioned that if you call a function or subroutine, which has arguments defined, you need to supply a value for each of those arguments. As is often the case in program in, this is only partially true. In fact, you can make arguments optional, which means that when you call the functional subroutine, you can either supply a value. Or you can simply admit it in this video. We'll look at how this process works. We'll stay with the same example of creating a document inserting a table and put in headings in the first row. But let's say we want to make the insertion of headings more flexible. We want to cater for a maximum of six headings, but we want the flexibility of being able to enter fewer than six headings. Optional arguments provides a mechanism for making this happen again. We'll call this main, and we can then call the function and subroutine that were used in the last video. And let's say this time we only want to end three columns of information. Let's now refer back to the macro that we wrote in the first video. So here we had this third subroutine called create headings. So let's now copy. This is so this will be 0404 b. And instead of having these fixed headings, we now want the flexibility. First of all, they're being able to supply our strings as parameter values and secondly, off being able to vary the number of parameters that are actually in use. So to do this, we define our arguments or parameters. But with each one, we specify that it's optional. There are three things to remember when creating optional arguments. The first is that optional arguments always follow obligatory arguments. So if your subroutine or function requires both, you put the regular arguments first and then you put any optional arguments. The second thing is that you use the keyword optional before the name of the parameter, and the third thing to remember is that you need to supply a default value for the optional argument, which will be used if no value is supplied when the function or subroutine is called. So in this example, we don't have any obligatory parameters. We want to create simply a Siris of optional parameters said. We used the key word optional and these air strength. So let's call the str h one etcetera as strange and then we supply a default value. In this case, a good choice will be the blank or empty string. That way, when we moved to the cell, an end to the text will be entering nothing. So we won't be modifying the table. So that's our first parameter. Let's just go onto another line and then we content across and just copy what we had before . So we want to enter a maximum of six of these so we can just pace you turn. I meant just modifying the names. And here, obviously this is the last one. So no comma and no under school. The next thing we need to change is that these values will be used in the function. Cool. And these parameter names will be actually used within the subroutine. So we're gonna have just three. Let's say we want the report name, the date do and the percent complete. So let's just move these into the function. Cool. So we want to call this and then we want to supply as parameters report name. So cut this. Just paste it in Let's use the same style by split in each value onto a separate line. Then we want due date. So this time let's drag and I just tapped out across split the line and finally we wanted percent complete on clothes. So these will be the only three arguments that we supply and obviously they will go into str h one, h two and H three respectively. The last three arguments will simply stay as their default value. So the final thing we have to do is to use str h one as the text of this one on eight to and so forth. So we just need one more. These two lines copy paste, paste and change that to six. So after we've created a table, we end up in the first cell. We ends of the first head in move down one end to the second and so forth. So there is a fair bit of redundancy in this process. And those of you who have programming experience will be thinking of condition, ALS and loops to make the code a bit more efficient. But with what we've covered, it still works. It's a bit long winded but basically these last three entries are blank strings, so they don't have any effect on the content of our table. So let's distract that our code works so we'll save and run. Now let's look at the table that we end up with. So we've got ourselves a three column table on the headings have gone in and the last three parameters, which we didn't supply value for. Don't do any harm. It'll when defining a functional subroutine. Obligatory arguments must always proceed. Any optional arguments to define an optional argument used the key word optional and supply a default value. Optional argument is data type equals default value. If no value is supplied for an optional argument, the default value will be used instead. 28. 4.5: Variable Scope: when you divide your code into separate sub procedure is the question of variable scope becomes important. Some of the variables you declare will only be pertinent to one particular sub procedure. Others you'll want to access in several different sub procedures within model. On a larger project. You may divide your code into separate modules, and there may be variable, which you need to access from sub procedures in different modules. Since the code that we've been using in this section was generated by the macro recorder, it obviously doesn't news variables. But as we've seen as your macro is growing complexity, the use of variables becomes more and more essential. So let's look at modifying our code so that it actually uses variables, the basic code that we have created a new document and places a table inside it. The three main objects involved therefore going to be the document second lead the range, which refers to positions within that document and thirdly, the table that we create. And in the first row of which we insert headings. If we examine the code generated by the macro recorder, you'll see it makes continuous use of the selection object and the problem with this is it's ambiguity. At one point in the code, it's gonna be referring to a range. At another point, it would refer to paragraph another point. It refers to a table, the table cell and social. By using variables, we can remove this ambiguity and make our code much clearer. So let's begin by copying the 1st 3 sub procedures that we wrote there will just modify the names on. We'll look at making them a bit more efficient by using variables instead of relying on the selection object, as the macro recorder always does. So that paste that in and let's modify the three names so that becomes a five. And to this illustration, we will really need create headed. So let's just delete that line on the first variable. That it's logical to create is the document variable. So him Doc knew as document, and then here, where we create the new document, replace a reference to it inside DOT knew so that sets talk new equal to, and then we can do the same thing to the table. So we created table variable. And then when we create the table, he plays a reference to it. You know, variable that. Having modified that simple statement by placing another statement in front of it, we now need to make sure that these parameters are enclosed in parentheses. When we delve deeper into VB, a syntax will discuss when you do and don't need to place parentheses after the names of functions and methods. Now we leave things as they stand. Both of these variables will have a scope, which is local to this sub procedure in which they're declared. So this means that when I create my new table in order to get it inside doc New, which is where it belongs, I'm having to use active document, which means that at that point in time dot knew one I've just created has to be the active document. If I change the scope of this declaration, however, so that it's module level instead of procedure level, I will be able to refer to dot knew in any some procedure within this module. So let's do that. It's very simple to do. We simply need to declare the variable at the top of the model above all the subjects ages so we can simply cut it from this position. Let's lose the blank line and then we just paste it after the option explicit above all the sudden procedures, the simple fact of declaring it in this position makes it module level, and we can use the keyword dim. However, it's much better practice to use the keyword private, because this explicitly specifies that this is a private module, which is the key word that specifies model level scope. Another useful practice is to have a name in convention, which makes it easy to distinguish between model level and procedure level variables. One common convention is simply to place the letter M at the start of the name, so the M specifies the scope has been model level, and in this case, the D. O. C specifies that it's a document variable, and the convention is where you have a local variable. You simply don't use any prefix. You typically have a prefix to specify the data type, but you don't then put a sort of P to represent procedure. You simply haven't prefix to specify the data time, so at the bottom here will now need to update our name so that they'll be comes in em. And in this position we can now explicitly state that we want our table to go inside. Don't new and don't continued rather than relying on the fact that it's probably going to be the active document. I've just noticed, by the way, that there are three lines in here which completely unnecessary. This is one of the things about using the macro recorder, which is how we generated this code. It records every single thing. You, too, so I must have timeto Why, after type in summary, an extra Y and then deleted it. And the macro recorder said, Ah, you want an extra way and then you want to delete it, so I'll put the code in for you so you can just delete those. You almost certainly haven't gotten these three lines in your coat. I hope their presence hasn't confused you, so we can now test our code. Let's just save, and it's the main back row here that we need to run. Let's look at the document that's been created, and there it is. So that's the difference between a procedure level Onda module level variable. There's 1/3 scope that you can give to a variable, which is to make it global. So let's just have a quick example of doing one of those. You declare global variables in the same position as module level variables, but instead of using the keyword private you use public, so let's just do a quick test and the convention is we use G as the first letter of the name. So let's call it G str test straight And in fact, what we can do to save time is we can make it a constant. So if we put Const at this point, we can land. Just populate it in one line so we could go into any of these modules. Andi, refer to this variable and it's going Teoh, actually give us this value of test that we've assigned to it. So I'm just gonna go into conditional is loops in the rays, which is what we're coming onto next. At this point inside, it's blank. And let's just put a test macron in there and use MSG Box on paste. Just copied the name and run and up comes out test. We probably won't be encounter in this very often if it all in this course, But as you start right in larger projects, you will find that you need to refer to variables and other modules on the way to do that is to give them a scope, which is public. So for the moment, let's just lose our public constant and save our changes. Variables declared within a functional subroutine have a local scope and can only be accessed from within the procedure in which they declared variables declared at the top of a module a bubble. Some procedures using the keyword private have a model level scope. Variables declared at the top of a module using the keyword public have a global or project level scope. 29. 5.1: If Statements: in this section, we look at the VB a statements which are used to control the flow of execution within your Mac rose. Their two main types of statement. Conditional statements allow you to control with Siris of steps have performed depending on the result of conditional tests. The second type of statement is a loop which allows you to efficiently carry out a series of steps several times, and we'll begin by looking at if statements. So let's go into the Section five model and let's create our first macro. I will call it simply if statement let's begin by looking at the structure of an if statement. The basic structure is as follows. If we used the key word, it's followed by a logical test in other words, a test which can only result in either true or false, followed by the keyword Then. Then we put the statements which will only execute. If the test is true, then we have the option of put in the keyword else. Then we can put a series of statements which will only execute if the test is false and we then closed the of statement with the keywords and if Let's take a simple example. Let's say that we want to have an application which will only run if the user is used in Microsoft Word 2007 or later, the statement that allows you to test the version of world that's being used his application dot version. So if I go into the immediate window imply question mark application version because I'm using word 2013. When I press enter, I get 15 20 warts. Now this result looks like the single day to try it, doesn't it? Because it's gonna decimal, so it can't be an integer or along. However. In fact, it's a strength. So if I look in the object browser moved down to application, don't version. You'll see it's a property, which returns a string. So when using this statement, if I want to do a comparison such as Greater Band, I first need to read this value into a numeric variable. Let's look at how that would work. So let's dim introversion as in Georgia and weaken venues the valve function to generate a numeric value based on the string that we've just seen. So this is a data variable, so it uses the keyword Let which is we knows option also be admitted. Introversion equals application version, and we then just wrapped the valve function around that. So I've been converted that into an insurgent. We can then test whether it's greater than or equal to 12 because the number 12 denotes were 2007. 14 denotes word 2010. So we simply say, If introversion greater than or equal to 12 then so that's our test, and we'll just put one statement. If truth MST box, Welcome to the application else. A mystery box. This application is designed word 2007 or later, so we'll see if we running on this machine. We should get this message. This version of in if statement caters for only two possibilities. If the test is true, we carry out one set of statements. If the testes false, we carry out another. However, there's a second variation, which allows you to cater for as many eventualities as you want to do this. You use the keyword else. If, as an example, let's create a macro which will calculate a grade based on a school entered by the user. Let's just switch across to procedure view and let's dim in school, as in Geagea and SD ah grade. As strength, we can then populate in school by the user using an input box and again we can use vow. So now we can use an if statement to evaluate in school and to populate. Str grade accordingly, so we'll start with the highest values. If in school is greater than or equal to 80 then str grade equals a next we want to test for Be on. Let's say that B is between 70 and 80. To do the second test. We use Elsie so it's greater than or equal to 70 and and we can't simply say and less than 80. We have to repeat in school less than 80 and we also have to repeat the keyword then and we can then just copy this line B A c okay, and a So you see, we would be looking at greater than or equal to 60 Lesson seven c and then that day created honoree was 50 less than 60 and the final test will be full e, which would be greater than or equal to 40 and less than 50 and anything less than 40 will then be in F So we don't need a test for this. It becomes the else and we end the statement with and it having populated str grade we can now output it. So let's simply say message box a school Volver I understand in school Abbas ends gives us a grade happy sand str grade and the sand full stop and that's tested. It's a little saved and run Man Center score of 75 Store 75 gives us a great be when using an if statement to handle two possible outcomes, use this Intacs if test then statements. If test is true, Pells statements it testes false. And if when using an if statement a handle more than two possible outcomes. Use this in tax if test one then statements if test one is true else. If tests to then statements if test to its true etcetera else statements. If all above tests of false and this 30. 5.2: Select Case Statements: in this video will look at the Select case statement, which provides an alternative to use in if and else it when testing for several different possibilities. In this example, let's say that we want to create a new document, insert a title and then below the title inside the creation date. However, after the day components off the date, we'd like to insert the correct ward inal such as S, T, N de R D or TH will create a function which will calculate the orginal based on the current date. So that's first created, Macron said. The macro will create a document, so let's just switch across to procedure view and then here we could dim stock you as documents. I'm also create str orginal as string. So first we create a new documents and then we want to calculate the orginal so we'll create a separate function which does this so it's gonna be str orginal equals and then we do a function called So a function is gonna be s 050 to be on. Let's say it's called calculates. Orginal will need to pass the function an argument which could be today's date which is generated by using the function now. Once we calculated the orginal, we can then write our head in and today's date in the new document. So that will be broken. You not content. Since it's implying document equals on our headed. Let's just call it status report, and we'll follow that with a return. Three b. C. R now has to split the line, and now we want the formatted date, including the orginal. So we'll put the day component first. And for that we can use thief format function that we saw before. So we four met in today states and the day component. Let's say we want four days, which gives us the name of the day and then a number with no lead in zero representing the day of the week. So that's the day component. Close that then we insert str orginal and then we finish off with the month and year again . We format in today's state. We put a space, let's say four m. So we get the full name of the month on a space followed by a four digit year. So now all we need to do is to write this function. Let's just go back to full model view so we can ensure that our function goes in right place. So it's private function and paste controlled me. So this now becomes the name of the parameter that we want to use. Which, of course, will be a date parameter. So d 80 inputs as date. Then after the closing parenthesis, we specify the type of data that are function returns, and this will return a string such as nd or SD, representing the orginal. Now let's return to procedure view. So now comes our select case statement. And let's just look at the overall syntax four selected case. So it's select case. Then you have the test expression to be evaluated. So we have case test expression and then, for each possible value, we use case and we can either put a single value for a list of values. So let's say value list one that we put the statements if valueless one contains the match , and we can then repeat that as many times as necessary. In our case, there are four orginal so we've got S t that we have nd rd and th in fact, having tested for S, T, N, d and R D. If it's none of those three, it must be th So Instead of having another case test, we can use the statement case else. So if none of the above the truth, then these statements will execute. And to close a select case, you simply use and select. So that's the skeleton of a select case. Let's now flesh it out. So our test expression is gonna be the day component of D 80 input. And to extract this, we can use the day function, which is simply day and then in parentheses. We put in this case the variable, which is D 80 input. So that's what's being evaluated in the select case. The first list of values were interested in considering will be one 21 and 31 if the day of the month is any of those three. We want our function to return S t. To specify the value returned by the function, we use the name of the function, so I'll just copy it. Since it's a bit low. Then we simply say equal and the value in question because we've specified that the function returns a string We have to put the value in quotation marks. So s to how we can then just copy this and paste it in. So here will have nd Yeah, I'll be yeah, th on the days of the month when we require n d will be too and 22 and will require RD in the day of the month is three or 23. It's none of these values in our case else will kick in and our function will return th So let's return to full model of you and we could now test down macro that save and let's run Let's see the document that's been generated So we've got a title status report and we've got our orginal inserted S t because it's the 21st old Bartsch. So that's a quick rundown of how select cases used. As you can see, there is a certain amount of crossover between if and else if and select case. But the key difference is you select case where you're evaluated just a single expression, whereas with the if and else if structure, you can have a completely different test with each else. If that you actually use the Select case statement is used in preference to if else, if when testing an expression all variable which may contain one of several possibilities. The selected case statement uses the following syntax Select case test expression, usually a variable case value list. One statements, if any, of valueless one matches Test expression case Valueless to statement if any of valueless two matches test expression, etcetera. Case else statements if none of the above matches test expression and select. 31. 5.3: For ... Next Loops Part 1: will now move on to look a loop in statements. The statements enable you to execute a number of wines repeatedly either a given number of times or while a condition is truth in this video, we'll look at one of the most frequently used of these Lupin statements before next loop. Let's begin by looking at the basic syntax. Let's call it for next basics. To control the for next loop, you need a counter, and there's a tradition in most program in languages where you use the throwaway name I as the name of the counter. Then the basic syntax is for counter equals start value to end value. Then you have a series of statements, and you close the structure with next and you repeat the name of the counter. The benefit of doing this is that if you have 14 next statement within another, repeating the name of the counter in the closing statement allows you to see where each of your loops ends. So for this first basic example, let's say that we want to loop through the quarters in a year, so we know that there are 4/4 the first of which is one and the last of which is full. So that takes care about start and end in value. Then our counter is called. I so replace that with I andro statements. Let's simply have one MSG box. We'll have the word quarter full it by I. So when we run when Lupin in this way, it's sometimes the case that you don't want to lose sequentially. You want to jump in certain steps. For example, we had an application that was working with the months of a year, but we currently wanted to process only the last month in each quarter. We need to start on Month three and then jump in steps of three. Let's look at how that's done. I'll just switch back to full model of you. Let's just call P this, uh, procedure and just paste it out to the original and rename it so it's macro be and let's call it four next steps. So we're going from three to 12. But the main thing is, we used the key word step, and then it's going to be, in this case, three. So here will have a month, and then it's run, so we go from 3 to 6 to nine on. Then we stopped. When we get to 12 you can also use the step parameter to loop in reverse. This is often required where your delete in elements and we'll see examples of this throughout the course. So I'll copy this one again on from macro, see for next reverse. So it's simply reversed these four 21 Now, if we don't put this step and we try to run it, absolutely nothing will happen because to get from the start value to the end value where step isn't specified, V B A will always add one by default. So if you had one to the start value of four, you get five. You're going in the wrong direction. So the end value has already been surpassed and therefore nothing inside the loop will be executed. So we simply have to put step minus one to tell BB A that we actually want to add minus one to get from start value to end value. One very common use of for next loops is to look through all the items within a collection of word objects, such as all the documents that are currently open. All the paragraphs in a document, all the tables in a document and so forth. V b a also contains a variation on the for next loop specifically for that purpose. Let's have a look at how this works. Let's say we want to look through all the documents which are currently open and do something with them in our case will simply display their names. So the syntax here is for each next to switch across the procedure. Give for a moment and the key difference between the four. Each structure on before next that we've already seen is that you don't use an integer counter. You simply use an object variable to loop through the object collection. So because we're working with documents, we'll need a document variable. Let's just call it Doc. You're now the same taxes generically for each object variable in object collection. Then we have our statements and we close it with next. And then we repeat the name of the object variable. So in this case, the object variable we've defined, it's called Doc. The collection that we're looping through is the documents collection, you know, so here will need to repeat the name of the object Variable Doc and for our statements will simply say MSG box, followed by the name of the documents. So we can say doc dot name. At the moment, we only have one document open the template that we put in the macron. So when you run, we simply get the name of that one document. And of course, if I go back into word and create a new document, then run the macro one small. My now get document one and the original document that I had opened. Let's just close that unwanted document. So most of the time where you want to loop through an object selection, this is the best way to do it. However, you can still use the for next loop on. One of the benefits of doing that is that you have the counter at your disposal, which you can use to fine tune how the Lupin is actually executed 32. 5.3: For ... Next Loops Part 2: so most of the time where you want to loop through an object collection, this is the best way to do it. However, you can still use the for next loop on. One of the benefits of doing that is that you have the counter at your disposal, which you can use to find Tune how the Lupin is actually executed. Let's go back to full module view and let's call this final macro for next objects. So let's take an example of looping through the items within an object collection. The collection in question. I'll just quickly show you in the word interface. It's file info and on the right here we have properties and advanced properties, and these are the properties that will be looping through. So they're divided here into different categories and in V B A. Been owners built in document properties and they are in turn a property off the document object. So let's declare some variables, starting with I as Integra, which will be on counter. Then we'll need to know how many items there are in the collection, so we will cool that so we'll call that one and BDP as in built in document properties on this will be Integra. Then let's create a new document so we'll have a You will have a document variable. Then we'll read the name of each property into a string variable. So str name have string and the value associated with that property also into a string variable. So these are our variables, So the first thing we'll do is to create a new document. Them will add a table to it. Then we'll enter some headings and then we'll loop through be built end document properties collection. Then inside, the Luke will capture the name of each property, capture the value of each property, and then we'll writes the name inside the table in column one, and then we'll write the value in column two. Okay, so to create the new document, as we've seen a few times, it sets both new equal to documents not add on to create. The table will simply go back to our previous section and let's just grab the code that we recorded using the macro recorder, and we can then use Doc New instead of simply active document on this occasion will need instead of two. We'll need to use in to be deep pain. So this is simply the number of items within the collection that we live in through. And since we also want tohave headings will need to put plus one here then for the number of columns will need to now, of course, we haven't yet calculated what this is. So that's something I neglected to do. Let's just put that in. Find number off items been collection? No. And to do that, it's going to be let rather than set. And as we know, we're mitt let into bdp equals and in vb A When you want to find out how many items there are in a collection, you normally use the count property. So we start with dot knew got built in document properties. Got count. Okay, so now we can go into end to the headings and again if we go back to the previous sections , Montiel and look at our create headings we did We basically need thes three lines or a variation of these three lines. So we write the first head in, we move to the next cell and rewrite the second heading. So this is property name and then here property values. So let's just say what we've got so far and test it. So there's our two column table and it's got the number of rows and hopefully that number corresponds to the number of items within the collection that we live in through. So now we can live through the collection. So this is our four next loop. So it's full counter equals start value to end value Step X and then we'll close it. Youth in next counter. So the counter is I that both of these become I. The start value is one either first item in the collection on the end value is our variable , which we called into DDP. Our step value is one, and since that's the default weaken, simply delete the step parameter. So now we want to capture the name into our variable cold STL name. So it's let which we admit STL name equal, and we then need this syntax which refers to the entire collection. And we specify which item we want to process by place in an index in parentheses. After that statement and the index that you very often used within a loop is the name of the counter. So because this value is constantly changing, this is equivalent to saying each because each time you go through the loop it will have a different value which will correspond to the next fighter inside the collection. Having identified the item, we then drill down and extract its name property, so the value capturing the value will follow the same pattern sdo valued. And then at the end, it's going to be don't value. Having captured those two elements, we now want to out put them to the table and we can simply use these two lines. So we moved to the next cell and then here we obviously want to write the name. So that's str name and we do the same here. And this time we want to write the value. So str value I'll just tabal of these lines in for make them a bit more readable. Now, when we test this code, we're gonna have a slight problem because the value of one of these properties can only be extracted if it's been set. And in the case of a brand new document, there are a number of these properties which haven't yet been set. So when you're trying to read the value, you end up with an era. So it's definitely a good idea to save before we test, which you should always do anyway. So we get an error message, and if we click on debug, this will tell us which line we've actually got to. So basically it's reading the value which hasn't been set, and that's what's generating the error. Now. We haven't discussed our handling is yet, but we have discussed, if statements. So I've made a note of which properties are actually problematic in that they haven't been set and they are properties number 10 12 25 26 27 28. So for the moment, what we'll do is to use an if statement to make sure that these values are red. They just skipped over, and perhaps we can enter a default value of not applicable or not available. Perhaps we can enter a default values instead of attempting to actually read the Value property. So let's just have a look at the document that's being generated. So it got this far with no problem, but then when it trying to read the next value. It failed. Several close about saving on this is the culprit. This is what's causing the problem. So we need to make sure that that is contained in an if statement, so it doesn't attempt to do that line unless the conditions are correct. So we basically need to say if I is not equal to that's the symbol that means not equal to 10 ums. I is not equal to 12 and well, we want to say eyes not equal to 25 26 27 for 28 and a quick way of doing that would be to say not and then in brackets I greater than or equal to 25 and I less than or equal to 28 and then close brackets, then we need the key word. Then we can tabal of their sent to make it more readable and then close that with and lif and it might be useful to put in else in there as well. So so str value equals and let's just say not available and again we can save and we contest. So by using the if statement, we've managed to avoid the problem parameters, and we're only attempting to read the value on those elements where the value is available before next. Lube uses a counter to control the repeated execution of a series of statements within the loop. The counter itself could be used to further refine code execution before each next loop. He's used to live through the items within a collection of word objects, such as documents. The for next loop uses the syntax for counter equals start value toe end value, Step X, where X is any integer statements. Next counter the four each. Next loop uses the syntax for each object variable in object collection statements. Next object variable. 33. 5.4: Do Loops: as well as using a counter to control Lupin. It's also possible to use a condition in V B. A. There are two structures which allow this conditional looping. In this video. We'll look at the first of these doom loop structure. The Do Loop statement has a couple of different formats, and this means that it's quite a flexible statement. It could be used in place of the four Next Leap, which we saw in the last video, and also the Wild Wend Loop, which will see in the next video. However, there is one trick that it has, which the other forms of Luke don't. And that is it has the ability to ensure that the statements inside the loop will be executed at least once. And it's this unique feature which the other forms of Luke and can't do that will focus on in this video. So let's write a macro. What, and we'll just cool it do loop statement and then I'll start across the procedure of you. Now we've seen several times how you can display an input box and get the user to Anderson data, which you then process one of the things you could do with a loop is to repeatedly display and input box until the user either click the cancel button or enters an appropriate value . So, in this example, will say that we want the user to end a company email or click the cancel button. So let's declare of a string variable str email in which will capture the user's email address, has string and let's then define a constant indicated how we want the email address toe end so we'll have to call me str end as string and then we've got a set it because it's a constant. Let's say it has to end with at our company dot com. So that's first of all, cater for the cancel button. So here's the basic structure that we'll be looking at. So it's do then the statements that you want performed then Luke and you can either use while or until so you can say Luke until a conditional testes, true or loop, while a conditional test is true. So let's leave it as while it makes very little difference for our purposes. So the test that will need is that str email is not equal to null string, so we can either put two double quotation marks with nothing between them. Or we could use VB null string and then inside the Luke will populate our str email using input books. No. So let's test it. The scientists and nonsense click OK, you'll see. Back comes the input box and when I can So then this statement ceases to be true. Str email does equaled a null string. Hence the loop will end. So now we need to change our test into a compound test because we want to test for two things. We want to test that str email does not equal the null string and also that str email There's no end with this strength. A simple way of testing that one string ends with another stream is to use the right function. If you've used Microsoft Excel, you may well have come across the functions left and right, so they will read a number of characters starting from the left or starting from the right . The number of characters to be read is obviously the number of characters in this string. We could heart code it. There are 15 characters in the string, so we could just put 15. But it would be better to use a function to do this so that if this string ever gets changed, the function will still extract the correct length. So we'll need and here because we want both conditions to be true and then it's right. The string that we're looking inside is str email length The number of characters that you want starting from the right instead of 15. We use the land function and again you may remember this from using excel functions and the thing we want. The length off is str and this statement will return the last 15 characters of the email that they've entered. We now want to compare those characters with str end so we put not equal to str end. So in other words, as soon as the last 15 characters of str email are equal to str and then will end, I'll do so. The user now has two ways of ending the loop. They can either click the cancel button and generate the null string, or they can enter a string which ends with at our company. Don't come on either of those two locals, the loot end. So let's test it so again, I'll just put some nonsense and it's no happy. We know that the cancel button works. So if I just put grants at our company, don't come then it's happy. So having received that email from them, let's just do something with it So we can test whether they actually entered an email or whether they simply click on cancel. So here we can say, if just paste that in. So now that's going to deat equal to rather than not equal to So in other words, Now we want to say If str email does end with str End Ben and history books unless this that's gonna be the report will be he mailed to the address ampersand str even and if that's just company that again and then tested. So we saved grams at our company. Don't come. Report will be emailed to the address Grant and our company. You don't calm. So just to point out then the thing that makes this structure unique is the fact that whatever happens, they will see the input box at least once. It's also illegal to put the while at the top so we could say do while And of course, if we do, absolutely nothing happens because the test proves to be false from the start, so this line doesn't actually get executed. So this is the style of Do Loop, which is unique among the three different Vehbi, eh? Lupin formats. The fact that if you put the test with the loop statements rather than the do statement, whatever statements are inside, the loop will always be guaranteed to execute at least once. Do Loop statement has a variety of formats. However, the structure that makes it unique is that it can ensure that the actions inside the loop have performed at least once to obtain this functionality with the do loop, use the syntax do statements while test condition oh do statements until test condition. 34. 5.5: While ... Wend Loops: in the last two videos. We've looked at four next and do Nuk statements in this video. Well, look at the third type of Lupin structure offered in V B A. While Wen statements, Let's create a new macro unless simply cool it while when. What does Mitt across the procedure view? And let's look at the basic structure of a while. Wend loop. We have the key word while and we have a conditional test. As long as they're test proves to be true. We carry out a series of statements, and the structure closes with wend a made up word which obviously is a contraction of while and end. You use this structure basically where you don't have a numerical component to control the looping. For example, you don't know how many items you want to process. You just want to keep processing them until you run out of them. In this example, what will do is to create a table in a new document and complete one column by making an entry, moving down the line and then checking to see whether we're still in the table. As soon as we no longer in the table, we stopped making entries when we stopped moving down. So in other words, we fall out of the loop. So let's begin by declaring a document variable and creating a new document inside it. Then that's at a table. We can use some of the code that were recorded in the last section. So let's go back to Section four. I will just copy this line and let's say we want to create 40 rows and two columns. Next, let's just scribble down to where we entered the headings and copy a couple of lines from here as well. So the head in that we want in that first cell will be total time taken. And this time, instead of moving right, we want to move down and the unit will be a line rather than the cell. So so far we have a table with 40 rows. We've entered total time taken in column one row one and we have moved down to road to column one on, Let's say from here What we want to do is to enter the time in mili seconds that it takes to actually complete the table. Now you may use this technique if you're testing the efficiency of code that you've written and you want to know. Basically, for example, which of two approaches is going to give you the best performance on Dak? You make your macro run most quickly. One of the simplest ways of time in an event is to use the timer function. The time of function gives you the number of milliseconds that have elapsed since the last occurrence of midnight. To use it, you start by taking a snapshot of the timer, and then you constantly refer back to it to find out how many milliseconds have elapsed since your snapshot. So let's declare a couple of new variables for this purpose. The time of function returns a single value. Several cool our variable SNG timer, a single and then the entry that we actually put into the table will be a string civil dim str time as strength. So before we lived, we need to capture the timer in SNG timer because it's a single will use the prefix let which we simply admit snd timer equals timer. So now we can do our loot and the thing that we want to test to control the loop is whether or not we're still in the table. We can do this by looking at the information property off the selection object so it's selection don't information and then in brackets, we use one of these constants W d within table. As long as that statement is true, we know that we're still in the table and we can continue looping. So now we want to calculate the time elapsed and place it inside str time So str time equals Tell him which will be the current time elapsed minus our snapshots SNG timer. This will give us a single value with an indeterminate number of decimals after it. To make it more consistent, we can write the full matte function around it. So that's format and then time a minus SNG timer is the value that were format in comma, and the format that we want has to go in double quotes. So we want one significant number before the decimal. If applicable, dots on, let's say for accuracy. We want four decimal places. Now we want to insert that value into the table and then move down one line so we'll just copy these two lines and tab them in to select them both and press tab. So here we want our str time and I said the word milli seconds. So let's see what we get when we run. This will save and run. Let's look at the results in table. So we've got a total of nor 0.9609 milli seconds African just close. This one of the things I'll be stressing throughout this course is that using the selection object in this way is very inefficient. And as your code becomes more complex and as the number of operations that you're performing increases, you'll find that this inefficiency leads to very slow code and perhaps the macros which take, um, announcer portable amount of time to run. So we will discuss these techniques in detail as we go through the course that, um for the moment, let's look at how we can actually make this slightly more efficient. What we'll do is to copy this code. I'm going back to full model of you so I can make sure I paste it at the end here on. We obviously need to rename this, so we'll call it while wend No selection because the main thing that will be doing is to remove the need to use the selection object so instead of used in this election, object will effectively use a counter so dim I won't call it I since it's not gonna be the inherent counter that we used with the for next loop. So let's call it into counter, as in Georgia. So the cell within the table that we're targeting will now be controlled by the counter. So we can start here by set in this counter to a value to which is the first road that we want to target. So encounter equals two before the lib starts, and then here, the equivalent of moving down will now be to increase that counter. And we do that by saying encounter equals encounter plus one. The next thing we need to do is to change the way that the loop is actually controlled. So now it's going to be controlled by the value of the counter. So we simply say, while encounter is less than or equal to now, we could say 40 because we know that's the number of rows. But what's perhaps better is to say, doc new dot tables on in brackets one because it's the first and only table in dot knew dot rose dot count. So that way, if w