Forget Option Explicit, Excel VBA Declaring Variables: 6 Reasons Why You No Longer Need It

To Dim or Not To Dim...

As an Excel Developer, Instructor, Vlogger and Public Speaker on Excel VBA, I get asked a lot about the importance of declaring variables with Dim and why I don't always use this in practice.

There are several reasons why I don't declare every little thing, and I will happily enumerate them below!

BUT - before I share my thoughts, I also want to share what the incredible Bill Jelen (MrExcel.com) also said in an interview by sharing the video clip and a transcript from the conversation (from 25m10s to 27m00s):

Here's the transcript I typed for your convenience:

Jordan: Kindly tell our audience why you don't like option explicit…

Bill: Yes, the most important thing you can do in your code is not option explicit - the most important thing you can do in your code is make code that works, right? - and you don't need option explicit to have your code work. We're not programmers, we're accountants we're Excel people and we're just flipping over into VBA because we got some horrible data cleansing problem that we need to fix that we won't spend 42 hours typing those dates in alright and yeah if we're real programmers and we're writing code yes go declare your variables and all that but this is just this is just – “hey I've got this horrible thing at work I need to knock out 20 lines of code to make this work”. And frankly I don't know what variables I'm gonna need so I'm not gonna declare them upfront. If I need a new variable as we go yeah then we go. Now the one argument Bob Phillips cornered me and he says, “hey well what about misspelling the variable names?” I'm like okay Bob if you're not perfect like me, sure then you have to use option explicit and this is an absolute joke, you know? It's certainly not that you're going to save four bytes because you declare it as an Integer instead of a Long or a Variant because I mean look we have so much memory. The original reason for declaring your variables was to save memory and that's just ridiculous now so you know…

Jordan: No that’s definitely true. The variable concern is - it's done I mean we have so much memory now we don't have to really care about it, you could if you wanted to declare everything as a variant you know and there's some speed improvements you get through using certain types but that said the spelling thing is for me the spelling thing is huge because um maybe I'm just bad at typing but you know..you can also as you're typing you can add it in you could say dim I as you're typing, um when you realize you want to use it… You don’t have to put it at the top…

So, in Summary, Bill Jelen was saying:

• The original reason for declaring was to preserve memory! Computers have SO much memory nowadays that we don’t need to worry about saving memory.
• VBA works whether you declare variables or not.
• We are all in the same boat, we’re Excel users who use VBA to solve some problems, not rocket scientists.
• The only valid reason (kind of) is if people have a REAL problem with constantly misspelling their variables and specifically need this Excel ‘training wheels’ type feature
• At the start, we don’t know what variables we need, so just create them as you go.
• Adding comments to the code is a suitable method to describe the code, declaring variables doesn’t add information.

Here's What I Typically Tell People:

I will generally declare Objects, such as Worksheets, Workbooks, Charts, or any other controls/objects that have helpful Intellisense dropdown menus once declared. I find this helpful in coding. I generally do NOT declare all my regular variables that are not Objects.

I often find myself running a macro in Break mode (tapping F8 slowly) and making changes on the fly while the macro is actually running so I can see all the variable values, ActiveX control values and get an overall idea of what's going on in order to make tweaks. This includes adding variables in the mix periodically for experimenting and although declaring the variable only takes a tiny bit of extra time, sometimes Excel will also force the macro to reset in order for the new declaration. I find this terribly annoying personally.

There are speed tests online that prove that declaring all variables explicitly can speed up your code if you're running a kajillion iterations in a loop, so I'm all for it, when I know it will increase the speed. My macros almost always run in a fraction of a second, so with modern hardware, I rarely find this to be an issue. If it helps you to do so, it's a fine idea, my friends.

A Fun Speed Test:

John C. is a frequent participant in my Ultimate Excel VBA Course forum and he posted a speed test that shows that if you're doing a zillion loops and making a ton of calculations, that sometimes declaring variables is a speed improver! Again - just declare it if you find you NEED to because it is for some reason taking forever:

John's Code:
Sub testVariables()
Dim start As Single, first As Single, second As Single
Dim result As Long
start = Timer()
result = fibonacciV(31)
first = Timer() - start
Debug.Print "Not declaring a variable took " & first & " seconds to calculate " & result
start = Timer()
result = fibonacciL(31)
second = Timer() - start
Debug.Print "Declaring a variable properly took " & second & " seconds to calculate " & result
Debug.Print "i.e. " & (((first - second) / first) * 100) & "% more efficient"
End Sub
Function fibonacciV(val)
If val = 0 Or val = 1 Then
fibonacciV = val
Else
fibonacciV = fibonacciV(val - 1) + fibonacciV(val - 2)
End If
End Function
Function fibonacciL(val As Integer)
If val = 0 Or val = 1 Then
fibonacciL = val
Else
fibonacciL = fibonacciL(val - 1) + fibonacciL(val - 2)
End If
End Function
'Thanks again for posting, John C!

What do YOU think? Put your thoughts down in the comments!

Happy Coding!

Dan Strong, ExcelVbaIsFun

Inspiration and interview from these articles: https://exceloffthegrid.com/do-you-have-to-use-dim-to-declare-variables/, https://optionexplicitvba.com/2014/04/13/me-vs-bill-jelen-on-the-value-of-option-explicit/

YouTube Interview URL: https://www.youtube.com/watch?v=yJRLzN3Dzmw&feature=youtu.be&t=1510

Tombstone Generator: https://codepen.io/jakealbaugh/full/MvjKYN/