VBA - Beginner To Ninja - Beginner Series | Andrew Jarick | Skillshare

Playback Speed


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

VBA - Beginner To Ninja - Beginner Series

teacher avatar Andrew Jarick, Intelligent Automation Specialist

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

7 Lessons (1h 52m)
    • 1. Lesson 1 - Introduction

      2:18
    • 2. Lesson 2 - Integrated Development Environment

      8:12
    • 3. Lesson 3 - Variables

      15:13
    • 4. Lesson 4 - Looping

      18:05
    • 5. Lesson 5 - If Statements

      32:12
    • 6. Lesson 6 - Reading & Writing, to & From Sheets

      20:59
    • 7. Lesson 7 - Functions

      14:49
  • --
  • 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.

175

Students

--

Projects

About This Class

VBA Beginner To Ninja teaches you how to become a proficient Visual Basic for Applications ninja programmer. The course is split into three series, beginner, intermediate and advanced. The beginner series focusses on the VBA fundamentals, providing you with the knowledge to be able to begin programming your own VBA macros. As you progress, the Intermediate and Advanced series courses, focus more on specific functionality that you will encounter in the real-world, providing you with all of the tools you will need to be able to tackle even the most complex tasks. 

Unlike other VBA courses, VBA Beginner To Ninja also: 

  • Highlights common mistakes that VBA developers often fall into, and offers the best-practice alternative approach.
  • Teaches you how to make the most efficient code, minimising computer resources while code is executing.
  • Teaches you how to build robust applications that are supportable, and more resistant to change and user errors.
  • Equips you with the knowledge and tools to be able to program by yourself, limiting the amount of googling you will need to do whilst programming to solve problems. 
  • Shows how to integrate VBA with other technologies, such as web browsers, HTML, XML & SQL
  • Shows you how to write VBA programs that avoid automating the MS Office User Interface, and how to programmatically integrate with these objects directly and virtually. 
  • Details how to adopt UX (User experience) and make beautiful, functional user interfaces that users will actually enjoy using. 

Meet Your Teacher

Teacher Profile Image

Andrew Jarick

Intelligent Automation Specialist

Teacher

Hello, I'm Andrew.

See full profile

Class Ratings

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

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

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.

Transcripts

