How to improve code from macros recorder?

Question

I use this code from macros recorder and add inside a for loop.

How can do better this code if is possible ?

I want use both 2003 and 2010.

Range(Cells(2, 2).Address, Cells(5, 5).Address).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlDouble
    .Color = -16777216
    .Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlDouble
    .Color = -16777216
    .Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlDouble
    .Color = -16777216
    .Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlDouble
    .Color = -16777216
    .Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .Weight = xlThin
End With

how do you want to make the code better? what is your problem with the code? — stormCloud, Apr 06 '13 at 11:53
http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179 — Siddharth Rout, Apr 06 '13 at 12:25
To improve it I would say never use select when working with ranges — Steven Marciano, Apr 06 '13 at 12:39
@StevenMarciano: Did you see the link that I posted above? :) — Siddharth Rout, Apr 06 '13 at 16:22
Ah no, sorry I didn't! That is a much more concise answer than mine. Good stuff — Steven Marciano, Apr 06 '13 at 17:12
@stormCloud The code is working fine but i think to be smaller — KyriakosGian, Apr 06 '13 at 22:12
This is the for loop 'For a = startcol To ((startcol - 1) + (colnumper * objectsnum)) Step colnumper Range(Cells(kefalida, a).Address, Cells(rowsstop, a + (colnumper - 1)).Address).Select' — KyriakosGian, Apr 06 '13 at 22:13

score 3 · Accepted Answer · edited May 23 '17 at 12:13

Further to my comment above which includes a link which talks in detail on avoiding .Select.

Here are my three cents.

A. Declare your objects/Variables

It becomes easier to work when you have declared your Variables/objects. This ensures that you do not have to type repetitive code. For example

Range(Cells(2, 2).Address, Cells(5, 5).Address).THIS
Range(Cells(2, 2).Address, Cells(5, 5).Address).THAT
Range(Cells(2, 2).Address, Cells(5, 5).Address).THIS

etc...

B. Ensure that you fully qualify your objects and work with them

This is the most common cause of errors. Consider this line.

Range(Cells(2, 2).Address, Cells(5, 5).Address)

Here Excel is assuming that you are working with the current sheet. What if you are not. See this example

Sheets(2).Range(Cells(2, 2).Address, Cells(5, 5).Address)

Here the Cells() object are not fully qualified and can cause an error. See this post

C. Cut out the extra/repetitive code

The Excel constants xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight etc each is equal to a number and that too in increasing order. If you type it in Immediate window then you can check it's value

'~~> This will give you 7
?xlEdgeLeft

So we can actually take advantage of this and shorten out code.

See the code below

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set rng = .Range(.Cells(2, 2).Address, .Cells(5, 5).Address)

        With rng
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone

            For i = 7 To 10
                'xlEdgeLeft = 7 : xlEdgeTop = 8 : xlEdgeBottom = 9
                'xlEdgeRight = 10
                With .Borders(i)
                    .LineStyle = xlDouble: .Color = -16777216: .Weight = xlThick
                End With
            Next

            For i = 11 To 12
                'xlInsideVertical = 11 : xlInsideHorizontal = 12
                With .Borders(i)
                    .LineStyle = xlContinuous: _
                    .ColorIndex = xlAutomatic: .Weight = xlThick
                End With
            Next
        End With
    End With
End Sub

I would also mention "Avoid using the `.Select` method" (which you are already doing) — RBarryYoung, Apr 06 '13 at 16:45
Barry, I already did that :) See the link in the comments below the question — Siddharth Rout, Apr 06 '13 at 16:45
However point taken and I will add that in my post above as well. — Siddharth Rout, Apr 06 '13 at 16:46

Steven Marciano · Answer 2 · 2013-04-06T12:53:55.417

1

Try using the range like this

Range(Cells(2, 2).Address, Cells(5, 5).Address).Borders(xlEdgeBottom).LineStyle = xlDouble

Do this for each border edge and colour etc

edited Apr 06 '13 at 12:53

answered Apr 06 '13 at 12:48

Steven Marciano

130
1
9

How to improve code from macros recorder?

2 Answers2