scott’s spot

Now that we’ve eliminated the most obviously unneeded lines of VBA from the routine, we can start shaping this into something a little smoother.

The first thing I did was to add comments. I find, whether I’m coding for myself or a business partner, or whether I’m running through somebody else’s code and trying to understand its flow and function, by adding comments in plain old english, I get where I need to go just that much faster.

To that end, I added a couple of lines about what exactly we were doing with this routine right after I declared it.

Submco_Populate()
‘   This routine moves data back and forth between two sheets
‘   in the same workbook, after first clearing out the old data,
‘   and ensuring that an autofilter is present.
‘
‘   Author: Scott Lyerly, scott[dot]c[dot]lyerly[at]hotmail[dot]com.
‘   Date: 5/7/2009.

I like to “sign and date” my code so that people know who did what and when (and how to get hold of me in case something goes wrong and needs to be fixed–not that anything ever goes wring in my code, of course…).

Once done, it was time to start making this thing over. I began by declaring some variables and then assigning values and objects to them:

    ‘ Variable declarations.
    DimThisBook As Workbook
    Dim Styles As Worksheet, TB As Worksheet
    DimrngTB As Range

    ‘ Set the variables
    Set ThisBook = ThisWorkbook
    Set Styles = ThisBook.Sheets(“Styles”)
    Set TB = ThisBook.Sheets(“TB”)
    Set rngTB = ThinkBig.Range(“A25:F26″)

First I declare the variables, then I assign them. Now, whenever I want to reference one of them, I don’t need to write something like:

ThisWorkbook.Sheets(“TB”).Range (“A25:F26″)

I can now just activate the sheet and reference the range variable:

   TB.Activate
   rngTB.Select

This makes it worlds easier to maneuver through the workbook objects (books, sheets, ranges, cells, etc.). And if I need to change one (say the range for example) I change it in one spot, the place where I assigned it, rather than in each instance where the macro recorded (and hard-coded) the range reference.

The next thing I wanted to do was to ensure an autofilter was applied to the range from which I’ll copy. Since part of this process hinges on a certain field being filtered for “Y”, I felt it was important to double-check and make sure the filter was actually present.

To do this, I decided to declare a constant first:

    ‘ Constant delcarations.
    Const FilterCrit As String = “Y”

This was one of those thing I probably didn’t need to do, since we only apply the filter once, and it’s always “Y”, but I like things neat and tidy and having this “Y” as a constant gives me complete control over it. Now, instead of using “Y” in the filter criteria in the code, I can use the constant. And if I need to change the constant for some reason, I change it in its declaration, and lo! it’s good throughout the whole of the code anywhere it shows itself.

Then I needed to check to make sure a filter was applied:

    ‘ Check to make sure an autofilter is actually present.
    If NotStyles.AutoFilterMode Then
        MsgBox “There’s no autofilter here. Please apply an autofilter before continuing.”, vbOKOnly + vbExclamation
    Exit Sub
    End If

    ‘ If the our sub-routine is still running at this point, it’s safe to
    ‘ assume an autofilter exists in the sheet in question. Now we simply
    ‘ apply our constant, the criteria, to the filter.
    Selection.AutoFilter Field:=1, Criteria1:=FilterCrit.

And at the end of this snippet, I assign the constant to the appropriate autofilter field, which causes the sheet to become filtered.

So…we’ve streamlined out workbook object references, and we’ve made sure an autofilter exists and have applied it.

Our code clean-up to date:

Submco_Populate()
‘   This routine moves data back and forth between two sheets
‘   in the same workbook, after first clearing out the old data,
‘   and ensuring that an autofilter is present.
‘
‘   Author: Scott Lyerly, scott[dot]c[dot]lyerly[at]hotmail[dot]com.
‘   Date: 5/7/2009.

 
    ‘ Constant delcarations.
    Const FilterCrit As String = “Y”

 
    ‘ Variable declarations.
    DimThisBook As Workbook
    Dim Styles As Worksheet, TB As Worksheet
    DimrngTB As Range
    ‘ Set the variables
    Set ThisBook = ThisWorkbook
    Set Styles = ThisBook.Sheets(“Styles”)
    Set TB = ThisBook.Sheets(“TB”)
    Set rngTB = ThinkBig.Range(“A25:F26″)

 
    ‘ Activate the sheet that will be the source of our data.
     Styles.Activate

 
    ‘ Check to make sure an autofilter is actually present.
    If NotStyles.AutoFilterMode Then
        MsgBox “There’s no autofilter here. Please apply an autofilter before continuing.”, vbOKOnly + vbExclamation
    Exit Sub
    End If

    ‘ If the our sub-routine is still running at this point, it’s safe to
    ‘ assume an autofilter exists in the sheet in question. Now we simply
    ‘ apply our constant, the criteria, to the filter.
    Selection.AutoFilter Field:=1, Criteria1:=FilterCrit

On to the movement of data…our topic for tomorrow.