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.

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

[@tannertucker22]

Thank you. Your vba and excel courses are excellent.

[@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 :)

[@tanwen1077]

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

[@md.mahbubrahman7891]

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

[@LeilaGharani]

I'll definitely do that :)

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

[@marcellokassa6975]

Thank you Leila. You are always amazing!

[@LeilaGharani]

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

[@felipao1976]

Useful and clear as always, I love

[@learnandgrowwithfun289]

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

[@LeilaGharani]

Thank you! Glad you like the explanation.

[@dddoda]

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

[@LeilaGharani]

Great!

[@sridharlagishetty2078]

Thanks you for the information

[@vivekphadke16]

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

[@LeilaGharani]

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

[@pedrocamara6451]

Your tutorials are very good. thank you.

[@LeilaGharani]

Glad to hear that Pedro.

[@Melki]

Thank you, very helpful

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

[@255205titel]

Thanks Leila, helps me a lot!

[@LeilaGharani]

I'm glad it is helpful!

[@HamidurRahman]

Thanks for such a basic concept sharing,

[@LeilaGharani]

You're very welcome Hamidur.

[@naheda.ikhtier6596]

Your every video is very helpful 😊😊

[@stevennye5075]

good review!

[@firdausbasheer3066]

Amazing tutorials

[@amc8437]

Intriguing rabbit hole I have stumbled upon.

[@jokkiossaka3306]

Thank you very much Leila!

[@LeilaGharani]

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

[@Thanogr1]

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

[@eduardooc8531]

I appreciate it thank you very much

[@johnborg6005]

Thanks Leila :) :)

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

[@HamidurRahman]

Helpful :)

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

[@udaykant3504]

Explained in systematic way

[@LeilaGharani]

Thank you. Glad you like it Uday.

[@yugalsharma3842]

Well explained.....

[@LeilaGharani]

Thank you. Glad you like it.

[@shivankmittal8864]

Really Amazing

[@LeilaGharani]

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

[@stefandlucas]

Awesome.

[@HamidurRahman]

Helpful

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

[@zeFoksXIII08]

Thank you

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

[@MrLbott]

You are amazing...

[@LeilaGharani]

Glad you like it :)

[@MrAmit777]

Thankyou

[@MIMGURU]

Good👍

[@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
th-cam.com/users/ExcelAutomationByMudit

[@nabilsn411]

Cool thx

[@indianpigeon3680]

Love u lots

[@hosseinhosseinpoor4845]

thanks thanks thanks

[@aev6075]

10/10

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

[@roberto-martins]

Thanks.

[@LeilaGharani]

My pleasure Roberto.

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

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

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

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

[@jashanbrar8244]

Very good ese hi ielts ke bare mai b batayo

[@LeilaGharani]

Glad you like it!

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

[@tigertiger51]

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

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

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

[@Pro-gamer_20108]

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

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

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

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

[@golubhai1910]

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

[@60_daysff_per_year]

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

[@latiffesa]

You are beautiful

[@anuragkumar2531]

Good