When do you need to use DIM and when do you not?

[Will85]

I dont exactly understand the concept or all the variations of DIM, but I know that I am declaring it as a variable, something unique. But half the time it seems like I dont need to use DIM at all

For example, instead of

Dim LastRow
LastRow = Range("F1").End(xlDown).Row

Sometimes I can just skip the Dim bit and just use

LastRow = Range("F1").End(xlDown).Row

Continuing the example of never using Dim the following works just fine:

For Each cell In Range("G2:G" & LastRow)
If cell.Value = "0" Then cell.Clear
Next

Why does this work without ever having to do the Dim part? Does Dim make the variable available in other macros? Is Dim only good for that execution of the code? Same question for not using Dim.

Another example of not using Dim, later on in my code same module I am sorting some data, and now I am getting an error, an I wonder if its because I didnt use Dim?

SortStart = Range("F1")
SortEnd = Range("H" & LastRow)
CriteriaStart = Range("G2") 'Change this column reference to change sort Criteria
CriteriaEnd = Range("G" & LastRow)

ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Add Key:=Range(CriteriaStart & ":" & CriteriaEnd), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Scratch").Sort
.SetRange Range(SortStart & ":" & SortEnd)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[igold]

You may want to read this...

Declaring Variables

www.cpearson.com

 

[Jeffrey Mahoney]

Here's the long answer. I Dim all my variables. What can happen if you don't? If you don't declare a variable, the variable is automatically a Variant. Variants use more memory and can be problematic. If you need to store a integer, but then add the sum of values with a decimal value into a non declared var, then you could get an erroneous result.

Declaring variables also make it more clear what you intended to do with that variable. If you use it interchangeably as a range and also as a number, you could errors in your code.

 

[Alex Blakenburg]

Your failed Sort code is not a bad example of why Dim might help. It might also support using some naming convention which would indicate the type of value being stored.

I have not changed you code so the below will not run but have commented on the problem line and given you the info to make the change.
The Dim statement helps you and others to be aware of what data type the variables are and prevents you from using them incorrectly eg SortStart = would had failed to compile if you had a Dim SortStart as Range

Sub testSort()

    ' LastRow not provided in code extract
    LastRow = Range("H" & Rows.Count).End(xlUp).Row
    ' SortStart & SortEnd being used as a range object needs to be
    ' SetStart = Range("F1") and same for SortEnd
    SortStart = Range("F1")
    SortEnd = Range("H" & LastRow)
    
    CriteriaStart = Range("G2") 'Change this column reference to change sort Criteria
    CriteriaEnd = Range("G" & LastRow)
    
    ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Scratch").Sort.SortFields.Add Key:=Range(CriteriaStart & ":" & CriteriaEnd), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Scratch").Sort
    
    ' Using Range Object SortStart and end Expression needs the text string for
    ' range address
    ' eg .SetRange Range(SortStart.Address & ":" & SortEnd.Address)
    .SetRange Range(SortStart & ":" & SortEnd)
    
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

End Sub