How to Declare (Dim) and Set VBA Variables (use data types correctly)

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
It’s best practice to declare your Excel VBA variables before you use them. Declaring variables means that you give VBA a heads up that it needs to free up some memory space for your variables which will in most cases be less than the default. That's why it's important to understand the concept of data types and how much memory space each of these types will take. The less memory you allocate to a variable, the faster your code will run. I show the range associated to each data type - such as numbers, text, date, currency etc in the video.
🎓 Get access to the full course here: www.xelplus.com/course/excel-...
Key Highlights:
- Default Data Type: By default, VBA assigns the data type called "Variant," which adapts based on the data it holds, leading to larger memory consumption.
- Memory Efficiency: Different data types consume varying amounts of memory. For instance, a "Byte" takes up only one byte of memory, while a "Variant" uses around 16 bytes.
- Data Type Ranges: Each data type has a specific range it can handle, which becomes crucial in scenarios where the data exceeds that range.
- Common Data Types: Explore popular data types like "Byte," "Integer," "Long," "Boolean," "Double," "String," and "Object," each with its own purpose and memory footprint.
- Variable Declaration: Learn how to declare variables using the "DIM" keyword, and understand naming conventions for variables.
★ Links to related videos: ★
Learn about Visual Basic Editor: • Excel VBA tutorial for...
Properties & methods in VBA: • Learn How to Use Prope...
Blog Post: www.xelplus.com/excel-vba-dat...
★ My Online Excel Courses ★ ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#ExcelVBA

[@LeilaGharani]

Get access to the full Excel VBA course here: www.xelplus.com/course/excel-vba-excel-macros/

[@excelisfun]

Thanks for the great Data Type video!!

[@LeilaGharani]

You're very welcome Mike :) Thank you for your support.

[@dhruvilbarbhaya884]

During such lock down, watching such easy explanation of tough topic is awesome.. U r up to the point and so clear, and same time so easy......

[@dschulz5647]

This helped explain a bunch for me. Usually videos don't help much when it comes to coding but this was actually very helpful. Thank you!

[@LeilaGharani]

I'm glad it was helpful.

[@tannertucker22]

Thank you. Your vba and excel courses are excellent.

[@tanwen1077]

Love your videos, they are clear and easy to understand. :D

[@1gopalakrishnarao]

Very informative, waiting some more Videos on the VBA, eagerly. Your Explanation is Excellent, and crystal clear.

[@LeilaGharani]

Thank you very much Gopala! Very happy to hear you like the VBA series. I will be alternating between Excel and VBA in the next weeks :)

[@marcellokassa6975]

Thank you Leila. You are always amazing!

[@LeilaGharani]

You're very welcome Marcel. Thank you for dropping by :)

[@jennydrumm7223]

Another great video! Dim, Set and such have been the bane of my existence, so It's good to have it explained clearly. I'll be watching this a couple more times, just to be sure I really get a grasp on it! (People have tried to explain these to me before, but they get into too much "tech jargon" that makes my brain spin around, LOL!) Thank you!

[@LeilaGharani]

I'm glad this video helps :) Thank you for your support Jenny.

[@divyarajsinhchauhan]

Can you please tell me DIM Full Form

[@md.mahbubrahman7891]

Very Informative & useful. Thank you for uploading & expecting more.

[@LeilaGharani]

I'll definitely do that :)

[@felipao1976]

Useful and clear as always, I love

[@dddoda]

oh! Just the info I was looking for. Now my macro works! Thanks.

[@LeilaGharani]

Great!

[@sridharlagishetty2078]

Thanks you for the information

[@joshzink97]

Thank you for the error example. That has been frustrating to work through and your explanation is very helpful.

[@LeilaGharani]

You're very welcome. I'm glad to hear the explanation was helpful.

[@vivekphadke16]

I can easily remember what you teach. Thanks a lot.

[@LeilaGharani]

Thank you! I'm glad to hear that Vivek :)

[@learnandgrowwithfun289]

superb. i like the way you touch every small point while explaining.

[@LeilaGharani]

Thank you! Glad you like the explanation.

[@pedrocamara6451]

Your tutorials are very good. thank you.

[@LeilaGharani]

Glad to hear that Pedro.

[@naheda.ikhtier6596]

Your every video is very helpful 😊😊

[@Melki]

Thank you, very helpful

[@firdausbasheer3066]

Amazing tutorials

[@amc8437]

Intriguing rabbit hole I have stumbled upon.

[@255205titel]

Thanks Leila, helps me a lot!

[@LeilaGharani]

