What is the syntax to declare a worksheet object?

Dim <object name> As Worksheet
Set <object name> = Worksheets("<sheet name>")
'example of simplification with the worksheet object

'Original code
Worksheets("exampleSheet").Range("A1") = 6
Worksheets("exampleSheet").Range("B2:B9").Font.Italic = True
Worksheets("exampleSheet").Range("B2:B9").Interior.Color = rgbRed

'Simplified code
Dim sht As Worksheet
Set sht = Worksheets("exampleSheet")

sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed

 

 

Advertisements

The danger of loops

Loops are easy to learn, simple, and thus, abundantly overused. Beginners to the VBA language often fall into the trap of overusing loops when much simpler code statements can be substituted. Be eager to continuously learn new code semantics, and not always fall back on loops.

(ex) The following example searches a column for empty slots and flags them as “empty”

‘example with loops
Dim searchRange as Range
For Each searchRange In Range(“A1:A15”)
    If IsEmpty(searchRange) Then searchRange = “Empty”
Next searchRange

‘example without loops
Range(“A1:A15”).SpecialCells(xlCellTypeBlanks) = “Empty

unnamed (4).jpgunnamed (3)

 

 

What is the syntax to use a With statement?

With <object expression>
 .<statements>
End Selection

Use these statements when you need to set many properties for the same object. Improves code readability

‘example without with statement
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlLeft
Selection.Orientation = 0
Selection.WrapText = false
Selection.MergeCells=true

‘example using a with statement
With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlLeft
 .Orientation = 0
 .WrapText = false
 .Mergecells=true
End With