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

@8ballWASD

4 жыл бұрын

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

4 жыл бұрын

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

@Djrome1

2 жыл бұрын

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?

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

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!!

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

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

3 жыл бұрын

Thanks Ryan, Glad you liked it.

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

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.

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

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!!

Thank you paul for sharing this advanced VBA content :)

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

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

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

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

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

This was great, thank you for this explanation.

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

Awesome awesome awesome video thanks Paul 👏👏👏

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

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

2 жыл бұрын

Glad it was helpful!

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

Great video with clear explanations thanks a lot ))

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

@Excelmacromastery

3 жыл бұрын

You are welcome!

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

Thnak you for explaing this "New" feature with objects

A good explanation of a complex topic

was good and brief thanks

thanks, very good explanation

Thanks, Paul.

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

@Excelmacromastery

4 жыл бұрын

Glad you like it.

Thanks Paul. 👏 🌟 🌟 🌟

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

4 жыл бұрын

Thanks Charlie

@lloydmcleod629

2 жыл бұрын

How do you use VBA at your job?

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

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

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

4 жыл бұрын

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

Thanks, Paul

Good session...

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

3 жыл бұрын

You're welcome Jacob

Excellent

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?

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.

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.

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

4 жыл бұрын

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

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

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

What happen to late and early Binding with VBA Objects?

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

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

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

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

@Excelmacromastery

4 жыл бұрын

clsCustomer is the class module that I created.

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!

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.

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.

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.

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.

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

4 жыл бұрын

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)

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

where can i download the excel file ??

@Excelmacromastery

4 жыл бұрын

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

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

👍

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!!

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

@ricos1497

4 жыл бұрын

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

4 жыл бұрын

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

@ricos1497

4 жыл бұрын

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

@Excelmacromastery

4 жыл бұрын

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

what is clsCustomer! I can't use it

@Excelmacromastery

2 жыл бұрын

It's a class module that I created.

@akibul_islam

2 жыл бұрын

How can I create this?

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.

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

2 жыл бұрын

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

2 жыл бұрын

@@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

2 жыл бұрын

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

2 жыл бұрын

@@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.

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

@Excelmacromastery

4 жыл бұрын

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

@PauloTNCunha

4 жыл бұрын

Best question!

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

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

@Excelmacromastery

4 жыл бұрын

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

4 жыл бұрын

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

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

2 жыл бұрын

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

2 жыл бұрын

@@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.

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