How VBA Objects Really Work in Memory

hello and welcome to this video so in this video I'm going to show you how VBA objects actually work in memory and once you understand this you'll be able to declare create and pass around your object variables with ease and incidentally this is something that most VBA users don't understand so let's get started by looking at basic variables so in VBA we create a variable like this we do dim to a or tal which is the name and we say as and then the type so what's actually happening here is that we're creating a space in memory which is like essentially a cell in memory and we're going to place a value in this cell and we do so by using a sign like this so essentially what we use variables for is while our code is running we want to store values in a temporary place and that's the whole point of using variables now we do two things with variables we place the value in the variable and we take a value out or read a value from it so in this case I've placed the value in total now we might want to read this to a message box for example and what I can do is just use message box and the variable now VBA is smart enough once we use total it will go to that memory location and give us back the value and we don't have to worry about memory where is stored or anything like this so you can see that the value we got here was 67 now we can also write it out to a worksheet we can write it out to a file if we wanted and we can do many different things but essentially with a variable we're placing the value in I'm taking a value out now if you've used basic back in say the 1980s you may have seen the left keyword so what lead is used for his assignment now we don't use it anymore but it's useful here because we're going to be looking at the set keyword so let basically is the assignment it's saying let total now have the value 67 and we can use this with other types of variables such as a string variable and in this case we say let the name now equal bill and this places the value bill in the name which as I said is a cell in memory now if we are dealing with objects so for example an object will be something like a collection so let's say call as new collection and we'll do more about this a bit later but imagine we say dem call as new collection we then use the set call keyword to set the collection to something now normally we wouldn't use new in this situation and we'll see why later but we'd say set called equals new and then the set will assign the new collection to call the key point here is that when we're dealing with basic variables we use let but when we're dealing with objects we use set so let's now have a look at objects and we're going to use the collection in VBA or for our example so you may have created a collection like this you use dim and you use new and now we've got a collection variable well what's actually happening is VBA creates a new collection somewhere in memory and secondly it creates the variable called and then it places the address in memory in Col now what happens is anytime we reference call VBA seamlessly gives us access to the collection so we don't need to worry about the fact that it's not stored here but this does have some implications as we'll see a bit later but the reason VBA does this is because if we want to pass around our collection we don't want to create a copy of it we just want the pasty address around and by having the address in the variable we can do this so for example if we wanted to do a print collect function like we have here so we want to print out what's in our collection so we do print collect and we pass it let's just say we have a collection of fruit and imagine like we add Apple here and then just imagine that like we add another 10,000 items now if we didn't have the the pointer method that I've just shown you if we just passed in the item well then VBA would have to create a copy of the collection and when it creates a copy of the collection it has to create 10,000 items so by having it as a variable we can just pass the address this means that it's much more efficient now as you can see you don't need to worry about this you basically just pass this in to the soap and VBA takes care of the fact that it's a pointer now in fact it's hidden so well that probably most people don't even know that it is a pointer rather than a variable so in VBA you may see code like this so the first one is saying dim through this new collection and the second one is saying dim through this collection and then we set it to new collection so what's the difference between these lines of code well what these lines of code are doing is actually the same but set gives us more flexiblity so when we use dim fruit as collection here what actually happens is VBA creates a new variable called fruit and it's kind of saying in the future this is going to be a collection but at the moment it's not not anything it's just kind of primed in the future to be a collection now when we do set it's doing two things the new collection part creates a new collection it's somewhere in memory and then VBA takes the address of that collection memory and it puts it into our fruit variable now the dim line does all these three things in one go creates a new collection creates a new fruit variable and it takes the address from the collection and places it in the fruit variable from this point of view these lines are doing the same thing but the difference is is that if we use dim as new this means that every time we run our code exactly one collection will be now when we use set we can use set anywhere in our code so this means that we can put it in far loops in if statements and it gives us more flexiblity so it means we can create any number of collections in our code so let's look at a practical example of using set and new so this is a list of names and got the country that they're from and what we're going to do is we're going to store each name and country in a class module and then we're going to add it to a collection so let's have a look at the code for that now if you look in the left you can see we have a class module called CLS customer and in this class we have basically three variables first name last name and country so we want to fill these every time that we read a customer record so how we do this code is as follows the first thing that we do is we create a collection and this is a new collection because we're always going to have one collection no matter how many customers there is for this particular application to just gonna be one collection so we don't need a set statement now the next thing we want to do is we want to get to range to prepare a range variable and we simply say a set range equal sheet data and then range and we say a1 and what we're going to get back is the current region so the current region is all the adjacent cells so once we have the range it's very easy for us to read through the data now we start up to because we want to skip the header and then we go down to all the rules and data are now each time that we come to a roll what we want to do is we want to add it to the collection so we do range cells and it's going to be I which is always the control one is going to be the first name value and so what we do here now this is that the critical part is we have a customer and the customer is the object CLS now now each line or each row that we come to we set the customer equal to a new customer object so we're creating a new object in memory each time we hit that roll and then what we do is with your customer and we do first name for example equals this one how I basically just copy these lines down and this one is the last name and this one is the country and then what we do is we simply add it to our collection so writing the cost to mark to our collection which is called customers and then at the end it will be full that is of course Yui 2 and this should be a 3 so let's run this code and we got our range we step in now let's do all of the H so let's have a look here at our watch window delete these existing items and let's put it in our collection first so the collection says object with block not set and that's even saying that even though we've declared it as a new collection now the reason for this is that when we use them as new it remains on set until the first time we actually use the correction so this is just something the vba does to be efficient and we don't have to worry about it so much now the set customer sets it straight away so you can see that this customer is just an empty item but it is a new item so we step past the first one you see that I put Laura in the next one put in Stanley is the surname and then I put in Germany as the country and we add this to our customers now watch this as we had you can see customers now has stopped with that error about not having been set and you can see it as one item which is Laura Germany Laura Stanley so we go up to the next one and now we're creating another one in memory so customer was previously pointing to the first it's now gonna point to another one we don't need to worry about that pointed to the first one because we've already placed this in our collection so we step down Jude and again this time first name is Alan and last name is Oliver and it sets the country to be Luxembourg and then we added it to our collection so now you can see we've got two items in our collection and so on we set it again we go down and now we've got a third item in our collection and this is why so for every item that we want so for every object which is in this case the CLS customer class module every time we want an object we use new and it creates a new object in memory now if we didn't do this every time we would just be overriding whatever's in the item and this is a mistake a lot of people make so let's have a look at what that would mean because it's very interesting to see so we run the code again we get down we have the customer first name you see that it says variable block not set so what we will do is we'll put in a new here so we've got one customer and one customer only we're only doing new once now we go down here we said customer first name let's have a look it's Laura Stanley and Germany so the same as before and we add this to our collection so the same as before but when we come to the next one it's the same customer item so in other words it's the same object in memory because we've only ever created one object and we go down we set the values again and you can see the values are changing in the collection because it's the same object that both are pointing to now when we could do the next one doing it next country and see that Germany is going to change for both of them and it changes to Luxembourg for both of them because they're both pointing at the same one so the key thing here is that every time you use a new object you use the keyword set and the first time you don't need new because you're not setting at this point so what happens when we assign from one object to another so in this cold what we have is we have customer one let's step to the cold and we can see exactly what's happening so we say customer one we set the first name and then we set the surname so we've got Jane Murphy as you can see now the second customer is set to nollie we haven't assigned it to entertain yes and now we are signing it to customer one so you can see they have the same values but what actually happened here is that we didn't create a copy we're just bored pointing at the same object and we can easily prove this all we've got to do is change the first name and each one of them so for example we'll change the first name in customer one and we'll change the first name in a customer too so we can just go up here and has changed the first one to Bob and the second one will change to Tim so put a breakpoint in the code and we'll run the code again to here so at this point in time they're both the same we've assigned one to the other and now we change the first name of two to Bob you can see that it changed in both of them and that as I said is because they're both pointing to the same object now if we change Tim or change the first customer name to Tim you can see it other changes in bolt and this is because again it's the same object so one way we can look at this is just like a variable so if we have a simple variable let's just say use vera and let's just have two variables we'll call it X as long so two simple long integer variables if we set x equals four six and only say y equals x the board have the same value in them so what VBA is doing is copying the six from X and puts it in white now if we change the value in X and won't change the value in Y because they just told values so it's kind of similar what's happening with customer board of customer one a customer - they're holding addresses so when we say set customer 2 equals customer 1 VBA will copy the address from here and places it in customer to now there's no way with these objects of copying them without manually doing it so the way we do it is we declare a customer to as a new and then we assign the value so we have to assign each of the values so we normally create a soft to do this so we say a customer - first-name equals customer one first name and then we don't need to set here so if we run this code again you'll see that the board equal to J and what the last name is different in Murphy because we've only set the first name so we didn't we created a second one using new we've used new twice so that's the clue and we have two objects so now when we change Bob in customer - it changes in customer - but not in customer one because they're both pointing at different objects so you can see the only way that we can copy the item is by going through each object and then copying the different parts that we want to copy so it's very important when you're using VBA and using objects that you understand this so in this video we looked at VBA objects in memory now the first thing we looked at was that new creates a new object in memory but the object variables contain the address they don't contain the object now we don't need to worry about this in most cases because VBA takes care of it for us whom you set a new together on the same line it gives us flexibility because we can create as many objects as we like and setting one object to another copies D address only it doesn't copy the object so this is very important to keep in mind especially if you're using collections and dictionaries so I hope you enjoyed this video if you enjoyed the contents then please click on the like button click on the subscribe button to get notified about my upcoming weekly videos and if you've got any comments questions ideas then please add them to the comments section below so I also have a free VBA vitals cheat sheet you can get that from the link in the description so see you on the next video

Channel: Excel Macro Mastery

Views: 46,401

Keywords: vba, excel, microsoft excel, excel vba, visual basic for applications, excel tips and tricks, vba objects, excel vba object memory, excel vba objects, memory in vba excel, microsoft excel (software), excel visual basic, advanced excel tutorials

Id: -sj0vvYT-Mk

Length: 17min 45sec (1065 seconds)

Published: Thu Nov 21 2019