How VBA Objects Really Work in Memory

[@Excelmacromastery]

I hope you enjoy this video about a little-known but important topic in Excel VBA.

[@8ballWASD]

I've always wondered how it actually works in memory. And the truth is that even experienced programmers don't know how to explain this. They usually answer with "oh, I don't know the technical details behind it, it just works", but here I got my answers. Thank you for the video.

[@paulzieper2269]

Thanks so much. Your lessons are great. I'm actually going to use your lesson at work today.

[@Djrome1]

Thank you for your pedagogy and VBA popularization. Just one things about "Setting one object to another copies the address only", I thought you could talk about "Byref and Byval" on the same topic like it play a key role about Memory (like pointer in C), isn't?

[@rrrprogram4704]

I had previously read your "complete guide to VBA objects" and that was 50% going over my head.... by now after watching the video, i went through the article once again .. and I am able to comprehend 90% of the content.... THAT's how the video makes it easy to Understand and in a very short time ... Thanks for the video

[@DarrenSaw]

Not sure my life will ever be quite the same again after watching the series on class modules! Fantastic, detailed and simple to follow explanations!!

[@ramuthra1]

Thank you so much. Explaining what is going on from the perspective of memory literally cleared up months of confusion for me. Why does nobody else explain it like this!? Please keep making VBA/Excel videos - you're the best out there, hands down.

[@Excelmacromastery]

Thanks Ryan, Glad you liked it.

For me, as a beginner in VBA, that was the biggest hurdle for a slightly better understanding. I wish I had seen this video a few years ago. Thanks a lot for this great channel.

[@gonzaortin739]

I can now understand some behavior that was totally strange for me... your videos are pure gold

[@gonzaortin739]

For example, the Byval modifing an objet as if it was Byref. The thing is that VBA gets the address and modifies the object

[@iincitr]

Hi Paul, you are making the most original videos on VBA. Thanks again.

[@jimfitch]

Thanks, Paul. Best explanation I’ve see so far.

[@nguyenvuphuong2048]

Thanks so much. You are the best! I applied classes in my work.

[@rrrprogram8667]

BRILLIANT piece paulll.... Absolute respect to ur work... Best channel for vba

[@jacobstewart5120]

I wish I would have known about Let and Set last year. I redesigned an entire project because I couldn’t figure out why I kept overwriting my collection! Thank you for the always helpful videos!

[@Excelmacromastery]

You're welcome Jacob

[@charliesk6239]

Awesome video Paul. Finally, after 15 years of dodging objects and using Types at best, I am writing Class-y code (no pun intended). Keep on carrying the torch into these less travelled - yet highly valuable - areas of VBA. Can't speak for the rest, but I sure as heck learn something new with every video you put out. Thank you for continuing to give back.

[@Excelmacromastery]

Thanks Charlie

[@lloydmcleod629]

How do you use VBA at your job?

[@jonkirk2118]

This was a fantastic explanation of how objects are stored. Classes can be thought of as blueprints, and New creates a new instance of that class (the verb is "instantiate") giving you access to its properties, methods, etc. They're very useful once you get to grips with them. Many thanks.

[@Excelmacromastery]

Glad it was helpful!

[@aikkoonwee9870]

Very concise and easy to understand vs reading through stackoverflow.. Thanks Paul for this great video.

[@Excelmacromastery]

You are welcome!

[@stevennye5075]

A good explanation of a complex topic

[@WeisSchwarz]

I'm starting to learn python along with VBA, this explanation even helps me on understanding object and class. Great job.

[@Excelmacromastery]

Glad you like it.

[@edge5817]

this kind of "How Tos" are super helpful... thank you so much Paul

[@frikduplessis3869]

Awesome awesome awesome video thanks Paul 👏👏👏

[@richrobins5711]

Nice work! I hope that later on we get into Objects with methods and properties! :)

[@GanovAlex]

thanks, very good explanation

[@nadermounir8228]

Thank you for recommending me to watch this video. It is well explained and helped me understand why we use coll as collection then set = new collection and also new cls. I now understand it. Thank you again :)

[@Excelmacromastery]

You're welcome Nader

[@wayneedmondson1065]

Hi Paul.. great stuff.. taking the engine apart and understanding the mechanics makes us better drivers. Thanks for sharing all your great resources and knowledge. Thumbs up!!

[@kiwim3p587]

Thank you for the new knowledge. A quick question, I have noticed when you perform and loops you use i as long, I have always used i as integer, why do you use a long for this purpose, is it more efficient?

[@MamToCos]

