VBA - Beginner To Ninja - Intermediate Series (A) | Andrew Jarick | Skillshare

Playback Speed


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

VBA - Beginner To Ninja - Intermediate Series (A)

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

8 Lessons (2h 49m)
    • 1. 01 - VBA Lesson 8 - Intermediate Content Intro

      1:56
    • 2. 02 - VBA Lesson 9 - More Workbook Functions

      20:53
    • 3. 03 - VBA Lesson 10 - File Dialog Boxes

      19:23
    • 4. 04 - VBA Lesson 11 - Run On Events

      20:04
    • 5. 05 - VBA Lesson 12 - Dealing With Data

      27:47
    • 6. 06 - VBA Lesson 13 - Arrays

      32:15
    • 7. 07 - VBA Lesson 14 - Dates

      25:07
    • 8. 08 - VBA Lesson 15 - User Forms and User Experience

      21:36
  • --
  • 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.

65

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. 01 - VBA Lesson 8 - Intermediate Content Intro: Welcome to the next phase of becoming a Visual Basic ninja musta. My name is Andrew Derek and I will be your instructor. If you are new to VBA, I suggest that you start with my begin, of course, because we're about to jump right into some topics that will require some prior knowledge. Simply refer to my skill share profile page to find the link to the beginner course. For those of you that are ready, get comfortable open Excel on your computer. And let's begin. I will begin by stating that the structure of the intermediate content will change slightly from the beginner course. Begin of course with structured in a way to cover the basics along with some additional content in each lesson to ensure a nice broad understanding of VB I fundamentals. With the fundamentals covered, the intermediate content will be spanned across multiple courses. As you can imagine, there is a lot to cover. While I recommend you learning and understanding all of the topics covered, this approach provides you the ability to learn the things you want to selectively. This may be because you are faced with a particular problem in the workplace or out of curiosity or desire for mastery. As with the begin, of course, I will continue to address the common mistakes that VB I developers make and what the best practice methods ought would shave the desired outcome. Ultimately, I want to help making your code efficient, robust, and provide your end uses a compelling experience. In this course, we will cover more critical workbook functions, had a Create File dialog boxes. How to make code run on specific events, will talk about dealing with datasets. And I'll introduce you to one of the most powerful functions in VBA. Arrays will also cover multiple ways of dealing with dates and will begin to delve into user forms and user experience. Now let's begin. I'll see you in the next video. 2. 02 - VBA Lesson 9 - More Workbook Functions: Welcome to Lesson nine, more workbook functions. In the real world, you will need to know these critical VBA workbook functions. In this lesson, you will learn about opening, closing, and saving workbooks programmatically, creating a full file path when it is I'm known from scenario based business logic and assumptions. You'll learn how to deal with pop-up messages when you programmatically open a workbook and writing code to open password protected workbooks. You'll learn how to load enormous files instantly. And I'll explain the optional arguments for the workbook open command. Let's move on. Let's consider this scenario. You are asked to write a macro that needs data from an external source. The data is automatically produced at a specific time every day and it is deposited into a specific directory. So that is a known, that's a constant that's not going to change. The file will always be deposited into that folder every single day. The fall that is automatically generated has a specific naming convention and that is year, month, day and year being full character year two character month to Character Day on the school currency conversion dot csv. And this type of naming convention actually is pretty common with these automatic jobs that create files. And that's to avoid the Fall being overwritten by the next dies file. And there actually are the uses that do use these files. So that means a couple of things. That means that we can't edit the file or delete the file. We can't move it and we shouldn't keep the follow-up in too long just in case that locks others out of the file. And lastly, the team that you're in also requires a copy of the fall for their own purposes. So we need to make a copy of that file. So let's quickly take a look at the filing question. I'll just bring up my documents here. Okay. So this is the file. I'll just open that quickly. Okay, here we go. As we can see, it's a fairly simple file. It's just three columns. We gotta dates with Got a country and we've gotta values. This is currency conversion values. You may also notice that the file type is a CSV or a comma separated value. Csv files are actually just text files. However, they can be opened in Excel because it CSV file basically stores a table of data. I'll just quickly close this and open it in Notepad just to show you didn't save. I bring this back up here, right click open with. So this is what the file looks like, opening it in notepad. So at first row contains our hitters. And as you can see, h value is separated by a comma all the way through. I'll close that. So the first problem that we need to think about is that the file name changes every day. However, we provided enough information to be able to dynamically create that file name and open the file provided that it has been created automatically at the specific date and time. Normally in the workplace with these types of jobs, the falls are deposited on a network share drive. However, in this instance it's just going to be on my local say drive in my documents folder. But that's fine. The concept is still the same. So I'm just going to start with a constant because we know that the file is going to appear in a directory every single day at a specific directory and that directory is not going to change. So that's just my documents folder. As I said before, these are usually on network shared drives, but the concept is the same. So the purpose of using variables to create a filename is so that we can concatenate these strings together to create the full path and file name so that we can open it up. As such, it's important to consider what the fall man will look like once you've concatenated all these strings together. What I mean by that is looking out for these directory backslashes and make sure that you actually have backslashes where they should be one. So the next constant that I want to create is this part of the file name, because we can dynamically create this based on the current date. I'll just use this in a constant because this part's not going to change. Also. We'll copy that. Alright, so let's now define our sub-routine. I'm just calling it open data file. And let's think about some of the variables we're going to need. So we are definitely going to need a workbook object, specifically the workbook that we are going to open. We're also going to need the current workbook because we might be taking data out of the one-way opening and depositing into this one. And additionally, we're going to need the worksheet objects for both the workbook that we're going to open and the workbook that we're going to write the data into. So now let's work on how we are going to create the filename, which we're expecting it to have this date format. So the year in full characters, month into characters, day into characters. How we can go about generating that so that we can create a full file path and file name. I'm just gonna drag up the immediate window for second. Vba has some built-in functions that can return us the current date and time. Typically use the now function. So let me just interrogate that function. As you can see, the NOW function has returned dice and the current timestamp. It's not exactly in the same format that we made. However, you can actually use this and coerce this into the format that we require. And let me show you how to do that. So I'll just delete that line. Then. We can use the format function. And as you can see, it's asking us to provide a document which is expression. So the thing that I want to provide it is the expression now, which is going to return the date that we just saw before. And the next argument is the format. So this is very similar to the Excel cells formatting structure. We can basically just specify the format that we want the height to be returned in. So I, that's why, why, why, why m, m d d, but close bred and hit enter. You'll see that it's provided us the date in the format that we require. So the year, month, and the day, you just drag that down. Okay, so we now have enough information to open the workbook that we want to open. So the way that we opened a workbook is with, first of all, the workbooks collection. And the workbooks collection has a child method. Cold open. And then you can supply that a bunch of arguments. But we're actually able to open the workbook and at the same time sit that object to an object that we have defined here. And we'll just use that in a set statement. Just close as brackets, so it's not upset with us. So essentially the syntax of this is that we are setting the object and with specifying which objects we're talking about. And that is the one that we are going to open. So now let's supply the file name, which will be the full file path, the date, and the filename. And we use ampersand concatenate strings. And now I need the date, so moment. And now I need the rest of the file name. Now as you can see, the file name is the first document and there are many other arguments after that, and the arguments that after that are in square brackets. What this means is that they are actually optional arguments, and I'll go into what these key arguments are later on in the lesson. So next what I wanna do is I want to take this workbook that we've just opened, and I want to cite my own copy of that. So now that I have my workbook object, I can use one of the methods of that object to save and save as. And I'm just going to put it in the same place. So in the same directory that we have just taken it from, Normally, I mean, you would probably have this in a different directory, but just for the sake of demonstration. So I'll just use that again. And I'm going to save the file in a different format to the one that we have opened. So that's a CSV with this naming convention. So I'll change the name and convention and fall type. So first of all, I want the date the other way around. So di, di month, month, year, year, year, year. And now we also need to specify the file format. And I can do that by typing Excel workbook default or Excel workbook normal. I'm going to use default. And that's all. So let's run this to see how we go. If adding through that suddenly happened, if I hit FI one more time. Okay. And I produce finished. So let's bring up the fall explore to see what's going on. So we can see that we have SI fall here, which is great, which is the format and the file name that we have designated. Let's open that up to have a look. It's already open. We forgot to close it. There we go. So we've opened the file and we've changed that file format into an XLS x whip book. Let me just open also the currency conversion c is v, so that we can compare them side-by-side. It perfect. So I can go ahead and close those. There's one more thing that I wanna do is close the workbook after website, it changes false. So let's run this again and let's see what's going to happen. If we have a workbook that already exists with this file name which won a save as over the top of it. So I opened it. Let's have a look. Yep, that's the CSV. And now we're going to save as. So it's provided us an error message box. And it's asking us, Do we want to overwrite side note for now, which has given us a debug area because it wasn't able to save, which is going to end up in that and close it again. So obviously this isn't ideal in terms of VBA automation because these pop-ups will hold the execution of your macro and will require user intervention to dismiss them. Luckily, we are able to suppress those types of pop-ups. And the way that we do that is by setting the display property which belongs to the application object. The application object being the instance of Excel, which is the parent for the workbooks collection object. So if I type application and then display alerts, you can say that method pop up there equals false. What that means is that while code is executing, it will suppress the alerts from displaying. And it will automatically select the default option. But just remember to always set it to true after you've finished running. So let's try that again now to see if we have suppressed that pop up box. So as you can see, we have finished executing the code and we didn't see a popup, which is great. And if I just open up out File Explorer, we can see that we have a new file, so it's saved over the top of the existing fall that was there. Which is great. So I'll just jump back into our code. Okay, so now let's go through some of the additional arguments we can send the workbook open and workbooks Save As command. Now I'm not gonna go through all of them and kind of go through the ones that I find are critical to know, which may be helpful to further suppress the alerts that are shown during an open or save command. All that may be helpful in automation in general. So first of all, after the open method, we have got our opening brackets and then we've got our arguments that we can send it to. The first one is obviously filename, and that's mandatory. You need to specify what, what book you would like to open. And we separate the arguments by commas. So as you can see, as I start typing commas, the argument that we are referring to becomes bold in intellisense. So that's a given we need to separate our arguments with commas. So let's say for example, I wanted to set the argument corrupt load. One way to do that would be to hit coma until corrupt load is bold, which is there. Or what I could do is I could just say, first of all, separated with a comma and then actually taught the corrupt load argument. Then with a colon and an equals, and you'll see that now the corrupt load argument is bolded in IntelliSense, meaning that anything I type here is for the argument corrupt load. So you can actually specify the order of the arguments, or you can just use the comma option to specify which argument you would like to supply. So the first one is update links. Now, if you've opened a workbook in the workplace and it contains links to external sources, whether it's another workbook or a data set. You may see that message pop up that says, would you like to update the links to this data? Now, obviously, I'm opening a workbook programmatically and it does contain links. It's going to display that message, which is going to hold my macro and I don't want to do that. So you can actually specify false, which will prevent that message box from appearing. The next argument is read-only. What this does is it specifies that we opened the workbook in read-only mode, which is pretty self-explanatory. And this can be useful specifically for the scenario that we have just built in that we don't want to edit the file that we are opening. We just want to grab data out of it and close it quickly. And also can prevent fall locking, which can be a hassle in the workplace environment with multiple users using the same file. The next one is format. And this is used when we're opening a text file such as a CSV. But we want to specify that the file delimiter or the character that specifies the separation of h field is different to a comma. So it could be a tab, it could be a space, it could be a semi-colon, or it could be accustomed character. And this is an enumerated list, meaning that you need to supply a number and you can actually just Google which number you need to specify for the top of faulty opening, but I'll just quickly go through them. So one is for tab separated delimiter is a comma separated delimiter, which is the default. Three is for space, four is for semi-colons, five is nothing, and six is the custom character, which you will need to specify the delimiter fall. The next one is password. So again, in the workplace who may have opened a workbook that asks for a password when you're opening the workbook. And obviously this is another failure point for automation because it's prompting the user to specify a password. Now you can actually supply this password. Let's save the password is just password. You can actually just specify that as a string in this argument. So if your workbook does contain a password, it's a good idea just to specify that in a constant, similar to how we've used the constants for the file path and for the file name, so that you don't have to just talk that at every time that you opening or closing the workbook, the next one I want to talk about is delimited. So if we've specified that it's a custom delimiter in the format argument, we need to specify what that is. So it could be lack, it could be a double quote or it could be anything. It's just the character that's used in that file that specifies that that is a delimiter for the next field. And the next one I want to talk about is corrupt load. So this is extremely useful. And in the workplace falls a commonly stored on file servers, even your own My Documents folder. And if your workplace has many uses and your dealing with large Excel files, this can obviously cause problems. In my experience, I've seen workbooks take two to five minutes to open. And that's due to a couple of things. First of all, it's due to network bandwidth. So if you've got lots of users on your network, there isn't enough bandwidth to supply the file quickly. But the main culprit for slow loading files other than bandwidth on the network is unnecessary formatting. I've noticed that some users like to fill down formatting across an entire table or even to the last row in the workbook. And this not only makes the workbook incredibly large, it makes opening the workbook very slow because Excel needs to complete a lot of tasks before actually presenting an opening the file. So what can corrupt load do for us in the instance that we have workbook that takes a long time to open, what I will do is I will specify that it's radar only, but I will also specify Excel extract data in the corrupt load argument. And what this does, it will ignore all formatting and we'll open the fall as fast as it possibly can from the file source, meaning that as fast as it can be read from the network, it will be opened. And it will ignore all the formatting and just open it so that you can quickly jump in there, extracted data and jump out. Ok, so I'll just delete these now. And now let's talk about the arguments we can send to the Save As command. So the first one is file name, which is fairly obvious. It's just the filename that we specify that we want to save the file as. And the next one is file format, which we have quickly covered. There are numerous options available here. So if you have a specific requirement for a file to be saved in a particular format, then just google the XML file format enumeration and you'll see a complete list of the file formats that you can export the file to. The key ones here though, Excel workbook to fault, which is XLS X, you can also use XML CSV. Another really common one, at least in my experience, is XL excel sheet, which is for backwards compatibility with Excel 2003 back to 2007 workbooks. So that's just the dot XLS file format. And the next one that I want to talk about is conflict resolution. So this is what happens if we have multiple uses in the same file. And it specifies which changes should be prioritized to be saved. So the default is Excel USA resolution, which isn't ideal in automation because it asks the user to resolve the saving conflicts. The other option is XL local session changes. So what that means is Excel will prioritize the local uses changes over any remote uses changes. And the other one is Excel session changes. So that's the opposite of the one we've just covered. Which means that Excel will prioritize the other uses changes over our own in this local session. And so that sums up the key arguments that I wanted to cover in this lesson. So that wraps up listen nine. In this lesson you learned about opening, closing, and saving workbooks programmatically, I showed you how to create a full file path for a file based on business logic and assumptions, we learned how to suppress pop-up messages while running code, and how to open a password protected workbook. I showed you how to quickly load files that normally take a long time to load. And I explained the key optional arguments for the workbooks dot open command. Thanks for watching, and I'll see you in the next video. 3. 03 - VBA Lesson 10 - File Dialog Boxes: Welcome to lesson ten, File dialog boxes. A File dialog box is a built-in feature of Office applications. In a nutshell, it's basically a window that presents to the user with a view of the file system so that the user can select a file or directory for the purpose specified by the application that cold it. An example of a file dialog box is the window you will see when you select File and open from any office application. In this lesson, I will teach you how to create File dialog boxes. I will explain the various properties that we are able to sit to customize our file dialog box. I'll show you how to deal with the response returned from the USA. The additional content we will cover includes with statements, the built-in VBA DIR function and the built-in VBA LAN function. Let's kick on. File dialog boxes is something that I often use when building VBA applications. The purpose of this is basically to prompt the user to locate a file or folder. Just a couple of scenarios where you may use. One could be to ask the user to locate a file that you'll be extracting data from or where the user wants to save a file, how and where their uses really up to your own imagination using the scenario in the last lesson as an example, which opens and saves a copy of an external workbook, you could attempt to automatically locate the file as we know the directory it is supposed to be in and the naming convention of that file name. And if it is unsuccessful, then prompt the user to locate the missing file. So let's try and achieve just that. Looking at the routine from the last lesson, we want to attempt to open the file using the logic we have already defined. And if that fails, present the user with a file chooser. There are a few ways we can do this if the file doesn't exist and VBA attempts to open it, that would force VBA into a runtime error. In that instance, we would need to write an error handler so the VBA knows what to do when that specific error is encountered. However, we will be covering error handling in a lattice session. And also, I feel like there's a better way to achieve this, as opposed to just trying to open a file that may or may not exist, which would cause an era. We could first test to see if the file actually exists before attempting to even open it. That way we are preventing a runtime era from occurring and the need to cater for that era. So in summary, what we're trying to do is we're trying to open the file automatically. And if that file doesn't exist, then we will prompt the user to select the file to open. Now let's talk about how do we determine if a file exists or not. Vba has a function we can use to achieve this and it's called D. Let me bring up the Immediate Window. So the DIR function, we'll test to see if a directory exists. And if it does exist, it's going to return the file name as a string. And if it doesn't exist, it's going to return a 0 length string. So the file that we used in the first example, I have just copied the file path and I'll just supply that as a string. And we know it exists because we've used that in the last lesson. So if we hit Enter, we can see that it's provided us back the file name. Now if I was just to change one character here. I know that this file doesn't exist, and if I hit enter, you'll see that it has provided us back a 0 length string. So therefore, we are able to use the DIR function to determine if the file exists or not. Because if it does exist, it's going to return a string that is greater than 0, or the length of that string is greater than 0. And if it doesn't exist, it's going to return a string that has the length of 0. So just move the immediate window out of the way. Let's get started. So in order to validate what the DIR function returns, I'm just going to use a basic if statement. Wait a minute. I'll just bring the immediate window back up again. I'll just point that out for 1 second, and I'll just show you the length statement as well. So if I wrap this function inside another function which tells us the length of a string, and that function is called Lin. It's counted 31. So that means that inside this string there has 31 characters. Now if I change that so that it doesn't exist, the file doesn't exist. It's given us a length of 0 characters. So inside that if statement, we're able to use this len function and wrapped inside the len function is the function. And if the file doesn't exist, we're going to get 0. And if the file does exist, we're gonna get something greater than 0. So I'll move that down out of the way again. Un-comment this. If, so, if the length of D and the path is the, okay, so that's how we wrote this if statement. So the first function there is the length, which is this, everything inside these brackets. And if we hit spice in here, it will show us the expression in the middle. And the expression in the middle is this one. So if the length of what is returned from D, if that length is greater than 0, then we can go ahead and open the workbook. So I'll just copy this copy. So I should say. And I'm going to put that here. Otherwise, do something. So what do we want to do if the file doesn't exist? We want to prompt the user to select the file. So in order to do that, we're going to need a file dialog box. We're going to need a variable for that. So it's, it's on top, which is pot of office or it's built into Office, I should say. They go File dialog. Now, this is just a generic object that is blink. Therefore, we need to set that object. So I'll just do that now. Now the File dialog box is a child of the application object. And as I mentioned in the last lesson, application is apparent of the workbooks collection and the workbooks objects. So I need application, I've spilt that incorrectly. Dot file dialogue. There are a few different file dialogue types. And luckily it tells us which ones we have at our disposal. So we want a file picker. But the other types that we have there, there's a file picker, uh, folder pika, and open dialogue and a Save As dialog. But I want to file picker. So once the code has executed this line, it has sit this one's blank object into a file dialog window that belongs to the application. And at the top of that file dialogue is a file dialog, file pika. There are a few other properties that I want to sit. So for example, I don't want to allow the user to select more than one file. I don't want them to select a file that isn't the right file type. And I also want to help the user by starting to look in a particular folder. So in order to do that, we need to sit more properties of that file dialogue object. And to do that I'm going to use a width statement. So basically a with statement allows you to sit multiple properties at the same time. So I'll just set that up. And similar to an if statement, the width statement needs an end statement. Now inside this width statement, we can sit multiple properties at once. Another thing too is that inside these with statements, we don't need to again declare which variable which talking about. So for example, if I want to set a filter, which filters what type of files can be selected by this File dialog box. I don't have to first say the object name and then the property. I can just start with the property or the dot to the first property that I always amend when I'm sitting up a file dialog box is I cleaned the filters. So I feel this is a child property of the file dialogue object, and clear is a child method of the filters property. And so the reason why I do this is because filters for these objects are stored in cache. Meaning that if I sit a bunch of filters and then I hide that dialogue box and then I showed again the filters from the last time I've shown the dialog box present in the new one. So it's always just a good practice to clear the filters. And the next one to add is to add a filter so that we can select a CSV file. So again, we're using the filters property. But this time I want to add one. So there's the add method. Now it tells us what we need to supply. So we need to supply a description as a string and extension a string and a position. So description is pretty straightforward. It's a string that you can type just to describe what you allowing them to select. The extensions are the file extensions or the characters that appear after the dot. And the position is if you've got more than one, which position in that list should this list item B? So let start with CSV files, the description and the extension. I'm going to use a wildcard here. I'll explain why in just a second. So what a wildcard does is it tells Windows that I accept any character before this character. So what it's gonna do is it's going to look for the patent of any character or any characters, and then dot CSV, which means that it's going to filter out everything that is not a CSV file. I also want to prevent the user from being able to select multiple files, even though they can theoretically select multiple files in this instance, I don't want them to, I only want to allow them to select one file. So that is. Multi-select false. So I'm setting it to false. And to help out the user, I want to make sure that they don't have to hunt around for too long. So I've got an educated guess that it's going to be in my documents folder. Therefore, I want them to start looking in that folder. And in order to do that, I can say initial filename equals and I'm just going to supply that constant. So what that will do is it will open the window and it's going to be in that folder. And so the last property would be dots show, which we'll present the file dialog to the user. So there's a catch here. The catch is that the form that is displayed, which is the file dialog box, is a modal form. So what that means is when the form is displayed and present on the screen, it means that no code can execute. But that's okay. We can actually use that to our advantage. So what I'm going to do is I'm going to put this dot show property inside an if statement. So what this means is when it evaluates this line, it's actually going to show the file dialog box to the user, but it weren't continue because it can't, it's modal. Therefore, no code is executing in the background. However, once they've selected a file and the heat, okay, as long as it gets past this line, it means that they have not only seen the form, they have selected a fallen hit OK. And if Show equals false, then that means that they've actually hit Cancel and have dismissed that file dialog box. So we have two conditions here. We have if they've selected a file or fifth hit OK. And if they've cancelled out of that file dialog box, if they've selected a folic and go ahead and arpanet as we've done up here. So I'm just going to copy this line. Probably easier to just to top it that again because I'm going to be changing it, but that's okay. And I'm going to remove all the stuff inside the Open arguments brackets. Now, what do I want to supply? I want to supply the items that they've selected. So that is dot selected items. Now, because the font dialog potentially can allow multiple selections, this object here will this property is a list, therefore, I need to supply the index. And because of on the allowed them to select one, the index is only ever going to be one. But if there were, for example, multiple, you would have to then loop through them and perform an action on them one by one. So that's good. If the show is true, that means they've seen it, they've hit ok. Therefore, i want you to open the file if selected. Otherwise, if they've hit escape or cancel, I want to present a message box. Not really having a good time with his Mac keyboard. And I also want to exit the sub. So Exit Sub is a little different to N sub. N sub being the final termination point of the entire routine. And Exit Sub is just a point where you can get to that, we'll say, okay, I've had enough. I'm going to exit the sub gracefully. So that means that if we've exceeded this if statement, it means that either the file is existed and we've opened it, or the file has been selected and we've also opened it. We're not going to be able to get to this point by any other means because we've exited the sub before it can get to that line of code. So that means we can perform the same actions that we were doing before, which was save error in version. And close the workbook and then turn the display alerts back on. Okay, so we're almost ready to test this. What I'm first going to do though, is I'm going to jump into my Documents folder and I'm just going to change the file name so that it definitely doesn't exist. So I'm just going to add one at the start. I may as well also just do it for this one as well. Anyway. So let's test this code. I'm going to step through it using F8. So I've turned the alerts off and the file doesn't exist because I changed the file name. So now it's going to jump into this side of the if statement, the else condition, it's gonna sit that object. Now we'll say that the object has been set. It's a File dialog box and it's a file dialogue, a file picker. So the next line is entering inside the width statement. So we're going to clear the filters. We haven't seen the window yet because we haven't hit the dots show. And we're gonna add the CSV files filter. We're going to turn off multi-select. We're going to set the initial file path to the folder so that it's going to appear inside my OneDrive, My Documents folder. And now after I hit F8, it's going to show us the file dialog box or hopefully. Okay, there we go. So and it's inside the My Documents folder. They would go in. That's the file that I just renamed. So I'm just going to select them. Hit OK. So now I have an Ottoman side, the selected items index of one. And I'm going to sit this WB open object using the workbooks dot open command, which is going to open the file that we have selected in that file dialog box. So if hit F8 one more time. Oh, okay, so the code has skipped through to the end, so we're no longer stepping through it. But I can confirm that it has completed because we have the file that we just saved it as. So we had the because we renamed it. Remember, we put a one in front of it, back in, in the File Explorer, but we've just created there's good at current time here. So it has completed, but it's just kind of run away from us. The code has run away from us. Now this is something you kinda need to be careful of when you are debugging and stepping through your code. That sometimes the code can run away from you and it can just start executing itself at full speed. Which obviously is an ideal, especially when you're debugging, you're trying to make sure that things aren't broken before you run it at pace. And the reason why this happens is kind of unknown, but we do know the certain conditions that make it occur. And those conditions when a remote procedure call happens, which is opening that workbook, and it returns back to this code module to continue on with the code. So in this instance, what's happened is it's open that workbook, which is sent off a procedure call to open up that workbook. And then as it's finished, that command, it's come straight back into this code and it's just executed at full pace. Now what you can do to get around that is litter your code with breakpoints. And the reason why you do that is that if it goes off and does its remote procedure call and it comes back and it executes it full pace. It's going to stop at the very next breakpoint. So let's try that again. I'm gonna hit F5 and we should immediately see the file dialog show up. But then once we select a file, it will stop at this endif or this endif. And I would hit OK. Boom, so it stopped at the first breakpoint after the workbook open line. And I can just continue on. Ok, so that sums up this practical example. So in summary, what we've done is we're first of all turned off the alerts. We have then tested to see if the file exists and we're doing that using the function. And that's going to return us a filename if the file does exist in that directory, and if it doesn't, it's going to return us nothing. So therefore, we going to test the length of the string. If function returns and if the file exists, we're just going to jump straight in and open that file up. But if the file doesn't exist, if we can't automatically located, then we're going to ask the user via a File dialog box. We then sit multiple properties at once. And whilst we're inside this width statement, we also had an if statement that took advantage of the fact that the fall dollar box shows it modally, basically enabling us to pause on this line and White for the user to select a file or hit cancel and then execute code accordingly. So we either open the fault they've selected or we just cancel out and exit the sub. We then continue on as normal, which is to save Erin version of the file and close the file without saving. That wraps up this lesson. In summary, today we learned how to create File dialog boxes. How to customize our dialog box. For example, creating a folder or a file picker, allowing the user to select multiple files, getting the window to start at a particular folder and filtering out files by file extension. We learned how to handle the response returned from the USA end I introduced you to with statements. Additionally, the built-in VBA functions that we covered were the DIR function and the LEN function. I hope you enjoyed this video and I'll see you in the next one. 4. 04 - VBA Lesson 11 - Run On Events: Welcome to Lesson 11, run on events. In VBA. We are able to catch some events that are triggered by the user code or other environmental reasons. Just a couple of examples of some events. The click of a button when a sheet is added to a workbook or when a change is made to a worksheet. Catching events can be very useful to make sure that our VBA applications operate in a way that we intend them to. In this lesson, we will answer the question, what are events? We will talk about the various event types and events scope. And we're going to be doing a couple of practical examples of using events in our code. We'll also be talking about the built-in VBA function called left. Let's talk in a little more detail about what events actually are. Events or simply an action or a trigger that has occurred. An event may be initiated by a user programmatically or by date or time. There are numerous events or triggers we can use in VBA that you are able to catch and then immediately execute code as soon as the event is detected. In the interest of making our VBA applications as robust as possible. Sometimes we need to catch events so that we are able to ensure a consistent user experience. My philosophy in making functional consistent VBA applications is to control a lock down as much as possible, and at the same time, catching the user's actions and handling them in a way that I specify what this does is remove as much environmental variance as possible, which can cause unexpected issues with the applications we've built. A simple example of this may be if an inexperienced user opens my VBA workbook or application and they don't enable macros. They may perceive that the application that I've built to be broken, and that's because it's not doing what they're expecting it to do, believe it or not, this is a reasonably common issue that I've experienced in the workplace. What we are able to do to get around this is to set up a workbook in a default locked down state and apply logic in the workbook open event, which implies that the macro has been enabled and then execute code that initializes the application and unlocks it. I will show you exactly how to do this later on in the lesson. Let's talk about event scope, similar to the workbook object model hierarchy as discussed in detail in my beginner course, there are multiple levels or types of events. Those are worksheet events, workbook events, application events, use the form events, chat, events, on time events and on key events. Basically, these are the events that occur at those discrete levels of the application. For example, changing a cell would be a worksheet event. Adding a new worksheet to a workbook would be a workbook event on window, minimize, maximize or re-size. That would be an application event. On click of a button on a user form we have created would be a user form event on change of a graph series or graph re-size would be a chart event. When a specific time occurs would be an on-time event. And when specific keys are pressed on the keyboard would be an onKeyEvent. Please keep in mind that this list is not an exhaustive list of events. Now let's get down to using events in our code. In this practical example, I wanna make sure that the user activates macros. And to do this, what I want to do is change the default state of the workbook. What I mean by that is I want to hide all of the workbook sheets by one. And the one that has unhidden will contain a message prompting the user to activate macros and re-opened the workbook. When they then activate macros and open the workbook, I will catch the event of the workbook opening, which means that they have definitely activated macros and then show those hidden sheets and hide the message asking them to activate macros. Because we want to be catching events that belong to the workbook. I'm going to go into the, this workbook object. And I want to draw your attention up to these true drop-down boxes at the top of the IDE. So the one on the left shows us the object we're talking about in this instance, we wanna talk about a workbook, which is the workbook. And the other side can show us all of the events that were able to catch in this workbook object. As you can see, there's quite a few. So what that's done, I'm not sure if this is visible on the screen, but what I'll do is just copy and paste it. What this has done has automatically created this sub forest. And it's a private sub, private, meaning that it belongs to that this workbook object. And it isn't visible from all the other sheets or code modules unless we explicitly refer to the this workbook object first. So this sub is automatically going to run when the workbook opens. There's nothing further that we need to do to ensure that this sub runs on workbook open. Aside from, of course, ensuring that the macros are enabled on the workbook itself. So in this retain, what I want to do is I want to cycle through the worksheets and I want to hide a particular worksheet. So I'm going to start by adding a new sheet, and I'm going to put a message on that sheet that says, please activate macros. So here's our workbook. I'm gonna add a new sheet, and I'm gonna call this one activates sheet. And I'm going to and we're going to sit this background to white. So you don't say those grids. And I'm also going to hide everything underneath it. And on that side of it, I'm going to make sure that that cell is large enough. And I'm gonna make that red. And we're just gonna make it bold just so that it stands out on a might actually increase the size. And then just yeah, cool. So the intent is that when they opened the workbook and they haven't activated macros or are they going to see, is this sheet, the activate sheet, all the other ones will be hidden and it will clearly tell them what they need to do in order for this macro to work. So I'll just go ahead and quickly hide the other worksheets. So we have this one activated now, so that's what they will be seeing when they opened the workbook if they don't enable macros. So we'll jump back into the IDE. And let's work on this workbook, open retain. Thinking about the objects we're going to need. We're going to need a workbook object, which is the workbook we're currently looking at. We're also going to need a worksheet object. And what I want to be doing is I want to cycle through each of the sheets in the workbook and I want to show or hide the workbook depending on the name. So I know that this one is going to be visible and the rest is going to be hidden. Therefore, I can just look at this name property and I can determine if I want to show or hide that workbook. So we did this in the begin of course, cycling through each of the worksheets. So I'll just quickly do that. So we have asked for loop. So that's going to cycle through each worksheet in the workbook shades collection. But before I continue, I want to explain something as we are going to be cycling through these worksheets arbitrarily. If there's only one sheet visible, which we have. And the first shape that we encounter in this loop is one that we want to hide, that will break. We can't hide all worksheets in the workbook that needs to be at least one shape visible. So what I'm gonna do is I'm going to do this in two separate loops. First one being sure the ones we want to show, and then second one being hide the ones we want to hide. So I'm going to be interrogating the worksheet name and it's activates sheet. Now because this is the one that we want shown we want the negate of this. We're only interested in the worksheets that don't have this name. Therefore, I'm just going to add 11 thing, which is not. So it's only going to enter this if statement. If the name is not active sheet, then and we're gonna named argument for that and we want to make it XL visible. And if it disappears on you just hit control space and it brings it back up point x0, xo feasible. Cool. Now the next loop, it's going to copy and paste. I'm going to get rid of that and not, and I'm gonna change this one. Make it hidden. So I'm going to save now and that's gonna preserve the visible states of each of the sheets and also echoed one other thing that might trigger faults or errors is the protected state of the sheet or the workbook. So you might have shaped protection which only allows the user to edit certain cells. In this instance, what you'll wanna do is you would want to unpick the sheet before you hot or show it and you do that like this. And this is where you would also supply password if you've included one, it's a good idea if you have a password to store that as a constant, make sure that after we set the visible property to protect it again, that's if you have a shape that is protected. But in this case we don't. I'm just gonna delete that. Same again for the workbook. If you have a protected workbook and it's and it's currently protected, you can just do that outside of the folate just before it enters the full loop. As this workbook isn't protected, I'm just going to delete that. We can now run this code to test what happens when a user opens the workbook without the need to actually close it and reopen it. So I'll just do that now. We'll step through if I oh, so we're gonna runtime era. Object variable or with block variable not set. This is good. I'm glad we got a runtime error, so we'll just debug it. It's on this line here. So for each worksheet, so there's nothing. Do you know what we didn't do? We haven't set the object which is workbook. So I'll just quickly change that. Because this workbook, just adding this line doesn't do anything because the code is already passed this law. And so what I'll do is I'll just drag it back up here so that it sets that object. And let's say now, yes, okay, that works when now interrogating the worksheet name and we only want to enter this if statement if the name is not active shape and the current worksheet with looking at in this loop is actually activate shape. So it skips grad going. And the current sheet is custdata. So yet we want that one to be visible. Parent one is output, which is there. Yep, great. Okay, going. I'll just wait until we get to the next loop. Okay, so now we're in the second loop and that's the one that's going to hide the sheets we want hidden. And the current one is activate sheet. And it's hidden. That 1 fourth given hasn't yet, and it hasn't hidden anymore. So now if we then minimize this, I just make sure this code is finished running. I'll minimize this and bring up Excel. So I see that the activate macro message has disappeared. All that sheet has disappeared, and it has shown all the other sheets that we want shown. What happens if we want multiple sheets hidden? We can add extra criteria or else if's to this if statement that's inside the loop that is intended to hide the shapes. But that can get kind of out of control, particularly if you've got lots of worksheets in the workbook, there is an easier way. Back in Excel. I'll just add a new worksheet and I'll show you how. So I've added a new shape which is now called SHA-2. What I do is use a naming convention in the sheet name. So I make sure that the first seven characters are hidden an underscore, and there could be numerous reasons why you want sheets hidden, could be that you have a data set that you want to hide from the user. Or it could be that you have a bunch of settings on a shape that you are reading from, from code. It's basically anything that you don't want the editing. So let's go ahead and add this to our if condition. Let me first show you in the immediate window how I'm going to use this naming convention. So we're going to use a built-in VBA function which is called left. And what lift does is it returns us a certain number of characters from the left side of the string. And we specify how many characters we want from the left side of the string. So I'll just type left and then you'll see the arguments we made. So we've got string and a string, I'll just type it out. So that is the shape that we've just added to the workbook. And I'm gonna hit comma, and it's the first seven character. So I want it to return the first seven characters of that sheet name. And as you can see, it has returned the first seven characters which is hidden on the school, because we've now returned the first seven characters of the worksheet name. We can use that logic in our if statement. So I'll just jump back up into the if block LCF because we've got the first if condition there and it's not an else, It's an else. If we've got another condition we want to test. Left as we've just learned, of the string, which is worksheet name. And it's the first seven characters equals hidden underscore. Then. And I also want to do the same thing. Another way we can achieve that is also adding an all condition in our if statement. So I'll just show you quickly or and then we can just copy this. So we can actually add two conditions to that if statement. But I'll just delete that one. So we can now run this and just test to see if this worksheet disappears. Okay? And we've actually tested here. So if we select the sheet, we can say that the property of the sheet is already hidden, shaped hidden, but I'll just move these out of the way. Yes, the sheet I created which was hidden, underscored data, has disappeared from the workbook. When you building in testing this type of functionality that runs on open and UNCLOS, it's a good idea to build yourself a routine that resets the state so you can test it again quickly so you don't have to manually go through each of the worksheets and select the visible property. So I'm just going to quickly do that now. And then I'll show you what else you can use that fall. And I'm gonna do that in a new code module, Insert Module, module three. Ok. So I'm cooling it reset workbook which I can coal at anytime. And that's going to return it to the state that the workbook should be in when it's opened. I'm going to name my variables. Again. I'm going to need two for loops. And the first loop will be for the auditors we want shown. And the second loop will be four items we want hidden. So the one we want to show is the one that is activate Xite because that's the only sheet we want activated when the workbook is opened. And I want to also add the LCA for the hidden under school. Okay, so we can now use that to reset our workbook back to the state it should be in when it's opened. I'll just test that now with F5. Oh good, we gotta talk mismatch. And I'm glad we're getting these runtime errors because it really helps us to learn. So if hit debug, it tells us what's wrong. So if not worksheet 0.9c and the current worksheet dot name is active sheet. It's a string. Okay, you can see here that I've got a minus instead of an equals. So that's just an example of debugging your code and testing your code to make sure that it's working and robust. So hit F5 again. And I'll go back to my book here. And as you can see, only this sheet is visible. So another way that I want to use this routine is when the workbook is closed. So let's say for an example, one user uses it and they have macros enabled, and then all of the sheets are unhidden, but they save the workbook and then use a true comes along. And they don't have macros enabled because that user has already saved the workbook and it's unhidden the sheets. I want to make sure that every time the workbook is closed, those sheets are hidden and the message that tells them to activate macros is visible. So I'm just going to copy this sub name and I'm going to jump back into the this workbook code module. And I'll just look through these events to find one that will catch the user closing the workbook. So then I'm able to set the shape visible property to what I want it to be. He would go before close. And it's created a routine that will catch the workbook close event and allow us to run code before the workbook actually closes. So what I'm gonna do is I'm going to now call a macro already built. So in summary, this routine here is going to catch the event of the user closing the workbook. And before the workbook actually closes, it's going to call the reset workbook routine. And the other thing I wanna do is also save the workbook, which will save the visible states of each of the worksheets. So I'll just quickly do that. Okay, there we go. Ok guys, that's all for now. In this lesson, we learned about what events and we scratched the surface of how we can use them. We talked about event scope, IA, at what level of the application, the events that triggered or captured. And we completed a couple of practical examples of catching on workbook open and Workbook close events and executing code as soon as the event is detected. We also learned about the built-in VBA function called lift, which returns the specified number of characters from the left of the string. Some other things that came up in this lesson were test your code. We ran into a couple of runtime errors. That was a good thing. It's better for us to find out these errors than your users. We also touched on controlling the user experience, which was entirely what the practical examples were about. And lastly, we learned about calling a macro from within another macro. My name's Andrew, thanks for watching, and I'll see you in the next video. 5. 05 - VBA Lesson 12 - Dealing With Data: Welcome back. This is less than 12 dealing with data. I realized that dealing with data is a reasonably vague and broad title for this topic because there are countless ways of handling data in VBA. So this title will be recurring topic over the rest of my courses for both intermediate and advanced content. In this lesson, we'll learn about importing data into our workbook from various sources. We'll learn about connecting to external databases and running some basic SQL queries to fetch data and return it back to Excel. We're going to be doing this lesson into two parts. The first part we will be importing data from the CSV file that we've opened in the previous lesson. And the second part, we will connect to a database and extract all of the data out of a table within that database. And in both examples, we will be populating a sheet inside this workbook with the data extracted from either the file or from the database. So in this first example, we will be building some more functionality into this subroutine that we've been building over the last couple of lessons. And just as a recap, what this routine does is it first looks for a file to see if it exists and opens it if it does, otherwise, it asks the user to locate that file. So the next step is that we want to extract the data out of the worksheet that we've opened, populate that onto a sheet within this workbook and then continue on in the same fashion. So we will say their own copy of the workbook and we will close it. So we will begin coding around here. This is the point where the workbook will be open because of the macro will automatically open it. And if it can't find that file, it will ask the user to locate that file. So again, if we get to this pod here, that means we have the workbook open. And not only is it open, it's set to this WB open object. First thing I'm going to do is set the current workbook object. So that's the workbook is actually running the code. And now we'll switch talking about this current workbook. I will set a worksheet and in this instance, what we're going to be doing is use the sheet one object which is up here. And that's where we're going to be depositing the data into. Now I'm going to set the worksheet object from the workbook that we have opened, which is the CSV file. Now, CSV is a very basic document. It's just a text document. A CSV also can only contain one sheet. Therefore, I don't need to specify a name here. I can just supply an index which is one, because there is only ever going to be one sheet in a CSV document. Once the code reaches this point here we will have both workbooks open. We will also have both worksheet objects set. Now we can go ahead and start taking the data off the worksheet from the workbook that we've just opened. I find the fastest way to do this is with the use of ranges. And that's because I can simply use the range value property and that will hold all of the information that is in that range. And then I can assign the value from one range to another range. So in order to do that, I'm going to need a couple of range objects. One specifying a range from the workbook that we have opened, and one specifying a range from the current workbook. Let's first set the range from the workbook that we have just opened, which is R and G open. There's a nifty little trick that we can employ here to get Excel to tell us what the used range of that worksheet is. Ia, Excel will tell us all of the columns and rows that contain data. It's important to remember that this is only useful when we are talking about a table. So a sheet that only contains one table to use range property con identify if there are more than one table on a sheet and it will just include both by default. So it's as simple as that we using the worksheet object and we're specifying the used range. Now I can sit the other range object based on the size of this range object. Because we're shifting values from one range to another range, we need those ranges to be the same size. So let me show you how to do that. So what we're doing here is we're sitting a new range object, which is in the current workbook on the current worksheet, which is sheet one. And with setting that range to be the same size as the range open objects. And how we're doing that is using the address property of the range open object. The address property will tell us the top left cell and the bottom right cell of the table. So now that we have both of our range object set, we can simply set the value of one to the value of the other. And that's it. The reason why I prefer this method is that it is extremely efficient. We didn't have to spend time manually figuring out how big the ranges, because we're using a built in Excel functions to do that. We're also not using the user interface at all, meaning that we aren't selecting sheets or selecting workbooks or selecting ranges. We aren't using copy and paste which copies data into the clipboard with simply using the properties and the methods that are at our disposal within these objects. So let's try to run this. I'm just going to F5 through the first pop using currency conversion CSV. Okay. We're now sitting out current workbook object and we're setting the current worksheet object. And I was sitting a second worksheet object which is from the workbook that we've just opened. When now using the used range property of that worksheet object to sit the range dimensions. Now, quickly just going to bring up the immediate window after we've set that object. And I'm going to see what the address of this huge range is. So I'm going to return the address. Now it's returned I1 to see 805. So if I just bring up our currency conversion, csv, We can say that the top left corner is I1. And we should say that the bottom right corner is C 805, that we got 805. So that's a really quick and easy way to get Excel to tell you how many rows and columns are in use on this worksheet. So I'm just jumping back in here, bring down the minute window. So now I'm sitting arrange on a worksheet that is in the current workbook to be exactly the same size as the range that we have just taken from the workbook we have opened. Now I'm taking the value from the range in the workbook we've just opened. And I'm setting that as the value of the range in the current workbook. And let me just quickly show you a current workbook we have on Sheet one. I'll just bring this up. On Sheet one. We have I1 to see 805. And that was instant, which is objectively faster than using copy and paste. Let's minimize that. Now continue on as normal. We're just going to save and close and end the routine. Let's now move on to the next example. In this example we will be connecting to a Microsoft Access database and will be pulling all of the data out of a table and populating that on a new worksheet in this workbook. Before I continue, I just want to show you the excess database. So I'll just bring that up here. If you are following along with these videos, the database and the CSV file will be available for download in the project section of this course. So we'll just open this database. As you can see, we have two tables here. We have the currency conversion table and we have a test title. But right now all we're concerned about is this table here, TBL underscore currency conversion. And if I open up this table, you'll see that it contains the same information that is in the CSV file. Okay, so I'll just close this now. And let's begin. I'm going to create some space down here. So I'm just going to start by defining our sub-routine. I'm just calling it connect to two dB. Let's think about what we're going to need here. So we're definitely going to need Outlook book and a worksheet object because we will be populating data from the database into this current workbook on a worksheet. I might as well also go ahead and sit that workbook object. So in order to connect to a database and extract data out of it, we're going to need two things. We're going to need a record set and we're going to need a connection. Think of a record set like a worksheet. It's essentially a table of the records that we return from the database. And the connection is the link between Visual Basic or Excel and the database itself. As always with VBA, there are numerous ways that we can connect to a database. In this example, I'm going to use IDEO DB or active x-dot objects. And this is because we can use this method for multiple different database types. So in order to be able to use an active x-dot object, we're going to need to actually add that reference into our workbook. And to do that, we're going to select tools and we're gonna select references. And I'll just bring this up here. And we're going to need to look for the Microsoft Active X dot object library. So I'll just scroll down here until we get to Microsoft. And you want to select the most up-to-date one on your machine? On my machine that is 6.1 and hit okay, once we've added this reference library, we're able to now sit variables of those types. So we'll start with a record set object. It's going to call it my RS. Next I will declare the connection object. Now that I've declared these objects, I'm going to sit them as new generic blank versions of these objects. So the next step will be establishing a connection between Excel and the Microsoft Access database phi, this DB connection object. And in order to do that, we're going to need to set up a connection string. So I'm going to need one more variable. I'll just add that up here. And now I will create a connection string. The connection string requires a specific syntax, but the syntax will differ depending on the type of database that you're connecting to. There are some great resources online that can help you build a connection string for the database that you're trying to connect to in connection string. In this example, we need to provide a couple of things. The first thing is the provider and the second thing is the data source. The provider just tells VBA what mechanism are we using to connect to this database? And the provider that we are going to be using in this example is a default Microsoft ice OLA DB mechanism and its version 12. And we're going to separate app parameters with a semicolon. And to create the data source, I'm just going to reuse the constant that we used in the example before, which is the link to my Documents folder. And this last part is the file name of the database. So I'll just grab that from here. So with these two combined, it's the file path of the My Documents folder and the file name of the database. So that is our datasource. Now that we have a connection string built, we can connect to a database. Just notice one thing, I've got a typo here, I've got one N on this and one And on that. So I'll just change that. And I'm going to be using the Open method of the connection object. And it's going to tell us what we need to supply when it's a supply connection string which we've created. And if we have a user name and password, we can supply them. And we can also supply other options, but all we need in this one is just the connection string. So after this line is executed, we will have established hopefully a connection to our database. And now we can begin to send commands to a database in order to bring back the records that we want and the commands that we will be sending out what's called SQL or SQL. Sql main Structured Query Language. I'm going to need to build a SQL statement, and I'm gonna do that inside a string objects. So I'll just create a new object and calling it STI SQL. Now let's build our SQL statement. Now, SQL can get incredibly complicated. However, in this instance, all we want to do is return all of the records that are in a particular table. So in order to do that, we use Select and we're going to use a wild-caught here star, which is everything from. And now we need to supply our table name, which was TBL on the school currency conversion. Okay, now that we have our SQL statement, we can go ahead and open at record set using the Open method that's going to tell us what we need, the source, which is the SQL statement. And we need to supply the connection, which is the connection that we have established to the database. And there are other arguments we can supply, but that's all we need to supply for now. So the first thing that I always do after I open a record sit is that I check that we have returned records and the records sit object does have a record count property, however, there is a catch and let me explain. So let's talk about the differences between Excel and database. Excel is very macro level in that we can easily refer to individual cells inside a table. However, a database is more focused on sets of data, meaning an entire table or record set that is returned from a query. So in a database there is less focus on individual cells. And while that's a great way to handle datasets in a database, this can be seen as limitation when referencing a database from Excel. So to get around this, they invented a mechanism that we call a cursor. So let me just bring up Excel. So think of a cursor lock this, it's literally just the cell with currently on which indicates what column and what Roe were on. And we're able to navigate around using the cursor. So keeping this in mind, we need to specify where the coastal location is on the connection object. So let me just quickly do that now. And it is used client. So we're going to use the Excel cursor, not the database cursor. And by doing this, we're able to easily get an accurate representation of how many records in the record set. So I'm just going to use the record count property inside a basic if statement. And so the reason why I'm doing this is if we start to interact with a record set as if it has records, however, it doesn't have records, then that's going to produce an era. So I only want to execute code when a record has actually been returned. So if we get inside this if statement with open data connection with open direct would sit and we have records returned from the database. So now I can go ahead and create a new worksheet that I'm going to populate the data onto. And to create a new worksheet, I need to use the sheets collection, which belongs to the workbook object. So WB, because that's the workbook we wanna create the sheet in and the sheets collection. And there's a method that ad for this line, we'll just add a generic sheet. However, I want to also specify the name. And I'm just going to use the current date as we have done in a previous lesson using format. Choose an underscore there as well. So this line is going to add a new shade in that workbook object, which is this current workbook. And it's going to set the name property at the same time as adding the new worksheet and it's going to create a sheet with this name. So that's the current day in day and month. So d, d m, m, and it's going to have an underscore and then currency. Now that I've created my new shape, I can then sit my worksheet object based on what we've just created. Just gonna copy this because that's the name property. It's exactly the same. Cool. So on one U-shaped are not only want to populate all of the data, I also want to include the field headers. And if you look at a record set like a table, it doesn't include the field headers, but the field headers are available from the record set object. So what I'm going to do is use a for loop and loop through the counts of fields in the record set. And then we're gonna populate the field names onto the worksheet inside that for loop. So I'm using the fields property, and that property has a method called count. I'm just going to need to define this x variable. Set it as along just in case. And now I can go ahead and populate that onto the worksheet. I want it to be on row one because that's where the field headers go. And I'm going to use x, which is our point on inside our for loop. And now I'm going to grab the field name property out of the record set object for the current column that we're on. One thing I want to add here is that unlike a spreadsheet or a worksheet object, this field's property is 0 indexed. What I mean by that is the index number begins at 0. Whereas a worksheet object, the Index begins at one, as in row one, column one, et cetera. But the fields property, the indexing begins at 0. That means I need to take one away from the x number because it starts at one here. Which we're using to address which column it is on the worksheet. Now I want to make sure that the record set cursor is on the very first record. So I'm just going to use a move method. As you can see, there are multiple different types of methods, but I want to say move first. So that's just going to move the cursor right up to the first record in that record set. And now that I also have my field head is populated, I can now go ahead and start populating the data from the record, sit on to the worksheet. In order to do that, I'm going to use a due until loop, and I'm going to loop until the record set reaches the end. So I'll show you how I'm gonna do that. Do E, o, F, or end of file. Now because I'm not using an index loop here, I'm going to need another number or counter to count which row we're currently on sums gonna grab a new variable here. And I'm gonna sit that here, 22 because the first row is already taken with the headers and we're going to start on row two. So if you think about it, this loop is going to be looping through the records or rows, and I'm going to need another loop to cycle through the fields or columns in my record set. So I'm going to use a similar method to what we've used here. Now I can populate this current cell from the record set into the cell on the worksheet. And I'm going to use y as the rho because that's the row there, starts at two. And the column index is x dot value equals. And similar to above, we're using x minus one because fields is 0 index. We are using the value method this time. Just found a mistake. So in this loop we're supposed to be using name not value, because value is the contents of the cell and name is the name of the field. So I'm going to change that. Now after we've finished this loop here, that means we've cycled through all the columns on that current row in the record set. So now we need to adjust that. We need to move to the next row in the worksheet and the next row in the record set. So therefore, I'm going to do y equals y plus one. And I'm going to move to the next record and the record set using the move next method. Okay, so think we're ready to test this now I'm just going to step through it using F8. So just click up here and stops occurred. And if we don't have an arrow straight away, it means we have compiled and everything's good or at least it's good for now. Yep. Okay. That's good. It's compiled. Let's start going through this. So we are going to sit out workbook object. We're gonna create a new blank versions of the record set and the database connection. We're building a connection string. And I might just bring up the immediate window just to show you what that looks like completed. So that's what it looks like, a plated. So I've concatenated the path and the file name. Let's bring that down. So hopefully now we can connect to our database using this connection string, fingers crossed. Well that seemed to work. So now we're going to set the coastal location. So it set the cursor location to the client side, which means that we're able to get an accurate representation of how many records have been returned in record set. I'm going to build out SQL query now. I'm going to open that record set fingers crossed that with written out SQL statement correctly. Yep, that's good. So let's bring up the immediate window and we can interrogate what the record count is. Okay, so we've got 804 records in that record set, which is everything in the title. Let's continue on. So now we're going to create a new worksheet. And we can say that it's created a new one up here tonight. Oh, which is the current day here in Australia under school currency, which is excellent. And I'm going to set that objects so we're using the same name that we've just created so that we've set out worksheet object. Let's continue on. So now we're going to populate the field headers onto the sheet. So I'm just gonna bring up Excel so that we can say that it's done that yet. So we've got the sheet here, I'm gonna magnet if it's small. So we can say the sheet name here, and it's just populated the field headers onto the sheet. That's great. Um, I also kind of do this in split screen, so just bear with me for a moment so that we can execute the code at the same time. We can say it, populate the worksheet. So back to the code now. We are going to move to the first record. We're going to set the cursor location to the first record and the record set. And we're going to set the first rotor two. And now we're going to cycle through every record in this record centers, bring this down until we get to the bottom of that record set. So at the moment, as you can see, we are not at the end of the record set and that property is false. So we'll just dot cycling through now. It's interesting, but it's because the value is to launch to C. Okay, so now it's moving through. So you may also be thinking, wow, we're going through this record set cell by cell, but this is reasonably efficient. If we were to have more columns and more rows or more records, I'd probably do this in another way, but we weren't touched on that until we get to the advanced content. So at the moment, this method is actually quite fine for what we wanna do, which is populate a small number of rows and columns onto a worksheet. And as I said, it is reasonably efficient because when not using the user interface with just putting values from one place to another. So what I'll do is I'll just put a message box at the end which indicates that we've reached the bottom. So there was 805 each rows. So that we can set it going at full price and we can get the message box when it's done. So now I'm going to hit F5 and done. So as you can see, it is pretty quick. And let's just quickly scroll through here. I'll bring that up a bit further. And yeah, it's populated all of the records. Hopefully, if I hit control down yet, I had a 105. There we go. So that's populated all of the records. Okay, so that wraps up Lesson 12. In summary, we looked at importing data from various external sources. We opened a CSV file and imported that into our workbook. We also connected to an external database and we run a basic SQL query to return the records we were interested in. The new VBA features we introduced was the active x data objects Library and also the record set type object. My name's Andrew, thanks for watching, and I'll see you in the next video. 6. 06 - VBA Lesson 13 - Arrays: Welcome to Lesson 13. Arrays. In my opinion, a rise of the most powerful and useful function in VBA. Arrays are so useful because they allow us to efficiently hold enormous amounts of data in memory while consuming very little resources. This allows us to perform labor-intensive tasks in fractions of fractions of seconds. In this lesson, we will learn about my favorite topic, arrays. We will talk about single dimension arrays, and we'll also talk about multi-dimensional arrays. Think of an array as a virtual table. Locket table. It might have rows and columns. This is what we mean when we are referring to the dimensions of an array. A single-dimensional array contains only one column. Therefore, it only needs one dimension to refer to a specific 0 value, which is the rho. A multidimensional array contains more than one column. This means you need multiple dimensions to refer to a specific point or value inside that data set, for example, column and which row. The advantage of using an array or if it's say a sheet range or record sit is because of its efficiency. He is just a quick comparison of a range object versus an array. A range object is a reference to a specific location on a sheet. And because arrange variable is a representation of that specific shape location, it has numerous other parent and child properties. And because of this, the variable in VBA consumes more memory compared to an array of the same dimensions. A range object also has to sustain a reference between the theoretical object declared in VBA and the physical range object on the sheet. This means that there are always unavoidable read and write operations performed between the variable representation of the range object and the physical range object on the sheet. Therefore, when we are talking about very large datasets that require a labor-intensive operation. And by that I even made just looking up a single value inside the title. The performance of the range object is very poor when compared to an array. On the other hand, an array is just a basic object type that has very few properties. It's literally just the right dimensions and the datatype. That means it allows you to simply load it up with data and hold it with minimal fuss. And because of its simple nitrite, it makes the same labor intensive tasks to be performed effortlessly and therefore incredibly quickly. We will be looking at two practical examples today, a single-dimensional array and a multi-dimensional array. Let's jump right in. And this example, I will introduce you to single-dimensional arise and also the built-in VBA function called split. I'm just going to quickly add a new code module. So we're in module five. Now let's start with split. Split is a built-in VBA function that allows us to perform an operation on a string, which splits it into numerous discrete components based on a delimiter. A delimiter is a character that denotes a separation point. If you remember back in the last lesson, we used CSV files. A CSV file has a coma as a delimited between fields. The split function also allows us to define what character we want to use as a delimiter. I'm going to start first of all, just by creating a subroutine. Okay, I'm just calling it SBA SPL array. Okay, so first thing I'll do is declare our array variable. Now this data type is a string. What that means is everything inside this array is going to be stored as a string. You are actually able to hold multiple data types by using variant. However, I rarely use variant. And the reason why is because I want to just hold it in the most basic way possible, which is a string. When using the variant data tab, you can encounter issues such as we had date formats and that sort of thing. And that's obviously undesirable. So I always just hold everything is a string. Now I also want to declare a string variable, and that's going to hold out piece of text that contains a things that we want to split up. So let's think about why we might want to split something up. Ok, so I'll just jump back into our previous example here. As you can see here, we have the full file path of the database that we had in the previous example. So I'm just gonna copy that jumped back into module five, and I will just assign it to this variable here. Let's say for example, we know that this is a full file path, but we just want to grab the filename itself. We know that a directory contains backslashes. Therefore, if we split this string by backslashes, We can now grab the file name, which will be the last autumn in that split. So let's just do that now. So I'm using the split function here and my bracket, and the expression that I want to send the split function is split and the delimiter is backslash. I don't need to supply anything else. So once this line is executed will be holding an array in SPL and it's going to contain this string split by the backslash character. So what am I expecting is the first row in my array to hold C colon. And the next one will be uses, and the next one will be I and II. Next one is OneDrive and so on and so on and so on. So what I'll do is I'll step through the code and after we execute this line, I will show you what happens in the watch window. And unconcerned about this context, the current context of this subroutine. So hit okay. Okay, so now down in the watch window we can see that we have our variable here. We can say that at the top is a string and it's got a property of 0 to five. And array is 0 indexed, meaning the very first record is indexed 0. Unlike a spreadsheet that's indexed at one, there is no 0 row, there is only row one. So in array, the first item is always a 0 for both row and column. But this one is just a single dimensional array, therefore, we only have rows. So as you can see here, if I hit the plus, you can see that the 0 index is C colon, which is exactly what I expected it to be. And the next one is uses, and the next one is EI and DRA. The next one is One Drive documents by blah, blah. So as you can see here, and I double SPL five, I'm holding the actual filename itself. So now let's talk about how I go about pulling the file name out of this array. In this instance, I know that the file name is the last autumn in this string. Remember when we were looking at the array in the watch, we saw that in the top it said string 0 to five. Therefore, we know that there is six items in this array, and we refer to these things as boundaries. So the low boundary, meaning that it is the first item in the array and the upper boundary is the last item in the array. So if I want to grab the very last autumn, I would refer to the upper boundary of my array. So I'm just going to use that upper boundary and now I'm going to debug print that into the Immediate Window. So, so I'm using the brackets again because I'm actually referring to something inside the array. And now in age refer to the index. So another that's five. So just show you 5 first, which is the sixth item because it starts at 0. So if I bring this back up here and hit F8, just bring this up. You'll see in the immediate window that it's given us the file name. Now, what if I don't want to explicitly Cy5 because their numerous folders, there are numerous, more folders than this or less folders than we have in this current string. So what I can do then is referred to the actual boundary. So that is a calculation that tells me what the lower or upper boundary is of that particular array. So instead of supplying an index, I'm going to supply you bound U-bend of what abound of. And if we hover over that, you will say that it tells us the upper boundary is five. So if I just do this and hit F5 again, you'll see that it's done the same thing. Likewise, if I want to return the lower boundary or the first item in the array, which would be C colon. I can also use L bound. So if I just do that, you can see now it's got the C colon in the Immediate Window. So that was just a really quick and easy example to introduce you to arrays at the same time introducing you to the split function. So now let's move on to multidimensional arrays. This next example might not be entirely practical. However, it serves as a good introduction to 2D arrays. And at the same time we'll demonstrate how efficient and fast die when used as a table compared to using a sheet in exactly the same fashion. We're going to be doing this example in two parts. The first part, we're going to load up 5 thousand rows on a worksheet with random numbers. And then we're going to look through those 5 thousand rows for a specific value. After that, we will also load up a 2D array with 5 thousand rows of random numbers and hunt down a specific value within that array. I'm just going to stay in module five here. But I'll begin with just defining our subroutine. Okay, so thinking about the variables that we are going to need. The first plot, I'm going to load up a worksheet with 5 thousand rows of random numbers. So I'll definitely need the workbook and the worksheet. Now I have a blank sheets ready. That's cold shaped three. So I'll just use that one in this example. I'm going to be loading the 5 thousand rows using a couple of for loops. Therefore, I'm going to need a couple of numbers or integers. If you know already familiar, I'll be using the x and y integers as the row and the column pointer in my full loops. So the first for loop will be for the row. So it's going from row one to row 5 thousand. And I went from column a to Z, so that's 26. So that will be from one to 26. So in this loop, as we are on a row and as we are cycling through the column one to 26 on the current cell that went on inside this for loop using the y point, I want to print a random number into that cell, and I want that number to be between 15 thousand. I'm going to quickly talk this out in a comment to explain how we create a random number between certain values. So first of all, we're going to start with some brackets. And inside those brackets, what we wanna do is we wanted to have the upper limit and lower limit plus one. So the upper limit is 5 thousand. And now we want to subtract the lower limit, which is one. And then we need to plus one. Now, outside of these brackets, we want to times that by the R and D function, which is a random generator. Now also we want to add the lower-bound again, so that's one. But we don't want any decimal places, therefore, we need to cast this as an integer. So if I now just take this and take it down to the immediate window, I'll just bring the immediate window up so that I don't have to zoom down later on and do a question mark and then interrogate what this value returns. It's given us a random number. And that number never exceeds 5 thousand at it never goes below one. So now we're gonna use that and populate that in a cell. Sounds, movies Immediate Window identity. As we've done numerous times and I've listened, we want to address the sheet. We want to address the cells on that sheet. Which cells we want. We want x, y, because we've got the row pointer, we got the column pointer. Dot value equals random number, that's cost as an integer. So after we have written all the values to the 5 thousand rows and 26 columns. And I want to essentially do the same thing, but read the values and look for specific number. And the number that I want to look for is 4,999. So I'm just going to copy this really quickly. Because we're essentially doing the same thing. We're going to start at row one and go to row 5 thousand. And on each row we're going to cycle through column 12, colon 26. And I'll just get rid of this for now. So to interrogate a value, I'm just going to use an if statement. If. So, if the current cell we're on, if the value of that cell is equal to 4,999, loops 4,999, then I want to do something. I want to replace the value in that cell with autumn found. And I'm also going to just debug print that into the immediate window. And I'm going to then also include the address. And this time I'll copy it. Okay, now let's move onto our array. So I'll just jump back up the top Ian Euro declaration statements. And I'm going to declare our array. And you'll notice, even though that I'm talking about integers, I still am referring to it as a string. And this is purely for efficiency, but also there is a really quick and easy way to compare strings and integers. So now that we've defined our array, we can now dimension the first dimension or declaration statement, which just sets it up as an array. Now I want to provide the dimensions of that array. So how big the table is, how many columns and how many rows? Now I can also do that in the declaration statement, but I always prefer to do it separately. The way that you assign the size of an array variable is using the read deem statement. And we taught the array variable name. Now we need to provide the column and row numbers. But first let me just comment this out and I'll explain something. So we already know that arrays are indexed differently to other object types, such as cells and ranges and sheets, et cetera, in that they start at 0, so the 0 indexed. Now, the indexing properties are also different. So for example, when we're referring to a particular cell in a range or on a worksheet. We first need the row. And then we made the column. But in an array, it's actually the other way round. You first apply the column and then supply the row. And that can potentially get really confusing, but it's actually for a pretty straightforward reason. And the reason is while you are executing code, you can't actually add columns onto an array. You can only add rows when you think about a table of data that you're using in runtime, that your rating Iranians who are interrogating or trying to find an object in. It's very rare that you're going to need to add columns to that title, but you may need to add rows to that title or add new records to that table. And because of this, you can't actually expand the column property in an array without first destroying that object and then starting again. So then read dimension the whole thing and then loading it up again. Anyway. So let's set the column value and we know that there's 26. We want a to Zed. And because the array start at 0, that means that needs to be 25. And we know that there's going to be 5 thousand rows therefore, because it starts at 0, it's 4,999. So that has set our array size to 5 thousand rows and 26 columns. And so now that we've set the size of the array, we can use the boundaries of the array in loops. So I'll just do that now. So the first thing that we want to do, which is identical to what we did up here with the sheet. We want to first load it up with random numbers, and then we want to cycle through that array and we want to find 4,999. So four. And I'm just going to use 0 here. Now. Could use 4,999 because I already know that that is the upper boundary of the array. However, I can also use the upper boundary calculation, which is bound. And we need to supply what upper boundary we're looking for. So that's Array Rand. But because we have multiple dimensions, we need to supply which dimension we want to look for. And weirdly this index dots at one. So we're looking for the rows in this instance. So the columns will be one and the rows would be two. So we're looking for the index of two. Don't ask me why they did that. That's just how they did it. And now we can cycle through the column's going to use you bound again. And I'm looking for the column, so that's one. And I'm not just copy this up here. But I will now assign. And I'm looking for the column, first column, row. I'm going to assign the value into that. So exactly the same to the full loop up here. What we're doing is we're cycling through these two for loops, going through the rows and the columns. And we are assigning a value to each individual cell inside each row and each column. And now also exactly the same as the example. What we wanna do is we want to cycle through the array and we want to look for a specific value. So again, I'm just going to copy this and paste it there. It's going to move down a little bit. Here we go. And I'm going to have the same if statement, but for the array. Now before I continue, you may remember that I defined the array as a string, meaning that it's holding only strings. So what that means is let's say we've got the number nine. If we have a nine, that's an actual integer, but its holding it like this, like a string. So VB eye doesn't see it as a numerical character o. What that means is if I am using an integer here and I've got a string here, these will never compare. So what that means is I need to cost what I have here, which is held as a string, as an integer. And the way that I'm able to cost a string as an integer is width, the cost as integer function. There are a couple of problems with doing this. One problem we have with doing this is that what happens if this particular column or, or this particular value doesn't hold a number, we actually going to get an era. So I'll just show you in the immediate window. Let's say we send the Sea INT function an alpha character. We get taught mismatch because it can't convert. That into an integer because it's an alpha character. There are numerous ways around this using very similar built-in single line functions, but I'm not gonna get into that right now. We're going to cover that at another time. Okay, so exactly the same as our loop up here. I want to replace the value held in that cell with item found. And I also want to debug print a message into the immediate windows. So I'll just do that now. Just going back to that taught mismatch before. If I sit this or declare a variable as an integer because we're holding integers, this wouldn't work. I wouldn't be able to send it a string. And that's another reason why I use strings. Because basically every other data type can be converted into a string and then convert it away from a string. But if you set it as an integer data type, you can't put anything other than integers in there, even numbers above 32.5 thousand, because that's the maximum limit of an integer number. Anyway, moving on, we'll alter debug print. I'm going to be doing this a little differently. So I'm also going to include the array name and the point is in this print statement. Now, I also wanted to compare the difference between the two. And a good comparison is how much time it takes to do a the task. So how much time it takes to complete the sheet example and how much time it takes to complete the array example. In order to do that, I'm going to need to do some date sums. So I'm just going to add a date variable. And I'm also going to add a couple of integers. The first one will be holding the number of seconds it takes to complete the first part. And the second one will hold the number of seconds it takes to do the second pot. So how am I going to achieve this is by setting the time to a variable before it's dots and then comparing that time to the current time after we've finished it so that we can see how much time has elapsed between those two points. So we'll start by putting the current time and date into the Date variable. And remember it now always returns the current time no matter what. And after we finished. And now we can use another function to count the number of seconds it took to complete this task and then assign that number to this variable here. I'll just stop with that equals. So the function you use to compare the difference between two diets is date DEF. And you can see it's asking for interval a string. So is it hours, minutes, seconds, days, weeks, months, years, and then so the first date, So that's the earliest date and the second date, which is the current time or the date you want to compare the the light of day that you would like to compare against the first date. And that's what you need to supply. So I want seconds. And the first dydt is D G dot. And the second night, well, that's right now. So now. And we're going to be holding then the number of seconds it took to get from here to here. And now we can do the same for this one. So I'll reuse that stock because we don't need that anymore. We've already held how many seconds it's taken. And I'll grab the other integer variable. And I'll do exactly the same thing. Okay, so now that I have those two integers, I'll just debug print those into the Immediate Window. I'll just copy that. While we're running this, Excel might report that it's actually not responding and that's because it might take a long time to do so. If you think about it, we're doing 5 thousand rows and we're doing 26 columns. So that's like a 203,000 thousand, yeah, a 130 thousand operations. And then we're doing another 130 thousand operations right here. And then we're doing another 130 and another 130 thousand operations because it's the number of rows times the number of columns. And we're doing a specific thing for H1. And the purpose of this was really just to show you the difference in time it takes to pull values from a sheet versus pulling them from memory. And every time we actually go through either of these loops with doing something to the cell where reading or writing to it. So we're reading from it in this instance, which is to check the value. And we're also writing to it. So we're providing it a value. And yet we have providing it a value here when the cell value actually equals 4,999. But at the same time we're doing the same with the array. We actually writing to it a 130 thousand times here. And we're reading from it a 130 thousand times in this one. So I'm going to expect that this is gonna take a while to complete, and it will definitely report that Excel is not responding. And that's because it's got a timeout value on its procedure coal, meaning that it can't be busy with tasks for more than a certain amount of time before it reports that it's not responding. And after we run this, we'll actually show you a quick way to prevent that from happening. But it does come at a cost of performance. So you'd want to use it very sparingly. Alright, let's start this now. Put the cursor up here. I'm going to hit F5 in 321. Okay, we've done the fans on my computer spun up really fast. Alright, so I'll just move this immediate window right up the top here. And we'll say, I'll just get rid of this one here. Will see that it did find 4,999 in multiple places and keeping them on their random numbers. So it's, you know, it's not always going to be even, but it's a reasonable comparison. So if found 4,999 in these addresses in the range, and it also found 4,999 in the array in these point is. But most importantly, it completed 260 thousand iterations of read and write to a sheet in 20 seconds. But a completed the same amount of iterations or 260 thousand to an array in less than 1 second. So, as you can imagine, this time adds up significantly and this time reduction is extremely noticeable. Okay, so just one last thing before we finish. So as expected, the VBA window or Excel entirely showed is not responding whilst this code was executing. So there is a y that we can get around that. And that is we're using a command called do events. And what the do events command actually does is that it allows Excel to prioritize other events in the queue, such as refreshing the window, et cetera, et cetera. And this can be very useful when you have other uses using a macro that you've built. And it may prevent a force panic closure because they think you're application has frozen. But for performance reasons, we don't want to use do events on every iteration of the loop, we only want to use it sparingly. So let's say here every 1000 rows. So if I need to put do events in my loops, I use the mod function, the mathematical MOD function to do that for us. So I've just put an if statement in here that ensures that the do events command is only executed every 11000 rows. So just go ahead and add this if statement to our ANOVA loops. And 11000 might not be enough because it might take more than the timeout value to do one hundred, ten hundred rows. So in your testing, set this to a reasonable value. It's going to add it into Ri loop as well, just to be fair, because we're actually adding extra time into this. That's rot do events does introduce extra time. Let's run that again. I'll just move this down here. Hopefully we shouldn't see Excel displaying as not responding, but who knows? Let's have a look. Ok, it's finished. Those bring this up here. And we didn't see Excel display is not responding, which is good. And we also didn't see a performance reduction. It's still 20 seconds and 0 seconds, so that's positive. And that tells me that I think doing, I do events command every 1000 rows is a fairly reasonable interval to actually do that command. Okay, so that sums up the second practical example in this lesson, the introduction to 2D arrays or multidimensional arrays, and a demonstration of the performance differences between using just ranges and arrays, which holds that data in memory for easy read and write access. I just want to make one final comment before wrapping up this lesson. As you witnessed in the second practical example, performing iterative operations of a ranges of cells adds considerable time to executing our code. And this is a common trap that VB I develop as often fall into. In my experience, I've been able to optimize data crunching macros that wants took minutes or even hours to run to me a couple of seconds. And that is simply by using and manipulating the data sets inside arrays as opposed to on ranges of cells. Obviously, the worksheet, read and write operations are what takes the most amount of time. And it is inevitable that in order to load an array or deposit the data back onto a worksheet, you need to perform read and writes to or from the worksheet. In the advanced content, I'll show you how to absorb an entire data set off the sheet into an array with one single read operation and had a dumped the entire array back onto the sheet with one single Raj operation to save even more time. So guys, that wraps up the lesson today. If it made it this far, I want to say congratulations, you've just learned the single most useful concept in Visual Basic other than the single dimension and multidimensional arrays learned in this lesson, you also learned about the split function, which enables you to break up strings into small components using a specified character as a delimiter. You also learned how to use the R and D function to generate a random number between two numbers. And lastly, you learned how to return a difference between two diets in a specified interval, IA, seconds, minutes, hours, days, weeks, months, or years. My name's Andrew. Thanks for watching and I'll see you in the next lesson. 7. 07 - VBA Lesson 14 - Dates: Welcome to Lesson 14. Dates. I just wanted to talk about the problems you will likely encounter with dates while coding and VBA, what to look for and what you can do to mitigate these problems. So today we'll talk about how Excel stores dates, how VBA stores dates, and yes, they are different. We will also go over the major problem in the way that dates are implemented. And I'm gonna show you a couple of ways to address these problems. It's important to understand how Excel and VBA actually stored dates. Because if you comparing the roll format of an Excel date and a VBA date, there will be a discrepancy. Excels doors dates as a number. It's a sequential number that represents how many days have passed since the first of January 1900. So as an example, the 11th of August 2020 will be stored as 44,054. And this is because the 11th of August 2020 is 44,054 days since the first of January 1900. Vba also stores dates as a number, and it's also a sequential number, but expressed as how many days have elapsed since the 31st of December 1899. So the 11th of August 2020 is stored as 44,055, being 44,055 days after the 31st of December, 1899. Full both Excel and VBA dates, hours, minutes, and seconds are expressed as a decimal of the dike number. So if for example, 0.5 is equal to 12 PM noon, or 0.25 is equal to six AM. Therefore, in VBA, the number 1.25 represents the 31st of December 1899 at 6AM. And in Excel, the number 1.25 represents the first of January 1900 at six, I am storing dates in this fashion makes it simple for Excel and VB I for that meta to perform calculations on dates. For example, counting the number of days between dates, adding dates together, et cetera. Microsoft sees this as a very simple yet effective concept. When Excel presents the date to a user on a spreadsheet or with VBA in the Immediate Window message box or use a form, et cetera, a calculation is performed that presents the data in a user readable format. And this is where we encounter problems because there are so many ways a date can be expressed. For example, US Long Date, us short date, European long day, European short date, Julian date, Unix, timestamps, et cetera, et cetera. All of these different ways to express diets can confuse or even completely stumped the built-in date calculation used to take a representation of a date and store in the sequential number format. If you use Excel as part of your working life, you will have no doubt experienced this. For those in the US dealing with data where the data stored in a European format and vice versa. Arguably, the problem is actually worse for those of us who don't use the US date format as the built-in calculation seems to favor us date formats. It also should but doesn't always take into consideration the local Date and Time Format sit on your computer. Here's an example of this date calculation problem. Excel has treated this European date as a US date up until the 13th day of the month, and then it is flipped around to a European date. Here's another example of some erratic inexplicable behavior of the date calculation in Excel. When we need to read or write data to or from worksheets with VBA and perform our own calculations on these dates. This is obviously a huge problem. Additionally, if we send a formatted date to a cell, it is again subject to Excel's flawed date calculation because Excel sees it as a date and it has to perform it's flawed calculation on that date to convert it into its number format. So what can we do? Put simply, you need to control as much as possible. And in order to be able to control as much as possible, you need to be prepared so you need to understand what you might be faced with. In other words, do as much analysis as possible. If you do require user input, don't allow them to talk in a Date. Use a date picker control to ensure that the date is accurate, where possible, store your data as a string on the sheet. This will prevent Excel from performing. It's wacky flawed calculations on dates. When writing a date to a cell, simply place a single apostrophe before your date string. This prevents Excel from performing a calculation on it. If you must store data as dates on a worksheet, which would be required if the users need to filter by date or use a date in a formula, use a non-ambiguous date format. For example, use alpha characters as the month, IA, AN as January. What this does is it will ensure that the Florida excel calculation can immediately tell which is the dy component and which is the month component. Lastly, perform your own calculations. If you are picking up dates off a sheet, don't rely on the basic built-in functions. Analyze your data as the first step in building your application. And this is so that you can have a better idea of what random date formats Excel will throw at you. They have for reliably performing your date calculations. Here's a real life example of a scenario that I was recently faced with and the steps I took to make sure that the dates were correct, my application had to import data from a CSV and as Excel opened the file, it performed it's flawed calculation on the date field, and this was the result of the Excel calculations. So my first step was to get the largest sample size I could get and assist the possible dates that I could potentially be faced with. That way, I can write my own function that can handle all of those dates and convert it into an actual date. So let's rebuild that function now. So let's begin building up function. I'll just stop by adding a new code module. Module six. And I'll just define a function. So I'm calling it gets detached from string. And what we need to supply the function is the dydt as a string. And I want to return a date. So the purpose of this function is to take the date that's in any random format and return that as an actual died. So performing my own date calculation on that string. So I've already performed my analysis on the largest sample size of data that I could possibly get. And I noticed that in this example they were true potential date formats that I could possibly see. I also know that this file is created by an application or a web application. Therefore, I can more or less guarantee that the diets are receiving the input aren't going to change terribly much because the file is automatically generated by a piece of software. And in the large sample size that I received, I realized that there was true potential outcomes of how Excel will present these dates. So what I normally always do is pass them up as comments in the top so that I can work from these. So the first thing that I noticed is that one date format has one space and the other date format has well, devs to spices here and another space there. The thing I've noticed is one of the diets has a twelv hour timestamp with him and pay him at the end. And the other is in a 24-hour time stamp. So now that I know these things, I can cater for these scenarios. So the first thing that I wanna do is handle these double spaces. So the way that I'm thinking about doing that is using the Replace function to replace a double-space with a single space. So first of all, I'll just declare my variable, just calling it STR attempt date. And now I will replace double spaces with a single space. But the Replace function here and the expression we want to send it is the STI date. And what we wanna find is true spices. And what we want to replace it with is a single spice. So I'll be able to send either of these diets to this replace function, and it will only affect the S1. The next step is I need to identify the difference between these two. So obviously we have one that is in 24-hour time stamp and the other one isn't a twelv hour timestamp and it contains an IM OPM. So what I can do is look for AM or PM in a string. And if that exists, then I know that I have this format. And if it doesn't exist, then I know that I have this format. So I can just use a simple if statement for that. So I'm just introducing you to this in STI or in string function. So what this does is it will look inside a string for a string that you define. And if what you're looking for does exist inside that string, then it will return the position in that string as an integer. And if it doesn't exist in the string, it will return 0. So I wanna start at character one. So you can start at any point in the string. And string one is what you're sending it, which will be this. String two is what I'm looking for. I'm looking for I am and I don't need to supply anything else. Now, if that is greater than 0, then we have I am in the string. So what happens if you've got PM in the string? Well, we can just use an oral statement and use the same thing. Or and we'll have an else else condition as well. So if we enter this portion of the if statement, that means we have this type of diet. And if we enter this portion of the if statement, we have this top of date. So the first thing we want to do now is split this date by spices. And that was a reason why I got rid of these double spices to begin with. They afford I'm going to need a new variable, an array variable. And I'm calling it SPL spice, so it's splitting by space. We go SPL spice equals split. What are we splitting with putting the template string? And we're splitting it by space. What that means now is I now have an array that should have three rows. First row being this portion of the date, the second row being this portion of the date, and the third row being this portion of the day. Let's first work on this portion of the site, which is the actual physical date. So now what I can do is I can split by backslash character, which will then lead me with another array. That is this portion. This portion. And this portion sums gonna need another array variable. And I'll copy that and I'm going to paste that here. Now, I know that the dydt portion is going to exist in the first row, which is bottom 0 in that array. So I'm using split again. And what am I going to split? I'm going to split SPL space. And I'm using the row 0, which is the first row. And the delimiter is backslash. So now in this, I'll hold three rows. The first row being the day, second row being the month, and the third row being the Gaea. Next up, let's deal with this part of the date, the time. And so I know that I've got two colons here and I can separate that by colons. And another, this will be the alpha and that will be the minutes, and that will be the seconds. So I'll just do the same thing here. Provide it some brackets. And I know that the Tom component exists in row two, which is one. So now I can begin coupling to give out date from what we've separated out from this string. And I'm going to be storing that firstly in a temp variable, which then I'll assign back to the function variable, which will be returned back from the function. So we'll just define that now. And that variable top is a dite. Now I'm going to introduce you to another function which we call date cereal. And so what this does is it will convert a date and we can specifically provided the Gaea month an integer so that we don't have to rely on the Yankee excel calculation. So I know that the year is in SPL backslash array, and I know that it is in the third row of that array. So the year is in SPL, real backlash, and it's in R3, which is item two because it starts at 0. And remember, the month is also in that array, and that is in row two. Therefore, bottom one, end the day is in the first row. Item 0. So now I will have the correct date stored as a date in this variable. So now what about the time? Normally I would use a function similar to date serial, which is called time cereal. However, the time serial function requires a 24-hour time. And unfortunately in this example, we don't have a 24-hour time, but that's actually OK because we have this component of the dye in time and it's got a I am and pm here. I know that I can reliably send this to a date variable and it have it convert correctly. And that's because it doesn't have our European diets doll here. It's literally just got the time with an IM and PM. So I'll just create a variable that will help us deal with that now. So scrap that equals. So normally actually was calling this out. So normally I would be using this component here, but in this instance, I actually don't need to do that. So I'll just comment that out for now. Because what I'll be using inside this array is actually in 12-hour format. And I can't use that indict serial. So therefore, I will just concatenate some strings together to generate this. And I'm going to assign that to this date variable as Beale space. And I want row two, which is one, because I'm referring to the array that is splitting the string by spices, so that's row one, and this is R2. And so I'm just concatenating strings here. So what this is going to return is this here. And it's going to be assigning that to a date variable. So now it's a simple matter of adding these together because we have a fraction here, which is the fraction of a whole number that represents the die and the time. And then we have a whole number here which represents the actual date. So I can just simply add these together. And we'll have a full date time. Now I'll return that back to a function variable two equals. And I will also function. So that handles this date. Now let's talk about this date. So I know that this contains one space and I can split it by day and time. So I'm just gonna copy this one because we did exactly the same thing. Likewise, I know that I've got backslashes in my date component, so I can also use this. And I know that I have a 24 hour time value here. So I can actually use my colon split now. And I can also now begin coupling together my diets. So with this date, similar to this date, I know that it's day, month, year, so I can actually just copy that straight up. And this is where it starts to differ from the one above. And they are going to use time cereal, which is similar dydt cereal to generate the time value. Okay, so the first thing it's asking for is alpha. Alpha is in the first row, spiel colon, and it's the first row, meaning 0. And the minute is in row two. And this data up he had doesn't contain seconds, so I'm just going to supply to 0. So now we have our time fraction and we also have our diet as a full number. So I can just add them together. And I can return that back to a function. And I also want to exit function here. Okay, so let's test this. I'm just gonna put a breakpoint on the entry point of a function. And I'm going to copy this date string here, the first one, and I'll just bring up l Immediate Window. Now, question mark, get date from string and I'm going to send it that date. What does it give us? Oh, okay, that's good. That's given us a sabo function not defined. And that's because I've misspelled this. I didn't control space. Backslash. That will also mean that this one is not correct because I copied it from above. Does that work? Yep. Okay. Cool. Okay. So let's have a look at this here. If I jump back down into the immediate window, you can say question mark STI date. And it's returning the string, which is correct. And now it will replace the double-space. But in this instance we don't have to spices. So as strings should be identical, if I just jump back down into the Immediate Window, will, we should be able to say that. Let's delete, delete that line so we can compare them side-by-side. Yep, identical. Right? Now what we're expecting is this to skip this if statement then jumped down into this one. Which does. Okay, that's good. We're now splitting by space. So if I now add this to watch. Current contexts is fine. Okay, and in the watch window we can see we now have a string split by spices. So we have the first row which contains the actual date, and we have the second row which contains the time. That's great. So back into our code window. Now is splitting by backslash. So sign again, what I'm gonna do is add a watch. And I'm only concerned about these contexts which is great and no damage my watch, we can say that SPL backslash. If we open that up, we have split up by backslash switches the dye in row one, the month in row two, and the year in row three. Perfect. Yeah, backup to echoed window. We're splitting by colon. And I'll just add a G9, I'll add a watch. And again, are only concerned with these contexts. And we can say that we have the time they answer that ours, and we have the minutes. This next line we are using the serial function with supplied at the year, which is 20-20. And we've supplied at the month which is July. And we've also supplied at the dike, which is the 21st. So if I, if I pass that lawn and a hover over here, we should see is exactly that. The 21st of July 2020. Likewise for this date time stamp at the moment is 12. I am. But we've got the time serial function was sending it nine as the hours and was sending it 24 hours and minutes and was sitting at 0 is the seconds. And as we can see, we've got 924 I am. And no seconds, which is great. So now I can just add these two together, which will do here. We can see if we hover over, we say the 21st of July, 20-20 at 924 AM. And then we'll assign that back to the function variable so that we can return that down into the Immediate Window, hit exit function. And we'll see that it's provided us the correct date and time. Okay. So i'll just delete everything in the immediate window and we'll try the other date. So I'm just gonna copy this one here and hit the question mark and I type it. Yeah. And I have the bracket that a string here and inside the quartz and I'll just paste in that. And let's hit enter. Okay, so now we're once again back into function. The first thing that's going to do is replace the two spices. As we can say is we hover, we get through spices between the hot and the time pot. So now we don't only have one space because we've replaced two characters with one. Now we expect the code to into this portion of the statement because in the string we have AM or PM. That's great. Yeah, we're gonna split by Spice and alpha jumped down into the Watch window. It should still contain those watches. We added the SPL spice array contains three rows, which is great. We've got the date, we've got the time, and we've got PM, and they're all stored as strings. Next, backup on the Code window, I'm going to split the first pot by backslash. Can say that we have split that string by backslash. So we've got out day, month and year. Excellent. Now I'm going to assign that to a date variable using serial. We've sent it the year, we've sent it to month. We've sent at the day. And yet first of July 2020. And this line here is the riskiest line in our entire routine. And that's because we are relying on excels calculation to convert this string into this date variable. But because we are only talking about a time at confidence level is extremely high because we don't have any weird date formats to worry about. So I'll just do that now if height and we should see that. Yep, 7058 PM. Great. It's actually turned that into a time. So now we'll just add those two together as we do. And I'm returning that back to the function variable and exiting the function. And we should see that appear in the immediate window. You go. So that's just a quick example of how I dealt with that problem. So the first step was to analyze my data. And I noticed that I had one of two date formats. And then I built this function that handled a the one of those formats. Okay guys, that wraps up this lesson. So in this lesson we talked about how Excel stores dates and how VB ay stores dates. We talked about the numerous problems that you might encounter with diets thanks to Excel's dydt calculation. And I walked you through how I go about preventing or minimizing these issues. I also introduced you to a couple of new UVB I features and functions. The first one being the Replace function, which is essentially exactly the same as fond and replace in Microsoft Word. And I also introduced you to in string or an STR, which will look for a particular string inside another string. And if it does exist, it will return a number which represents which character number the string you are looking for exists inside that of a string. And if it doesn't exist, it will return 0. And lastly, we also looked at date and time cereal. And that allows us to specify what is the day and what is the month, as opposed to using Excel's default calculation method? A guy, guys, my name's Andrew. Thanks for watching and I'll see you in the next video. 8. 08 - VBA Lesson 15 - User Forms and User Experience: Welcome to Lesson 15, user forms and Introduction to User Experience. In this lesson, I just want to introduce you to building user forms and provide you with a couple of user experience tips that will help make the use of your forms a more compelling experience. Let's begin back in the IDE. So the first thing we wanna do is we want to add a user form. So I'm going to right-click over here in the project window. And I'm going to select insert and I'm going to select USA form, which you can see has displayed the use of form design window. And this is basically what you see is what you get editor. So this is how you design your forms in this view here. And you'll notice that it is also brought up a little toolbox here, which contains all of the controls or at least some of the controls that we want to be using. Now after adding my form, the first time we're going to do is I'm going to give it a name. And to do that, I'm just gonna bring up the properties window up here, and I'm going to call this f RAM underscore name address. So that's the name of the object. That's what we're going to be referring to this form as in code, if RM underscore name, address. And I'm also going to give it a caption, which is what is displayed up in the top left of the window. And so the caption property here, it's going to change that. And as you can see, it's changed the text that's in the caption on this uniform. So what are we trying to do with this form? What we want to do is capture user input and then put that onto a spreadsheet. And in a previous lesson, I'll just bring up Excel. We had the cost data sheet. I'll just bring up the bottom of that so we can, so we can say that the cost data sheet and that has the firstname, surname, city and country. And just disregard the sentence because that's the formula that we built which just gets sentence. But what I wanna do is I want to create a form that captures firstname, surname, city and country, and then populates that on a new row in that sheet. So thinking about what we will need, we're going to need for textboxes. And they're going to contain the firstName, the surname, the city and the country. We're also going to need four labels to label each textbooks. And that so the user knows what to enter an age of the textboxes. Lastly, we are going to need a save button. And the cite button is then going to execute some code that's going to populate what they've entered into the spreadsheet. So I'm just going to minimize that and we're gonna go back here. So where's my toolbox here? Yeah, so first things first, I'm going to add some text boxes. So how I'm gonna do that is from the toolbox, just click on it and then drag it across to the form itself. And I'm just going to drag for cross. They're not aligned or anything at the moment, that's fine. Just for now, also adjust the size using a track pad in this video so the mass agility is pretty low. And also we want to add a few labels. So let's bring these across. And I'm going to add some labels. And I'll just make a small trackpad is annoying. I actually just even just the size and then copy and paste days. So copy and paste sees exactly the same, just Control-C, Control-V. First things first what I wanna do after we've added days is I'm going to give them a naming convention. So I'm all about naming conventions. And when we get down to the code, I'll show you why it's very useful to use a naming convention. So we'll start with a text boxes. I'm going to call them t, x t. And the first one was firstname. I'm the school name. I might even give us a label as well. Lbl, firstName, and I'll give that a caption. Yep. And this is so and I give that a caption to what else do we have? We had firstname, surname, city and country. Great. So go back to here, TXT city and there'll be LCD and give that a caption as well. And TXT country, give that a label. So later on in the video, I'll explain more about user experience, but I just want you to think about this, right? So when you're designing your forms, think about the travel of the mouse and the travel of the uses eyes. So in the real world, you must definitely see a label to the left of a textbox. But when you think about how you use it, digests that information, they need to first look at the label and then move across to the textbox itself. So keeping that in mind, it's actually much better. I'm just gonna move these down to put the label directly above the text box itself. And that makes it easier for use. It's digest because they can just look down the form as opposed to looking left and then right and then left and then right and then left and then right. So I'll just do that for all of these. Bring that up a bit more. I also may expand the size of each of these text boxes there a little bit small the moment, so I just might make a little bit bigger. And now I can make out form smaller. And now we'll add a command button and that's to trigger the code that executes that takes what's on the form and populates on the sheet. Let's put that down there. Again. I'll just call this C and B and just go save. And I'll give it a caption as well. Safe. I might just make that also the width of l textboxes. Bringing that back up. Alright, so now we have our basic form design complaint, and we've also got naming conventions for our controls. And that is very useful as I will show you right now. So I'll just bring this properties window down a bit. So now I'm going to go into the code module of this object, which is the form object. So it's got its own private code module inside it. So if I just go view code, and that is the code module inside this form. So the next thing is how do we assign code to the event being the click of the cite button. So there's a couple of ways to do this. The first way I'll show you that the ACS way. So go back to the form design. Just double-click on that. And if I select this and double-click on the object, it will automatically generate a sub, a Private Sub that catches the event of the click so that we can execute code on the click of that. So as you can see, it's got the command button name and then it's also got the event after that. So the other way is using these drop-down boxes at the top. So as you can see, we've got the controls that are on that form, which is inside the uniform object. And if I hit CMB save on the left side, it automatically creates click. But there's also on the right-hand side, a bunch of other events we can catch as well. So if I double-click it, if they drag over, et cetera, et cetera, let's just leave it with Click. So the first thing we want to do is one of validate what the user has entered. I don't want to enter blank rows into our worksheet. That's actually quite important when you are building code based on user entered information that you validate what the user has entered. So I'm going to do that is I'm first of all going to look at the textboxes and I'm going to measure the length of the value, which is what the user has entered, and ensure that they have actually entered text in all of the fields. So in this case, all of the fields on this former mandatory. So let me show you how I'm going to do that. So I'm going to first create a blank object. And this type is just an object which is a generic object type. So you can assign it pretty much any object. And I'm gonna use this object inside a for loop. And we've used these types of folates before. But for explanation, what we're doing is we are cycling through all of the controls on the form and each iteration of the loop, it's going to assign the current control to this object so that we can interrogate that object and then perform some logic on it. So let's stop that for loop. So the controls method of the form object is a collection of controls. So just specifying controls will be a collection of controls. But you can also specify an individual control by supplying it in index or name. But in this instance, I just want the collection of controls. And we're going to cycle through each object in that collection of controls. Now, inside this collection of controls, it will contain every control on this form. So it will include the labels, it will include the text boxes, it will include the button and any other control that you've added to the form. And this is when naming conventions are very useful. So it because we have named all about textboxes, TXT on the school, and then the name of the control. We know that if the control name starts with TXT, It's a textbox. So I'll just add the next. So that's our loop. So now I'm going to interrogate the first three characters of each of the controls. I'm using left as the first three characters. So now I can interrogate the length of the value IA, what the user has entered into that field. So if they haven't entered anything, so if the length of what they've entered in the field is 0. And I also want to exit the Sabah don't wanna continue if we've reached this point of the validation. And the it's a text box that doesn't contain a piece of information because all of the fields are mandatory. So I'll just make a quick comment. They are true. So I validate all fields. So now we can start on the solving process. So thinking about what will make will definitely need the workbook and the worksheet object. So I'll just declare those. And I'll set them. And I'm only going to sit them after the validation because I don't wanna just waste resources. And the sheet we're looking for is cost data. So now I want to enter the information onto the worksheet. So I'm going to rely on the used range row count method of the worksheet itself. But I'm going to assign that to a variable or a number. So I'm using a long top here. So it's a whole number without a decimal, but it could potentially be greater than 32.5 thousand. And that's because in this instance, we march over time, have a user into more than 32.5 thousand rows. And I don't want to limit it to just 32.5 thousand. So in this instance I'm going to use a long and I'm going to assign that using the used range row count method. And so this method, they used range method, dot ROS dot count is going to return us the current row that holds the last piece of information. So in this instance it's going to return us a six because six is the last row counts of the used range. But because we want to add the values into the next hard down, I'll just be adding one to that when a populating information onto the worksheet. So let's do that now. We've done this numerous times before now. So x plus one. So that's the row number and the column is one. And I'm actually just going to copy and paste these safe topic and met a bunch of times. But I'm just going to adjust the column numbers. And I'm also going to adjust the actual controls themselves. Surname. Ct. So after we have executed these lines, we have saved that record to the worksheet. And now I also want to reset the form. So I'm actually just going to use this method, which is a full loop. And I'm just going to go through each of the controls that start with TXT and we're going to set the values to nothing. So I'll get rid of that. So let's test this now. So I just set up a window that we can see the worksheet underneath so we can say it adding as we go. And I'll bring this down here. And I'll jump back on to the form. Close up for now. Play. Okay, so first of all, let's test the validations. If I hit save, I should see a message box. It says please enter old text boxes or value in all textboxes. And it has, we go, I've spelled valuing correctly. And let's just enter a one-by-one. John. Yep. And now we should see this populated new row. Boom, there we go. That's populated a new row on that worksheet. And also it has cleared all the values out. And that's so that if we hit save multiple times in a row, it doesn't into multiple rows of the same record. Okay, close that. Now, bringing this back up, jump back to the code. Let's just explain it one more time. So in a validation step, what we're doing is we're first grabbing the complete list of controls on the form. And in each iteration of this for loop, we assigning the current control to this blank object. And then we're interrogating that object to see if left of that object's name, or the first three characters of the object's name is TXT. And then if it is, we are then testing the value, but we're testing the length of the value. And if that value is 0, so if the length of what is entered in that text box is 0, then we're going to present the user with a message box and that's going to say, please enter a val lie. This change that value in all of the textboxes. And so then once we have completed the steps of validation with sitting the workbook and the worksheet object. Way grabbing the used range row count, which will be, which was six note seven. And then we are populating that onto the sheet itself. And then we are using the same foreach loop method down here to clear out the values of that textbox to refresh the form and allow the user to enter something else. So now that we have the basics under control, let's start talking about some user experience tips. Tip number one, keep things simple and Cape things smart. Eliminate any unnecessary fields on the form. Use conditional logic. So if you do need a field, but you only need that field in certain conditions, set their default state to hidden, and show them programmatically. It is much better for us to put in the effort writing code to handle the complexity in order to keep things simple for the user. Tip number two, similar to tip one, use conditional logic as much as you can to keep things smart. Use the many events that are at your disposal. Catch the event of a user entering something and then do something based on what they have entered. A good example of this that you may have seen that in the real world is on some address capture forms, the state combo boxes automatically populated based on the country that is selected. So for example, if I state that I'm in Australia on the form is not going to show me US states in the state combo box. Tip number three. It's better to multistep your data capture over multiple forms, then make one enormous form that overwhelms the user with the amount of data that they need to enter. While VBA forms on built for this modern concept, it's fairly easy to implement this in a seamless fashion. For example, you can build numerous screens across multiple forms and then switch between the screens programmatically, giving the illusion of one single form. To do this effectively, you will need to build a transition routine that takes things like screen position into consideration. What I mean by this is to take the x and y coordinates that represents where the form is displayed on the screen. And then assign that to form two so that the next form appears in exactly the same place as the one before. It just provides the illusion that multiple forms are actually one form that is dynamically changing. Tip number four, as stated in the practical example, puts you a field labels above the field not aligned to the left or the right. What this does is it simplifies the uses comprehension of the form. It also provides fewer fixation points, i, j, the places that the user needs to look on the form. Tip number five, group related fields into logical groups. If you are using more than six fields, group the input fields into logical sections. Additionally, research suggests that logical groups should be oriented in columns. Tip number six, size you'll fields appropriately. There's nothing worse than entering text into a field that is too small to show what they're entering in one line. Conversely, it looks really weird to enter a small amount of text into an enormous textbooks. Tip number seven, ensure that the tab index is correct for all controls on the form. This allows the users to easily navigate through the form with the Tab key. You can view and set the tab index in the property window of each control. Tip number eight. While Excel and VBA doesn't make this particularly easy, the use of images or icons can make you a far more attractive, branded and professional. But you really need to be careful here. Due to the VBA forms platform, using images as a replacement for other controls may have undesirable results. And that's because the image controls are literally just static objects and they don't behave in the same way as other controls. In a letter advanced lesson, I'll show you how to properly use images as controls. For example, how to get a hover over effect, and how to get an image to depress like a button control and other control animations. So that sums up this lesson and the first intermediate series of VBA beginner to Ninja. I want to thank you for watching my lessons. If there's anything specific you'd like to see, please leave a comment. Also, if there's anything that you are having trouble with Eva with the course or your own personal VBA project, please feel free to reach out to me. I am always happy to help. The next series of videos will also be intermediate content. However, we will delve into more scenario-based problems that you will no doubt encounter in the real world. My name is Andrew. I hope you've enjoyed this course and I will see you in the next series of VBA, beginner to ninja.