I'm glad it is helpful!

[@stevennye5075]

good review!

[@HamidurRahman]

Thanks for such a basic concept sharing,

[@LeilaGharani]

You're very welcome Hamidur.

[@jokkiossaka3306]

Thank you very much Leila!

[@LeilaGharani]

You're very welcome. Hope it will come in handy.

[@eduardooc8531]

I appreciate it thank you very much

[@Thanogr1]

LET used in old BASIC programming language. I remembered it first time i saw Spectrum's Sinclair Basic

[@johnborg6005]

Thanks Leila :) :)

[@ismailismaili0071]

thank you so much Ms. Leila I wish you could do more videos because i really want to learn VBA

[@LeilaGharani]

I'll do my best :) it's not easy to get in more than 1 video per week. I'll alternate between usual Excel and VBA....

[@udaykant3504]

Explained in systematic way

[@LeilaGharani]

Thank you. Glad you like it Uday.

[@stefandlucas]

Awesome.

[@HamidurRahman]

Helpful :)

[@HoppiHopp]

Die Übersicht ist extrem hilfreich! ☺️

[@LeilaGharani]

Freut mich. Vielen Dank.

[@HoppiHopp]

I heard you speaking German in one of the lectures in your "Unlock Excel VBA and Excel Macros" course (3/16) when you explained the syntax of the object model etc. and I was tempted :)

[@LeilaGharani]

:) I live in Austria.

[@meniporat3527]

Hi Leila, a minor correction: The largest positive number that the INTEGER data type can hold is not: 32768 but: 32767. Thank you for your excellent videos 🙂

[@HamidurRahman]

Helpful

[@yugalsharma3842]

Well explained.....

[@LeilaGharani]

Thank you. Glad you like it.

[@zeFoksXIII08]

Thank you

[@shivankmittal8864]

Really Amazing

[@LeilaGharani]

I'm glad you like it. Thank you for your support.

[@abdallah.kandiel]

This is an amazing video Welcome And happy thursday ur highness

[@LeilaGharani]

Happy Thursday :) You're very welcome. Hope your workload is better this week....

[@abdallah.kandiel]

Leila Gharani it is Thanks 4 asking This is so kind if you But lucky me . If you know about fasting and ramadan month We are in holly month any way i like to share with you Our lovely istructor

[@miteshkumar5477]

So could you please let us know the usage of "new" keyword, as far as i understood "new" keyword is used to instantiate object variables. Thanks in advance !

[@indianpigeon3680]

Love u lots

[@MrLbott]

You are amazing...

[@LeilaGharani]

Glad you like it :)

[@MIMGURU]

Good👍

[@Progressmarini]

Can I use the set statement at the module level? I have declared many variables as sheets and don't want to have to set them in every sub() I have in the same module. thank you

[@MrAmit777]

Thankyou

[@hosseinhosseinpoor4845]

thanks thanks thanks

[@nabilsn411]

Cool thx

[@aev6075]

10/10

[@roberto-martins]

Thanks.

[@LeilaGharani]

My pleasure Roberto.

[@amruthabathini3455]

Hi LEILA, Your video is very helpful and it is very clear to understand. Can you please help me with the following scenario: 1) I have a workbook of sheet1 with two columns and a few rows. 2) Applied filter for sheet1 data 3) I have duplicated the sheet1 in the same workbook and renamed it as "Test" 4) Deleted the duplicates in sheet2 5) Now, I need your help in copying a cell value(the data is dynamic) of sheet2 and paste it in the filtered column of sheet1 so that I can write the comments beside the two columns of sheet1. **NOTE: DATA IS DYNAMIC The columns are as below: SHEET1: COLUMN-1 COLUMN-2 abc apple abc windows 123 android abc os b phone 123 android abc blackberry abc apple b nokia abc lenovo SHEET2: COLUMN-1 COLUMN-2 abc apple 123 android b phone I need the final result like this in SHEET1 COLUMN-1 COLUMN-2 COMMENT abc apple abc windows same as apple 123 android abc os same as apple b phone 123 android same as android abc blackberry same as windows abc apple same as apple b nokia same as phone abc lenovo same as apple IT WOULD BE REALLY HELPFUL IF YOU CAN GUIDE ME ASAP.

[@mdcs1992]

I have multiple disabilities and health problems. In my sixties now and lost my job because of them......but a love of Excel makes me want to learn vba. I recorded a macro that "Cleared Content" from multiple non contiguous Ranges. I went into gab to run it but kept getting an error at lines of code starting with "Union Range." Can't understand it. Is it simple to rectify? Your channel is IMHO the best on You Tube for Excel. Well explained. Great examples and explanations. Superb. Thank you very much