Dear Author, I am your listener. I have very often situation when I have a few modules which have the same initiations variables. How is the best way to set up those variables in one place? How can I recall those variables? I will you grateful for you answer. Thank you for you effort. Greetings!

[@duncanmorrison9860]

Thanks. This was a really good video. The content on your channel is excellent!

[@ouzytheoriginal]

was good and brief thanks

[@dogustinas2918]

Great video with clear explanations thanks a lot ))

[@jtmachovsky7216]

This was a good video with some great examples, but I kind of wish your examples had stayed with using two methods instead of doing it all in one. It's good to know that passing a collection into a method doesn't make a copy of the whole collection in memory, but I think especially with methods it's easy for people to get confused about how the "original" collection changed when they didn't intend it to. A good example of showing the difference between ByRef and ByVal may have made that clear.

[@rrrprogram8667]

Keep up the awesome work... Love following this channel

[@joaocustodio2094]

Paul, another great video. Thank you for sharing your knowledge.

[@gondi1272]

Great videos and explanations, I’ve learned tons of VBA stuff from you. I have one question. You mention that simple variables are stored ’like in a cell in memory’. Is this actually true..? Aren’t all values - both ’simple ones’ (long, int, etc) and objects - always stored in VBA controlled memory space and in the user/application context only memory pointers exist? So if you let the integer total=67 the value 67 is actually stored in VBA memory space and the variable ’total’ holds only an address pointer.

[@joniandre10]

Excelent explanation, thanks. is it possible in the future to do a video about progress bars ?

[@Victor-ol1lo]

Great !!! Thanks a lot for the awsome video !!! Thumbs Up !

[@ahmedabdelkhalek3489]

Thank you paul for sharing this advanced VBA content :)

[@albertbatfinder5240]

You are sad,” the Knight said in an anxious tone: “Let me sing you a song to comfort you.” “Is it very long?” Alice asked, for she had heard a good deal of poetry that day. “It's long,” said the Knight, “but it's very, very beautiful. Everybody that hears me sing it - either it brings the tears to their eyes, or else -” “Or else what?” said Alice, for the Knight had made a sudden pause. “Or else it doesn't, you know. The name of the song is called ‘Haddocks' Eyes.’” “Oh, that's the name of the song, is it?" Alice said, trying to feel interested. “No, you don't understand,” the Knight said, looking a little vexed. “That's what the name is called. The name really is ‘The Aged Aged Man.’” “Then I ought to have said ‘That's what the song is called’?” Alice corrected herself. “No, you oughtn't: that's quite another thing! The song is called ‘Ways And Means’: but that's only what it's called, you know!” “Well, what is the song, then?” said Alice, who was by this time completely bewildered. “I was coming to that,” the Knight said. “The song really is ‘A-sitting On A Gate’: and the tune's my own invention.” Lewis Carroll, Through The Looking Glass

[@charliesk6239]

Nice Analogy. WhatSongisCalled > pointing to > SongName > pointing to > Song. Moral: Lewis Carroll knew a thing or two about Objects - albeit in the medieval times.

[@rods6405]

Thnak you for explaing this "New" feature with objects

[@mike_case]

Thank you for great content!!! I will use it for sure

[@maciejbeker6212]

This was great, thank you for this explanation.

[@weibinren92]

Hi, in your guide you wrote this: Sub EmptyColl2() ' Create collection and add items Dim coll As New Collection ' add items here coll.Add "Apple" ' Empty collection Set coll = Nothing ' VBA automatically creates a new object coll.Add "Pear" End Sub If we used Set in the above code to create the new Collection then the “Add Pear” line would cause an error. I don't get why would the “Add Pear” line cause an error.

[@khalidalisawi8037]

really you are great and your videos is very interesting. I get more benefit when follow your account and my level programming is progressing but I get problem when write some programs, mu language is arabic , vba display the names of students as collections of questions mark I hope you help me to beat this problem

[@7Denial7]

Thanks for the video! But I think there's a little mistake here. When u write Dim col as New collection at this point collection isnt created right away and memory isnt yet allocated for it. But once u use the collection for the first time down your code exactly at this point collection is created. And the difference between Dim as New collection and Dim as collection is that in the first case u Can never check If your object is Nothing because whenever u refer to it when it's Nothing it gets created and is not Nothing anymore

[@PrincePedia]

Thanks, Paul

[@PrincePedia]

Thanks, Paul.

[@ursschlegel4948]

Hi Paul, many thanks for sharing another excellent video, which will incorporate into my projects straightaway. One question or request from my side: are you going to cover Power Pivot and related subjects as well, e.g. DAX, Cube functions?

[@rrrprogram8667]

