How To Properly Use Dim

Question

I am putting together a code in excel VBA. I am no wiz, but have been learning for awhile now, but learning as i go. I have searched the internet and have found much info on when to declare a variable, string or integer. But I have yet to fully understand. Using the code below i am successfully able to execute, without declaring any variables. Question I have use should i be be using Dim var1 to dim var100 or is this just not doing anything

Var1 = frmdriverstep1.Labelpa54.Caption Var2 = frmdriverstep1.Labelza54.Caption Var3 = frmdriverstep1.ComboBoxga54.Text Var4 = frmdriverstep1.ComboBoxna54.Text Var5 = frmdriverstep1.a54label.Caption DBFullName = "H:\*************.accdb" Cs1 = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & DBFullName & ";" & _ "Persist Security Info=False;" Set CnnConn = New ADODB.Connection CnnConn.Open Cs1 'zone1 If Var1 >= "0" Then Set Rst1 = New ADODB.Recordset Rst1.Open "Zone1", CnnConn, adOpenKeyset, adLockOptimistic, adCmdTable Rst1.AddNew Rst1.Fields("Audit_Date") = vardate Rst1.Fields("Sequence") = varseq Rst1.Fields("Style") = vartrim Rst1.Fields("Auditor") = varauditor Rst1.Fields("Catagory") = "WRINKLES" Rst1.Fields("Portion") = Var1 Rst1.Fields("Zone") = Var2 Rst1.Fields("Grade") = Var3 Rst1.Fields("Number") = Var4 Rst1.Fields("Score") = Var5 Rst1.Fields("Seat_Model") = varmodel Rst1.update Rst1.Close

Use option explicit at the top of your file and try adding dim to your variables and you'll learn quickly if you're doing well or not. For example your var1 to var5, they're string. Write dim var1, var2, var3, var4, var5 as string — Hearner
– Hearner, Commented Jul 31, 2015 at 12:04
Using Dim will allow Excel to autocomplete and, the most important, try always to put Dim to all your Set — Hearner
– Hearner, Commented Jul 31, 2015 at 12:06
On older systems, it was imperative to Dimension or 'Dim' variables for the sake of memory management, as this told the compiler how much memory should be set aside to use for the scope of that variable (a Boolean wouldn't use as much as an Integer which wouldn't use as much as a Long for example). In modern day, this isn't as much of an issue, but without it there you will run into debugging errors and deprive yourself of IntelliSense which is a wonderful thing especially when you're learning. — SierraOscar
– SierraOscar, Commented Jul 31, 2015 at 12:20
@Hearner Using dim var1, var2, var3, var4, var5 as string only declares var5 as String - the others are all Variant. You have to declare the type for each: dim var1 As String, var2 As String, var3 As String, var4 As String, var5 as string. Or use an array: Dim avar(1 to 5) as String — Rory
– Rory, Commented Jul 31, 2015 at 13:05

Patrick Honorez · Accepted Answer · 2015-07-31 12:14:46Z

The real issue of not using Option Explicit is that you won't be informed of undeclared variable, and that may easily lead to debugging nightmares.
Consider the following code:

SomeUndeclaredVariable= 100 Debug.Print SomeUndeclareVariable

The second line will print a 0 without raising an error, and it might take a long time before you realize that the variable name used in the second line is different from the one in the first line.
Additionally, Variant are much less efficient than 'native' types.

Also, for Object variables, not declaring them properly deprives you from the benefit of Intellisense.
Note that there is an option in VBE to automatically insert that Option Explicit at the top of each new module.

Jeanno · Accepted Answer · 2015-07-31 12:01:26Z

Unless you have Option Explicit specifically on the top of the module, you will not need to declare your variables in VBA, any undeclared variable will be considered a variable of type "Variant". However, this is a really poor coding habit, you should always declare your variable indicating their types.

Collectives™ on Stack Overflow

How To Properly Use Dim

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related