Excel Macros & Excel VBA Programming for Absolute Beginners | Andreas Exadaktylos | Skillshare

Playback Speed

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

Excel Macros & Excel VBA Programming for Absolute Beginners

teacher avatar Andreas Exadaktylos, Teacher | Expert Computer Scientist | Entrepreneur

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

56 Lessons (3h 58m)
    • 1. Introduction

    • 2. Compatibility and Macros Security

    • 3. What are macros and how we can use them

    • 4. Ways of Recording a Macro

    • 5. Ways of Running a Macro

    • 6. Assign a Macro to Ribbon Icons and Create your Ribbon

    • 7. Relative or Absolute Method

    • 8. Macro Recorder vs VBA

    • 9. Enter the world of Excel VBA

    • 10. The VBA Environment

    • 11. VBA Editor Tips 2020

    • 12. VBA Modules and Editor tab Tips

    • 13. Excel VBA Object Model

    • 14. Using VBAs Help System

    • 15. The Anatomy of Macros

    • 16. Make a macro available all the time

    • 17. Organizing your Personal Macro Workbook

    • 18. Watch A Macro Write Your Code

    • 19. Case Study: Use navigation buttons to change Chart Type

    • 20. How to Create your First Piece of Code

    • 21. How VBA speaks

    • 22. The core of VBA: Workbook, Worksheet and Range

    • 23. Object Properties and Methods

    • 24. Sub procedures

    • 25. Sub Procedure Examples

    • 26. Function Procedures

    • 27. Function Procedure Examples

    • 28. Variables

    • 29. Constants

    • 30. Strings and Dates

    • 31. Range and Cells properties

    • 32. Assignment Statements

    • 33. Arrays

    • 34. Range Property

    • 35. Cells Property

    • 36. Offset property

    • 37. Useful Range Object Properties

    • 38. Useful Range Object Methods

    • 39. With End With Constructs

    • 40. How to read from a cell

    • 41. Dim and Set statements

    • 42. Using IF

    • 43. Using Relative or Absolute Reference

    • 44. Using FOR…NEXT Statements with Macros

    • 45. Using DO…UNTIL Statements with Macros

    • 46. Add a confirmation dialog box with VBA IF statement

    • 47. Prompting a user to select a worksheet and run a Macro

    • 48. Combining Macro Recording and VBA Statements

    • 49. How to join 2 different Macros

    • 50. Tips for running your Macros fast

    • 51. Transfer Data From one Excel Worksheet to another

    • 52. How to Create Notifications Reminders

    • 53. Excel Animated Charts VBA

    • 54. Saving a Workbook before Closing

    • 55. Closing All Workbooks at Once

    • 56. Introduction to Excel 2019

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

Community Generated

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





About This Class

In this class my goal is to help you learn ways to create Excel Macros that won’t burst your brain. Also to convey the main principles of VBA language and allow beginners, taking their first steps, to learn without requiring individual training.

A macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer Errors. Macros are written in VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft.

In this Class you will find several short, to the point lessons covering Excel Macros and VBA entertaining and accessible to non-techies. The goal is not to make you a star Excel programmer. If you want to be that, go for it! But I’ll give you some best practices and make this course extremely practical. After all, you are not a programmer, you just need to write an Excel Macro to help you get your work done.

Are you looking to learn practical Macro and VBA techniques you can put to use instantly? If so, then this is the class for you. It’s entirely project based and it’s full of examples which are fully explained and easy to understand. It has been recorder with the brand new Excel 2019 in full HD 1080p.

So let's get started!

Meet Your Teacher

Teacher Profile Image

Andreas Exadaktylos

Teacher | Expert Computer Scientist | Entrepreneur


LinkedIn: https://www.linkedin.com/in/andreasexadaktylos

Hi, I am Andreas and I'm a teacher. I really love learning and teaching whenever i can.

I have over 16 years of experience in teaching as an instructor. I have my own computer learning school, called Futurekids, from 2001 to now. I am the founder of Mellon Training, an online video tutorials learning school.

I have a Masters Degree in Computer Science and a Degree in Electronic Engineering.

I always wanted to create my own business. Being your own boss is an incredibly liberating experience but difficult as well.