ExcelIsFun youtube channel by mike is the best for dax and power pivot

[@dipankarbhakat3567]

Sir, thank you for your videos in youtube. These are very helpful to me. But i cant solve a problem. There are rwo userforms. In the second userforms label's caption is the first userform name. Then how to show the first userform. Please

[@rrrprogram8667]

Good session...

[@xf9639]

Excellent

[@akulanarayana5284]

Is it same as name manager fir a range of cells.

[@trueindian2108]

How can I print object values shown in example 2? FirstName, LastName and Countries.

[@gabiold]

Maybe it is just me, but I feel you differentiated Dim x As New Object from Dim x As Object Set x = new Object so that the first is only run once and the latter isn't. So someone might think it creates a static object which is getting reused on subsequent calls. That is not the case, If it is declared as a local variable then it still creates a new instance of the object every time the function is being called.

[@johnywhy4679]

i thought this vid was going to help me understand AddressOf, VarPtr, etc.

[@sasavienne]

Thanks Paul. 👏 🌟 🌟 🌟

[@cyphi1]

There isn't a way to copy an object in VBA. Can you make a video about Mementos and saving object states? I have found a few snippets on this topic, but I'm interested in implementing a save state in my project with existing objects with it's own properties and methods.

[@rlh7210]

What happen to late and early Binding with VBA Objects?

[@marouanetayachi9613]

Hi, is there a limit for the number of items a collection can hold? I tried to create a collection of over 8000 of a class customer that has 15 properties through a for loop that read the sheet line by line and add each cell to one of the propriety, then adds the object to the collection, excel crashes everytime before finishing. Thank you.

[@paulzieper2269]

Sorry, new at collections. Where you get the clsCustomer from?. It's not in my list?

[@Excelmacromastery]

clsCustomer is the class module that I created.

[@ahndeux]

I'm surprised you didn't show at the end that you SET the objects to NOTHING or else you end up with memory issues since those objects does not get destroyed. Its a common problem with some subroutines where objects are temporarily created to do some work but not destroyed at the end of the subroutine. Good job in explaining how the objects are stored in memory. If people have experience with C or C++, this is drilled into them. Programming in Arduino, you really have to watch your variables or your run out of memory fast, especially when you work with cstrings.

[@Excelmacromastery]

VBA has automatic garbage collection which means that it cleans up the memory once a variable goes out of scope. This is different than C++ where you have to manually clean up memory or you will end up with memory leaks.

[@ahndeux]

@@Excelmacromastery I don't believe that is true for all objects. With variables, they do get cleaned out of memory, however objects may not always be completely cleaned out of the memory. VBA uses reference count for garbage collecting. If a programmer had poorly constructed code with circular references, it will stay in memory. There are also bugs in VBA which allows some objects to remain if there were errors. In general, its a good practice if you have larger objects or ones where there are potential for error conditions that results in objects that remain in memory. People may argue against it explicitly doing garbage collection, but I generally do it for objects created in the subroutine to be on the safe side.

[@Excelmacromastery]

It is a common misconception that you need to set objects to nothing. From Advanced Visual Basic 6: "This single poor object model behavior has led to the misconception that VB leaks memory unless you explicitly set all the local variables to Nothing at the end of a function. This is a completely false notion in a well-designed object model. VB can clear the variables faster at the End Sub line than you can from code, and it checks the variables even if you explicitly release your references. Any effort you make is duplicated." See also: nolongerset.com/memory-management-in-vba/

[@ahndeux]

@@Excelmacromastery Its one of those items where we agree to disagree. I have actually seen it cause memory issues in the past. In today's world where we have gigs and gigs of RAM, you are less likely to encounter problems. I dealt with cleaning up after code from others where crashes were repeatable on complex routines. Adding those lines to clean up the objects made the crash go away. One might argue the code were poorly written, but it doesn't change the fact that poor object cleanup was the root cause of the issues.

[@rhlogic]

As someone who has made programs in Java, I kind of new this. But it is worth reminding, because those concepts are subtle.

[@boomer5015]

I mainly use Dictionaries and I don't think it sets a pointer (don't think ArrayLists do either). It appears to create a new reference every time and it's quite strange as it makes your code longer and really defensive. If I have a dictionary of classes, I'd loop through then set the class to the dictionary item. If I want to mutate, I would do so on the class but this wouldn't be reflected in the item within the dictionary. Once I've operated on the class, I have the set it back to the dictionary to be reflected. Have you come across this much before? I haven't invested a lot of time in to understanding the cause.

[@Excelmacromastery]

