VBA code question
 

I have been using the amature way of writing micros, which is RecordMicro.
Then I will got the code, make few changes and it worked fine.

I would like to learn how to write the code a little better, more efficient
and effective. Most of all I like to learn how to get rid of repeted
Selection that you get when using RecordMicro.

Example code to be fixed: (how to make it better)

ActiveSheet.Unprotect Password:="eli"
Range("D:D,B:B").ColumnWidth = 4.57
Range("A:A,E:E").ColumnWidth = 0
Range("B1", Range("Last")).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
EnableSelection = Excel.XlEnableSelection.xlNoSelection
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect Password:="eli", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True
End With

Any help appriciated. Thank you.

VBA code question
 

Here is my take on what it would approximately look like if I wrote it from
scratch...

ActiveSheet.Unprotect Password:="eli"
Range("D:D,B:B").ColumnWidth = 4.57
Range("A:A,E:E").ColumnWidth = 0
With Range("B1", Range("Last"))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlDash
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlDash
.Borders(xlInsideVertical).LineStyle = xlNone
End With
EnableSelection = Excel.XlEnableSelection.xlNoSelection
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect Password:="eli", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True
End With


Note that all of the selections are gone. I would also be inclined to remove
the activesheet reference and chane it to referenece a specific sheet.
--
HTH...

Jim Thomlinson


"Damian" wrote:

I have been using the amature way of writing micros, which is RecordMicro.
Then I will got the code, make few changes and it worked fine.

I would like to learn how to write the code a little better, more efficient
and effective. Most of all I like to learn how to get rid of repeted
Selection that you get when using RecordMicro.

Example code to be fixed: (how to make it better)

ActiveSheet.Unprotect Password:="eli"
Range("D:D,B:B").ColumnWidth = 4.57
Range("A:A,E:E").ColumnWidth = 0
Range("B1", Range("Last")).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
EnableSelection = Excel.XlEnableSelection.xlNoSelection
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect Password:="eli", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True
End With

Any help appriciated. Thank you.