Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using the same piece of code more than once

0

I have created a rather large project which involves a dynamic dashboard, a static KPI and a report function including automatic e-mail function (LotusNotes) to all colleagues. The entire project has about 20 modules and up to a 100 macro's. Since I am not a professional this took me months to create, starting with a good book on VBA, some courses on the internet and sign up for some great newsletters.

I am always looking to improve my knowledge in order to improve my code. One thing I always read is not to use the same code twice. However I have about 20 subs with just one line of code being:

Worksheets("DT's").Range("AO" & Worksheets("DT's").Cells(Rows.Count, "AO").End(xlUp).Row).ShowDetail = True

Only "AO" being the column will change everytime.

What this code basically does is that it will show all the data selected by the slicers in the dashboard. Like double-clicking the total in each pivot-table.

Would it be ok to use this code the way I do or is there a better way to do this. I feel that everything I come up with will take at least one more line of code.
Answer
Discuss

Answers

1
Selected Answer

Consider calling your sub with a parameter, like this.

Private Sub ProcedureCall()
    ShowSlicer "AO"
End Sub

Sub ShowSlicer(Clm As String)
    Worksheets("DT's").Range(Clm & Worksheets("DT's").Cells(Rows.Count, Clm).End(xlUp).Row).ShowDetail = True
End Sub

As you see, the variable Clm is supplied by the procedure call. The sub will execute its function on a different column depending upon which one is specified by the parameter.

The same idea can be written more efficiently like this.

Sub ShowSlicer2(Clm As String)
    With Worksheets("DT's")
        .Range(Clm & .Cells(.Rows.Count, Clm).End(xlUp).Row).ShowDetail = True
    End With
End Sub

The big difference between your code and this one is in the period leading .Rows.Count. This period specifies the row count of Worksheets("DT's") whereas your code, lacking that specification, will take the row count from the ActiveSheet. Not that it will make much difference because Worksheets("DT's") probably is the ActiveSheet and, in any case, the row count of all sheets are likely to be equal. But if you ever get to a situation where that isn't so your hunt for the missing period will be a protracted effort.

I draw your attention to this part of the above code (in fact, your code).

 .Cells(.Rows.Count, Clm)

This is the correct way to specify a cell, i.e. Cells([Row number], [Column {number}]). Your way, Range([Column name] & [Row number]) is cumbersome as your example shows perfectly. You actually specify the cell you want and then take its row number to construct its range address. This is the easier way.

Sub ShowSlicer3(Clm As String)

    With Worksheets("DT's")
        .Cells(.Rows.Count, Clm).End(xlUp).ShowDetail = True
    End With
End Sub

Excel gives columns names, such as "AO", but it actually works with numbers for both rows and columns. When you write a loop you will soon know the advantage. VBA understands column names both in range addresses (like Range("AO" & 3)) and cell addresses (like Cells(3, "AO")). However, for the reason just given you will come to prefer addresses with column numbers if you continue to program. That would make your code look like this.

Private Sub ProcedureCall2()
    ShowSlicer3 41      ' or Columns("AO").Column
End Sub

Sub ShowSlicer3(Clm As Long)

    With Worksheets("DT's")
        .Cells(.Rows.Count, Clm).End(xlUp).ShowDetail = True
    End With
End Sub

To wind up this lesson, if you specify cell addresses you will automatically stop specifying cells as ranges. You will come to specify ranges only if they comprise a number of cells. This is the code for that.

Range(Cells(3, "A"), Cells(5, "D"))
or
Range(Cells(3, 1), Cells(5, 4))

This looks a lot more elaborate than simply Range("A1:D5"). You will stop complaining about that when you want to specify the address of each cell in the range in a loop with a For ... Next counter for each, rows and columns. Using column names often makes code more legible but the cost is in lost flexibility. Code also runs more slowly because the names must be converted to numbers.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login