The Dictionary and ArrayList variables are both pointers(a variable that holds the address) just like a collection variable. The code below will print the address of the variable(i.e. dict) and the address of the object(i.e. the dictionary itself). You can see that they are different. Dim dict As New Dictionary dict.Add "Apple", 2 Debug.Print VarPtr(dict), ObjPtr(dict)

[@rrrprogram4704]

where can i download the excel file ??

[@Excelmacromastery]

It's not available but I think it is easy to reproduce these examples.

[@mannynaz7723]

Hi Sir, this is Manny, kindly make a vba code video on how to make a main menu with submenu drop-drown. Hope you will see my comments and give time because it will helps me a lot. Thank you in advance.

[@burakkorkmaz7802]

I am confused with "shdata" at 8.26. Where is it declared ? Thank in advance.

[@ricos1497]

It is the name given to the worksheet. If you double click on a worksheet (eg sheet1) and look in the properties window you'll find the "name" property. Overtype this with any name you like. Then go back into the code window and type that name and you'll see you can use that declared name in your code. It's very useful!

[@burakkorkmaz7802]

@@ricos1497 Wow it is really perfect tool, ı have never noticed that before. Thank you !!

[@ricos1497]

@@burakkorkmaz7802 ha ha, yes. I used VBA for years before I noticed it by accident one day!

[@Excelmacromastery]

@@ricos1497 I actually should avoid using it in videos as it confuses those that haven't heard of it.

[@sapisoftware1942]

Thank you Paul for your videos! Could you make a video of MSXML2.XMLHTTP60 using queryselectorall to scrap a site? Passing user and password too. Thank you!!

[@akibul_islam]

what is clsCustomer! I can't use it

[@Excelmacromastery]

It's a class module that I created.

[@akibul_islam]

How can I create this?

[@rrrprogram8667]

Stupid question.. Sorry but.... What is a pointer??

[@Excelmacromastery]

It's a variable that stores the memory address rather than a value. It is 'pointing' at another variable.

[@PauloTNCunha]

Best question!

[@tughanozsezer9332]

👍

[@rrrprogram8667]

Below syntax works fine.. Why?? Dim Customer as New clsCustomer Set Customer = New clsCustomer Both line have New... But this stills works

[@Excelmacromastery]

The first "New" creates a new customer object and assigns the customer variable to it. The second "New" creates a second new customer object and assigns the customer variable to it. The first object is deleted because no variable is referencing it. In conclusion the first New caused an object to be created and deleted without it being used.

[@rrrprogram8667]

@@Excelmacromastery thanks... How do you verify this.... Any documentation or... By intuition

[@wilholland100]

Hoping to find help with an absolutely devastating problem. I use Excel VBA for business. Works fine on older computer. A brand new (more powerful) computer (new) now has my excel program. It constantly says "Out of memory," and the macro doesn't work. . . Old pc doesn't do that, still works, and I never see that. Please please please somebody help. All the webpages say do this or that, close programs. . . none of that is right. . . again old pc works, new pc doesn't. Old pc less powerful, the new pc is new. . nothing else is changing about me attempting to use program. . . please help. . . they have no !!%@!! customer support PHONE NUM. .I wish there was a way they could be brought up on charges for that.

[@7Denial7]

I can try help you with your VBA problem. Tell me here If your issue is still relevant

[@johnywhy4679]

6:31 The explanation was a bit unclear. You compared `Dim as New Collection` vs `Set = New Collection`. Then you said `Set = New Collection` is more flexible. You made it sound like you can create multiple collections with Set. But then your example was about MEMBERS of the collection. Your explanation seemed to confuse collections vs members. If you use Set, then only one collection is created. If you say: Dim Fruit as Collection Set Fruit = new Collection Set Fruit = new Collection Then there's still only just one collection.

[@Excelmacromastery]

Yes, but 2 Collections were created. The first "Set Fruit = new Collection" creates a new collection. The second one also creates a new collection. However the first one gets deleted because it is no longer referenced by any variable so VBA automatically deletes it. If you add a temp variable to reference the first collection then you will have 2 collections: Dim Fruit As Collection, temp As Collection Set Fruit = New Collection Set temp = Fruit Set Fruit = New Collection See: excelmacromastery.com/vba-objects/#VBA_Objects_in_Memory

[@johnywhy4679]

@@Excelmacromastery All true, but you only have one variable, so yes-- the first instance gets deleted. You only have one instance at a time. The useful technique here is the temp variable used for creating objects to add to the collection, as you show. That's great. But you don't demonstrate a use case for a temp collection.

[@Husky_Passion]

collection is useless. all you need is array, and dictionary