CODEWITHSUNDEEP
vbaexcel
INOH
INOH

Reputation: 385

How To Properly Use Dim

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

Upvotes: 2

Views: 463

Answers (2)

iDevlop
iDevlop

Reputation: 25262

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.

Upvotes: 10

Jeanno
Jeanno

Reputation: 2859

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.

Upvotes: 5

Related Questions