Coding Convention

  • Thread starter Thread starter RussellT
  • Start date Start date
R

RussellT

Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Sheets("FilterControl").Range("A28:Y29"), _
CopyToRange:=Sheets("Results").Range("A2:W2"), Unique:=False
end sub
Public Sub FilterDbase_Click()
Dim filtercontrolSheet As Worksheet
Dim resultsSheet As Worksheet
Dim devdataSheet As Worksheet
Set filtercontrolSheet = Sheets("FilterControl")
Set resultsSheet = Sheets("Results")
Set devdataSheet = Sheets("DevData")

devdataSheet.Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=filtercontrolSheet.Range("A28:Y29"), _
CopyToRange:=resultsSheet.Range("A2:W2"), Unique:=False
End Sub

Public Sub FilterDbase_Click()
Dim SourceRng
Dim CritRng
Dim CopyToRng
Set SourceRng = Sheets("DevData").Range("SourceDev")
Set CritRng = Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = Sheets("Results").Range("A2:W2")

SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, _
CopyToRange:=CopyToRng, Unique:=False
End Sub
 
I would prefer the last one but with the variables declared as Range coz

1. You are accessing the range object directly
2. Any changes to the code (sheetname or the range reference) at a later
stage would be easy to make ..Just one place to edit and that too at top of
the procedure..

Dim SourceRng As Range
Dim CritRng As Range
Dim CopyToRng As Range
 
I agree - it makes the code much easier to understand, and so making changes
later become easier...not only for you, but for anyone maybe later in the sa

I'd have added a reference to the workbook too, but thats me being picky ;)

with ThisWorkbookEnd With
 
Thanks for the input. I understand the making changes to the code in the
futre would be easier using option #3, but do are any of the formats more
readily identified by vba? thanks again.
 
Back
Top