I have a passion for software products that make people's lives easier. I love to create websites and optimizing them with Search Engine optimization techniques. I created my online e-sh... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
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. Introduction: Hello students, you know, don't have your reasons for picking up a course on macros and VBA programming. Maybe you got a new job and I want to congratulate you on that. Or maybe you're trying to automate some of the repetitive data crunching tasks, you have to do whatever the reason. Thank you for joining us Excel macros and VBA course. I'm Andreas, your instructor for this course. Inside, you find everything you need to get up and running with VBA fast. This course requires no previous programming experience is appropriate for Excel 2013, Excel 2016, or Excel 2019, and is designated for beginner to advanced Excel users who want to get up to speed with Visual Basic for applications or VBA programming. This course doesn't cover all aspects of Excel programming. But then again, you probably don't want to know everything about this topic. My teaching style uses a mix of screencast demos, slides in onscreen projects. At the end of each section, you'll find quizzes and exercises so you can test your knowledge. Will begin by learning the basics of macros, such as different ways to record and run a macro, will then learn about the security of macros and how to create your own Excel ribbon also will understand the difference between absolute and relative reference. In the next section, we'll learn about the Visual Basic Editor environment and slowly digging in further to understand the core principles of the visual basic language. After you've got the fundamental understanding down of what VBA is, we're going to jump right into more in-depth macro concepts and create our first project. You'll also have in the next section a deep understanding of what VBA procedures are and explain them with over ten examples. Then I'm going to show you all the VBA language elements like variables, constants, string, dates, range and sell properties, assignment statements, arrays, offset properties, range object properties and methods. The next section is very crucial as you'll learn how to make decisions with VBA, with loops, like if else, for next, do until with end, and many more over ten examples in three projects here to connect the dots between theory and practice. In the next section of the course, you'll learn more advanced techniques like VBA events, handling errors, Active X controls, and user forms, how to interact with other applications and much more. This course is constantly changing with new material and new content. Think of it as a never ending subscription. 2. Compatibility and Macros Security: This course is built for the desktop versions of Excel 20132016 and Excel 2019. If you don't have one of those versions, you run the risk of getting confused in a few places. If you plan to distribute your Excel slash VBA files to other users, it's vitally important that you understand which versions of Excel they use. People using older versions won't be able to take advantage of features introduced in later versions. Excel 2010 and later also have some new objects, methods, and properties. If you use these in your code, users with an older version of Excel will get an error when they run your macro. If you're using Excel on a Mac, I'm not going to be able to provide you support. Before you can call yourself in Excel programmer, you must go through the initiation rites. That means that you need to make a small change. So Excel will display a new tab at the top of the screen. Developer. Getting excel to display the Developer tab is easy and you only have to do it one time. Follow these steps. Number one, Right-click any part of the ribbon and choose to customize the ribbon from the shortcut menu. In the Customize Ribbon tab of the Excel Options dialog box, locate the developer in the box on the right. Number three, put a checkmark next to developer number for click OK. You're going to see it pop-up here. When you're working in the lecture, files, always make sure that you close all other Excel workbooks. If you store one or more macros and a workbook, the file must be saved as a Macro Enabled file type. In other words, the final must be saved with an XL SM extension rather than the normal XML SX extension. For example, when you save the workbook that contains your macro, the file format in the Save As dialog box default to XML SX, a format that can not contain macros unless you change the file format to XLS em Excel displays the warning shown here. You need to click no and then choose Excel Macro Enabled Workbook from the Save As Type drop-down list. Macro security is a key feature in Excel. The reason that VBA is a powerful language, so powerful that it's possible to create a macro that can do serious damage to your computer. A macro can delete files, sending information to other computers, and even destroy windows so that you can't even start your system. The macro security features introduced in Excel 2007 were created to help prevent these types of problems. To display the macro security settings, choose Developer tab, then macro security. By default, Excel uses the disable all Macros with notification option. So that's what I have is my setting. With this setting in effect, if you open a workbook that contains macros and the file is not digitally signed or stored in a trusted location. Excel displays a warning like this one here. If you know the workbook is safe, click the Enable Content button to enable the macros. To use the workbook without macros, click the X to dismiss the warning. Perhaps the best way to handle macro security is to designate one or more folders is trusted locations. All the workbooks and the trusted location are open without a macro warning. You designate trusted folders in the Trusted Location section of the Trust Center dialog box. Click on this option and add a new location here. So just browse your folder where you keep your macro files and add that to this list. In some lectures in this course. I do use advanced Excel features. I know this is not an advanced Excel course, so you may not be familiar with the formulas or some of the techniques I use. If you feel you can improve your Excel knowledge, make sure to check out my other dashboard or advanced chart courses. If you feel stuck, remember that you can go back and re-watch previous sections or videos as many times as you like, learned a paste, it feels right for you. That's it for today. I want to thank you for watching this video. Now, let's go create your first macro. 3. What are macros and how we can use them: This is an exciting day. I'm excited for you as you'll learn the basics of macros and create the core pieces of Excel macros and VBA. In this lecture, we're going to see some examples of what macros can do. Then we will create our first macro. So let's dive in. Repeating a certain sequence in Excel is frequent and annoying to do again and again, the same actions into five or ten steps is a time waster. With macros, you can take multiple steps sequences and turn them into a single step. We can say that a macro is a sequence of instructions that automate some aspects of Excel so that you can work more efficiently. You might create a macro, for example, to format and print a month and sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically. Macros are written in VBA, also called Visual Basic for applications, a programming language developed by Microsoft. Let's see some examples to understand how you can use them to automate repetitive tasks. Look at this worksheet. It has 18 rows, but it doesn't have the style we want. We want the company name and sales in columns to copy each cell in a column, the way we want is a time waster. This difficult task can be done with the help of a macro. You can record a macro to take this data and put them into columns, then this data, et cetera. You can also add a button like this that is associated with the macro to do the job very fast. Let's click on this button and see it in action. We show the result almost instantly. But if you want, you can watch it step-by-step. Now go to example two worksheet. There is a data table with this information like name, address, and phone. We can record a macro that takes the information and paste it into a new worksheet as a new record. In our example, I have created a worksheet called Table. Then the macro will go to the previous worksheet, clear the old data so we are ready to go and insert the next record. Let's click on the View tab and press the macro button. Highlight data entry macro and run it. Look at the result. To make it even easier, we can add a button so that the user can just click it and activate the process. These two examples of macros and many more that we're going to explain in detail in the next lecture are huge time savers, insult day-to-day tasks in Excel. Now let's create your first macro. The macro you're bound to create Does this type your name and a cell enters the current date and time in the cell below. Formats both cells to display bold and change the font size of boat cells to 20. This macro won't be winning any prizes in the annual VBA programmers competition, but everyone must start somewhere. The macro accomplishes all these steps in a single action. You start by recording your actions as you go through these steps, then you can test the macros to see whether it works. Finally, you can edit the macro to add some finishing touches. Ready? Okay, let's create a new empty workbook, pressing Control and n is a quick way to do that. Click the Developer tab and take a look at the US relative references button in the code group. If the color of that button is different from the other buttons, you're in good shape. If you use the relative references button is the same color as the other buttons. You need to click it. To enable this option, you will explore the relative references button in a later lecture. For now, just make sure that the option is turned on. When it's turned on, use the relative references button and it will be a different color. Select any cell and click on the Record Macro, enter a name for the macro. Excel provides a default name, something like macro one, but it's better to use a more descriptive name like my first macro. Specifying the shortcut key is optional. Leave it for now. Make sure the storm macro in setting is this workbook. You can enter some text in the description box if you like. Click OK. The Record Macro dialog box closes and excels macro recorders turned on. From this point, Excel monitors everything you do and converts it to VBA code. Type your name in the act of cell, move this L pointer to the cell below and enter this formula equals now open and closed parenthesis and enter. The formula displays the current date and time. Select both cells and make them bold. Change also the size to 20. From the Developer tab, press stop recording. The macro recorder is turned off. Congratulations, you just created your first Excel VBA macro. Now you can try this macro and see whether it works properly. To test your macro moved to an empty cell and from the Developer tab, choose macros to display the macros dialog box. You can see also the macros from the previous open workbook. Before selecting the macro from the list. In this case, my first macro and click run. You must close the previous workbook. Remember this DEP always and don't leave open previous workbooks. You've recorded a macro and you've tested it. If you're the curious type, you're probably wondering what this macro looks like. And you might even wonder where it's stored. The macro is stored in the workbook, but you need to activate the Visual Basics editor or VBE for short to see it. So from the Developer tab, click on Visual Basic. Visual Basic Editor program window appears. This window is highly customizable, so your VBE window may look a bit different. The VBE window contains several other windows and is probably very intimidating. Don't fret, you'll get used to it. In the VBE window, locate the window called project. The project window, also known as the Project Explorer window, contains a list of all workbooks and add ins that are currently open. Each project is arranged as a tree and can be expanded to show more information or contracted to show less information. Here we have two projects. Select the project that corresponds to the workbook in which you recorded the macro. If you haven't saved the workbook, the project is probably called VBA project or book one. Click the plus sign to the left of the folder named modules. The tree expands to show module one, which is the only module in this particular project. Double-click module one, the VBA code and that module is displayed in a code window. Your screen may not look exactly the same as mine. But this is not a problem. At this point. The macro probably looks like great to you. Don't worry. As we continue the lessons, this will all be clear as crystal. The macro consists of several statements. Excel executes the statements one by one from top to bottom. A statement that is preceded by an apostrophe is a comment. Comments are included only for your information and ignored by Excel. In other words, Excel skips right over a comments. As you might expect, you're not only view your macro and VB0, but you can also change it. For example, you can change the name that's entered into the active cell. Working in a VB code module is much like working in a word processing document. After you've made your changes, jump back to Excel by pressing alt plus F11 and try the revised macro to see how it works. Makes sense right? Now, go back to the VBE editor. As said in a previous lecture, if you store one or more macros in a workbook, the file must be saved as a Macro Enabled file type. In other words, the file must be saved with an XLS m extension rather than the normal XML SX extension. If you press the Save button, you can change the extension from there. That's it for today. I hope you understood what macros are and how you can use them. If you have any questions, please let me know. Thank you for watching and I'll see you in the next lesson. 4. Ways of Recording a Macro: Recording a macro is like programming a phone number into your cell phone. On your phone, you first manually dial and saved the number later. You can read down those numbers with the touch of a button. In Excel, you start recording a macro and then you perform your intended actions. While you record, excel gets busy and the background, translating your keystrokes and mouse clicks into a macro. The written code is known as a Visual Basic for applications or VBA. After the macro is recorded, you can play back those actions anytime you want. There are many ways to start recording a macro. The first method is by using the macro recorder, which is on the Developer tab. Select record macro button from the Developer tab and the Record Macro dialog box appears. Now you're going to be prompted with a macro name. Excel gives a default name to your macro, such as macro one. But you should give your macro a name that's more descriptive of what it does. Use a meaningful name here so you can remember it and it should describe what the macro does. Leave the default macro name for now, you can use a shortcut key like lowercase or uppercase letters. It's better to first use the shift key and then a letter because there are already shortcuts with control that use them like Control, C, Control plus N, et cetera. Note that you don't need a shortcut key to trigger or macro, so this field is optional. Let's store the macro in this workbook for now, storing your macro and this workbook simply means that the macro is stored along with the act of Excel file. Next time you open that particular workbook that macro is available to run. There are other choices, as you can see, but we'll discuss them later. The description is helpful, especially if you use a lot of macros in this box will type a detailed description of what the macro does. Click OK to close the Record Macro dialog box and begin recording your actions. For example, select a cell in your Excel spreadsheet, type in your name and I selected cell and then press Enter. Also, make the text bold. To stop recording our actions, choose the Developer tab. Code, stop recording, or click the Stop Recording button in the status bar. The macro was recorded in a new module named Module one. To view the code in this module, you must activate Visual Basics editor. You can activate the VBE editor in either of two ways. Press Alt plus F11, choose developer code Visual Basics. In VBE editor, the project window displays a list of all open workbooks and add adds. This list is displayed as a tree program, which you can either extend or collapse. The code that you record in previously is stored in module one in the current workbook. When you double-click module one, the code in the module appears in the Code window. We will learn all the details about VBE editor later. So close this window for now. Okay, now let me show you the other ways of recording a macro. I think the fastest way is from the lower left corner where you can see a button with the word ready. This bar is called the Status Bar. Right-click and enable the choice macro recording. Now you can see a Bonds besides the ready word. Slide the mouse over and a pop-up message appears. No macros are currently recording. Click begin the recording if you want. Another way is from the View tab from the ribbon, there's a button on the right called macros. Click the down arrow and select the option, record a macro. So we have many choices to record a macro, select the one you prefer, saved the file as x LSM extension and have the file name type. My first macro. We covered quite a lot in this first lesson. Now in the next video, I'm going to take you through the ways of running a macro. I can't wait to show you, so I'll see you in the next video. 5. Ways of Running a Macro: Today's lesson is about running and testing a macro. So if you're ready, let's jump into it. There are two ways that I used to run a macro using the ribbon. I already opened the money first macro Excel file from the previous lecture. As we said in a previous lecture, if you open a workbook that contains macros and the file is not digitally signed or stored in a trusted location. Excel displays a warning like this one here. If you know the workbook is safe, click the Enable Content button to enable the macros. To use the workbook without macros, click the X to dismiss the warning. Let's click on the Developer tab and then click macros on your left. From the dialogue box. Choose the macro you want to run and click the run button. Great. Another way to run the macro from the ribbon is from the View tab. Highlighted and click the macros button. And there we will see a list of macros. So we select the one that we want and click run. Let's record a very simple macro. To begin with, click the View tab on the ribbon. Choose the record macro from the macros drop-down list. You will get the Record Macro window, right, the macro name, for example, first macro, the shortcut key. Let's create control shift f for a keystroke shortcut, and select where you want to store your macro in the corresponding field. Let's store the macro in this workbook. If you want to store the macro On a personal macro workbook, it'll always be available. Optionally, you can write the description, but for now we'll leave it blank. Press OK. Now the macro is recording an action. For example, you can write my first macro in the B1 cell. Now go to the macros icon and select the stop recording option. The macros you save or stored on the macros button. What we want is to associate a macro with the button simply by going to the Quick Access Toolbar and clicking this button. Okay, now choose more commands. On your left side from choosing commands from click the drop down arrow and then macros. We are looking for this one. Select it, and by clicking add, we can add it to the Quick Access Toolbar. If you want to change that button, click modify, and you can choose the button you want for almost 200 choices. I'll choose that. Change also the display name and press OK. OK again, and the button is available. To try it, we can highlight this worksheet and press this button. It's better to store a macro that you use frequently in the Quick Access Toolbar. It's the faster method to run a macro. When you create a macro, you may want to have an easy and effective way to run each one like a button. You can not expect users to open the code window and run it. You can use a button, a scroll bar, or whatever form of control you want and assign a macro to it. A macro you've already recorded. When the control is clicked, the macro is executed or played. The first step is to highlight the answer tab. From there, we can insert a shape, a rounded rectangle, for example. Nice. Now click the drag somewhere there and the button is ready. If you like, you can change some of the formatting options like TextField or text effects. We are ready to type a name into this button. Great. The second step is to right-click and select Assign macro. A new dialog box appears. We have a macro called My first macro. Select and click OK. That associates the macro with the button. To try it, simply click the button and look at the results. Another way is to associate a macro with an image. The steps are the same. From answered tab, click online pictures. For example, type a line here, enter and choose this image. Right-click as before, assign macro, choose this macro one. And okay, fantastic. We manage to associate a picture with a macro. One final note. If you have lots of macros, you may end up with buttons all over your workbook. This can be messy. So the idea is to put all your buttons on one sheet with descriptions like a control panel. It will make your life easier for sure. 6. Assign a Macro to Ribbon Icons and Create your Ribbon: We've seen how to add buttons and shapes to trigger a macro, which is great, but they are only relevant to macros that work in a single sheet. Once you move away from that sheet, you can not see the button or shape to click it. In alternative to that is to have buttons on the Excel ribbon. How do you create your button and place it to the ribbon? Go to File tab and click on the Options button and customize ribbon. Now click the dropdown and choose macros. I have two macros that I've created from a previous lecture. First, we need to choose which of the current tabs we'd like to add them to. I'm going to add them to Home tab. Within home ribbon, there are seven groups. I cannot add my macros to an existing group, so I will add a new group from this button. And what did rename it and call it my macros. If I click OK, I will see a little empty group called my macros. Select my macros, transpose macro from the left, click the Add button, and do the same with data entry macro. Okay? And it appeared here. There are not quite presentable icons. So go back to the customize ribbon. Click the first one, Rename and choose a nice symbol for this macro. Do the same for the second macro. Okay, let's see how they appear on my home ribbon. Much better. Let's see if these icons work as macro triggers. Click transpose, and it nicely worked. Also click data entry. And this works fine. We've just seen how to assign icon to macros on the ribbon on a current tab within their group. But if you start to add more macros, more icons, current tabs are almost full, so the icons will become smaller and smaller. If you are likely to have a lot of macros than a good idea is to create your tab. You can have your groups and organize your macros. Let me show you how you can do it. File Options, Customize the Ribbon, and I'll add a new tab. I'm going to rename that Macros. And I will add two groups, data and transform. Now move the two macros to the macros tab. Press OK and look at the new tab. That's it for today. I want to thank you for watching this video tutorial, and I'll see you in the next lesson. 7. Relative or Absolute Method: When recording your actions, excel normally records absolute references to cells. This is the default recording mode, but quite often this is the wrong recording mode. If you use absolute recording mode, Excel records actual cell references. If you use the relative recording, excel records relative references to cells, let's see the difference. Open a new workbook and let's record a simple macro into absolute mode. This macro simply enters my first and last name in a worksheet. Go to the Developer tab and make sure that they're used. Relative references button is not highlighted and then choose Record Macro. Type absolute as its first name for this macro, and click OK to begin recording. Activates cell B1 and type in Andrei as in that cell. Now moved to sell c1 and I'll type my last name. Click cell B11 to activate it again and stop the macro recorder. Press Alt plus F 11 to activate the VBE and let's examine the code in module one. When executed, this macro select cell B1 and inserts the first and last name into the range B1 to C1, then the macro reactivates cell B1. These same actions occur regardless of which cells active when you execute the macro. A macro recorded by using absolute reference always produce the same result when it's executed. Now in some cases, you want your recorded macro to work in cell locations in a relative manner. You may want the macro to start entering the first and last name and the act of cell. In such a case, you need to use a relative recording. You can change how Excel records your actions by clicking the use relative references button in the code droop of the Developer tab. This button is a toggle button. When the button appears highlighted in a different color, the recording mode is relative. When the button appears normally, you're recording an absolute mode. You can change the recording method at anytime, even in the middle of recording. To see how relative mode recording works, delete the contents of range B1 to C1, and then perform the following steps. Activates cell B1 from developer press Record Macro. Named this macro relative, and click OK to begin recording. Click the US relatives references button to change the recording mode to relative. When you click this button, it changes to a different color from the rest of the ribbon. Type Andres in cell B1, then moved to cell C1 to type in the lastname, returned to cell B1 and stop the macro recorder. Notice that this procedure differ slightly from the previous example. In this example, you activate the beginning cell before you start recording. This is an important step when you record macros that use an active cell as a base. This macro always starts entering text in the active cell. Look at the code that Excel generates, which is quite different from the code generated in absolute mode. Notice that the code generated by the macro recorder refers to cell A1. This may seem strange because you never used cell A1 during the recording of the Macro. This is simply a byproduct of the way macro recorder works. Thanks for watching. 8. Macro Recorder vs VBA: The macro recorder records every task you perform in Excel, all you have to do is record a specific task. Once. Next you can execute the task over and over with the click of a button. The macro recorder is also great help when you don't know how to perform a specific task in Excel VBA, simply open the Visual Basic Editor after recording the task to see how it can be programmed. Of course, programming knowledge is not required. Unfortunately, there are a lot of things you cannot do with macro recorder. For example, you cannot loop through a range of data with a macro recorder. Moreover, the macro recorder uses a lot more code than is required, which can slow your process down. So building macros can be easier and faster than writing VBA code for simple applications and making global key assignments. However, more advanced and complex applications are not so easily accomplished using macros. On the other hand, VBA requires programming knowledge and experience at Excel. Vba is a fully fledged program language built into Excel, so it allows a lot of control over the program. These are the most pros and cons of macros and VBA. I hope you understand the differences between these two methods to create an automated task, you might think that recording a macro would generate some award winning VBA code. That's not true because the macro recorder records virtually every combination of actions to demonstrate just how inefficient the macros recorders code can be. Try this. Turn on the macro recorder. Choose Page Layout. Then from page setup, orientation, Landscape, and turn off the macro recorder. Open VB editor and take a look at the macro. You may be surprised by the amount of code generated by this single command. Although you changed only one print setting, Excel generated code that set many other print related properties. Here's how the macro looks after deleting the irrelevant lines. You can simplify this macro even more because you don't need the width and width construct. By the way, landscape is a built-in constant that makes your code easier to read. This Constant has a value of two, so the following statement in works the same. Lesson. After lesson, we will discover how to modify a recorded macro to make it more useful. 9. Enter the world of Excel VBA: In any workbook that has macros like this workbook that I've just opened. Sometimes you want to view the macro code with Visual Basic, which is the programming language of macros. Visual Basic Editor VBE is the environment while all Excel macros are stored and processed. Visual Basic Editor is a separate application that runs when you use Excel. When you create a macro, VBE quietly comes to life, ready to process the various procedures and routines you give it. To see this hidden VBE environment, you'll need to activate it. From the View tab on the ribbon. Now dropped this arrow from the macros button, then View Macros. Now from macro window, we can select the macro and click Edit. And we enter into the world of VBA. A few key things you'll need to remember. Workbooks contain modules, modules contain procedures, procedures contain your code. You can have as many modules and procedures as you wish. There are two types of procedures, subs and functions. In VBE editor, the project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram. Would you can expand or collapse. The code that you recorded previously is stored in module one in the current workbook. When you double-click module one, the code in the module appears in the Code window. So what we are seeing on the screen is the macro that was recorded from a previous lecture. I can understand it. It is a bit scary to see this code. But if you view the code of a macro every time you record a new one, believe me, you'll become familiar with it. Close the window for now. Another way to open a VBE window, and I think it's the quickest way is to press Alt plus F11 when Excel is active. But still it great to return to Excel. Press Alt plus F11. Again, you can activate VB0 also by using the Visual Basics command, which is on excels Developer tab. Try this method too. Nice. A few keys that I would like to tell you is that if you notice on the top code lines, there was always the name of a macro that you've recorded. The recorded macro is a sub procedure. When you start the sub, it goes through the lines of code in the order in which they appear. So at first performs the task given by the first line, and when that is finished, it moves onto the next line and performs this task. Also note that Excel inserted some comments, which consists of information from the recorded macro dialog box. These comment lines which begin with an apostrophe aren't necessary. Deleting them does not affect how the macro runs. Placing a single apostrophe in front of any text creates a command and then turns the text grain. So Excel will skip these lines when running a macro. And finally, you can copy code from a macro and place it into another macro. You can change the content of a macro and make as many changes as you want. We'll see examples of these in the next few lectures. 10. The VBA Environment: Every time you record a macro, your actions are translated into the programming language VBA. As I said to previous lectures, the easiest way to open Visual Basic Editor is to press Alt plus F11. Vbe contains several windows and is highly customizable. On your left, you can see a project panel and a project window. If you can't see that, for example, let's delete it and go to the menu bar and click View tab, which contains commands that you use to do things with various components in VBE and click project explorer. Below, we have the properties window. It's not so useful and you'll use it most of the time to change the name of the macro. To the right, there is a window called the Code window. Every object in a project has an associated code window which contains the VBA code. If it's not present, go again to view and select code. If it's blank, opened the plus icon of the modules and double-click one of these. You can customize the size of all the editor parts by moving this vertical split line. Code windows are much like Workbook windows in Excel. You can minimize, maximize, resize, hide, and rearrange them and more. I'd like to maximize the Code window so that I can see more code and reduce distractions. This course is not about being proficient with VBA, but we'll learn a lot of basic principles. If you want to change the name of a macro, simply go there and change it to bold macro. We also explained in the previous lecture that placing a single apostrophe in front of any text creates a command and turns the text green. Let's delete these lines. If you deleted a statement that you shouldn't have used the undo button on the toolbar or press control plus Z until the statement appears again. Over time, you'll begin to understand many of these code lines. If a single line of VBA code is long enough, then you can use the line continuation character to break up long lines of code to continue a single line of code, also known as a statement, from one line to the next. And the first line with a space followed by an underscore. Then continue the statement on the next line. On the left side in the Visual Basics went OUT modules. The workbook that we currently have opened has two modules. I'm going to click Module two. You see a different macro there. You can easily change the name of the module by simply going into the properties window. You see the name down below, clicking this textbox and rename it. Great. One last thing that I want to say is that you can cut the code from a module and pasted into another or a new one. We could have multiple macros in a single module. I'll do an example with module one and a new module. Double-click on it and you can see it has two different macros. This one and this one. Highlight the code that sets the font and bold carefully. Press cut. Now right-click the modules folder, create a new module and paste the code. Now the new module with the code is inside and is ready. So take your first look behind the curtain to explore a Visual Basic Editor. Let's continue to the next lecture. 11. VBA Editor Tips 2020: As a separate application, Visual Basics editor contains several windows that you can customize it make your life easier. Starting from the menu bar, many commands have shortcut keys associated with them. For example, you can right-click and customize commands. This tab includes all of the commands. So if I frequently use the print command, all simply drag it and drop it on the toolbar. The toolbars wanna for toolbars, if I right-click, I'll see the other three. The project window displays a tree diagram that shows every workbook currently open in Excel, including add-ins and hidden workbooks. If the project window is not visible, press control plus r or choose View, and then project explorer. To hide the project window, click the Close button, the X in the title bar. Or you can right-click anywhere on the project window and select hide from the shortcut menu. Now to view and objects code window double-click in the object in the project window. For example, to view the code window for module one object double-click module one in the project window. Finally, the Immediate Window is useful for executing VBA statements directly for its debugging your code. For a VBA beginner, this window won't be that useful if it isn't visible. Press control plus G or choose View Immediate Window. To close the immediate window, click the Close button, the eggs in its title bar. It's not the scope of this course to become a VBA expert, but I think it's necessary to learn the parts of VB editor. Don't forget that will create code by using the Excel macro recorder. 12. VBA Modules and Editor tab Tips: Press Alt plus F11 to open the VBA editor. To add a new VBA module to a project. Follow these steps in the VBE. Select the project's name in the project window. Then choose Insert Module or right-click the project name and choose Insert module from the shortcut menu. When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. Workbook holds the module for the recorded macro depends on where you choose to store the recorded macro just before you started recording. Sometimes you need to remove a VBA module from a project. For example, it may contain code that you no longer need or it's empty because you've inserted the module and then changed her mind to remove a VBA module from a project. Select the module's name in the project window and choose File. Remove XXX, where X, X, X is the module name. Or right-click the module's name and choose Remove module one from the shortcut venue. You can remove VBA modules, but there is no way to remove the other code modules. Those are the sheet objects or this workbook. Every object in a VBA project can be saved to a separate file. Saving an individual object in a project is known as exporting. It stands to reason that you can also import objects into a project. Exporting and importing objects might be useful if you want to use a particular object, such as a VBA module or a user forum in a different project. Follow these steps to export an object. Number one, select an object and the project window. Number two, Choose File, Export file, or press control plus E. You get a dialogue box that asks for a filename. Importing a file to a project goes like this. One. Select the project's name in the Explorer window. To choose File, Import File, or press control plus M. You get a dialogue box that asks for a file, locate the file and click Open. In general, a VBA module can hold three types of code, declarations, sub procedures, function procedures. You must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways. Enter the code directly. Use the Excel macro recorder to record your actions and convert those actions to VBA code. Copy the code from one module and paste it into another. To open the editor tab, choose tools and then Options. You'll see a dialog box with four tabs, editor, editor format, general, and docking. Let's see the first tab. Auto syntax check option. If you don't choose this setting, the VBE flag syntax errors by displaying them in different colors from the rest of the code. And you don't have to deal with any dialogue boxes popping up on your screen. Require variable declaration option. If the required variable declaration option is said, VBE inserts the following statement at the beginning of each new VBA module, you insert Option Explicit. You'll learn in later lessons why it's better to require a variable declaration. Auto list members option. If the auto list members option is said, the VBE provides some help when you're entering your VBA code. So it should be checked always. Auto quick info option. If the Ohno quick info option is said, the VBE displays information about functions and their arguments as you type. This can be very helpful. Auto Data tips option. If the ATO data tips option is said, the VBE displays that the value of the variable over which your cursor is placed when debugging your code. Useful option. Let's click on the editor format tab. With this tab, you can customize the way the VBE looks. Usually, I don't change anything here. Let's click on the General tab. Now, these are options available on the General tab. The most important setting is error trapping. It is considered a best practice to use the brake on unhandled errors setting, which is the default. If you use a different setting, your error handling code won't work. I hope you understand the VBA modules and how to set up the editor tab. If you have any questions, please let me know. By 13. Excel VBA Object Model: Excel, VBA, or Visual Basic for Applications, is a programming language. In other words, it is a system of statements you use to manipulate a computer. Imagine an intelligent robot that knows all about Excel. This robot can read instructions and it can also operate excel very fast inaccurately. When you want the robot to do something in Excel, you write a set of robot instructions by using special codes. Then you tell the robot to follow your instructions. Vba is an object-oriented programming language. It focuses on the objects involved in the calculation. In Excel, you could think of it as a workbook or a worksheet of ranging cells. Let's look at this car and think of how you can describe it. Look at these three characteristics, properties, methods, and events, that they are the backbone of object-oriented programming. Descriptive items are called properties for the car, you can have properties, and these could be the model price or color. Actions you can perform with the car are called methods. For example, you can start the car, drive a car, or wash a car. Things that can happen to a car are called events such as the car has started. They are instructions that you can give. Now we're going to examine the Excel object model. Let's start with the application object, which represents the excel program itself. It has properties like the version number, the default file path, or the caption, which is the text that appears on the title bar. The application object also has methods like calculate that, updates all the workbook formulas, check spelling and quit, which closes the Excel. The application object event include She calculate new workbook and Workbook before close, which runs some code after the user has selected to close the workbook, but before Excel closes it. Now, let's go one level down to the workbook object. It has properties including name sheets and saved. The workbook object has methods like close, print out and save. Workbook events include before close, which runs the code you specifically called for before the workbook closes New Sheet and before save. Finally, the worksheet object represents the worksheet before the workbook. Worksheet object has properties such as name, used, range, and visible. The worksheet object methods include activate, copy, and delete. Worksheet events include activate and change, which detects any worksheets cell is changed by the user or an external data. Put it another way. If you want to do something with a range of a particular worksheet, you may find it helpful to visualize that range in the following manner. Range contained in Worksheet, contained in workbook, contained in Excel. Here is a summary what VBA is all about. You perform actions and VBA by writing or recording code in a VBA module. A VBA module consists of sub procedures. A set procedure is a chunk of computer code that performs some action on or with objects. A VBA module can have function procedures. Of function procedure returns a single value. Vba manipulates objects. Example of objects include a workbook, a worksheet, a cell range, a chart, and a shape. Objects are arranged in a hierarchy. Objects of the same type form a collection. For example, the worksheet collection consists of all the worksheets in a particular workbook. You refer to an object by specifying its position in the object hierarchy using a dot, aka a period as a separator. For example, this code. Objects have properties. You refer to a property of an object by combining the object name with the property name separated by a dot. For example, this code. You can assign values to variables. For example, this objects have methods. You specify a method by combining the object with a method separated by a dot. For example, this. In the next lesson, we'll dive into the world of VBA. 14. Using VBAs Help System: The VBA help system describes every object, property, and method available to you and also provide sample code. If you're using Excel 2013 or later, you must be connected to the internet to use the VBA help system. You can however, download the VBA hump system from Microsoft's website. So you can have it offline. Open Google server from a browser and type download Excel VBA documentation. And here is the page. Click Download. Check the box Excel 2013 developer documentation. And click on Next. This is the file that you can save or open it. If you're working in a VBA module in one information about a particular object, method or property, Move the cursor to the words you're interested in and press F1. In a few seconds, you see the appropriate help topic displayed in your web browser, complete with cross-references and perhaps even an example or two. I've already opened a module with VBA code inside. Highlight this code and press F1. It open a webpage with help for this topic. The VBE includes another tool known as the object browser. As the name implies, this tool lets you browse through the objects available to you to access the object browser, press F2 in the VBE is active or choose view object browser. The drop-down list at the top contains a list of all currently available object libraries. The second dropdown list is where you enter a search string. For example, if you want to look at all Excel objects that deal with message box, type, message block, and the second field and click the Search button. The search results window displays everything in the object library that contains the text message box. Finally, there is a handy feature called auto list members. This feature provides a list of properties and methods as you type. For example, click here, start typing workbooks. And after typing the dot, after workbooks, the VBE displays a list of properties and methods for that collection. Typing the letter narrows the list to the items that begin with that letter. With each letter you type, the VBE further narrows the list of choices. Thanks for watching. 15. The Anatomy of Macros: You already know that when you record a macro, Excel is writing the associated VBA code. Vba is an object-oriented programming language that consists of various individual objects. Each object has features that are Properties and uses which are methods. A cell is an object. Cell's properties are filled with color or width. The cells method is to have a chart or shaping it. For example, we already record a macro that goes to the second sheet. Type of text, change the width of the column, make some formatting, and finally rename the sheet. Here is the code, the cell range A1, the worksheet sheets to and the workbook itself are all objects. Properties are essentially the characteristics of an object. A worksheet object has a sheet name, which is a property. Look at the following example of a property. Methods are the actions that can be performed against an object. For example, a select method is this line or a copy method of the range object is the following. Range, A1 dot copy. All of these are theoretical concepts but very important to understand. Finally, we have event procedures, a few examples of events or opening a workbook, changing a value in a cell and Double-clicking a cell. 16. Make a macro available all the time: Most of the time when we create a macro, it is designed for use in a specific workbook. But if you want a macro to be available all the time, regardless of which workbook is open at the time, you have to store macro in the personal macro workbook. Now, whereas the personal Mac grow workbook, Let's record a macro that will make a cell A1 bold, italic and underline it. Click on this box and Record Macro window opens. Look at this option. Store macro n, and click the down arrow to open the dropdown list. If you select a personal macro workbook, this macro will be available in all your work. If you store macros in a personal macro workbook, you don't have to remember to open the personal macro workbook when you load a workbook that uses macros. We don't care for now for the macro name or a shortcut key or description. Press OK and stop recording. Now press Alt plus F11 to open the Visual Basic Editor window and look at the project window. On your left, there is a VBA project called Personal dot XLS B, which means it's a binary file. Click the plus sign and we see two folders, modules and Microsoft Excel objects. Double-click module one. And here's the code for the macro we've just recorded. I want to tell you an important note. If you recorded a macro for a specific workbook and want to make this macro available all the time. You can copy it and paste it into the personal macro workbook like this. Great press, old plus F11. If you want to see the code of a macro stored in the personal macro workbook. You can't. The personal macro workbook is a hidden window to keep out of the way. So the personal macro workbook will always be available and open when we open excel as a hidden file. But you can see the code if you open the VBE editor. 17. Organizing your Personal Macro Workbook: If you work with macros alot, you may need to organize them based on what a macro does. For example, you may have macros for charts, for formatting or formulas. I talk of course, for the macros that are stored in the personal macro workbook, Let's press Alt plus F11 to open the Visual Basic Editor window. From the project window and personal XL SB VBA project. You can see one module. First of all, we can rename modules, simply highlight them and from the properties window, we can rename them. Let's type personal one. Nice. Also you can add a module by right-clicking on the modules folder, Insert and then module. Name it personal to. Now you can copy inside the new module whatever macro code you want. Finally, it saved to save every change you do in Macros. I frequently click on the File menu and save personal dot XLS B. So organizing your personal macro workbook is crucial, especially if you have a bunch of macros that you often use. 18. Watch A Macro Write Your Code: When we create a macro, we actually create the VBA code behind the scenes. As you can see, I arranged horizontally the excel worksheet and the VBA window. While we record a macro, the VBA code is created. This is very useful because by observing the VBA code, you learn VBA programming. So let's get started. From the Excel sheet. Go to the Developer window and click the Record Macro button. We give the name observed macro story in this workbook, okay? And the macro begins. As you can see that in book one file, we now have a module section called Module One. Double-click to open. Here is the name of the macro and as a comment to go back to the Excel window. And as we start doing things, the VBA code appears. For example, we highlighted B2 cell and the command range appeared in the VBA window. I will type my name, enter and I get active cell dot formula R1, C1 equals Andrea's range C3 select, of course, if I move this active cell, the range is changing. Now go to D3 and type the number 20. And see how the macro is building the VBA code, highlight the name again and fill the cell with a light gray color. And the code is ready. You can now understand how important it is to watch the code and how fast you can learn VBA programming in this clever way. Don't forget to stop recording at some point. Also, Module one will appear as you start recording a macro. I hope you've learned how to arrange the two windows and start watching the macro build the code. Thanks for watching. 19. Case Study: Use navigation buttons to change Chart Type: A simple but effective way to run a macro is with the help of form controls and specifically buttons. How can you do that? You place a button on the ribbon and then assign a macro to it. When the button is clicked, the macro is executed. The most common use of macros is in navigation. In the following example, you'll create a quick and easy way to see the same data on different charts. In this worksheet, I have a data table with product sales from the year 2010 to the year 2014. Also, I have a clustered column on the right below the data table. You'll create two macros, one for column chart and for one for the bar chart. What gets started? Press this button to start recording, name ID chart one, and store it in this workbook. Nice. Highlight the chart. Now go to the design tab and click on Change Chart Type, select column chart, and okay, stop recording the macro. Let's do the same steps for the second macro and start recording again. Name it to chart two and store it in this workbook. Highlight the chart, go to the design tab, and click on Change Chart Type. Select the bar chart and okay. Stop recording the macro. The final step is to design our buttons on the Developer tab. Click the Insert command. The dropdown list that appears, select a button form control. When you drop the button control on your spreadsheet, the sign macro dialog box appears. Select the macro chart one and then click OK. Let's do the same for the second button. Great. If you want, you can do some formatting, like adding text to the buttons. Test them. As you can see, they work great. Recording specific actions enable you to manage risk because your users will interact with your reports in a method you've developed and tested. So we managed to automate simple reporting tasks using macros and buttons. 20. How to Create your First Piece of Code: Welcome to this new section. It's fantastic that you made it here as you are going to create your first piece of code. And in this lecture we are going to write our first line of VBA code. Don't worry for now about how it works. We'll get to all that. The line is saying hello world, we already opened a new workbook. Save it as hello world. Press Alt plus F11 to open the Visual Basic Editor. Now from VBA project Hello World, right-click and select, Insert and Module. Great. A blank page appears. Type in the text Public sub, first code and press enter. Now that we've created our first procedure, we are ready to write the first line of code. Type in a message box. Double-quotes, helloworld, double-quotes again, let's run it. Return to Excel with alt plus f 11 and from developer ribbon, select macros, then select your sub and run. If you did the procedure right, it will display a message with the text Hello World. Congratulations, you've created your first macro writing VBA code. Now let's dive deeper. We want the text Hello World to be displayed in cell A2 on Sheet one. Delete the previous line and started to type the following. Workbooks. Open parenthesis, helloworld dot XLS, eggs in double quotes. Close parenthesis, dot worksheets. Parenthesis, sheet one in double-quotes. Dot range a2 cell equals two. Helloworld. The decimal point that you will see before worksheets and before range means is a member of. So, so a2 is a member of sheet one and Sheet one is a member of helloworld workbook. In the next lecture, we'll discuss these three elements in more detail. Finally, equal meaning here is assigned to tested again and look at the results. Great. Please remember that almost always you need to tell VBA which Workbook and Worksheet you want to use. Jot it down. It's very important. You have seen how easy it is to do simple Excel tasks than VBA if you want, play around with these examples. Thank you, and I'll see you in the next lecture. 21. How VBA speaks: Hello Excel fans. I hope you're excited about writing your first code in the previous lecture. If you were going to write code for instructions to serve a tennis ball using basic, the instruction would go something like this. You have a verb, serve and then a noun ball. Vba, which is an object-oriented language. It doesn't work like this. It's an object-oriented language. The objects nouns are most important. So the instructions to serve a ball in tennis using VBA would be as follows. Ball serve in VBA, ball is an object and serve as a method. The basic structure of VBA has this syntax Object.create method for almost any object and VBA, there's a collection of that object. You don't have only one ball. There's a soccer ball or a basketball, volleyball, and tennis ball. When you refer to something that is a collection, you have to tell the programming language to which item you are referring. You can refer to any item by using a number. The only difference between an object and a collection is that you have to add an S to the name of the object. For example, if the tennis ball is the fourth ball, you could type balls for dot serve. A better way to do this is to use a name for the object in collection instead of a number, balls, tennis dots serve. You could also add some parameters to the method. Balls tennis dot served direction, right? Some methods simply act on their own. To simulate pressing the F9 key, you can use this code application dot calculate. It's also important that objects have properties just as adjectives describe a noun, properties describe an object. For example, in object is an active cell and has the following Name code. Active cell. If you want to change the height of the cell, you can active cell dot height equals to 20. This can be very confusing because it property is similar to method. But don't worry if it all seems very confusing right now when you are writing VBA code from scratch, it is tough to know whether the process of changing a cell to read of changing the height requires a verb or an adjective. Is it a method or a property? This is where the macro recorder is especially helpful. When you don't know how to code something. You record a short little macro, look at the recorded code and figure out what is going on. Also, Excel VBA is extremely useful. You can highlight any word of any line of code, for example, active cell. By pressing F1, you will see the Help topic for the active cell object. Now it's time to record a macro and see whether it makes more sense now that you know about objects, properties, and methods. Return to Excel from the Developer tab. Press the Record Macro button. Okay, first, select a range of cells. For example, from D to G 20. Make them bold. Then color the background with yellow color. Change also the font color to red. And lastly changed the font size to ten. Stop recording, press Alt plus F 11 to return to the VBA editor. Here is the code. The Sub statement defines the name of the macro, which is macro one. These line code with green font color our comments to let Excel VBA and know that you want to insert a comment, precede the tax with an apostrophe. This lines like the rain from D1 to G2D. Do you remember the objects.me basic VBA syntax? Well, ranges the object and select is the method. The next line makes the font bold. Font dot bold is a property. The following lines of code change the background of a cell to yellow. We will learn the width end with statement in the next lesson. These XML solid or Excel automatic are constants that are pre-defined in Excel VBA. We will talk about constants in the following lessons. These lines changed the font color to red, and these change the font size to ten. Generally, a macro runs quickly. You started, and less than a second later, it is done. If something goes wrong, you do not have an opportunity to figure out what the macro is doing. However, using Excel's step into feature makes it possible to run one line of code at a time. To use this feature, make sure your cursor is in the procedure you want to run. And then from the menu select debug, step into. Alternatively, you can press F8. The VBE editor is now embraced mode. The line is about to be executed is highlighted in yellow with a yellow error in the margin before the code. In this case, the next line to be executed is the sub macro one-line. This says you were about to start running this procedure. Press the F8 key to execute the line in yellow and move to the next line of code. Press F8 again to run the next line. If you switch to Excel by pressing alt plus tab, you will see the execution of the macro. In a later lesson, we will see more debugging options. That's it for today. I hope you understood the basic structure of the VBA language. I want to thank you for watching this lesson and we'll see you in the next lesson. 22. The core of VBA: Workbook, Worksheet and Range: Hello everyone. In this lesson we'll continue to learn more about the three important elements of VBA, workbook, worksheet and range. I've already opened the helloworld workbook and I'm ready to open VBA editor using alt plus F11. Now double-click module one. Let's get started with workbooks. To access any open workbook, we can use the following code. Workbooks, parenthesis, double quotes, type the workbook named here, close quotes and parenthesis. Keep in mind that the workbook you specify must be opened or you'll get an error. To alternatives to access workbooks are this workbook and active Workbook. Let's change the code. This workbook refers to the current workbook, the one that contains the code. By using this workbook instead of workbooks and the name of the workbook, we do not need to worry about the name of the file. Active workbook refers to the workbook that is currently active. But if you have more than one, any workbook could become the active one. So I suggest using active workbook very carefully. Now let's talk about worksheets. To get the worksheet, you can do so using the name of the sheet. Notice that if the worksheet name changes, then you'll need to also change the name of the worksheet in the code. The last element is ranges. It's used to identify the cells you want to use. Of course, you can use a single cell, like our examples or a cell range. Let's change the code to display the message Hello world into the cell range from a1 to a2. This workbook dot worksheets sheet one dot range A1 to a ten equal. Hello world. Okay, tested now and you'll see it works great. These are just a glance at the properties of workbooks, worksheets in range. As we continue, we will learn more about them. Thanks for watching. 23. Object Properties and Methods: Hi everyone, and welcome to this lecture. Here I have a workbook with some data inside the cells. Press Alt plus F11 to open the VBA editor. Now insert a new module. Okay? Collections are another key concept in VBA programming. A collection is a group of objects of the same type. A collection is also an object to reference a single object from a collection, you put the object's name or index number in parentheses after the name of the collection like this Worksheets Sheet one. Notice that the sheets name is in quotation marks. What about chart sheets? Well, as it turns out, the object model has a collection called charts. This collection contains all the charge sheet objects and a workbook. And there's another collection called Sheets. The sheets collection, all sheets, worksheets and church sheets in a workbook. Sheets, sheets, one in double-quotes. So a single worksheet named Sheet one is a member of two collections. The worksheet collection and the sheet collection. You can refer to it in either of two ways. Now let's learn how the hierarchy works. If you want to work with Excel objects, they are under the applications object. So start by typing application. Every other object in excels object model is under the application object. You get to these objects by moving down the hierarchy and connecting each object on your way with the dot operator to get the workbook object named book one dot XLS x. Start with the application object and navigate down to the workbooks collection object. Inside workbooks type book one dot XML SX. Navigate to a specific worksheet at a dot operator and access worksheets collection object. Type one inside worksheets because we want the first sheet, not far enough yet. If you really want to get a value from cell A1 on the first worksheet of the workbook named book 1.2x Ls X. You need to navigate one more level to the range object. So type range A1 in double quotes makes sense, right? If you were required to fully qualify every object reference you make, your code would get quite long and it might be more difficult to read. Fortunately, Excel provides some shortcuts that can improve the readability and save you some typing. For starters, the application object is always assumed. There are always a few cases where it makes sense to type it. I already delete the application object, omitting the application object reference shortens the example from the previous section. That's a pretty good improvement. But wait, there's more. If you're sure that book 1.2x Ls X is the active workbook. You can omit that reference to. Let's delete this. Now you're down to this code. Now you're getting somewhere. You've guessed the next shortcut. That's right. If you know the first worksheet is the currently active worksheet, Excel assumes that reference and allows you to type just this. Now how cool is that? Okay, delete the code. Every object has properties. You can think of properties as attributes that describe the object. And objects properties determine how it looks, how it behaves, and whether it's visible. Using VBA, You can do two things with an object's properties. One, examined the current settings for a property to change the properties setting. For example, a single cell range object has a property called value, and the value property stores the value contained in the cell. You can write VBA code to display the value property. Or you may write VBA code to set the value property to a specific value. The following macro uses the VBA built in message box function to bring up a box that displays the value in cell A1 on Sheet one of the act of Workbook. Let's start by creating a sub procedure with the name show value. Now let's name the variable that displays this value in cell A1 contents. Finally, I used the message box function with the variable contents inside. Run the code. The result of the box is Andrea's because it's the value in cell A1. Great. What if you want to change the setting for that property? Delete the code. The following macro changes the value in cell A1 by changing the cell's value property sub change value. Now we will change the value of cell A1 to number ten. Click on play to run the code and alt plus F 11 to return to Excel. Add-in for Excel executes this procedure cell A1 on Sheet one of the active workbook contains the value ten. Each object has its own set of properties, although some properties are common to many object. For example, many but not all objects have a visible property. Most objects also have a name property. Some object properties or read only, which means that your code can get the property's value but can't change it. For example, the application object has a property called version that returns the version number of the Excel that it's running. You can't change the version property index. It's read only. In addition to property's object have method. A method is an action you perform with an object. A method can change an object's properties or make the object do something. Delete the previous code. We will type a simple example that uses the Clear Contents method on the range object to erase the contents of five cells and the act of sheet only in the sub procedure clear range. Now type range B1 to be five dot Clear Contents. Let's run it. As you can see, the cells from B1 to b5 or empty. Some methods take one or more arguments. In argument is a value that further specifies the action to perform. You place the arguments for a method after the method separated by a space. Multiple arguments are separated by a comma. The following example activates sheet one in the act of Workbook and then copies the contents of cell A1 to cell D1 by using the range objects copy method, delete the previous code and start typing. Worksheets sheet one dot activate range A1 dot copy. I'll copy the contents of all cell A12, cell D1, and run the code. Nice. Because the collection is also an object. Collections have methods. The following macro uses the add method for the workbooks collection. As you may expect, this statement creates a new workbook. 24. Sub procedures: A sub procedure is a set of codes, a list of instructions. You can create a sub procedure either by recording a macro or you can manually create your own from the Visual Basic Editor. Here we have a blank Excel workbook, press Alt plus F11 to open the VBE Editor. From the Insert tab click module. It's module one. The sub procedure always starts with sub than a name, open and close brackets. We could have parameters inside, but for the moment, leave it empty. Press Enter and the editor places the n sub command. Type inside the sub procedure range. A1 dot value equal to five. The max characters of the sub procedure name R2, 55. The first character can't be a number, not space, and most of the punctuation characters are not allowed. Let's go back to Excel. From the Developer tab, click on macros. There is your sub procedure, which you can run it directly from here. Pretty straightforward right? Now returned to VBA editor. If you want to trigger the subroutine from the editor, you can do it from the Run tab. Run sub edit starts. You can use this little run icon also, or from F5 from the keyboard. Another way is from the immediate window, which is from the View tab. Now write the name of the subroutine, enter and the execution is started. Ideally, you don't want to call the subroutine from the VBE editor, it's better to do it from Excel. I like sometimes to create a button and auto assign a macro with this button. For example, from Form Controls Insert button here, and with right-click, assign the macro. The button triggers the subroutine. Thanks for watching. 25. Sub Procedure Examples: Let's make an example of developing a sub procedure and executed. By the way, executing a set procedure means the same thing as running or calling a set procedure. We will create a sub procedure that asks the user for a number and then displays that square root in a message box. Press all plus F11 to activate the VBE editor. Satellite, the workbook in the project window. Choose Insert Module to insert a new module. Enter the following in the module. You can execute a VBA sub in many ways. That's one reason you can do so many useful things with sub procedures. One way to execute this procedure is by doing it so directly from the VBA module in which you identified it. Activate the VBE and select the VBA module that contains the procedure. Move the cursor anywhere in the procedure's code and press F5, type 25, for example. And the procedure displays the square root of the number you entered. Most of the time you execute some procedures from Excel, not from the VB0. So activate Excel and choose developer and then macros. Select the macro and click run. Let's make another example by creating a new module. In this example, the main program calls for the sub procedure find hidden and executed. The end result is a message box that displays the hidden text. In this example, we execute a procedure from another procedure, run the code. Now how cool is that? Now we will assign the macro to a button on a worksheet. Add a button from the Form Controls group. Click the button tool in the Form Controls group. Dragging the worksheet to create the button. Select the macro you want to assign to the button and click OK. After you've made the assignment, click the button, executes the macro. That's it for today. I hope you learned some new ideas on how to create some procedures and how to execute them. Thank you for watching, and I will see you in the next lesson. 26. Function Procedures: We do use functions in Excel like some average or Max. And all these functions are built in. Sometimes you need to create a function that does a specific calculation. The bad news is that you can't record a function. So we have to create a function from scratch using Visual Basic Editor. Alt plus F11. Insert a module and type the keyword function. Now type Celsius degrees in parenthesis. Enter. Celsius is the name of the function and the parameter is in parenthesis F degrees. The nth function stops the routine. Now type Celsius equal f degrees minus 32 in parenthesis, multiplied by five divided by nine. The Celsius function calculates degrees Celsius from degrees Fahrenheit. You can create as many functions as you want. When it comes to calling a function, you can either call the function a sub procedure or you can call a function from the actual cell in Excel. Let's have a subroutine here. Sub R1 calculus, open, close brackets. And here let's type my Fahrenheit to Celsius 25. So take 25 degrees Celsius and converted into Fahrenheit. And then we put the answer into a message box on the screen. Let's try that from the immediate window, type the name of the subroutine, enter. And here is the conversion from 25 degrees Celsius to Fahrenheit. It works nicely. Now return to Excel. I will create random values from ten to 45 degrees Celsius using the RANDBETWEEN function. And I'll copy them from a1 to a ten. Okay, now we're ready to call our own functions equals Celsius. It appears in the list. Click on this cell and enter, replicate that down, and the function works great. The functions that we created will appear in the user-defined category from the functions dialog box. Here it is. Thanks for watching. 27. Function Procedure Examples: Functions, unlike sub procedures, can be executed in only two ways. One, by calling the function and other sub procedure or function procedure to, by using the function in a worksheet formula. Let's make a new module to enter the following code. This function calculates the square root of the number passed to it as an argument. Notice that the single line of code that makes it this function procedure performs a calculation. The result of the math is assigned to the variable square root. Square root is also the name of the function because you can't execute a function directly, you must either call it another procedure. Enter the following simple procedure in the same VBA module that contains the square root function. Let's name the sub called the function. The result variable calculates the square root of number 100. Finally, let's type a message box command. If we execute the sub Excel displays a message box that contains the value of the result variable, which is ten. Now it's time to call this VBA function procedure from a worksheet formula. Enter the following formula in any cell, equal square root 144, close parenthesis, and enter the cell displays 12, which is indeed the square root of 144. You can use this function any number of times in the worksheet. Like Excel's built-in functions, your custom functions appear in the Insert Function dialog box. Click the Insert Function Toolbar button and choose the user-defined category. Let's make a more complex example of defining a simple function procedure Add to and then using it in a VBA sub procedure, show result. Create a new module and I will paste the code to save time. All the Excel files and VBA code are inside your resources files. Run the code. The custom function add two has two arguments. The show results sub procedure uses this function procedure by passing two arguments to it in parenthesis. The show result procedure then displays a message box showing the value returned by the addtwo function. Creating custom Worksheet functions is a very useful topic and we will have more details in later lessons. 28. Variables: A variable is like an Excel cell. It can hold text, numbers or dates. With variables, you can store temporary values. All you have to do is create a variable and then use the variable name to put something in or read something from. You have lots of flexibility in naming your variables. Make the variable names as descriptive as possible. You assign a value to a variable using the equal sign operator. Vba enforces a few rules regarding variable names. You can use letters, numbers, and some punctuation characters, but the first character must be a letter. Vba doesn't distinguish between uppercase and lowercase letters. You can't use any spaces, periods, or mathematical operators in a variable name. You can't use the following characters in a variable name. Pound, sign, dollar sign, percentage, sine, and sine or exclamation marks. Variable names can be no longer than 255 characters, but nobody even gets close to that limit. Vba has a variety of built-in datatypes. The common variable types are as following. String is text, date is date, long is an integer, double is a decimal number. Boolean is true or false. Opening the VBA editor and insert a module before you use variables in a procedure, it's an excellent practice to declare your variables. That is, tell VBA each variables data type. Declaring your variables makes your macro run faster and use memory more efficiently. If you don't declare the data type for a variable you use in a VBA routine. Vba uses the default datatype variant to force yourself to declare all the variables you use, include these two words as the first statement in your VBA module. Option Explicit. This statement is present. You won't be able to run your code if it contains any undeclared variables. You need to use Option Explicit only once at the beginning of your module, before the declaration of any procedures in the module. The most common way to use a Dim statement, it's an old programming term that's short for dimension, which simply means you're setting aside memory for a particular variable. Let's take an example. Makes sure that the name that you'll give to a variable is meaningful. Sub variables, dim a total amount as Devil. Dm quantity as long. Dim, order date as date. I've created three variables, total amount, quantity, and order date. The lowest level of scope for a variable is add the procedure level. A procedure as either a sum or a function. Procedure. Variables declared with this scope can be used only in the procedure in which they are declared. When the procedure ends, the variable no longer exists and Excel frees up its memory. Sometimes you want a variable to be able to all procedures in a module. If so, just declare the variable using dam or private before the modules first sub or function statement. Outside any procedures. You do this at the beginning of your module and after the Option Explicit statement, type here, Dim start date as date. If you need to make a variable available to all the procedures in your VBA modules in a workbook declared the variable at the module level in the declaration section by using the public key word. Here's an example. Public interest rate, as long normally, when a procedure ends, all the procedures variables are reset. Static variables are special cases because they retain their value. Even when the procedure ends. You declare a static variable at the procedure level. A static variable may be useful if you need to track the number of times you execute a procedure. You declare static variables by using the static keyword. Create a new module, and let's make an example. Sub execute static executions as integer. Now declare message variable as string. I want the executions to be increased by one. Now type MSG equal number of executions in double quotes. Now add the variable executions to tell us the total number of executions. Finally, type the code to display the numbers in a message box. The code keeps track of the number of times the procedure was executed and displays the numbers in the message box, run the code. It all works. Great. Variables are extremely useful when you need to run a VBA code. Thanks for watching. 29. Constants: Until now, you've learned about variables and at a variable's value usually does change while your procedure is executing. Sometimes you need to refer to a value or string that never changes. In such a case, you need a constant whose value doesn't change. Let's write an example. Constant rate. Let's suppose the rate is 0.95. and the period is 12. Now, we need to assign a value to the declared constants named the constant month and as constant value type 12. Why constants are useful? If your procedure needs to refer to a specific value such as an interest rate, several times, it's better to declare the value is a constant and refer to its name rather than the value. This makes your code more readable and easier to change. And if the interest rate changes, you have to change only one statement rather than several. If you want to make constant available to all modules in the workbook, use the public keyword and declared the constant in the declaration section of any module, there are some predefined constants which you can use without the need to declare them yourself. I have attached a PDF file including a table list of all the predefined constants available for conditional complication. You can download it from your resources. The object browser can display a list of all Excel and VBA constants in the VBE. Press F2 to bring up the Object Browser. Keep in mind that there are some rules for naming a constant. One, you must use a letter as the first character, too. You can't use a space period exclamation mark or the characters at and dollar sign, pound sign In the Name. Three, name can't exceed 255 characters in length. Let us create a button constant underscore demo to demonstrate how to work with constants. Delete the previous code and start typing. First, let's declare three constants. We want to use. My integer, my date, and my day. Then display a message box with the values of three constants. Keep in mind that the CHR function is a built in function in Excel that is categorized as a string Text function. Use the CHR function to return the character identified to the specific number. If you run the code, the output will be displayed all the values the constants have, as shown in the following window. Thanks for watching. 30. Strings and Dates: Excel can work with both numbers and text. Text is often referred to as a string. There are two types of strings. Fixed length strings are declared with a specific number of characters. Variable length strings theoretically can hold as many as 2 billion characters when declaring a string variable with the Dim statement, you can specify the maximum length of, you know, it. This is a fixed length string, or you let VBA handle it dynamically. This is a variable length string. The following example declares the myString one variable as a string with a maximum length of 20 characters, using an asterix to specify the number of characters. Also myString to is declared as a string, but its length is unspecified. Another data type you might find useful is date. You can use a string variable to store dates, but then you can't perform date calculations. For example, you might need to calculate the number of days between two dates. This would be impossible if you use strings to hold your days. The following macro gets the year of a date to declare a Date, Use the Dim statement to initialize a Date, Use the date value function. Finally, I used the message box to display a dialog box with the year of the example Date variable. I named the sub procedure year mod. I run the code. Date variables displayed dates according to your system's short date format. And they display times according to your system's time format, either 12 or 24 hour formatting. When writing VBA code, however, you must use one of the US date formats, such as month, day, year. Let's make another example with date add function. The date add function has three arguments, interval number and date. Interval is the time date interval that you wish to add. Number is the number of intervals that you wish to add. And the date is the date to which the interval should be added. In your resources, you'll find a PDF file with the syntax of date, add the parameters and many examples. Fill in D for the first argument to add days. Fill in three for the second argument to add three days. The third argument represents the date to which the number of days will be added. Run the code and see the result. Thank you for watching. 31. Range and Cells properties: So far we've seen the range property. As we've said before, range is used to identify the cells you want to use. You can use a single cell or a cell range. Sell property is used in a very similar way to range, but there are two main differences. The cell property can only allow US one cell at a time. The cell's property takes a row and a column as arguments instead of arrange. You may be wondering when you should use cells and when you should use range. Using range is useful for accessing the same cells each time the macro runs. For example, if you were using a macro to calculate a total and write it to a cell, a ten every time range would be suitable for this task. Using the cell's property is useful if you're accessing a cell based on the number that may vary, it is easier to explain this with an example open VBA editor and look carefully at the following code in module one. If we run this macro, it will type Hello World text in cells b1 and b2, then returned to excel and run the macro. Okay, now we open again the editor. Sometimes we don't know in advance where we're going to write a value. Cell statements, take numbers, so you can easily manipulate them to select the correct cell. If you want to write a text in column 30, would it be easier to convert column 32s, column letter, which is a d By the way, or uses sell property. In conclusion, we could say that you could use range when the cell will be the same every time the macro runs. Use cells, when the cell may vary. 32. Assignment Statements: An assignment statement is VBA statement that assigns the result of an expression to a variable or an object. Let's make some assignment statement examples to fully understand it. The expressions are to the right of the equal sign. Vba uses the equal sign as its assignment operator. But here we don't use an equal sign as mathematics symbol for equality. Let's look at the second example. The assignment statement would execute. It increases the value of x by one. So if X is for executing the statement makes X equal to five. Just remember that in an assignment uses the equal sign is an operator, not a symbol of equality. Besides the equal sign operator, VBA provides several operators like addition, division, multiplication, et cetera. Take a look at the following table. Now enlight to discuss the medulla arithmetic. When you're writing an Excel formula, you do modulo arithmetic by using the MOD function. For example, the following formula. Equal MOD 18 comma five returns three. The remainder when you divide 18 by five, if you wanted to write it in VBA, the mod operator is used like this. Often expressions use functions. Vba is built in functions, Excel worksheet function or functions that you develop with VBA. Vba has some logical operators like the following. The precedence order for operators and VBA is exactly the same as an Excel formulas. Exponentiation has the highest precedence. Multiplication and division come next, followed by addition and subtraction. You can use parenthesis to change the natural precedence order. Let's make an example. X equals two for y equals to two, Z equal x plus ten multiplied by y. When the preceding code is executed, what's the value of z? If you answered 24, you're right. The multiplication operation ten, why is performed first? And net result is added to x. Thanks for watching. 33. Arrays: An array is a group of variables that share a name. It is used to store lists of data of the same type. You refer to a specific variable in the array by using the array name and an index number in the parenthesis. For example, you can define an array of 50 string variables to hold the names of states in the United States. If you name the array states, you can refer to the first element of the array as states one, the second element as states two, and so on. Before you can use array, you must declare it. Unlike with normal variables, VBA is very strict about this rule. You can declare an array with the Dim statement just as you declare a regular variable. However, you also need to specify the number of elements in the array. You do this by specifying the index number, the keyword to, and the last index number, all inside parenthesis. The following example shows how to declare an array of integers. If you write the following statement, dim my array 50 as integer. Vba assumes that the lower index is 0. If you want VBA to assume that one rather than 0 is the lower index for your arrays, include the following statement in the declaration section at the top of your module. Option based one. The array is created in the previous examples are all one-dimensional arrays. Think of one-dimensional arrays as a single line of values. Arrays you create an VBA can have as many as 60 dimensions, although you rarely need more than two or three dimensions in an array. The following example declares a 25 integer array with two-dimensions. Look at this five-by-five table. Think of this array is occupying a five-by-five matrix. Now to refer to a specific element in this array, you need to specify two index numbers, the row and the column in the matrix. My array, open parenthesis, two comma three, close parenthesis equal 12. You could also create dynamic arrays. A dynamic array doesn't have the preset number of elements. Declare a dynamic array with an empty set of parenthesis. Dim my array, open, closed parenthesis as an integer. Before you can use this array, you must use the read him statement to tell VBA how many elements the array has. Read them. My array, open parenthesis, one to num elements, close parenthesis. When you read dimension in array by using read him, you wipe out any values currently stored in the array elements. The topic of arrays comes up again when you explore the concept of looping. Thanks for watching. 34. Range Property: Much of the work that you will do in VBA involves cells and ranges in worksheets. One of the most important objects is the range object. A range object represents a range contained in a worksheet object. Range objects, like all other objects, have properties and methods. A range object can be as small as a single cell, for example, be ten or as large as every one of the billions in a worksheet. The range property returns a range object. And this property has two syntaxes. Object dot range cell one object dot range cell, one cell to. The range property applies to two types of objects, a worksheet object or a range object. When you refer to a range object, the address is always surrounded by double quotes, like this, range A1 to be five. If the range consists of one cell, you still need the quotes. If the range happens to have a name created with formulas, define names, you can refer to the range by its name, which is also in quotes. Let's type range web visitors. Unless you tell Excel otherwise, by qualifying the range reference, it assumes that you're referring to a range on the Active worksheet. The example that follows enters the same value in the range of ten cells on an Active Sheet. Let's delete the previous code and type. Active Sheet dot range A1 to a ten dot value equal three. I will run the code now and here is my result. Makes sense, right? You can refer to a range outside the active sheet by qualifying the range reference with the worksheet name from the active workbook. So realize to replace this with worksheets sheet one. If you need to refer to arrange in a different workbook, that is any workbook other than the active workbook, you can use a statement like this. Nice. As we said before, the range property recognizes the Define Names in the workbook. Therefore, if a cell is named input, you can use the following statement to enter a value into that named cell, like this example code. A range object can consist of one or more entire rows or columns. You can refer to an entire row, in this case row five. By using syntax like this. You can refer to an entire column, the second column in this example, like this. In Excel, you select non-contiguous ranges by holding down control while selecting various ranges with your mouse. Vba also allows you to work with non-continuous rages like the following example. The following statement enters the value five in five cells that make up a non-continuous range. Note that the commas are within the quote marks. So far all the examples of use the range property on a worksheet object. As mentioned, you can also use the range property or a range object. For example, the following line of code treats the range objects as if it were the upper left cell in the worksheet. And then it enters a value of five in the cell, that would be b2. In other words, the reference returns it's relative to the upper left corner of the range object. Therefore, the statement that follows enters a value of five into the cell directly to the right and one row below the active cell. Fortunately, you can access the cell relative to arrange in a much clearer way the offset property. We will discuss this property after the next lesson. 35. Cells Property: Another way to reference a range is to use the cell's property. You can use the cell's property like the range property on the worksheet objects and range objects. And it's a handy way to refer to arrange. The cell's property has three syntaxes. As you can see in this text box. The cells property takes two arguments are row number and column number. Both of these arguments are numbers, even though you usually refer to columns by using letters. For example, the following expression refers to cell D2 on Sheet one. Here's an example that enters the value seven in the cell C3, that is row three. Column three in the Active worksheet named the sub procedure sells example. Now type Active Sheet, DOD cells. Three comma three equal seven. Run the code and here is our result. You can also use the cell's property on a range object. When you do so, the range object can be returned by using the cell's property as it is relative to the upper-left cells of the reference range. Confusing. Probably. An example may help clear up any confusion. The following instruction enters the value ten in the active cell. Remember, in this case, the active cell is treated as if it was cell A1 in the worksheet. Before running the code returned to excel and click on A1. Now run the code and see the results. The value of A1 is ten. Pretty straightforward, right? To enter a value of five and the cell directly below the active cell, you will change the code as follows. Change the row from one to two, and change the value from ten to five. Run the code again. Think of proceeding examples as it follows through with its said. Start with the active cell and consider it this cell as cell A1 plus five in the cell in the second row and the first column. You can also use the cell's property to refer to a multi-cell range. The following example demonstrates this thin tags you can use. This expression refers to a 20 Cell Range that extends from cell A1, row one, column one to cell D5, row five, column four. The third syntax for the cell's property simply returns all cells on the reference worksheet. Unlike the other two syntaxes in this one, the return data isn't a single cell. This example uses the Clear Contents method on the range returned by using the cell's property on the act of Worksheet. Active Sheet dot cells dot Clear Contents. The result is that if I run this code, the content of every shell on the worksheet, it's cleared. Finally, I want to make a more tricky example that combines cells with a range object. Start typing. Range A1, 2F, ten dot cells, open parenthesis, four, close parenthesis equals to 50. Run the code and see the results in Excel. In this case, the cell return is relative to the range object referenced. For example, if the range object is A1, F 1060 cells, the cells property can argue one to 60 and can return one of the cells in the range object. In the following example, a value of 50 is entered into cell D1, because D1 is the fourth cell counting from the top to the right and then down in the referenced ranged. Thank you for watching. I'll see you in the next lesson. 36. Offset property: The offset property, like the range and cell's properties also returns a range object. This property, which operates on a range object and returns another range object allows you to refer to a cell. This is a particular number of rows and columns away from another cell. Like the cell's property, the offset property takes two arguments that correspond to the relative position of the upper left cell of the specified range object. The first argument represents the number of rows to offset. The second represents the number of columns to offset. The argument can be positive down or to the right, negative up or to the left or 0. The example that follows enters a value of five into the cell directly below the active cell. So let's start typing. Sub offset examples. Active cell dot offset, one comma 0 dot value equal five. Run the code. The active cell was A1, so it enters the value of five below A1. The next example enters a value of ten in the cell directly above the active cell. Let's change the VBA code. Run the code. If the active cell is in row one, the offset properties in the preceding example generates an error because it can't return a range object that doesn't exist. And as you may expect, you can use 0 as one or both of the arguments for offset. The following expression refers to cell A1. Here's an attachment that insert the time of day into the cell to the right of the active cell. So let's change the role offset and the column offset and that equals to time. Run the code, increase the column size. And here it is the current time. Ok. Now, when you record a macro using the relative reference mode, Excel uses the offset property to reference cells relative to the starting position, that is the active cell. When macro recording begins. For example, I will clear the contents. Click on a1. Use relative reference. Start recording. Enter ten into cell D1, 20 into D2, and 30 into d3. Stopped the macro recorder and returned to Vb editor. Let's examine the generated code. The macro recorder uses the formula R1C1 property. Normally, you want to use the value property to enter a value into a cell. However, using formula R1, C1, or even formula produces the same result. Also, the generated code references cell A1, cell that wasn't even involved in the macro. This notation is a cork in the macro recording procedure that makes the code more complex the necessary. You can delete the references to range A1 and the macro still works perfectly. So keep in mind that the macro procedure doesn't make the code more efficient. Thanks for watching. 37. Useful Range Object Properties: A range object has dozens of properties. In this lesson, we're going to learn some of the most commonly used range properties like value property, Text, property, column and row properties. So let's dive in. Here I have a workbook with some random numbers in these cells. Open VBE, create a module and a sub procedure. Giving the name examples. The value property represents the value contained in a cell. It's a read write property, so your VBA code can either read or change the value. Let's start typing an example that displays a message box showing the value in cell A1 on Sheet one. Message box worksheets. Sheet one dot range A1 dot value. Renamed the Module one to value. Run the code. And here is my message box with the number one inside, because A1 has the value of one, as you see inside, you can read the value property it only for a single cell range object. For example, the following statement generates an error. But you can change the value property from a range of any size. Let's make an example. Change the range from A1 to D3 and add 50 as a value. If you run the code, you will enter the number 50 into each cell in that range. In a text property returns a string that represents a text that is displayed in a cell. The formatted value. Create a new module in name ID text. Create a set procedure and name it examples, right? The following code. Message box worksheets sheet one dot range 8-10 dot txt. Run the code. As you can see, it displays five with a dollar sign. But if I replace the text property with the value property, it will display the number without the formatting. Let's test it. Now how cool is that? The count property returns the numbers of cells and arrange. It counts all cells, not just the non-blank cells. Count is a read-only property. Create a new module, name it count, and let's make an example. This statement accesses arranged.count property and displays the result 20 in a message box. The column property returns the column number of a single cell range. The rope property returns the row number of a single cell range. Both are read-only properties. Create a new module and name it column underscore row. For example, the following statement displays for because cell D5 is in the fourth column. The next expression displays five because its cell D5 is in the fifth row. If the range object consists of more than one cell, the column property returns the column number of the first range in the column. And the row property returns the row number of the first row in the range. Don't confuse the column and row properties with the columns and rows properties. The column and row properties return a single value, the column and rows properties, on the other hand, return a range object address. A read-only property displays the cell address. A range object as an absolute reference, a dollar sign before the column letter and before the row number. The following statement displays the range from A1 to D4. Run the code. Great. The haas formula property, which is a read only, returns true if the single-cell range contains a formula, it returns false if the cell contains something other than formula or is empty. If the range consists of more than one cell, VBA returns true only if all cells and arrange contain a formula. Or false if all cells in the range don't have a formula, the property returns null if the range contains a mixture of formulas and non formulas. For example, if you write the following statement and run the code, you will notice that the message box returns true. The formula property represents the formula in a cell. This is a read write property, so you can access it to either view the formula in a cell or insert a formula into a cell. For example, the following statement enters a sum formula into cell a twelv range 8-12 dot formula equals quotes equals sum a1 to a2, 11. Press the play button and Excel adds the values with 249 as a result of the sum. To display the word sales after the result of the sum formula, we will make the following change to the statement. It's a tricky statement because to make this formula acceptable, you need to replace every quotation mark in the formula with two quotation marks. Otherwise, VBA get confused and claims that there's a syntax error. Run the code. And Excel added the word sales besides the number. Thanks for watching. 38. Useful Range Object Methods: Hi everyone. In this lesson we're going to learn some of the commonly used range object methods, like the select method, the copy and paste methods, the clear method, and the delete method. So let's dive in. Use the Select method to select a range of cells. The following statement, select a range in the Active worksheet. Okay, Let's create a sub procedure and name it examples. Now let's type range A1 to d ten. Don't select. I'll run the code to see that select highlighted cells from A1 to D1. Before selecting a range, it's often a good idea to use one additional statement to ensure that the correct worksheet is active. For example, if sheet one contains the range that you want to select, uses the following statements to select the range. Sheets. Sheet one dot activate. If you use the go-to method of the application object to select a range, you can forget about selecting the correct worksheet first. This statement activate sheet one and then select the range. The go-to method is the VBA equivalent of pressing F5 and Excel, which displays the go-to dialog box, run the code and the result is the same. You can perform copy and paste operations in VBA by using the copy and paste methods. Paste method applies to the worksheet object. You copy, arrange, and paste it to our worksheet. There are two ways to copy and paste a range of cells. Let's see the first method. Start typing. Range A1 to A5 dot select selection dot copy, range k1 dot select Active Sheet dot paste. The code copies range A1 to A5 and paste it in the same worksheet beginning itself k1. However, you don't have to select a range before doing something with it. Let's see the second method that accomplishes the same task as the preceding examples by using a single statement. The clear method deletes the contents of a range plus all the cell formatting. Let's see an example that clears everything in column a. If I run the code, all the contents from column a will be deleted. You should be aware of two related methods. Declare contents method deletes the contents of the range, but leaves the formatting intact. The clear formats method deletes the formatting and the range, but not the cell contents. Clearing a range differs from deleting arrange. When you delete arrange, Excel shifts the remaining cells around to fill up the range you deleted. The following example uses the delete method to delete row three. Thanks for watching. 39. With End With Constructs: Okay, now I want to talk about with n with statement. We've opened a workbook called width and statement. Press Alt F11. To open the editor. Looks at the code in module one. Notice that I have used a variation of this workbook dot worksheets sheet one in every line of code, returned to excel and run the macro to understand what it does. It selects a range of cells from A1 to be five. Writes the text with statement and makes the text bold and red. Old plus F11. Again, imagine that you have to write many lines of code for this workbook. Writing the above code for each line is boring and a time waster. A great statement that you can use and save you time is the width keyboard. Using with means you only need to write the code. This workbook dot worksheets sheet one. Once. Let's rewrite the above code using it. Delete first this line code. Then add the statement end with at the end of the code. Great. Now run the macro again. But first I will delete the cells from previous macro. It works great. You can see now how easier it is to write lines of code without repeating the same variation of this workbook dot worksheets. She won all the time. 40. How to read from a cell: You have already learned how to write a value to a cell, but what if you want to read from one cell to another? Let's look at the following code. We've already displayed the message Hello World in the cell A1. Now we want to display also the same message into cell D1. So we want to write a line code setting d1 value to the value of a1. The answer is in the following code. Dot range D1 equal dot range A1. Always the cell to the right of the equal sign is read from the cell or cells to the left of the equal sign is written to return to Excel and run the code. As you can see, it worked fine. I think it's an easy and useful technique. Make your example codes to fully understand it. Thanks for watching. 41. Dim and Set statements: From the previous lecture, we learned how to use width for a specific workbook. Using width is fine when we have one workbook or worksheet. But when using multiple sheets, we need to use something else. This is where dim and set command. In this workbook, dim set dot XLS m. We have two worksheets, cars and pharmacy. Both of them have a data table. Open the VBA editor with all plus F11. Here are the two sheets and module one. Double-click to see the code. As you can see, we have Dim statement, set statement with statement and some formatting in cell range A1 to see ate, like making text bold, putting red color, and also filing cell range with gray color. Returned to excel quickly and run the macro. Great. Now open again the editor. Dim is short for the word dimension, and it allows you to declare a variable names and their type. Dim is often found at the beginning of macro codes and has the following format. In the following example, this code line declares worksheet objects. Also using dim in the above example is simply giving the worksheet in alternative name. So it makes it easier to see what the sheet does. In this line, I declare an object variable called car sales and assign this object variable to a worksheet. Now let's see the set keyword. The set keyword is used to create new objects, creating a new range. For example, the set keyword comes in handy when you want to simplify long lines of code. It also speeds up your code. Would VBA executes it? Don't worry about set too much. Just bear in mind that you can create a new object variables with a set keyword. You'll see the benefits of dim and set statements as you will be more familiar with Excel VBA. You now understand that if you have many worksheets, demand set are extremely important and time-saving. 42. Using IF: In this workbook, we already recorded a macro that makes our text bold. Press Alt plus F11 to see the code, check that the VBA project has the same name as the workbook and that we double-click on an our module one. As I already said, every time I run the macro, the text becomes bold. In this lecture, we are going to learn how to turn this macro on or off typing some simple code. If the macro see that the text is already bold, it will make it normal or the opposite. In other words, if a certain condition already exists, we'll take one or another action to figure out the code which turns off the existing macro, we'll create a new macro that simply turns off bold. The first step is to highlight themselves to run the existing macro. Select the cell range from A1 to see ten from the View tab and run the macro. Good. Now click this button in the lower left corner. Type macro off as a macro name. Will store this macro in this workbook and click OK. The macro is recording. Click the bold button to remove it. Stop recording. Press again, all plus F11. The new macro code is putting in a new module, which is the latest module with the name module two. Double-click. And here is the code. This is the code line that we're interested in. What are we going to do now? We're going to copy this line to module one, which is our previous macro. I prefer to tile my windows vertically. Okay, now put this line here below that. Rename also the macro to on, off. Let's try to read the code now. The first-line applies bold in the text. The other line turns off the previous choice. If we run this macro, Excel will turn on and off the bold feature so quickly that we will not see the changes. Let's test it. Old F11, highlight the cells and run the macro. Here you are going to use the ifStatement returned to editor. And let's get started. First of all, type in front of the first line. If using lowercase, always use lowercase when typing code. If the bolt is in effect is on, if you prefer, than we would like to remove it to turn it off. So type there the Venn statement. But what if bold is already off? What are we going to do then there is a solution for that. Below these lines will add the statement else. Then we'll copy these first lines and paste them exactly there. So what are we saying in simple words? If these lines are on, then turn them off. But if it's not on the internet on, we're ready to try it. Press all plus F11 to return to Excel. Highlight these random cells and run the macro on off. Look at this error. We forgot to close the if statement with an end if before the end sub. Let's do it now by adding the end If statement and try again. It applies to bold. Run again the macro. And now it removes it. You understood how important is the if, then else statement and the freedom that gives us when we want to create trickier macros. 43. Using Relative or Absolute Reference: In this workbook called London, whether there was a worksheet with dates and temperatures of London City. This list is for January. Every single month we get a new list with new temperatures for each day and with the same format. This is not an ideal format. With the help of macros, we're going to copy these data and transport them into columns. So this is a repetitive task that we have to do every single month. In this lecture, you're going to understand the difference between the two modes of recording, absolute reference and relative reference. Keep in mind that Excel's default recording mode is an absolute reference. We're going to create two small macros, one with absolute and one with relative reference. Then we will compare and see the difference between these two modes of recording. First of all, highlight cell D4. Go to the View tab and click on the macros button. The use relative reference option is not highlighted. So let's record a macro with absolute reference. Name the macro absolute store in this workbook. And click OK. Now put B1 and stop recording. Press Alt plus F11 to open the module to see the code. It goes to cell B1. In other words, no matter where your cursor is in your workbook, when you run the selected macro, Excel will jump to cell B1. Highlight again cell D4. Now from the View tab, click the macros button, select the use relative reference option and start recording. Named the macro relative. Leave the other as it is, and click OK. Click on cell B1 like before, and stop recording. Press Alt plus F11 and open the same module to see the new code. As you can see, this code is different from the previous one. Lets read the code to understand it better. From d4 cell, we're moving up three rows and moving to the left two columns, and we are selecting a single cell. The range A1 means to select a single cell. So Excel uses the offset property of the active cell. This property tells the cursor to move a certain number of cells up or down, and a certain number of cells left or right. I hope this simple example has given you a firm grasp of macro recording with both absolute and relative reference. 44. Using FOR…NEXT Statements with Macros: If you need to count something or you need to loop through a series of predetermined elements with a given set. Then the four and next statements are a must. There are called for next loops. Loops are extremely important because they allow you to repeat a set of instructions until a predetermined condition changes or a criterion is met. So in this lecture, we'll learn how to use four next statements to create and perform repetitive code. In this particular worksheet, we have a list of dates and temperatures for January. We want the first cell. It would the date to be bold and the next two to be blue as the text color. Then the next date, bold, and the next two temperatures, blue color, et cetera. This is a repetitive task and it will be repeated for every month. The starting cell will always be a one. So we will use a macro with absolute reference only to go to A1 cell. After that, we want the relative reference to beyond. Let's get started. Go to the View tab and from the macro button makes sure that the relative reference is not highlighted. Ok, now start recording type bold blue as a macro name and stored in this workbook. Okay, and click cell A1. Now it's time to turn on the relative reference. Go again to the View tab and click here to activate the relative reference. Highlight the first cell and click Bold. Then the next two and select blue color. Then the next bold. And finally, the next two with blue color. One more time and stop recording. Press Alt plus F11 to open the VBA editor. To look at the code, search the module, and double-click on it. Notice that the cold begins with the selection of the A1 cell. Then we select the first cell and make it bold. As you can see from this line code. After that, we give instructions to excel to go down with the help of the offset function and select the next cell range. The same code is repeated over and over again, except for the first three cells. I mean, the first date. Now it's time to use the power of the four next loop. First of all, delete these lines. They are the same with the above lines and we don't need them. Click here and start by typing for. We are starting here because we want to leave the first three cells. Now type count, which is the variable we want to use. The name of the variables is not so important. Put equal sign and one, Type two. And now we have to think about how many times this task will be repeating. Go back to Excel worksheet and highlight all of the dates and temperatures. January has 31 days. We want to repeat the task 30 times except for the first date. Returned to Visual Basic Editor and put 30 here. Now before End Sub statement, and after the last repetition, we take the next count. So this action will be repeating 30 times. Let's see it in action. Resize the Excel, the editor windows so that we can see both undo to return these cells to their default formatting. It's better to run the macro step-by-step. How can we do that? Click on the debug menu and select step into option or F8 keyboard shortcut if you prefer. Now we are ready to press F8 and run the code line by line. Great. F8 now highlights the A1 cell, F8 and makes the text bold. Then F8 and the text becomes blue, F8. And the four next loop starts. Keep pressing F8 and look at the screen on your right. This procedure will be repeating 30 times. Okay? This time we will run the macro without any stopping. Undo, close the editor and maximize the Excel window. Now go to the View tab and run the macro, Excel random macro in less than a second. So the next loop is extremely useful when we have to repeat a set of instructions until a pre-determined condition changes or a criterion is met. In the next lecture, we will learn about the DUE until loop. 45. Using DO…UNTIL Statements with Macros: In the previous lecture, we managed to create a macro using the four next loop and helping us to repeat a set of instructions until a predetermined condition changes. This condition was the counter. We had to repeat the specific task 30 times. Let's remember the code of the macro. From the View tab. Click the macro button and select the macro. Okay, at it mail. We said excel to repeat this task three times. But we always know exactly how many times a task is going to be repeating. Of course not. Suppose we had thousands of cells and entries or many months, we want to create a macro making these cells bold and these with blue color. We will use the DUE until loop. First of all, delete these lines, then start typing. Due until is empty. Open parenthesis, active cell, closed parenthesis. And then go to the end and type loop. These statements simply mean do all these actions over and over again until the active cell is blank or empty if you prefer. So an Excel finds an empty cell macro will stop running, returned to the Excel window and test the macro view tab macros button and run it. Great. It worked just fine. It repeated the task until it found an empty cell. I prefer the DUE until loop rather than the fore next, because it gives me more flexibility and freedom. 46. Add a confirmation dialog box with VBA IF statement: Hello Students. In this lesson, we're going to create two macros, one for deleting a current row and one for removing a current column. Then with the help of VBA and if statement, we will create a confirmation box. The confirmation box cannot be done with recording because you cannot record it. So a will be added after the macros. The first steps are to create macros. Let's put my name to a cell. Let's start by recording a macro that deletes that current row. Click on the Developer tab. Name it, delete row. And I'm going to store it in my personal macro workbook. So I can use it anytime I want. Add a description and okay. Make sure that the relative references on select the current row. Right-click and deleted, and then stop recording. Let's test it. It works fine. The second macro, we'll delete the current column. Let's put my name to a cell. Click on the Developer tab. Name it, delete column, added description. And I'm going to store it in my personal macro workbook again, the relative references on select the current column, right-click and deleted. And then stop recording. Let's test it. Great. Let's add those two macros to the ribbon. File. Options. Customize Ribbon commands from Macros. And here are the macros we created. Now I will need a new tablet, my ribbon, rename it to Macros. And drag these two macros right there to this group. If you want, you can rename the group. Also. I want to rename the macros. So click it. Renamed to Delete current row. Select the symbol and do the same with the second macro. Macro buttons are here. I will open a worksheet with data to see if these macros work. Clicking that row, deleted, and the rho is gone. And the same with the columns. Nice. We have two handy macros, but dangerous to. That's why they require an RU shirt step. How do we add a confirmation dialog box? First, we need to edit the macros Developer tab macros. Select this and edit. As you can see, we can edit the macro because it's on our personal workbook and it's hidden. Cancel this view tab on hide. Personal. Ok. Then I can go to the Developer macros and edit. Here is the code from the delete column. Here is the description with green color. And here are the two lines with code. One that's to the column and one that deletes it. What I need is to put an if statement around these two lines of code. Ok, let's start typing. If message box brackets, the VBA shows us the syntax as you can see. So first I need a prompt. Double-quotes. Are you sure you want to delete the current column? Close double quotes comma. And now I want buttons for my message box a, yes or no. Let's search in the list. And here it is. Vb. Yes, no. Now we need a title for my box. I will name it confirmation box inside double quotes. Of course. I don't want another argument. So I will close my parenthesis and put equal VBS and then enter. After these two lines of code, I will type. And if the blue color coding tells me that my code has no error, there is no need to write the same code for the Delete Row macro. Only need to do is copy the line here, paste it there. Change the message, the column with row, and then add the statement. And if they're close the VBA editor, save my Excel file. For example, press that delete button, and here's our message box. Are you sure you want to delete the current row? Here's my title and here's my yes, no button. Click yes. And the current row deleted. Great. I hope you found this lesson helpful. Please practice these two useful functions, if and end if. Before closing, I want you to leave me a comment telling me the difference between the use of relative reference and the use of absolute reference. Thank you, I will see you in the next video tutorial. Bye. 47. Prompting a user to select a worksheet and run a Macro: In the previous lecture, we created a macro that makes the text bold and the letters blue for London worksheet. If we had many worksheets, let's say lots of cities, then we had to manually select the worksheets that the macro is going to run. In this lecture, we're going to add some statements to our macro, more interactive. Specifically, we're going to add a prompt statement that user will type the worksheet he wants to run the macro. Let's get started. Press alt F 11 to open the VBA editor. First of all, we need a variable name. So we're going to this line and type sheet name. Then equal input box, open parenthesis, and double-quotes. What we'll type here is what the user will see. So type in, enter the worksheet name, double quote again, and close parenthesis. Enter. The next statement will store the name of the worksheet type in worksheets, left parenthesis, and the name of the variable sheet name, close parenthesis, period, activate and enter. This statement simply means activate that worksheet and then run the macro. To test the macro, press Alt plus F11. Run the macro and look at the dialogue box type in the name of a worksheet. For example, NY, and look at the results. Great. So we made our macro more interactive by simply adding two statements. 48. Combining Macro Recording and VBA Statements: Sometimes we need to create more complex macros. We will make an example in the workbook combined macros and London worksheet. We would like to copy these cells and paste them as columns, transpose them to columns. In other words, to make complex macros, it's better to record some of the code. We need to check it out a little bit and then add to it. So we will do it step by step. Starting from cell A1, we will start recording as an absolute reference first, the copy and paste of the cells will be the relative reference recording mode. On. First of all, we will record an empty macro to create a module. Click that button. Press OK and stop recording. The VBA editor. As you can see, there is a module there, double-click it and there is no actual coded it. Delete these lines in arrange the Excel and editor windows so you can see all of them at once. Make sure that absolute references on and start recording. Give the macro a name like transpose. And as keyboard shortcut, press control plus Shift plus T. Store the macro in this workbook and press OK. highlight the B2 cell and see the code. Now it's time to turn the relative reference on. From the View tab, click the down arrow and relative reference. Copy the cells. Go to D2. Right-click, Paste, Special click transpose. And okay. Look at the VBA code and you can notice the copy and the paste special and the transpose section. The next step is to do exactly the same procedure for the next three Cells. Copy, right-click, paste, special click transpose, and okay, one more time and we're ready. Stop the macro. If you look at the code carefully, you'll see a pattern of repetition with almost the same code lines, except the first date, of course, which was part of the selection of three cells and the first three cells that are highlighted. Lets read the code. We highlight and copy the three cells. We select DSL, then Paste Special transpose and OK, and so on. Keep in mind that range. A123 does not mean the specific cells. It means we highlight three cells. Now that we understand the pattern, we need to get rid of the repetition code and will keep only one set of this. So highlight these and delete. We wanna do these actions over and over again. The best choice is the DUE until loop. Okay, start typing. Due until it's empty. Open parenthesis, active cell, close parenthesis. This line means do these actions until the active cell is empty at the loop statement before the n sub and you are ready. The final step is to run this macro. You can do this with two different methods. The one is to run the macro immediately and the other is to go step by step. Let's follow the second method. Arranged the two windows from the debug select step into or F8. Pressing F eight, you'll see all the steps one by one. F eight again, and it seems to work great. As a final note, you can delete the blank cells, select these columns and from the Home ribbon, press find and select button from goto Special option, choose blanks and ok. Now right-click Delete and shift cells up. The basic idea from this lecture is that when we have complex macros, we need to break the macro into small pieces tested over and over to make sure that it finally works fine. 49. How to join 2 different Macros: Let's suppose that we have in a workbook two or more macros that make specific tasks in a logical order. In this workbook, for example, we have the data with dates and temperatures. We show from previous lectures that we first run a macro that makes these cells bold. These cells with a blue color, et cetera. After that, we run a second macro that copies these cells, paste them as columns, transposed them to columns, in other words, so we have two macros that we want to put together. Press Alt plus F11 to open the VBA editor. The two macros and we created are here, module one and module two. The idea is to put them together with cut paste or copy paste. Let's get started. Go to module to highlight the code here. Cut with this tool or with control plus X. Go to module one and paste it after n sub statement. Now to merge these two Macros, I have to select these two lines, delete and we've joined the macros. Of course, to make sure that the merging works, you have to try the macro returned to excel with all plus F11 and run the macro from the View tab. So what worked fine? I think joining different smaller macros to a big one is a great technique that can save you time. 50. Tips for running your Macros fast: Macros are great for automating the task that you frequently use. However, running a macro and a worksheet with thousands of data could be a pain in the ass. Sometimes it takes a long time for the macro to complete the task, and you'll see all the activity on your screen. For example, run the macro who is stored in this workbook and look at the result. To avoid this, you can add a line to your macro that is going to inhibit all this activity. Let's see how we can do that and do the formatting. And press Alt plus F11 to open the editor. Now find the module that contains your macro and double-click on it. From the beginning of the code, we're going to add a line. So let's start typing here, application period. And from that large list, we're going to select Screen updating. Press the Tab key and type equal false. This line code means that it will not show us the screen updates when we start to run the macro, okay, now we tried the macro returned to excel and run the macro. Look, it ran much faster than the previous one. So the line we added is a helpful technique when we have a lot of data in our worksheet. 51. Transfer Data From one Excel Worksheet to another: Hi everyone, this is Andreas. And in this video tutorial, we are going to learn how to transfer data from one worksheet to another in Excel automatically. So let's get started. In this workbook called transfer data. I have two sheets. She won and she too, although we are working in Excel 2016, the techniques that I'll show you also work in every Excel version. In sheet one, We have two headers, name and phone. When a user inserts data, we want with the help of a button to transfer the data into another sheet, which is xi two in our example, notice that in xi2, I've inserted the same headers, name and phone. Click on the Developer tab and press the insert controls button. Now from active X controls, choose the command button. Click and draw it somewhere, they're great. Now right-click and go to properties. The properties window appears on your left. Find capturing property, and write transfer. If you want, you can change the format of the button and the text from these properties. Close the window and double-click on the button. The VBA window appears with the command button window and the click event. We will start by defining and writing the appropriate values. The first variable is customer name. So let's type in dim customer name as a string, type comma, and then type customer phone as string. Also. The first step is to select Sheet one. Let's start typing. Worksheets, parenthesis, double-quotes, sheet one, which is the name of our worksheet. Close quotes and parenthesis, type dot, then select, press enter. Now we have to define the range of our data returned to Excel workbook. And as you can see, the range that we're interested in is a2, to be to go back to VBA window and type customer name equals range, parenthesis, double-quotes and a2, close the quotes and the parenthesis, enter n-type. Now, customer phone. Equals range B2. After I select the range and cheat one, I have to go now to sheet two to transfer data. Type in worksheets G2, which is the same name as the second worksheet, dot select. Now that I've selected xi2, I need to select the exact cell for customer name. So I type in worksheets, xi two dot range, a1 dot select. There is a problem that I need to solve now. I want always define the next blank row and then paste the name and phone. So we'll not overwrite the previous name and phone. To solve that problem, we need to use a loop with the help of the if statement. Lets get started. If worksheets xi two dot range A1 dot offset, one comma 0 is non-equal. Double quotes two times, then enter to go to the next line. Worksheets. G2 dot range, A1 dot N X l down, enter and type. And if offset 1 means one row below the reference, the if statement checks if the row is not empty, go to the last cell, and after that go to the next empty cell using active cells statements. So let's type in active cell dot offset 1.00 dot select Enter and type in the next row. Active cell dot value equals Customer Name Enter. We will do exactly the same for the customer phone. If you want, you can copy and paste two statements and make the appropriate changes. Great. Now we have effectively transfer the data from C1 to C2. The final step is to go back to sheet one so we can enter the other data type worksheets. She'd one. Select. Declare the data in sheet one, we will write worksheet sheet one, dot range, a2, b2 dot contents. Oops, let's fix the errors. Are VBA code is ready and we have to try it to make sure that it works. So returned to xi-1, disabled design mode, type a name and phone and press the button. Go to C2 and see that the data copied and pasted correctly. Go back to sheet one and type another name and phone. Click the button again and see the results. It works great. I think we learned a very useful case study of Excel macros that it'll help you a lot. Thanks for watching. 52. How to Create Notifications Reminders: Hello Students. In this lecture we will create a notification or reminder if you prefer, in Excel using VBA. In this worksheet, we have two columns, the domain name and the expiration date of each domain. We want to create a notification when a domain expires in one week so that the customer must renew it. There are a few ways to do that, like using formulas or conditional formatting. But we will use Excel VBA to help us create reminders. Let's get started. Go to Developer tab and press the Visual Basic button. The VBA window appears, double-click on this workbook to maximize the window. Click the down arrow, and instead of general select Workbook. Then from these options, select open. As you can see, it's own ready this option. We select these options because every time we open the workbook, we want to have updated notifications. To manage this project. We will be using a looping process and specifically the for loop. When using Visual Basic, you often need to run the same block of statements on each cell of the range of cells. And easy way to loop through arrange is to use a for each next loop with a collection of cells specified in the range property, start typing. For each cell in range B2 to be four. Sell dot interior, dot, color index equal three. The code line above sets the background color for the range sell to read, because three is red, the color index property gives access to 56 preset colors. If you wanted blue, you will use five as a color index. If you want to make the font bold, you will add the following code. Cell font bold equal true. Now, all the formatting will happen if the date is seven days less than the expiration date. For example, if the date is January the 23rd than a notification will appear in the same cell because the expiration date for Mellon training domain is January 30. So we will write if cell dot value less or equal than date minus seven and sell dot value not equal, double-quotes twice. Then range B2 to be four. Don select, we add endif next, and we're ready. Let's close the window and close the workbook. Now, every time I opened the workbook, the VBA code will run and create us notifications for the domains that have expired. Thanks for watching. 53. Excel Animated Charts VBA: Today's lesson is about animated charts in Excel and how a simple VBA code can help us achieve this kind of chart. If this sounds complicated to you, don't worry, we will go step by step. We use VBA to control the animation. Here we've got our source data, and here we've got our dynamic source data. And you'll notice that this is formula based, which I'll explain later. These dynamic source data feeds the chart that we will create an awhile. Also, we have a controlled cell that dictates what is displayed in these columns, i, j, and k. In these columns, we've got an if Formula. And it's discerning which data is displaying a based on this value in controlled cell. Let's look at the formula. It uses the row function. The row function simply returns the row number that is inside the brackets. If that is blank, it returns the row number that the formula is in. We're in i3 cell. So it will return the number three as a row. If you highlight only the function and press F9, you'll see the result, which is three. This is a nice trick to see part of the result of a large formula. So the formula says, if the value of y3 is greater or equal to the current row, then display the value of a3, and if not, then return hush and a. Remember hoBshare and a doesn't display in charts, that control cell has a value of three, so it returns the value in A3, which is 52. If I change the value to ten, then it will display the values from I3 to IE ten. As the control cell counts up, more and more of these data here are slowly revealed. And that is how we create the animation of the chart. To complete the animated chart, we need two steps. The first step is to create the chart, and the second step is to create a VBA code that changes the value of the control cell from number three to number 38. Finally, with the help of a button, we will execute the VBA and the chart will start to animate. Let's start from the first step to create a clustered column chart based on these datas and presented them in a particular format for each time period in a different color. Here we have a column for each year, 201720182019. January's In the second year is repeated from the previous column and the same in the third year. This gives us a continuation of the chart without any gaps. So highlight the data table from G2 to K 38. Click on Insert tab and select the clustered column charts. Move it somewhere there. Changed the value to 38, so it will appear in all the columns. I will use formatting to give the illusion of one chart instead of three and give a continuous lug. Right-click any of the series, format data series, and increase the overlap percentage to 100%. Also decrease the percentage of the gap width to 50%. Delete the legend, the grid lines, and the title of the chart is ready. Now it's time for the VBA first Developer tab macros, named the new macro animate. And make sure that the macros in his Workbook press Create and it opens the Visual Basic Editor. Now I will paste the code and explain it. Save it, makes sure that you save it as an x L S M type, which means Macro Enabled Workbook. Let's take a look at the VBA. We strongly recommend using Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables. Sleep is a Windows function and non a VBA function. But you still use this function in VBA code by calling the windows sleep API. Actually, sleep is a function present inside windows DLL files. So before using them, you have to declare the name of API above the code in your module. The syntax of the sleep statement is as follows. Here, delay specifies the time in milliseconds till which you will have to pause the execution. The advantage of sleep statement over weight is that it is quite flexible as you can give the time delay in milliseconds. While in weight function, you can only delay the application by whole seconds. Here is the name of the macro. If you change the name and the VBA editor, you need to reassign your macro to the button you're using to run the macro. Used the Dim statement at the module or procedure level to declare the data type as a variable. For example, the following statement declares a variable as an integer. This is the number range you want the macro to scroll through. These are the numbers in your control cell. This is the worksheet name you want to control, which has Sheet one. And this is the cell you want your control entered in. Finally, this controls the speed. The lower the number, the faster the chart scrolls. Okay, that's had the VBA works and how to update specific parts like worksheet names and so on. I want to assign my macro tool button, go to Insert tab online pictures and find a Play button. Like this. Put it somewhere here and with the right-click, assign the macro. Now every time I hit that button, the macro runs and the animation of the chart is starting. So that's how you can do an animated chart. I hope you enjoyed this lesson. I want to thank you for watching and I'll see you in the next video tutorial. 54. Saving a Workbook before Closing: As you know, when you close the Excel window, it will warn you that you are attempting to close an unsaved workbook, giving you the option to save before closing. However, sometimes you inadvertently click nel, telling Excel to close without saving. With this macro, you are protecting against this by automatically saving before closing, press all plus F11 to activate Visual Basic Editor and double-click the module that contains the macro. We will use the before close event, and we want to ask users whether they want to close the workbook. I've already typed the code. As you can see, if you want to pause the video and copy the macro to a new module, the best place to store this macros in your personal macro workbook. This way, the macro is always available to you. The personal macro workbook is loaded whenever you start Excel in the VBE project window, it is named personal dot XLS b. But if you want the macro to only be available in this workbook and copy it in this workbook. And the event drop-down list, select the before close event. Let's look at the code and explain each line. The first line is the before close event, as we said previously. The second line is a message box that prompts the user to save or not the workbook with OK or Cancel. We use the VB okay, cancel argument to ensure that the OK and Cancel buttons are present as choices. In the third line, if the user clicks cancel on the message box, the macro tells Excel to cancel the workbook underscore close event bypassing true to the cancel Boolean. In the fourth line, if the user clicks the OK button in the message box, the workbook is saved. And finally, Excel continues with the clothes. Through the final line, you close up the Select Case statement. Keep in mind that every time you open a Select Case, you must close it out with the corresponding n select. Now it's time to run the macro. It works just fine. 55. Closing All Workbooks at Once: How many times you have many workbooks open and wanted to close them instantly without activating the window, closing it, and confirming of all changes. It happens to me all the time. This macro is the solution to the above annoying problem. Press all plus F11 to activate the Visual Basics editor and double-click the module that contains the macro. I've already typed the code, but if you want, you can pause the video and copy the macro to a new module. Let's look at the code and explain each line. The idea of this is pretty simple. The first line declares an object variable that represents a workbook object. This allows us to enumerate through all the open workbooks, capturing their names. In the second line, we created a loop that loops through the open workbooks, saving and closing them. That's it. If you don't want to save them, change the Save Changes argument from true to false. You can store this macro in your personal macro workbook. This way, the macro is always available to you. 56. Introduction to Excel 2019: Hello and welcome to this introductory Excel tutorial. This lesson is an overview of Excel's basic concepts that you should know before learning more advanced techniques. When you open Excel, you'll see a blank workbook with a worksheet in it. The very first thing I want to show you are the areas of Excel and how they are divided. The top area is called the menu bar. And the ribbons with TAB. Rubens give me more space to various things in Excel and clearly spelled everything with words like Conditional Formatting, find and select, et cetera. All the important things that we commonly use are in the Home ribbon. Similarly, there are other ribbons too, like Insert, Page Layout, formula, et cetera. The second thing is formula and name box. If you take any formula in a cell, this is where you would see it. The majority of the Excel screen is given to the worksheet grid, where you have numbers for rows and alphabets for the columns. Each cell in Excel, a unique address like b2, which is row two in column B. The last area in Excel is the Status Bar. Some of Excel's users complain that the ribbon takes up a lot of space. If you want to save up space and increase the worksheet grid area, you can double-click on any of the titles like Home Insert, et cetera, and Excel will collapse the ribbon. If you double-click again, it will be expanded. If you single click on it once, you will see the ribbon momentarily, but it'll be gone once you finish whatever you need from the ribbon. You can also use control F1, which will turn the ribbon on and off. You can use that arrow button to collapse the Ribbon. Another thing that you will see is the Save, undo and redo buttons right at the top of the screen. This is called the Quick Access Toolbar. You could add anything that you want to access very quickly to this tool bar from the Down menu. For example, let's suppose you often use this center alignment button. You can add the button to the Quick Access Toolbar so that it is easily available for you to use always. To do this, right click on the button and select Add to Quick Access toolbar. By clicking on the File tab, you can see the various menu options like opening a file, saving a file, printing a file, closing Excel, et cetera. Excel comes in a very beautiful way to access everything that is out there in the ribbon. Select any cell and hold down the Alt key momentarily. You will see that Excel shows you some shortcuts. For example, if you press H, you'll activate the Home ribbon. Excel will show further shortcuts like c for copy. One for bold, et cetera. It's a very popular way to learn shortcuts that are available. Press escape to go back one step. Also, when you press alt over things in the Quick Access Toolbar or assigned default shortcuts of 1234 and so on. It is a very easy way to access things that you want very quickly. Escape again, the Name Box tells you where you are in the worksheet. If you select D5, you'll see column D and row five highlighted. You can select a bunch of cells that'll tell you what the top left cell is. If you are a D5 and want to go to F3, you can type F3 in the name box and it'll take you there. Now let's open this Excel workbook with some data inside. Select the titles and make them bold, align them to the middle and add a fill color. Highlight all the data, and simply add a border around it. Click on the second sheet. As you can see, we have a similar table. Now we need to do the same formatting steps again, but we hate doing that because it's repetitive. Instead, we can select cells and the first table, and click on the Format Painter button and select the cells of this second table. The format gets copied to this second table, instantly, saving you the task of repeating the steps from the second table. But what if we have more similar data, maybe five more tables. So instead of clicking the Format Painter, we can double click on the Format Painter and it locks the mode. So you can repetitively use it and Excel will apply the format. When you're done, press escape to release the lock mode. Go to a blank sheet again. From insert tab, you can create a table from this button. Or you can click on the Smart Art from the hierarchy and you can create a nice organization chart. Now create a new sheet in A1 cell type goto organization chart. We want to add a link to this phrase in A1 cell. Right-click on it, link, click on pays in this document. And select Sheet one. Press okay, and click on this link. Excel is going to take this particular location. This is one way to use the hyperlink functionality from the insert ribbon. Let's go to sheet one and click on any cell to add symbols like arrows, et cetera. From the Insert tab, click on the symbol button. There are some fonts like web dings or web dwellings that have only symbols. Click on this and press Insert. Remember that if you change the font, the symbols may not work properly because the symbols may have been defined for those fonts. Return to data table. Suppose you want to see the names and columns and sales and Rose. Would we type the data again? No. Instead we can select a data, copy it, and come to any open area and select Paste and then transpose. You can always fix the alignment if it gets messed up. Now select a new data table and press Clear all buttons. Alternatively, you can also clear formats alone, etc. Select this data table. When you select the cells, you'll see a thick black border around them. If you place your Mel's on the edges, you'll see a mouse pointer change to a double arrow. When it is there, hold the control key and drag to make copies. Instead, if you place the males on the edge and then drag it will move the data. If you want to use the keyboard, you can press control X and then control V to move the data. Similarly, to make copies, we use control c and control v. Both these techniques work and save a lot of time. Let's talk about dates. To get today's date, I can type equal today. Open close parenthesis. You'll see today's date. But if you open the worksheet tomorrow, you'll see tomorrow's date. Let's type tomorrow's date. Select the first two dates and place your mouse at the bottom edge till it changes to a plus sign and then drag your telling Excel to fill the dates for you instead of having to type them, excel does it in a very smart way. And it works for numbers. Either. If you type 510 and both select and drag, Excel will nicely increment the values by five each. This is how you can use autofill Techniques to save time and do things faster. You can select from a variety of options like long date. If you select Long Date, you might now notice a bunch of hash marks in the cell, because now the text is too long to fit in a cell. To fix this, you can select the entire column and double-click on the header line of these two columns. The column width will now get adjusted automatically. Keep in mind that even though we're typing dates from excels point of view, everything is a number. Let me type one in a cell. If I select that and ask Excel to show it as a date, Excel shows me first January 1900. So all they'd start from first January 1900. Another trick is that if I type control plus semicolon in a cell, I'll get today's date. To print these data, I can click on the File tab and print button. From here, you can see the preview of how it will look when it is printed. Sometimes we'll see our contents split over multiple pages. This can be fixed by selecting fit she on one page. If I click on the page setup here, you can choose to print in landscape orientation. You can go to the margins tab and tell Excel to print it so that the content is centered horizontally and vertically on the page. Close the window and click this arrow to return to Excel main page. You can turn the grid lines, often the View menu and uncheck the grid lines box. Similarly, you can turn off other things. For example, you can choose not to show the formula bar and disable headings. These are the standard ribbons, but there are some advanced ribbons as well. One of which is the developer ribbon, where we can use some of the options like recording macros, inserting form columns, et cetera. One of the things that we commonly see when we have a lot of data, and we want to put only a portion of the data to focus. You can select the data. Go to View tab and click Zoom to this election button. Excel will zoom it to a large extent. Only these things are visible. Everything else is still there, but they are beyond the scroll area. Restored to a 100% like this. Before we close the workbook, we must save the file. So from the final tab, click on the save file and pointed to a location. I will choose desktop. We're saved our first file and we've learned some interesting things in Excel. I hope this has given you a good overview of Excel. Thank you very much for your time. Bye.