[@LeilaGharani]

Make sure you didn't record in relative mode. If you go to the developer tab make sure the "use relative reference" toggle is off. If these ranges are fixed ranges, it would be best to get direct references - otherwise you could run into errors.

[@Trendinvideowalasam]

This video is good.in some videos of others I saw no dim is used in the beginning. Can u define all the codes why it is used for and when with elaboration.

[@prayushijain4087]

Check this too kzbin.info

[@tigertiger51]

What will happen if I used integer data type for a data with a value of 32,769 or more?

[@subashanandofficial]

thanks

[@pinkeishz9106]

Leila, Thanks for sharing. I've question in your example of LastRow=Rows.count what if it is not declared no Dim direct LastRow... What problem will it cause?

[@LeilaGharani]

If you don't declare it, your macro will still run without problems (as long as you don't have option explicit turned on) but declaring it makes it more efficient specially in terms of macro performance time....

[@althma5203]

If the variable isn’t declared it will default as Variant, and will be less efficient (i.e. will use more memory)

[@jamesjujare2599]

Thank you Leila....!! Can we have more videos on VBA

[@LeilaGharani]

You're very welcome. Yes - I will be alternating between VBA, charts, formulas etc....

[@jamesjujare2599]

Thank u.. Leila

[@teymurrashidli7560]

İn VBA round(10.25,1)=10.2 but in normal excel round(10.25,1)=10.3 I need calculate exactly in excel VBA. How can I round in VBA exactly? I need round(10.25,1)=10.3 in VBA

[@Pro-gamer_20108]

Hi looking code that help me to add cell data while we use send email cose

[@Manasa1140]

Hi madam can you please do videos on averageif using vba in which user changes data every time it automatically updates.

[@mohammadalmomani1193]

Hello I am trying to find an vba code to copy and paste every range of rows like(A1:C19).copy and paste in word file as image then copy from (A20:C39) and paste in the same word file and so on for the rest of the table in excel. they have told me that I need to use the looping in excel but I don't know exactly how to do it …. please help if you can

[@LeilaGharani]

I have a whole section (no 10) on Looping in my VBA course: courses.xelplus.com/p/excel-vba-excel-macros

[@poultryland2609]

The only problem with it is that text you are typing fades away while you are typing, please fix it, your lectures are superb

[@kumarsandy2008]

hi , i am a beginner to vba macros, and want to get skilled in this field, i want to learn all the looping process and array......and charts with vba....could you please help....

[@LeilaGharani]

I have a section on arrays and charts inside the complete course. Feel free to check out the content (link is in the description of the video) to see if it covers the topics you're looking for.

[@sohailmoosa]

Hi Leila, I'm currently your student using Udemy Business. And I feel I'm lucky to get enrolled with an excellent teacher. I couldn't figure out how to send you a query in Udemy and hence ended up searching for you here and would ask one. I have currently completed lecture 35 that speaks abt declaring variables. I'm unable to understand why do we beed variables? Is it necessary to have variables? What are variables? And can't I do without them in vba. I am a novice and have seen many codes and most of them have variables. But I always had these questions in my mind. I have googled alot, but nothing has solved this question yet. Please reply.

[@LeilaGharani]

If you do not declare variables it usually consumes much more memory and making your code slower. Also, it makes it easier to refer to it later.

[@sohailmoosa]

@@LeilaGharani I sincerely appreciate you taking out time and replying to me. Kudos to you. I'll keep this point in mind and proceed with the course. May be it doesn't make sense to me right now, but later it may. You've been an excellent presenter and tutor. And I trust you for that.

[@jashanbrar8244]

Very good ese hi ielts ke bare mai b batayo

[@LeilaGharani]

Glad you like it!

[@golubhai1910]

set is basically a setter and getter concept from oo programming I guess

[@world_of_tech1213]

I am going through all your session even i do macro coding intermediate level.. having one suggestion if you give session number for each lesson ..make it more easily for us to gone through

[@LeilaGharani]

Thank you for the suggestion. I've included them in a playlist so they can be viewed in order....

[@world_of_tech1213]

Leila Gharani Thank you Leila...can you put a lesson for ms access and excel marco . How to transfer data from excel to access and how to use query for fetching data from access. I hope that would be useful for many people..

[@60_daysff_per_year]

You have to be careful when you use a long long variable.

[@latiffesa]

You are beautiful

[@anuragkumar2531]

Good