1. Lesson 1 - Introduction: Hi. My name's Andrew Jerrick. And welcome to my course. Visual basic for applications. Beginner to Ninja introduction. Why VB a beginner to ninja? In this course, I will teach you how to become a proficient vb a ninja. I will teach you all of the tricks that I have learned in decades of programming. Vehbi, eh? Old teacher had to make robust applications with V b A. I will teach you how to make Macron's run efficiently with minimal resource is I will teach you how to avoid the mistakes that most VB a developers make. You will learn the skills to be able to tackle even the most complicated tasks I will teach . You had a code by yourself limiting the amount of Googling that you will need to do while programming and I will teach you the importance off and how to avoid programming the automation off the user interface. What is V B A. Phoebe A or visual? Basic Replications is a programming language based on visual basic. The difference between V b A and say VB script is that VB A is hosted by Microsoft Office applications. In other words, V B A is built into office products and code executes inside that host container. For example, x l dot e x e, whereas VB script runs natively in Windows as its own process. W script dot dxy The syntax for these languages are almost identical. So if you know V b A, you should be able to cut in baby script and vice versa. As V B. A. Is hosted by Microsoft Office Applications Office also provides you with a great idea he or integrated development environment to code in V. B A. We will run over the i D in greater detail in the next lesson. Another great feature about coding in V B. A is that you're able to actually pause or stop and interrogate your code during runtime, which you can't really do in baby script. What can VB A be used for? V. B. A is extremely powerful, almost everything that you can do in Microsoft office. You can do with V B A. And with exposed com libraries, you can integrate V B A with numerous other applications installed on windows. That concludes Lesson one. Thanks for watching, and I'll see you in the next video 2. Lesson 2 - Integrated Development Environment: welcome to lessen to visual basic for applications, Integrated development Environment or I D. In this lesson will talk about what an idea he actually is. We'll learn the multiple ways to get into the idea. We'll discuss some important components of the idea, and I'll teach you how to customize the idea to your preferences. Let's talk for a moment about what the V b A idea is the V b A. I. D. A. Is a piece of software that is built into office programs that allows the development, debugging and execution of VB a code. Some ideas simply contain a text based code editor. But the V b A. I. D A. Contains a code editor user interface editor, which allows you to build custom user forms, Project Explorer to provide a view of the objects within a workbook or project a properties window to view and edit attributes of objects within the Project window and a handful of other tools to make development simpler. All right, let's jump straight in. Don't assume that you have excelled installed, but let's just launch it, okay? From here, just jumping to a banquet if you don't see developer up here in the ribbon. We want to add that to our ribbon. Say to be able to do that, click this little guy here. So that's customized. Quick access toolbar Just hit more commands in this little dialog box. Click Customize it ribbon. And then on this side here, you'll see all the enabled and disabled tabs that Aaron your ribbon. So we want to enable developer so click developer here. Okay, Now we will see the developer Tab. As I said, this contains all the developer functions that are available from the workbook itself. But to get to the visual basic idea, click this guy here. We're now in the idea for visual basic for applications. Another way that you could bring up. The idea is to press the bolt and the F 11 key at the same time. Yeah, we're in the idea. Let's walk around the I. D. For a minute. Focus on this part up here. This is the project window. So here we can see a couple of object trees here. Basically, what this shows us are all the objects that are available within the workbook that we're currently in here. You got the workbook object here is that she'd object. But you also see code modules, user forms, classes. All those types of things now directly related to this is the properties window. So, as you can see here, we've got the properties that associated with this currently selected object, which is sheet one and hear other properties for the workbook object. Now we don't see any code window because we haven't written any code. But if we wanted to say types and code within this workbook object, we could just double click that, and that will actually open a card window here. This is where your car would be, would be chopped. You can also just double click. She'd object and also likewise, you can top code within that sheet. Something else that's really helpful in the idea is the immediate window. So click on the View menu and then select a media window. I'm not gonna go into too much detail right now about what the immediate window is, but will be using it during coding to print statements from code and view what the output is. We can also do vice versus or top things into the immediate window and send that into variables in code during runtime. Another useful window to add in the idea is the watch window. To do that, click the View menu and then select Watch Window opens up down the bottom here. In a nutshell. What we could do with Watch Window is watch certain variables, and this is very helpful during development and debugging so that we can see what's going on in these variables, and we can even get the code to stop when certain conditions are true. Something else that might be of use is tool bars, so we can actually add a couple of We've got the default toolbox here at the top, but we can also add in a couple of extra ones so familiar. Let's just go to tool bars and add debug and it go again and we want to add And it let's talk for a moment about com libraries. So in the introduction, I mentioned that visual basic for applications is able to talk to other third party applications via these exposed com libraries. Think of it like an A P I to see what com libraries were installed on your computer slicked tools, references and This is the window here, so this shows you a list of all of the libraries that are exposed on your machine. Let's just talk about one for an example. Planet. Let's talk about haste. Email. So let's say I select HTML object library and I hit OK, now that this is enabled, I'm able to actually program and integration with V B A and haste email so I can pick up of HTML document, select things by tags, or put a value into a text box, etcetera. So that's just an example of one home library that integrates with haste email. As you can imagine, these calm libraries expand the capabilities of VB a greatly, so let's customize the idea to our preferences. So it got to the tools menu and then options. Now the first option I want to talk about is auto syntax. Check. I typically disable this option because it actually kind of becomes quite annoying. I'll give you an example of why, so I'll just enable it again here. Okay, let's say we're typing in code and we make a mistake so I'll just type something in, and then we get this little air box here, so and it says Compile era expected. Identify. Okay, that's great. But because it gives us a yellow box that we have to click away from, that becomes quite annoying when your typing code. So what I always do is going to options and disable that so that now if I do the same thing , it still tells me there's a problem by Harlan E. And Red. But it doesn't show that message box that kind of gets in the way. Now let's change the way the text looks in the I. D eight. So I've just developed a small piece of code here just for demonstration purposes. Let's just jump into tools and options and jumping to editor format. I have changed the front to fixed cysts I find fix Ists is a retro looking font. It's just much more easy on the eyes. I have selected the normal text to be foreground color green background color. Black indicated to order, so I like black backgrounds because if you're looking at code for hours and hours, it's just much easier on the eyes. Okay, it's a movie long selection text. I haven't changed. Order, order, order, syntax, error check is red Yellow auto execution point is auto Yellow. Yellow Break point text is white Marone Marone COMMENT Text is dark green, black and auto keyword is sai in black and auto identify Text is yellow. Black auto bookmark text is auto psy in Sion. Call return Auto Green green So he hit. Okay, you'll see that if you've selected black is the background, you'll see the background turned black and everything you type should be much more easy on the eyes. Okay, so that wraps up. Listen to the visual basic for applications. Integrated development, environmental i d. A. So just to recap what we learned in this lesson, Woz. What an idea is what the visual basic idea years and how it's slightly differs from other ideas. How do we get to the idea? So remember you hit all F 11 while Syrian workbook, or you can go into the developer tab at the top of Excel and in the ribbon and open the idea. That way, we talked about the numerous components of the idea. So the project window, the properties window, the immediate window and the watch window, and we also showed how to customize the idea. So making the text look much more pretty and easy on the eyes on a couple of other things there. So anyway, thanks for watching, and I'll see you in the next lesson. 3. Lesson 3 - Variables: welcome to lessen. Three. This listen is about variables. In this lesson, you'll learn what variables actually are. We'll talk about some variable types. You'll learn how to declare a variable and understand variable scope. So what are variables? Variables. Broadly speaking, something that you defined by name that are addressed within code by that name that you define variables. Hold onto and remember data. Donna could be values of varying types or objects and attributes. Let's say, for example, we define a variable by name as message in code. We can populate that variable message with a piece of text. That piece of textures. Hello, my name is Andrew. The variable now holds onto that piece of text that we can refer to simply by name, which is message variable types in programming, you need to hold on to things of different types. Variable type could be, but not limited to a number or a piece of text or a true and false state or an object that has many attributes along with the variables name. We can also define the variables type two. So how do we do that? Okay, let's jump back into the I. D. A If you're unsure of how to do that, just refer to the previous lesson in the i d double click on this workbook. Let's start declaring some variables. So in visual basic declaring a variable was also known as dimension ing a variable. So if you see dim like this means it's declaring a variable. So it's type of team. And let's call the variable message so message. That's the name of the variable. Now the message is a piece of text, so we want a string type, so we'll talk now as string and see and tell a sense they're actually order complete or helps us order complete that. Line it enter. Here we go. We have Dimension a variable called message, and that variable type is a string is declare another variable sorry type team. This one's gonna be a number. So let's say this one is called numb. And that, as a number is, if it's a whole number is in Georgia, so integers a whole number with no decimal places. If we wanted to declare a number with decimal places going to call that decimal numb, for whatever reason, double another one TF. So true falls if you can guess that you get 10 points. Julian. There's a couple of different top. So we got a dim message as string. So that's a piece of text. Tim Numb as indigent. That's a whole number. Dim, decimal, numb as double. So that's a decimal number and dim TF as bullying. So in code, we can actually now just refer true the names of these variables that we've set up in V. B. A. We rarely declare variables outside off blocks of code. But how do we tell the I d? What a block of code actually is? That's where subroutines come in. Let's just set one up now. So to start a subroutine type sub now we're gonna name. So let's just call it my underscore routine and then open and close brackets. And if we hit in tow, we will see that in sub is automatically placed there. So that just tells the interpreter, or the idea that that's the start of the routine, and that's the end of the routine. Let's put our variables inside that routine, so if we just copy and paste them or cut and paste them all right now, we've got out variables inside the routine. Let's continue on with a simple program. Okay, So if we want to do the hello world equivalent in V B A, let's just get rid of these variables right here, okay? So we'll only be needing one variable, which is a string of text. A message. So in order to a sign of value true, a stream variable. First of all, that's type the variable main message equals and to identify what a strangeness we need double courts just hot. Hello, world. And close it with a double quote. So now you can see this message, which is this variable here? We're now assigning at a piece of text. And that piece of text is Hello world. Okay, so now we want to do something with that variable. So we could do is we could print the value of that variable in the immediate window. So let's do that now. So in order to take a variable value and print it down into the immediate window, will need to debug print debug dot print. And now let's type in the variable name. Okay, Now we're ready to run the routine, so there are numerous ways we can run that routine. So first of all, let's just like, make sure the cursor is somewhere in this routine and we just hit play. Now you can see that it's worked because now we have hello world in the immediate window. Another way is making sure the curses at the top. There we can just hit a five. As we can see, we now have another hello world. We can also actually steps through code, which is excellent for debugging. So if we press if eight as you can see that line is highlighted, and if we keep pressing FAA, it will jump line by line until the routine is over. We have another hello world, so let's talk about what's happening here. So we have our defined routine or subroutine cold my routine. And inside that routine we have a variable that's do mentioned called message, and that arable type is a string. The next line we have or were signing that variable piece off text, which is hello world and the next line. We are debug printing into the immediate window, the contents off that variable and now in sub. Therefore, we're telling the idea this is the end off this routine. Let's do another example. Its credit. New retain. Hello Underscore World. This time we want to display a message box as opposed to debug printing into the immediate window so we'll do something similar. But let's just call the variable something else. Cool it. Message MSG. So deem MSG as string. Let's assign the variable a piece of text. Hello world. And if we want to present a message box that's MSG Vox, its base. You can see intel. A sense is telling us what to put in there so we can just put in this G. We don't need to worry about anything else. There we go. Let's run that. Put the curse right there. He play and you can see Mrs Box. Hello world. I want to talk about variable scope for a minute. So the reason why I have done to hello World examples is that the variables within one routine are actually visible by the other routine. So in other words, variables that you create inside of routine are only visible within that routine, and I'm just giving a little example that so if I just top message and play that one. We should see a message box with no text in it. There we go, no text. That's because is variable, which is here named here isn't visible from this routine. It's only visible within this routine. If you would like to have a very bold that is visible at a global scale or in a module scale, you can actually do that. Let's do that now. So these types of variable or called Constance and their declared slightly differently. So instead, off being variable, it's declared inside of routine, actually declared outside of the routine through that top constant, which tells the idea, this is a constant variable. Let's call this one. Capitalize it constant message as stream, and that equals hello world. So now if I get rid of this, get rid of that. See, now this is capitalized. So it's realized, Hey, we've got a constant variable here, and that's what this equals and same here. You can see that's being capitalized, so get rid of that. We can actually get rid of that, too. If we run this, one can see that it's putting you Hallowell doubts. Do that against delete those in the immediate window was run that again. See Hello World, and now we should also see on this routine. If we play this hello world routine, we should see a message box that's contains the message. Hello, world. What a guy! So that is how you create a variable that is visible outside or from any routine. Let's talk about some reasons why you might use a constant variable. So picture this. You are building a handful of routines, and each of those routines opens a workbook that's in a specific file path. So instead of actually topping out that file path in each of the routines that are opening that workbook, you could just assign that variable to a constant or a sign that file path to a constant and then refer to that constant in those routines, as opposed to topping it out. All of those times. While we're on the topic off variable scope, I want to talk about global variables, so those types of variables are just that global. They're visible from all retains from all code modules and from the workbook module and the shape modules. Now, unlike constants, global variables cannot be declared inside a workbook or worksheet code module. We need to create our own. So up in this project window, let's right, click and click, insert and then module. And so that's actually added a code module. Now let's go ahead and declare one. So to do that, let's type global and give it a variable name was gonna call this s t a message so that we know it's a stirring as string. So we're gonna copy that, and I'm going to jump back into my workbook. Now, I don't need to declare that again because I've declared it here is a global, so I can actually just go ahead and yeah, multiple exclamation mark so that we can tell the difference. And Okay, so let's run that. Yeah, there we go. We can say there. So that's how to use a global variable. I want to talk for a moment about object variables, specifically worksheet and workbook object variables. This diagram demonstrates the hierarchy of these objects. You can imagine when programming in VB A that you may want to address multiple sheets across multiple workbooks. This object model visually represents the hierarchy and relationship between a sheet and a collection of work shapes and a workbook and a collection of workbooks. So this big green box is a workbooks collection object. It will contain all of the currently open workbooks. In an instance, Off Excel. It will enable you to address any workbook in that collection from the workbook that is actually running the code. Next we have a workbook object. This relates to a specific workbook inside the collection off workbooks. It might be one that you have open or might be one that is minimized. Next, we have a work shades collection object. This contains a collection of worksheets that are available inside a specific workbook. Lastly, we have a worksheet object. This is a specific worksheet in a collection of worksheets. I just wanted to explain this object model because knowing the hierarchy of collections and individual objects within those collections is extremely useful when programming in Vehbi eh? So let's jump back into excel and put this knowledge into practice. So what if we wanted to refer toa workbooks or work sheets as object? Let's just set that up now, So let's just jump into our hello world subroutine we built earlier. Declaring these objects is actually very similar to the string object that we were declaring before, so to declare an object D I am deem or dimension WB. I'm just calling it WB because it's not some quick and easy and as because you can see until a sense has come up and it's unknown. Type that or just complete that as you will now Work sheet So dim ws cause nice and quick and easy as work sheet. Okay, so these are the correct types now, but they're still blank there, still empty. We haven't told them what specific workbook or worksheet we're talking about. So now we need to set these variables. So to do that, we talked set and what very blue talking about. So we want to do the workbook first. WB equals, and as we can see, this Properties window is called This Workbook. It's not some quick and easy way of doing it. And which worksheet is it? So Set ws equals. So it's work shape inside this workbook, so we want wv dot and there's a sheets collection. So there's It's one of multiple shades which specific shape in that collection we're talking about sheet one. Okay, So that's how we go ahead and define the workbook and worksheet object and set those objects. Wanna just quickly talk about a common mistake that VB a developers often fall into, and that is not being explicit when they're referring to a work shaped object. Often developers will refer toa worksheets without using variables on. They may improperly set a work shade object with no context of what workbook the sheikh belongs to. In the real world, you will definitely come across code that uses active sheet or just sheets without first using the context of which workbook we want to address in that statement so they may work. But it becomes problematic without the workbook context. And let me explain why. So imagine that you've developed a macro and someone else is using it. And so while that macro was running, another workbook is open. That's either programmatically or manually by the user. If your code only uses active sheets or shapes to address a particular work shape, that workbook context is lost and replaced with workbook that has just been opened. So if that happens, if you're just using active shade or shapes the active shape now becomes a sheet that used in that work you've just opened and not the one that's actually running the code. So that's why it's best practice to bind the context of variables and being explicit with your declarations. So, for example, which works shape our words this work shape within this workbook? Okay, let's recap Lesson three. In this lesson, we talked about what variables actually are. We talked about a handful of variable types, such as bullion, string and objects. You learned how to declare or dimension variables. Using the dim statement, we learned about variable scope, such as Constance Global's and local variables, and we talked briefly about objects, specifically workbook and worksheet objects and the importance of being explicit in those declarations. Thanks for watching, and I'll see you in the next video. 4. Lesson 4 - Looping: welcome to listen for this lesson is about looping in this lesson. You will learn about four loops, for example, for each and four next loops and also do loops, for example, do while and do until loops. Believe it or not, looping is a huge part of not just VB a coding, but coding in general. Put simply, looping just means to cycle through a certain block of code that could be a particular number of times or loop until a condition is mitt. There are two types of loops in VB. A four loops and do loops. Let's briefly talk about a couple of full loop examples. Ah, four H Loop would cycle through each item in a collection, for example, for each worksheet in a workbook or for each male item in an inbox. For next, Loops would iterated through a block of code a certain number of times. This method uses a pointer or counter to count the number of times the code will loop. Now let's talk about do loops do while loops will look through. While a condition is true, For example, do while the time is less than 5 p.m. or do while the count of something is above zero. Similarly, do until loops will loop until the condition is true. For example, loop until there are no more mail items in a mailbox or do until the current cell value is empty. I could go on and on all day talking about loops, but let's just dive into the idea to try them out. Okay, so we're gonna be working with hello world routine. So just jump back into this workbook code module from the Properties window here in the last. Listen, we declared the workbook and worksheet objects, and we're gonna need that frail loop. You don't also noticed that in the Properties window here, we only have one worksheet objects, so let's go ahead and change that. So just jump back into Excel and let's go ahead and create a few worksheets. Doesn't matter about the names. Just let's just create let's say four, okay? And let's just jump back into the idea. Okay, so now we can see that we've got those four sheets inside the properties window. Let's stop and think about what we want to do for a minute. We want a cycle through each individual work sheet inside the workbook. Let's think about the objects gonna need for this. So in the last lesson, we set the workbook as this workbook so explicitly set that workbook objects as this workbook, However, we explicitly set this work shaped object as an explicit sheet inside that workbook, as in sheet one. We don't want that, so let's go ahead and just delete that line. I delivered the set work sheet statement because I want to refer to the worksheet as a generic type within this. For Luke, for example, I want to cycle through H generic sheet inside a collection of worksheets. Now let's begin our fallen. So let's just go ahead and delete these lines here to start a for each loop type for each for each what for each worksheet for each generic worksheet ws. So that's a blank. As we've just declared it. We haven't said it for each worksheet object in W. B. And to end the full up, just type next. So what's gonna happen here is that it's going to look through this section here and once when it gets to the next statement. If there are still worksheets in that workbook collection. Then it's going to go back to the start of that loop and execute any code that's actually in the middle here. So we want a debug dot print and debug print the worksheet name. So Ws name. We're almost ready to go. There's just one thing missing and let me explain what we are missing. So in order to do that, let me demonstrate. So let's steps through the code. So if we put the cursor up next to these green brackets, let's do that. And if eight that starts stepping through so you can see the line is selected yellow hate f it again, you can see that it's jumped over the declaration statements because the declaration statements is not really part of the script. Yeah, he defied again, and now we've set that work oak object, and now we are jumping into this full loop. So if we hit, if we're gonna hit an era and the area is runtime era for 38 object doesn't support this property or method. Now let's explain what's going on here. The workbook objects contains many child properties and attributes. Think of it like a parent and a child, the workbook being the parent and all that's properties and attributes being the child or the Children off these parents. So some examples of the workbooks child attributes could be, for example, the file name or full file path, whether the workbook is visible or not, or if the workbook is read only or protected on. Look, there's plenty of Mawr attributes that a workbook object can have now because we're interested in the work sheet type objects within this workbook, we need to match the type in this four statement. Remember, In the previous lesson, we talked about the object hierarchy of workbooks and worksheets. A worksheet belongs true, a work shapes collection and the work sheets collection belongs to the workbook. So we need to instruct the for loop to look for the work shaped type objects within the worksheets collection within this workbook. So to do that, what we need to do is we need to set the property in the workbook object to dot dot sheets . So now we're saying, Look through each worksheet in the workbook collection, off worksheets. Let's he defied again and we've done it. So now we're inside the for loop and it's going to debug print w s dot name. And if we hover over that, you can actually see that intelligence is showing us what that is, which is another very, very handy thing when debugging and coding. So let's just hit a fight again and we can see in the immediate window sheet one excellent and if it again and again and we have shaped to in the media window and even if it again and again we have C three and again and again and we can see sheet for now because we have only four sheets were expecting that the code is going to exit this full loop, which it does when we hit a fight. And now, if it again and that will end the routine, let's do a four next loop. Now we want to do the same thing. We want to print all the sheet names into the immediate window. Now, remember, in the four next loop, we're gonna need a pointer. So the pointer is the counter in which number off the loop we actually in, which is aeration of the loop, were currently in. So we want a pointer and a pointer is a number or whole number, so let's go ahead and deem a number or a interject. So call an ambulance. This Court X as as Inter job. OK, and now let's just make some space underneath this for Lupul for each loop. Now let's start typing our four next loop. So for type four, and we're gonna be using that interview that we've just declared so X remember, that is the point or counter off what iteration off the loop were currently in for X equals one. So the first sheet in the collection to now let's be a little bit creative here, so we know that there's four sheets. But what if we didn't know how many sheets there are? So that would be and we have a collection of worksheets. One of the child properties off the shades collection is the count don't count. So for X equals 12 the count of how many shapes are in the work sheet collection within the workbook, and we're just going to sit the end off the four statement, so just type next, and we'll do a debug print statements that it's gonna be a little bit different because we don't have a worksheet object here. So what we want to do is we want to debug dot print. Okay, so we can address the worksheet via the workbook. So if we go wb dot sheets and we open a bracket last front index, so remember, before we explicitly said sheet one index is also a number, so you can actually address a worksheet by number. So 1 to 4. Because we got foreign there. So let's just put exit anemia dot name Before we were on this, I want to quickly talk about break points. Great points are really handy debugging tool that allow you to execute parts of code very quickly and then stop it a specific part which we want to do. In this instance, we want to run quickly, run through the first loop, and we want to stop when it gets to the second loop. To put a break point in in this little gray bar next to your code, just click and you can see it's got a little Marrone circle there and it's highlighted the line that it's going to break out. Okay, so let's run it. But before we run it. I'm just going to clear at the immediate window. So let's just click up the top next to these green brackets and you remember F five as ah is a method to play other than the play button. So we just clicked up. Then it's next to the green brackets and then hit five. And then we'll see immediately. Have faster code runs, as you can see in the immediate window. We have sheets 1 to 4, that being printed there, but it stopped at the line that we wanted to stop at. So if we hover over X, it's currently zero, so it hasn't initialized. But if we hit if eight once more now X equals one because it started that fall loop fate that's compiled. We have got an era and we can see in the immediate window. We've got shape one. So now that's just f a. A few more times, and we're done. Now we can see that we have sheet 1 to 4 printed twice in the immediate window, and that's exactly what we're expecting. You may also notice that inside each of the four loops that I've done, I've actually tabbed or indented that particular line that's inside, you know it doesn't actually alter the way that code is executed. Unlike other programming languages, it's just four easy off reading. So, for example, you might have nested fours four loops inside four loops. But if they're all along one line, it becomes incredibly difficult to see what's going on, particularly if you have a lot. You have missed out one of the end statements because if we try and run this, it won't compile. It says. Here is a four without next or it could be an if block or it could be could be anything. Now, if you've got multiple nested ifs or nested fours and they're all along this baseline here , it becomes incredibly difficult to find out which one that you have missed. So that's why it's just best practice to indent. Now. Let's do a do until Lou. Okay, so we're going to be doing the same thing. We're gonna be printing out the worksheet names. So to commence that you might have expected it's due until going to be using the interviewer or the pointers Well, so do until X equals and I want a loop until we get to the last work shape. So workbook dot sheets chic collection. And remember, we gotta count in there so the count of work shape. So do until X equals account off the number of shapes in the collection of work shades with in the world. Okay, And now, to end that do loop just type loop so the four statement or the four loop ends with the next and the do loops and with a loop, do loops behave slightly differently to four loops. So in a for loop, that X is actually part of the four statement, and it's counting so that when it reaches the next cycle, that actually automatically adds one to the X interview or the pointer, whereas a do until I think of it as two separate statements. So you've got doing chill, and then you've got your your actual condition that you want to be satisfied. So that's why, in a do until loop, it won't automatically adjust the X number. You have to manually do that inside the loop. Another thing we want to be careful of is the reuse off the variables now, because we're using the same Inter Joe or pointer here. When we exit this for Luke X is actually four. So when we jump into this loop, that will actually already equal this. Therefore, we need to reset this ex pointer before we into the do until loop. So let's do that now. X equals one. Okay, so let's go and put our conditions or out our lines of code inside that do until loop. So we want to do the same thing we want to debug. Don't print what we want to debate print. We want to debate quickly done this before. So we want the workbook dart sheets collection and which she in the collection. We want X and what we want from that sheet. We want the name. Also, remember how I said that the X doesn't automatically adjust with each cycle? So after we've got the name from the current sheet, we want to adjust this X. So how to do that? That's X equals X plus one. So it's just saying I said, Let's say if this is one, it's saying we take one and we're gonna add one to it. And then so when it gets to the next cycle, when it gets to the next do this will become its previous number, plus one. Okay, so let's run it. But before we do that, let's just remove this brake point. So to remove the bright point from the previous Fallou, just click that little Marone Circle and you'll see that line disappear. Let's add it to the one below it. Also, let's go ahead and delayed everything from the immediate window because it's quite a few things in there at the moment. Okay, so put the cursor up next to the green brackets and then he five. We have seen the meeting. We know it's already run through those two previous loops. We've got the shape names printed in the immediate window, and Ellett steps through this current loop and see what's going on. So here their fate once more and we can hover over X six is still one, and the workbook shapes count is still four, which is great because we have delayed any sheets. If it again and with got a sheet one name printed in the immediate window s ex still one. And if we if a past this line X is now too, so that when it comes back up here Still to and this do four, right? Printed again, We're gonna add one to the current number. Okay, let's just hit f A a few more times and okay, so what we can see here, you'll notice that it's only printed out three sheets and let me explain why, after we printed sheet three into the immediate window, X equals X plus points. So we had three there and then we added one to it. Then it became four. So then, when it came back up to this line, X was four and the Sheikh can't was fourth. Therefore, we have looped until that equals that. That's why we've only printed out three sheets Now, in order to fix that, let's just add one. So do until X equals the sheet count, plus one. So do until X equals five, and that should actually correct that. So let's just run and save it for do that, which delayed everything from you Media window and I'm still in my coat on the break monitor hit Stop and then I'll click up next to the brackets and then at five. And then let's hit F A. A few times until we reach the end. And yes, and we can see now we have printed out all of the shades. All right, So to complete the entire collection, let's just quickly run through a do Well, so do while X. So we're gonna be using a very similar logic here. So we want to do while X is less than so less than is a angle bracket like that less than wb dot sheets count and similar to this one. We wanted to be a plus one, right and similar to the previous Drew loop greens. And what do you want? A deeper cream. We want the workbook dot sheets shape we want shape ex dot name. And we also wants to manually adjust the point equals X plus one mistake there X equals X plus one. Okay. And also similar to this one when it's actually reset. Because after this Loop X is five, which would immediately make this true. So let's just to copy that guy and we're good to go. So let's the little this in the immediate window. Let's run this. So we weren't debug the code we will just run through. So hit up here five. And we should see the shape names written out four times. There we go. All rise. Let's recap this lesson on looping in this lesson. We talked about four loops and do loops, and in the Phoebe A I d. A. We did an example off a for each loop and an example of a four next loop. Likewise, we did an example off a do while loop and a do until loop a little bit of extra content. There we delved a little bit deeper into the workbook and the worksheet object model, and that was in the for each loop. Practical example. We also learned a couple of debugging tricks, such as break points and hovering over variables to reveal what their values are during runtime, while the code is paused. Anyway, Thanks for watching, and I'll see you in the next video 5. Lesson 5 - If Statements: Hello and welcome to a lesson five. This lesson is about if statements have you had any exposure to riding formula in Excel, this lesson should be a nice and easy one for you. If you haven't had any exposure to riding formula in Excel, this lesson will not only enhance your baby a skills, but also enhance your Excel formula skills by introducing you to if logic in this lesson will talk about else and else if conditions you'll also learn about and or conditional logic within if statements. In my opinion, if statements in V B. A is here to digest and if statements in excel formula and that's because in V B A. If statements are actually laid out across multiple lines were is in excel formula. The statements and all conditions are laid out in one line only. So what's the purpose of, if statements put simply, if statements allow you to run lines of code only if the conditions you define omit. Let's go back into the idea and start writing some. If statements in this first, if statement example will be building on the hello world subroutine that we have, Bean adding two in each of the previous lessons. So if you haven't already jumped into this workbook code module within your V b a i d A. In this first if statement example, I want to add to the existing functionality off the Hello World subroutine and that existing functionality is to cycle through each worksheet within the worksheets collection within the workbook and to debug print that sheet name into the immediate window. So the new functionality that I would like to add to this code is to print a different result into the immediate window. If this sheet name has been changed from the default sheet name, how do we tell if this shed is being changed from the default sheet name? Well, that's actually pretty straightforward, because we know that when you create a brand new sheet in Excel, Excel actually automatically names that just simply the words sheet and a number. If you pay attention up into the project window over here, you'll notice that all of the sheets are currently the default sheet names. One other thing I'd like to just kind of point out here is that you'll see here on the left side outside of the brackets, you'll see sheet one, and then inside the brackets, you'll see sheet one again. So the difference here is that this is the actual object names. This is the V B. A object name, and this is the actual physical name that you have named it, so you can actually address them either way. So as a as an object or as the index name as all of these sheets out a default name, let's go into excel and change a few things. Um, so let's go and change sheet to I'm gonna go rename that Marie name that Andy or Andrew my name and let's rename sheet for and I'm gonna let's call it Brian Okay, so you can name them whatever you wish. Just name them away from the or change them from the default sheet name and then jump back into the idea. We don't need all the existing four and do loops in this routine. The one that I'm interested in is four x equals 12 wb dot sheet dot counts. We want to keep that one, and for now we can actually just delete the rest. So I'm going to delete to do until and the do while, but also the very 1st 1 we did, which was for each worksheet in workbook that shapes. So get rid of that one as well. So the one that we have left is for X equals 12 workbook, not sheets dot count. That's the one that we want to add on to down. Now let's start topping our if statement. We want the if statement to be inside the for loop. And the reason why is because we want the if statement to be evaluated on each work shape. So let's just create a line in there and we got in dent. If now let's think about what we want to do here. We want to see if the default sheet name has changed. So if workbook daughter sheets and which shade is it? It's in the index X Don't name equals. Now remember, we know the default or the Excel default sheet. Name is the word sheet with a number on it. Okay, so equals sheet case. And now what do we want to do? We want to include the X pointer here or that index were in Katyn aiding strings and how we do that is with little ampersand and thanks What we're missing here. So we what happens after that? So if workbook Sheets ex dot name equals sheet and X, then so if and his al logical statement. So if this equals this, then I want you to do something. When these conditions are true, then we want a certain thing to happen. Otherwise, do something else. So if the sheet name is the default shit name and let's just be bug print the existing message that we had, which is this sheet name. And I want to add another line here. The bag does print, and I could just put in a little message, remember, double quotes opened the string and time. This is the default sheet name. Okay, so now we've caught that condition. So if the Excel sheet name hasn't been changed from default, it's going to debug print with sheet name and then a new line, which I need to correct. Debug not be bug. This is the default sheet name. Okay, So now what about all the other cases? So in any other instance? So in order to tell excel what to do in that instance, we talked else. So that is the catchall for every other condition. Then what we want to do then. So we want to debug the prince, and I want to be about shape name. So I might just use W B machines. Which shade is it? It's an index of X name and also want to print huge and what I want to be. But Cramer is one of debug printed statement similar to the previous one sheet. Name is not the something like that. So the sheet name is not the default Excel sheet name. Okay, Now, every if statement needs an end of statement so otherwise just similar to how we in compiling the previous lesson when we removed this next to tell visual basic. This is the end of this statement. Similarly, with if statements, maybe a needs to know when is the end off that particular statement in Dent again. So the in dentists slightly off here. So it tap on those lines and we're good to go. All right, now, let's step through this code. So put the cursor up beside these green brackets. Andi hit FAA just to begin stepping through. So was sitting the work object. That's great. Now we're gonna enter a full loop. So for X equals one to the count of how many sheets in that Whipple collection, which is force, it's gonna look through this four times. And if it again. So now it's going to be evaluating this condition here. So does the worksheet name match the strength in captain ation that we've done here, which is sheet and one. And if it does match, we should see enter in here. Otherwise we should see it coming down. Toothy else condition. All right, so yes. OK, so we've seen that. Now they do match now. Another really cool way. You can do this during runtime. Let's say we're building this while code is running What we can do in the immediate windows . We actually copy this this logical statement. So control. See, let's just jump down into the immediately originally when it was there, and now we can interrogate what's going on here. So if I press a question mark and I paste in so here I can see off pasted in w b dot sheets and because we're in run time and it's actually running X is a variable that we have already initialized on set and the current number is one, and in the immediate window actually is referring to the same X. And so now we want to do it won't evaluate if the sheet name is the same as Sheet and X, which is one, and it will tell us. True. So if we wanted to debug what's going on here and we jumped in here, we say it's false. We can actually now see why. So one of the very common reasons why string comparisons fail or don't work the way that we expect them to is because the comparison is is like for like, meaning that it's case sensitive. Now the best way to deal with that situation is makes them both the same case. And I do this the majority of the time unless I need there to be capitals and no capitals on debt. Cera cera. But the way to do that is is to in case our string or strings in this statement here you case. So now let's say, for example, if the sheet name was all other case and this one is not other cases, we can see it's now false. But if we make them both upper case, it doesn't matter now. It's true again because we've made them both uppercase. Therefore, it's the same secondary of statement because Excel has up and lower case in the default sheet name. I want the if statement to be case sensitive, or at least the text comparison inside this if statement, I want that to be case sensitive. Therefore, I will not use the U case method in this statement. Let's continue stepping through this card so I'll just delete everything in the immediately Don't women that and yeah. Okay, So he did fight. We should see. Here's to debug printing sheet one and we never again. It's printed out that this is the default shape name, which it is. That's true. Now it should go to it, skips over that else condition and now back up to the next iteration off this four loop and now So we're on the pointers to us and the sheet name remember and shit to named Andrew if eight. All right, so that's good. It's evaluated the difference there, and it's jumped into the else. So that's for all other conditions. So remember, if the sheet name is default, do this otherwise go into the next one. Okay, so debug. Print the name you can safety but printed my name inthe e in the media window. And now next line. Street name is not the default Excel sheet name. Okay, so let's hit, if a bunch more times, so we can see that it's done Sheet three. And that's a default sheet name and shape for which is called Brian. This is not the default associate name, and I just left that in their X equals one, which just resets that X pointed back toe, one that's just little left over from the previous previous. Listen, we don't actually need that line in there. All right, let's add three conditions here. So the first condition being is it default. The second condition is it Andrew and then a catchall for everything else. All right, so let's go ahead and do that. So what we want to be adding now is an else if condition because it's not a catchall like the else condition. It's not the first condition on. It's not a catchall condition. It's an else if conditions so in order to do that. Let's just go back to the the that baseline of if and we can talk else if and w being don't Shane's X. Because that's the pointer. What name equals. And this one? I will. I wanted to not be case sensitive. So how do we do that? So also it please note that else if it doesn't, it doesn't like it if there's a space in between this. So just just make sure else if is its own single. Yeah, single. Would you'll notice if it likes it? If let let's have, For example, we type else if in no capitals. And if we let that line compile, it will capitalize it for us school. All right, so and remember how I in the in just a few moments ago I capitalized thing. So, um, where we were Here. So you case. And so I'm you casing. What? This is returning. And so remember, this is this property, which is the name property of the sheet object returns a string. So it's It's a variable. Yes, but it returns a string, so the value in dot name is a string. So that's why you case, I'm you know, I can you case a string and this is a string. So whether it's capital Andrew or or Toddle case Andrew manner and so that makes that non case sensitive. Now I don't want a problem. Doug. Print debug print with sheet name in this instance, I just want to say something silly. Um, Andrew is, uh, maybe a ninja for and incorrectly. So I'm using an I Mac to record this in boot camp, and I'm using a Mac keyboard, which is really confusing in that doesn't have a Windows Kaye, in the case of kind of around a little bit the wrong way, but I'm getting there all right, and so that should be fine. Um, so what it's doing here is gonna come and evaluate this sheet name. If it's the default sheet. Names on the debate print that if else if the sheet name is Andrew and it doesn't matter if it's up a case with Low Case, it's so it's non case sensitive. It's going to say M. Andrews V. B A ninja very types really slowly and otherwise that cat catchall for everything else could weaken, weaken, go ahead and add a bunch mawr sheets. I should say it will just depot print the the name and the shape is not default. All right, so it's running. I was going to clean up a few little spaces here, and I'm going to go and delete everything, and we mean what we know and I am not gonna step through the code this time is gonna press play, so just make sure the cursor is next to little green brackets. Hit play, and let's go interrogate what happens in the immediate one day. All right, so in the immediate window, we could see she wants her in the first loop. It's found Sheet one, and that's said that that is a default sheet name. The second iteration. It's found that the sheet name is Andrew, and so is his Andrews. Maybe an engine. Nobody touch really slowly. 30 Operations founded a sheet three and said, this is the default sheet name. And the last one. It's Brian and the ship that was Brian, and she is not the Default Excel sheet name, and that's exactly what we were expecting. All right, so let's now do some end or conditions. But before we do that? Let's just jump back into excel and I'm gonna add a couple more worksheets. Ah, yeah. And yet so I'm gonna add two more and with a rename one Andy. So that Andrew and Andy and Brian and very Australian Let's rename this one riser from Brian do Bryza. Okay, so it's the formal name and the nickname Sorry. Full name Andrew and Andy and full name Brian and Brian. And then I'll jump back into the idea. Okay, so let's start on our or statement. So the logical what? I just want to add one little piece here into the logic. So what I want is that if the Sheikh name equals Andrew or Andy, I wanted to debug print that same message which is envious of a beignet. Injure pretty type, slow. Okay, so let's begin. So on that else, if line logically, that's where we're gonna put it. We're gonna top or so we've got to We've got to starts to our else. If step now, we've got this logical statement and when it create a new logical statement, so a logical statement is if you know, like if Blair equals block. Okay, so Ah, the sheet name. So and remember, we did the U case. But I just do that after, so workbook dot uh, sheets on a sheet into all the index Of what? Sure it is in the name equals Andy within. OK, so now let's set these strings is up. Case case to the end there. Close the bracket. And same here, you case open bracket and then closed record at the end. Okay, so we were on that because they're at the top here, and we could just hit at five. We we don't have a break point, and we just wanna just want to say what comes and they made it. We know it's quickly, Too late. What's in the immediate window? Uh, guys to put the curse that back up there on the green and next to the green breakfast here at five. Like, cool. And we should see. So she won in the first iteration. Um, so it's a sheet one, and this is the default sheet name. The next time it didn't print the sheet name minutes, which we want to not to do. And just says Andrews would be an injury. Really tight. Really. slow, and then the next one was shaped. Three and it's printed out sheet three and this is the fourth Street name. It's then printed out Brian on this ship. Name is not the fault street name, which is what we expected we've got. The next tweet was Andy. So then, in that next generation, it's just print printed out that single line, which is Andrew's. Maybe an injury. But he talked slowly and then the last one is bry za on. That is not the default Sheikh name, which is exactly what we wanted. All right, so let's just do one mall. It's at an end condition into this first if statement here. Okay, so this end condition in the first if statement, what I wanted to do is to catch when it's sheet one, but I don't want it to catch when its shape three. So we're gonna be adding something here into this into this. If statement the first instead. If statement that's going to be an end claws to make sure that it's just the sheet one that we're talking about now, there's probably many ways that we can achieve this, But for the sake of demonstration purposes and that we want to be using him and condition in this first if statement, we think about it. We've got here. The if the workbook shape name is equal to sheet, end the number, and so now I'm needed not or a, uh or condition that finds out when it's not something wb dot sheets and it's the current sheet we're on in that cycle or in that loop dot name equals sheet. Three. I'm gonna call it Shaped three, because that's explicit trying to exclude shape three. But so this is a logical conditional, that logical statement here. But that's that equals that. So I wanted to win. It's not equal to that. So I just add the word. Not if you think about it when this equals this, but it's the negate of that. So if not, this equals this. One thing I also want to do is just add a couple of new lines. So when we're debug printing into the immediate window, each individual cycle loop cycle has been split by a new line, so I'll just quickly do that now. Eso debug prince after it prints those two lines on a I want a debug print, a return character. And that is vb Uh, CIA. Yes, Phoebe cso. That's just the visual Basic name representation or named representation on the carriage return. So it's gonna copy that line. I see. And I want to actually put that after this line. And I also want to put that after this line. And that's just gonna make the text in the immediate window a little bit easier to read, some going to jump into the meeting window here and delete what we have and I want to play . So I'll just put the cursor at the green brackets at the top There from here. Five. All right, so now if we look in the immediate window seeing the split by carriage returns, Yeah, but something weird down the bottom as well. Um, so she'd want the first iteration of that loop sheet one. This is the default sheet. Name the second iteration where the sheet is. If you've seen the properties window this Andrew on Drew's VB a ninja. But he talks really slowly. Andi, we've got the carriage return again. And in the third iteration with state three. So sheet three. Okay, so as we can see here, this is it isn't technically correct. So we've got the sheet three. And underneath that, it says the sheet name is not the default Excel sheet name. We know it's that's true, but that's actually because it's being court. If you look in this else, time has been caught by that else, all the catchall for every other condition, because it doesn't actually fit in this condition in the first if or doesn't actually fit in that else If Ava. So while that's not technically correct, that's how we've programmed it, which was my dad and the next one we've got Brian. So this is not the default sheet name and the next generation, which was this sheet, Andy. Now that is Hendry's Arabia and injure. But he talked really slowly and last but not least, the sheet seeks, which is Prisa and Broza is not the default, So sheet name. Now, I was trying to investigate what's going on here. This we'd character here, so I might just do that. That and report back in a few moments after a small amount of research appear to have come across the cause, so looks like it's affecting Windows 10 and Windows eight, both 32 bit and 64 bit. And the issue is experienced when using debug print and carriage returns, which exactly what we were doing. As you can see he is. Someone's done some tests, and based on their test, they can say that when you do it this way, it's clean. When you do it, we need another way. It's dirty anyway. That's just to explain what's going on there with those We'd characters we were seeing Yes , back to the idea. Now another way to perform end conditions is actually by what we call nesting. So nesting just means to put an if statement inside another, if statement. So if we were to look at this first if condition here, which is if the worksheet name is Sheet and X, and then we've got the end, which is, and this so the sheet name is not shake three. Then it's gonna catch in here. So let's just quickly change this here to introduce you to nesting ifs was gonna copy that what color I should say been, that's when we can nest. And if yes, we wanted to essentially imitate that same logic weaken nest in here. So think about it. It's like when it reaches this first condition. So if this sheet name equals sheet and X, then and then we've got the next if statement here, which is an end condition, it's not on or condition. It's an end. Because if this is true and then this is true, then it and do something, otherwise, do something else. So they like to paste this in the one that I just cut and paste so that I could get rid of the end. But I've got here. They're not workbook dot sheets ex dot name is shaped three then. Okay, so that's a missed it. If right there, you know, I could just invent that. Remember, I need my indie if Okay, so now we've got a nested If inside this if statement, it's also just add another nested if inside this else statement here because remember, we've got something that was kind of factually incorrect when we were debug printing sheet three. The text claimed that it was not the default Excel sheet name, so it's just let's just correct that inside this l statement. So if w B sheets X Don't name Cols And I'm gonna be explicit in court Shape three and you. But I'm gonna copy and paste the debug Prince diamonds from here because that's what I wanted to say placed him in there. You noticed it that ones just wear the copy and paste. It anyways gets great. Cool. So now we can just quickly running out. It's going to lead what's in the immediately? No, and were Quickly run that cursor here. Here, there. Five. Okay, so this time we didn't get any weed characters. So that's good. So 12345 ago. Six sheets cleared. Let's let's go through this now. So sheet one, we've got sheet one, and this is the default sheet name on shape to which was called Andrew. We're expecting to see yet Andrew is will be an injury touch. Released early third sheet was sheet three. That should catch him this Elson Interstates. The default sheet didn't print anything, so let's just see why why that happened. This is a really good example, because we can figure out the debugging is a really cool part to make sure your car does exactly what you want, But first full delete everything there. I'm gonna move it back. So another. Another interesting thing is, when you're moving card back, you can just, like, select it or hold it down and then drag that because of back to the point you wanted it to to be at if a defied fate. That's great. So get the 1st 1 there. It's a now into the next sheet. The next sheet. We've got number two and number two is Andrew. So expected to go into that second else if or the foot. Sorry. The first else. If the second condition. Cool. Now we're on shape three. Thanks for three. That's great. So we're expecting it's going to this else didn't go into the else. Ah, see why? Because we move this via That's why I didn't go into that else because he had made that condition. But it didn't meet that condition. So this if statement should actually be in else if in this. Okay, try that now. Else. If on Shane X name. Thank you. Sheet three. Want to take this one? Does that make sense for everyone? Because this when we're in shaped three this condition is true, but because we didn't actually have any other rules and it didn't make this condition here , then it just skipped over. It didn't debug print anything. So I can now get rid of that statement in that else. And I should have it here the same. No. So it's just so we're still in was still on three. So I'll just move this curse of back to the first if statement on if eight. Okay, so we should see it jumped to this else. If here Here we go. Cool. Yep. So the next one's Brian. That's fine. Excitements Brian. And the next shape name is Andy. So we see it debug print that silly message and the last one is rising. Okay, there we go. So that's a demonstration off nested ifs, else ifs and also a demonstration off debug, printing and debugging to make sure that your if logic is correct, let's recap this lesson on if statements. So, first of all we talked about what if statements actually are, and that is to allow baby A to execute certain lines of code so long as conditions or certain conditions are true? We did some practical examples of if statements and those statements included else conditions else if conditions and also and or conditions. And lastly, we learned about nesting. If statements inside other if statements, some bonus content or some other things that we actually talked about, there were the worksheets within the properties window and the way that they're displayed. So, for example, if we have a default sheet one, you'll see this sheet object name, which is shaped one. And then inside the brackets, you'll see the text that is stored within the property name, which was sheet won by default. Alternatively, if the name has been changed from default, you will see the object sheet one, and inside the brackets you will see the text that is stored within that name property, which could be, for example, Andrew. We also learned about string concatenation, and that is the act of combining multiple strings into one string. We learned about VB C R, which is the V B A named representation off a carriage return character or a new line. And lastly, we learned about evaluating statements inside the immediate window during runtime. So remember we used the question mark and we pasted in the logical statement of an if condition, which was WB sheets X and name. And because we're in Runtime X actually held a value which was one at the time and on we hit enter and it gave us the true or false. And so we use that to make sure or to see if the logic inner if statement was working. Okay, so that wraps up less than five. Thank you for watching. And I'll see you in the next video. 6. Lesson 6 - Reading & Writing, to & From Sheets: Hello and welcome to lessen six reading and writing to and from work shades. If you've come this far, I just wanted to thank you for watching. I want to reassure you that you are well on your way to becoming a VB a ninja. We are nearing the end off the beginning content and will be soon combining all that. We have learned to tackle some real world examples when I was starting to learn VB a. One of the concepts that initially troubled me was How do we read and write to the worksheets? This lesson will explain this in great detail. So let's jump into the idea in this lesson. I'll be starting a new subroutine within the module one code module. So in this properties window here, just find module one and double click on that. If you're starting from a new workbook, you do not have a module one simply in this properties window, right click, select insert and then module in this example. What we're going to be doing is that we're going to be creating a table within a worksheet and then cycling through the contents of that table and printing some values into another worksheet. Okay, so let's begin by jumping into excel. And I was gonna go into sheet one. The very first sheet. Let's create a table. So in the cell, a one I'm gonna put in first name. So that's the header for the first column. And then the next column I'm gonna put in surname and the next column header will be City and the next column will be country. Okay, so the first name I will have Andrew and surname is Jerrick. That's that's May. And that is Melbourne Mel born and the country is Australia. All right, so that's Row One. And now Row two. That's to Brian Smith from Brisbane, Australia, and next let's to Joan Watson. And he's from that delayed Australia. Okay, the next one we do is Tim Bin it from Auckland, you New Zealand. So that's Tom Bennett from Auckland, New Zealand. You Zeland, I should say, and the last one will be Dale Johnson. Same Chicago U. S. A. Dale Johnson from Chicago, just wanted to apologize quickly for our friends in New Zealand and the USA for my bad accents. Now is this is a table I was going to make it appear such summer's gonna bold the headers the most is gonna make the columns fit. You guys have been read that properly. Okay, I'm also gonna rename this sheet from sheet one going to rename it to Cost start s a customer data and I'm going to rename sheet to which I have currently named. Is Andrew gonna rename that too? That put let's jump back into the idea in toothy module one code module. Let's start building this sub so sub and I'm gonna call this cussed underscore analysis, Think about some of the variables we're gonna need. So we're gonna need the workbook and worksheet variable so dim WB as work book. Yeah, and we're gonna need to work sheets. We're gonna need the work sheet that the customer data is on, which is custody. And we're gonna need the work sheet that we're going to put the output into. So let's do too. Eso deem w s eso I'm gonna preface my worksheet variables with ws throw. I know that that object is a worksheet and I'm gonna call the 1st 1 ws just datta as work sheet and well, I've got a mistake there and the 2nd 1 deem W s at put as work shades McCain L Let's set the workbook variable. So set WB equals this workbook. And if I hit control in space, it's going to intelligence is going to come up, it's going to show me the options. So yep, this workbook and sit w s on. I'm gonna do control space in this one and w status of the first work street because wb dot shrinks And the name of that sheet is cost data and the next one sit ws control space to bring up until a sense. And it's the output. Yes, Apple worksheet equals WB thought shapes, and that one is just called out them. Okay, so now I'm going to be doing a loop in this example just so that I can loop through the rows in the table. Now, in order to do that, I'm gonna use a do want to do until loop. So I'm gonna need an integer, and I'm gonna call it Rhone. So this room number is gonna hold onto and remember, what is the current row in the table that we're on into job? All right, now it's also a sign of number two that so we know that there is a table header in this particular table that will be reading from. So that means that the first piece of data appears on road to so what I'm gonna do is I'm going going to copy this and paste Control V and I'm gonna sit that to two. Because as I said, the doctor starts on row two. Now we're good to start out, do while or do until loop I use a DU. Until I don't want to do is I want to do until the current row or the current cell that were in the Valley was nothing. So if you remember, the table contains values, so does the header. But then the very next row, after the doctor stops the value, we're expecting it to be nothing. No. So let's do that. Do until and now this is where we're going to address the worksheet. So W s daughter based data dot cells That's a child off the worksheet and the row number that we already have is int rhone, um, controls Based that, and the column is one. So that's called a so the first column. So that's an index. So you can't just use, like, column and you can't just use a string. It went That won't work. You need to use the index, which is one not value. So that's the contents of that. Sell the value of that cell. Do until the work shape. Data cell in this position, not value equals nothing. So that's just a empty, empty string right there. I'll just put that loop there to make sure that that's a fully compiled statement. Okay, so what I want to be doing is I want to take data from that table. I'm gonna captain, ate them into a sentence, and then I'm gonna put that sentence in the output worksheet. So in order to do that, I want to make a temporary string variable that I will use in each iteration of the loop s t Ah, Tim String. So I've just declared that there, and then I'm going to use that in my loop. Okay, So now we will be building our string using contamination, and it's gonna be a little different to how you've seen it before. But let's just go right ahead. So s t uh, Tim equals And now remember in the first column we had the first name. So Italy s data cells turn Burnham into runem and it is in column one failure. So now, in that string, we will be holding after executes this line, it's gonna ended that it will be holding the first name. Okay, so now here's where we're going to be building the strings, so it's gonna be continually adding to this str temp on each wine. So how do we do that? We do that by taking what's already in there equals what's already in there and remember an percent. And now because we are building a sentence, we're gonna put our own spaces in there. So we're gonna add a space, and then we're going to add the surname, which is in column two. So might actually just copy that pasting after the M person then changed the column to to. So now after executes this line, it will have a first name held in there and space and the surname. Okay, so we're gonna keep building onto this string now so its teeth are too equals What's already in STN? A tomb and So remember that in problem one, we have the first name in column two. We have the surname and calm three. We have the city because I'll be putting a sentence together. I will make it lives in and make sure there's a space on either side of the lives and the in and dubious doubt her dot cells T cells in their cells child off the data sheet in the road, ears into Rohn. Um, column is the third column off you and now for the lost part of Extreme. So Esteem Tim equals s t temp and we don't need toe save lives in again. So, at a common a space on bond at the country best data dot cells. Which cell is it? It's in runem into drone. Um, and the column is four don't value. Okay, so now I want to write what's in this temp string? In truth, the output shape. So let's do that now, sir W s And it was ws output dot cells. And remember, we're not gonna be putting it into a table. There's no table header, so we only wanted to start a run on one, but because it's we're gonna be out putting the same number that's in our table from worksheet data. I'm gonna be using that same intron. Um, but I'm gonna also minus one. So in a row, space minus one. So that's the cell that I want to put it in, and I'm gonna put it in column one don't Value equals Castillo temp. So yet the output worksheet cell, Which cell is that? It's this index Certs, the Rhona minus one and the first column and the value of that equals str temp. So that's us putting it into that particular cell. Another thing I want to do is I want to now make this null. So just for safety sake, I'm going to because we are looping and we're using this reusing the same variables. So another way off doing that as str temp equals V B no string. So, baby null string is another way of just doing this empty quotes. I'm just showing you another way of doing that. And another thing I want to do is because we're iterating through and we're using this interject as a row counter. I want to add on to that so that the next time we loop, it's moving to the next row plus one. So in runem equals in front on plus one. Run this now eso we'll just step through. It s just the curse of next to the green brackets there and he their fate. So now we have gone past the city set statement. So if set the workbook, we've set the the two work shapes and we're going to be setting the integer as to Okay, now I'm going to enter this do until loop, so because we got to there, we can see what the value of this is. So if I copy this, this is just ah, little debug trick and I go down to the immediate window and I put a little question mark there and hit control V. I can actually pull out what the value of that sheet is provided, Of course, that this work shaped outer is an object that set on any variable is it is a set variable. It's currently in runtime. So because they are, I can hit into. And as you can see, it's pulled Andrew out off the cell that is on the cost data worksheet. Okay, so Let's just continue defending through. So it's building, attempt, attempt, string. So before anything, I was going to go back down to this immediate window and a question mark to interrogate what is happening in S t timpte. There we go. That's what's in this and populate that. Okay, So whilst we are still in run time, I'm gonna bring up Excel. And I'm now on the output worksheet. And as we can see in cel a one it says Andrew Jerrick, loose in Melbourne, Australia, which is exactly what was in the variable. And that's exactly what we wanted be printed. So just keep fading through or stepping through. So we've set the the temp string back to nothing or null as you can see as I hover over. That's got to empty quarts there and I increase the interview by one and we're onto the next one. So for hover over SD attempt, Ryan Smith lives in Brisbane, Australia. Yes, Ron Smith lives in Brisbane, Australia, so I might just F five through the rest. So as I am currently broken, what's paused in break mode, as you can see at the top there, if you are important you want to execute code quickly from, He can just hit a 500 complete the rest of the card for you. So it's done that now I'll just jump back. Yeah, and we can see that. We've got five rows and we've got and Jack lives in Melbourne, Australia. Brian Smith lives in Brisbane, Australia. John Watson lives in Adelaide, Australia. Tom Units lives in Auckland, New Zealand, and Dale Johnson lives in Chicago, USA. I just wanted to run over a couple of common mistakes that VB A developers often make, and you will definitely run into this in the real world. So what I've done here is I've taken the functionality from the routine that we've just built, and I've replicated that using the methodologies that developed is often used in the real world that are problematic, especially when reading and writing from the worksheet. So you'll notice here there's a lot of select methods and there's active cell methods, and the reason why these are problematic is as I've explained before, is that it relies on the object that is currently in focus, and I've got excel up next to it so you can see so what I mean by focuses the area that is currently selected or the cell that is selected, or the sheet that selected. And if you're using that encourage, that becomes problematic. If the user just randomly clicks a cell or opens a new workbook, which then becomes the active workbook because the code is non explicit, you're going to run into problems so the macro will leave it read from the wrong area or write to the wrong area or just fail in general. Another problem with these methodologies, the in particular the select methodologies is that you're actually automating three user interface. So you are thinking about how to do a task the way the human would do it, and that's not necessarily the best way to go about it. Sometimes you can't help but do that, but in these instances you don't necessarily need to select the user interface to see what's going on, because you can explicitly just refer to them as objects because they exist in the run time . And another reason why these select methods are non ideal is because the amount of time that you're actually introducing to the macro so when we've got a table of only you know, five rows. That's not so much of a problem. But if you're iterating through a table of thousands of records every time you hit, select that time wasted and it's time wasted because you can actually refer to these objects programmatically. So. Therefore, if you use the methods that we've just learned in the cost analysis subroutine that we've just built, you can actually build Micro's that can run in the background while people that doing other tasks. So one of the things that really frustrates me in the business world is that I see people running macros and they walk away from their desk and on. I asked him Why What are you doing? I'm using a macro, and I can't use my machine. Or why not? That's because it fails. If I do other Tark's or it consumes so much resource is that I can't do anything else and excel. So anyway, let's quickly run through this bad example. Line by line. I've got Excel open right next to the I D. E so that you can see the programmatic automation off the user interface and why that could be a bad thing. Let's begin here. Their fate. Okay, so the first line here is that it's jumping into the output. Worksheet similar to the previous example, however, have actually just set the object in the in the good example. And here we're actually selecting the object. If you'll see the Excel now will jump to the next the next sheet. And so what we're doing here is we're finding out if there's any data already in this column here, it's going to find the last row so that you don't right over any off the existing records. And that's a really common one. That really frustrates me when I when I see my crows in the real world. So yep, that's great. So now it's jumping back into the custody a sheet you can see. It's jump back there, and now it is selecting the first cell of data. As you can see, it selected that cell there. Now it's very similar to the example that we did before, which was around do until there is no data lift in the table, but it's using active cell as opposed to an explicit sell. So if eight so yet that's running, It's going into the do loop, so it's building for the The string is an example. It's building with string in a similar way. However, it's using active cell offset so active selloffs it literally means that. So it's the cell that you're currently on, offset by number of rows or number of columns. And so because we grab a one active still offset 01 would be sell be to. In this example, it's now it's jumping back to the output shape and in the active cell it is pasting in, or it's entering inthe e value that is held within string temp. You can see it's just done that there. Now it's gonna offset that by one and select. So it's it's offsetting by one row and selecting that. So it's going to the next cell, and now it's jumping back to the past data sheet, and it's doing the same thing. So it's gonna right down to the next cell, and then when it evaluates on the return here of the do until it's still not blank. So it's going to keep looping until it is blank. So let's just quickly to that select paste next. Okay, so very similar functionality. It's just not ideal and problematic if you use these methods because you're just introducing so many points of failure, not only points of failure you're introducing, you know, extra time stability issues. You're you're taking away time from the person that's actually running the macro, because the whole idea of automating something in Excel is automating that repetitive tasks so that I can do other stuff. Let's recap what we've learned in this lesson. We've learned about reading data from cells within worksheets, and we've learned about writing data back to the worksheet. We built a subroutine that picked out of out of a table on one sheet, use that data to construct sentences and then write the sentence onto another worksheet. We also learned about common mistakes that VB a developers make out in the real world. As always. Thanks for watching, and I'll see you in the next video 7. Lesson 7 - Functions: welcome to lessen seven functions. This is the last listen of the beginning content. If you've made it this far, congratulations. You're well on your way to becoming a VB a ninja. In this lesson, you'll learn about VB. A. Functions will discuss the common practical use of functions. Will learn about sending data to and returning data from functions. And we'll build an example function based on the subroutine that we've been building over the last couple of lessons. What are functions? Functions are similar to subroutines. However. Functions can return values. Think of a function like an Excel formula. You can send it inputs, it will perform a calculation, and it will return you an output. But you build a function from scratch as opposed to it being built in like an excel formula . Functions become increasingly useful as your VB. A program increases in size and complexity. A couple of example use cases for functions might be performing a calculation on data that is sent into the function and returning a value back or taking unstructured data, reorganizing it and returning structured data or even just building a custom excel formula Another. You're itching to start cutting So let's just jump back into the I d and dive headfirst into this. Okay? Back inthe e i d. Within the code module. Module one. We are going to be working on the cussed analysis subroutine that we've done. And don't worry. I've commented out the bad example of cost analysis. It won't be bothering us anymore. Thanks, Andrew. And that's a great segue way into her first piece of bonus content comments. Okay, so in all seriousness, comments are very basic. Comments essentially are just pieces of text that you can put into your code that the interpreter ignores. Simple way to comment out entire blocks of code or re comet in the back in is using the comment block and a NCAA meant block from the toolbar that we added before eso here of just on commented and you can see that the interpreter will actually see this code as as valid vb a code. But if I select all and I comment them again or comment that block, you can see now that it just knows this is a piece of text or thinks that this is a piece of Texan is not going to actually run that. But another thing that I'd like to mention is it's good practice to comment during your code so that if someone else is reading it, they know your foot process around design decisions, etcetera. So, for example, just doing here, open the comment with a single quote and I am now seen the setting the workbook work shared objects and then this is a good example. So here, if I say ingrown, um two equals two, someone might be going wise it to, and I could just say, two is the first row of data. Really straightforward. OK, all right now, back to the lesson, as I mentioned in the intro of Function becomes more and more useful as your program increases with size. And what I mean by that is that you can keep your main subroutines nice and lean, and then farm out all of the repetitive tasks toe functions that form the function for you and then return back. What you need an example of that in the sub cost analysis that we've just build is this, which is theme building off the temp string now, as opposed to topping out these four lines on a single line. What we could do is we could send the values that we have from the worksheet into a function and then have the function return the string back to us. So let's do that now, just for the sake of demonstration, I'll just write the function directly underneath the sub that we've just built similar to a sub. You just type function and what we're gonna call this function? I'm gonna call it get sentence and we wanted to return a string. So think of this like a declaration statement as string. So its return this function is going to return a string. Okay. And now you may have bean wondering why we have brackets beside either the sub and now the function. And that's because we can actually pass arguments into a subroutine or function, and they go within these brackets. So this function, what we want to do is we want to send the information from the work shape. So we had the first name. We had the surname, we had the city and we had the country. So we can now put that in as an argument into this function. So you write them out like declaration statements without the word team. So first name and what type is it? It's a string. Soon I am a string city, a string country, a string normally all would prefix my variables with the the term str and the reason why is because we want to avoid using reserved words and what I mean by that. So reserve word is like, for example, string or interject or this workbook. So if we have our own naming convention for variables, there's no chance of us actually interfering with the's reserved words. So the logic inside this function is going to be fairly similar to the logic that's inside the that do until loop that we built just before eso we're gonna need a temp string first. So I'm just declaring a temp string. And how much is copies, actually, and then just replace what I need so to that. So the 1st 1 was string first name, So copy and paste that here and then six Juanma string surname apologies. If you can hear my neighbor's dog, some neighbors out of the moment and it's just happy that it's outside the poor little dog . Oh, and then we had the city and we also had the country. Okay, so this is actually going to do exactly the same thing. I've just replaced the reference to the string from the cell value and replace that with the actual variable name that with the we've sent into the function Now, how do we get that out of the function? Well, it's actually pretty easy. So if the functions cold, get sentence, right, So I'm gonna copy that, and then I'm gonna pace that here and then say, Get centers. Do you know what that equals? That equals str temp, which is what we have just been building, and that's it. So when we enter the function, it will have all of these components unless we have declared them is optional, which I'll talk about later in the light of lesson. And it will combine the strings that's doing that repetitive task. And after it's generated that sentence, it's finding that value of the SD attempt back to the get sentence function variable, and that's returning that out of the function back into this piece of code. So now that with built have function, let's go and put that into our subroutine. Now back in the custom analysis subroutine actually replicated this functionality here. So I'm just gonna comment out these lines. We don't need them anymore. I haven't elated them because I kind of interested in still grabbing these when I need them . So what we need to do now is we need to populate the cells on the output work shape, which is this line here now, because there's nothing in ste temp. I'm gonna delete that for now. And what I am going to replace it with is the function. Get sentence. Now, As you can see, Intel, a sense already knows that we have declared a function and it's helping us complete what we need to enter here. So, as you can see, get sentence. It's your first time a string STS surname, a string as to a city, a string and str country a string. And if we weren't familiar enough with what we needed and how we get it, we can actually just right click on this and select definition and will actually bring us there. So if this was in another code module or or miles and miles away with hits occurred in between we could just right click on that and said definition it would bring us into the function that we that we were interested in so that we could actually read it. Okay, they might be comments at the top that says, um, there might be comment says has constructed sentence from first time. So in in city or country so that we know that that's what this function does and they would jump back in there. Go. Okay, so now I know what these things mean. Intel a sense. So, like the function arguments that separated by commas. So let's just do that now. So let's grab the 1st 1 So the 1st 1 is the first name, So I'm gonna put that here. And so we've put the first name in the first argument coma. And you can see now that the 2nd 1 is old. So it's saying this is what you need to enter now, So STS surname. So we're looking for a surname, and that is this one here. I'm going to copy that, um, coma and as he a city a string so that waas right here and the last one is country which is . Yeah. Okay. So if you've made a mistake here, you will see this whole line selected and read or you need to do is click in the curse of here and press space and then make sure that everything kind of evaluates right. So make sure that this is a full argument and then a comma and then go to the next argument . And then So if you're in here, as you can see, it's bold in the 1st 1 Now that I'm past that comma, it's bolted the next one so you can see where you're supposed to bay, and it becomes more confusing because our actual arguments that were sending horrid you have brackets in them. But that's just the night trouble. You'll get used to it very quickly. Now we just need to toddy up the custom elza sub before we run it. So it's just kind of go through everything so we don't need the str temps. So I'm just gonna first of all commented now, actually, I just deleted actually that we need that we need that I need the intra numb because we're still suckling through. We're going to need the interim for these arguments within the function don't need that anymore. I missed the obvious one. We don't need that, that's for sure. So now out do until loop is essentially two lines of code. I am. So as you can see it, it's kept our separating nice and neat and then weaken. Outsource all that the grant work, if you will, to these functions here, you even keep your functions in it. In their own code module, See might have a subroutines or a main road module. And you've got a module that stores all of your functions. But I think they're pretty good. So we run it. Hey, let's run it. I was gonna if eight step through the code. So, yeah, we know what that's doing already from all the times that I've stepped through them before with you. Sorry to bore. All right, so now we're going to interact loop, and we should see this jump to function, which it didn't it wasn't any errors. So you might see an error if you send something of the wrong type or you send nothing when it's expecting something. Yes. So let's say, for example, I send it a Boolean value or an integer actually introduced should be okay because it can easily do a top conversion on that because and interject can also be a string. So the difference between an Inter jurors like one and also the same thing as a string is one anyway. Now we're enough function. If it and it's doing the same thing Now let's hover over there. Yep, Andrew Jack lives in Melbourne, Australia. That's going Teoh. Enter that back into the get sentenced function variable, and it's written that already to the worksheet. So if I just bring up Excel, Hopefully, I didn't clear it from the last time we ran it. So I'll just quickly on and bring it back a line and then, if eight and bring up Excel again, moving here soon enough to do too much editing. Yep, Seeley, it's added it back into the output worksheet, So I will just hit. If I had a bunch more times, I might just hit at five. And here we go. All right, let's bring up Excel. Boom! There we go. Garden, as we expected. I want to show you something pretty special. So now that we've built that function that functions actually visible globally from this entire workbook. So what I mean by that is that we can Let's say, for example, now I want to use this in Excel. All I need to do is type equals. This is how we usually tell Excel Ham, topping of formula. Wow, You can see my formula there or you can see my function. So now that we have open bracket, tell us now doesn't give us until a sense, but anywhere we know that we need this one. Onda, we need this one, and we need this one. And we need this one. And there was close at records. Hang. There we go. Look at that. Um, the more you know what? People don't actually know this one, but yeah, this is a really, really handy one, if you like. I have to build a function that does some weird calculation that isn't part of the Excel Library of Formulas and has this weird, specific calculation that's tricky to do. And I need to do it across a whole bunch of cells. Just build a quick function and you can use it in Excel as a formula. Isn't that cool All right, so let's recap this lesson. So we learned about what functions actually are, and we learned how to go about returning values from and sending values to a function. We built a quick example function that took the imports from the spreadsheet and then concoct in aided them into a sentence, returning that sentence back to the sub. Lastly, we learned that we can build our own excel formula by building a function procedure. Anyway, thanks for watching. I hope you had fun and I'll see you in the next video.