Excel 宏基础知识-1

1. Excel VBA - Cell Referencing 有四种方法：

1. [b5].Value = 70
   [c1:c10] = "Tutorials"

2. Cells(8, 2).Value = "India"

3. Range("a1").Value = "BeiJing"

4. Range("a2:a10") = "Shanghai"

2. Excel VBA - Copy Paste

Sub copy_paste()

Range("a1:a10") = "Tutorials"

'1st Method
Range("b1:b10") = Range("a1:a10").Value

'2st Method
Range("a1:a10").Copy
Range("d1:d10").PasteSpecial
Application.CutCopyMode = False

End Sub

image.png

3. Excel VBA - Font

image.png

4. Excel VBA - With Block

Sub with_block()

Range("a1:a10") = "Tutorials"

With Range("a1:a10").font
        .Name = "arial"
        
        .Bold = True
        .Bold = False
        
        .Italic = True
        .Italic = False
        
        .Size = 10
        .Size = 20
End With
        


End Sub

5. Excel VBA - Borders

Range("a1:a10").borders.LineStyle = xlDot

image.png

Range("a1:a10").borders.LineStyle = xlDash

Range("a1:a10").borders.LineStyle = xlContinuous

Range("a1:a10").borders.LineStyle = xlDouble

6. Excel VBA - Alignment

Sub alignment()

'Horizontal Alignment
Range("a1:c5").HorizontalAlignment = xlLeft
Range("a1:c5").HorizontalAlignment = xlRight
Range("a1:c5").HorizontalAlignment = xlCenter


'Vertical Alignment
Range("a1:c5").VerticalAlignment = xlTop
Range("a1:c5").VerticalAlignment = xlBottom
Range("a1:c5").VerticalAlignment = xlCenter

End Sub

7. Excel VBA - Font Color

Sub font_color()
'8 Standard colors
Range("a1:a10").Font.Color = vbWhite
Range("a1:a10").Font.Color = vbBlack
Range("a1:a10").Font.Color = vbYellow
Range("a1:a10").Font.Color = vbRed
Range("a1:a10").Font.Color = vbGreen
Range("a1:a10").Font.Color = vbBlue
Range("a1:a10").Font.Color = vbCyan
Range("a1:a10").Font.Color = vbmagneta
'color index till 56
Range("a1:a10").Font.ColorIndex = 1
Range("a1:a10").Font.ColorIndex = 10
Range("a1:a10").Font.ColorIndex = 50
End Sub

8. Excel VBA - Background Color

Sub font_color()
'8 Standard colors
Range("a1:a10").Interior.Color = vbWhite
Range("a1:a10").Interior.Color = vbBlack
Range("a1:a10").Interior.Color = vbYellow
Range("a1:a10").Interior.Color = vbRed
Range("a1:a10").Interior.Color = vbGreen
Range("a1:a10").Interior.Color = vbBlue
Range("a1:a10").Interior.Color = vbCyan
Range("a1:a10").Interior.Color = vbmagneta
'color index till 56
Range("a1:a10").Interior.ColorIndex = 1
Range("a1:a10").Interior.ColorIndex = 10
Range("a1:a10").Interior.ColorIndex = 50
End Sub

9. Excel VBA - Paste Special

image.png

Sub pastespecial()


Range("a1:a10").Copy

Range("b1:b10").pastespecial xlPasteFormats
Range("b1:b10").pastespecial xlPasteColumnWidths
Range("b1:b10").pastespecial xlPasteValues

Application.CutCopyMode = False


End Sub

10. Excel VBA - Orientation

image.png

Sub orientation()


Range("a1").orientation = 0
Range("a1").orientation = 10
Range("a1").orientation = 20
Range("a1").orientation = 30
Range("a1").orientation = 40
Range("a1").orientation = 50
Range("a1").orientation = 60
Range("a1").orientation = 70
Range("a1").orientation = 80
Range("a1").orientation = 90


End Sub

11. Excel VBA - Wrap Text

image.png
image.png

Sub wrap_text()

Range("a1:a5").WrapText = True
Range("a1:a5").WrapText = False

End Sub

12.Excel VBA - Merge Unmerge

image.png

sub merge_Unmerge()
Range("a9:d9").Merge
Range("a9:d9").UnMerge
End Sub

13 Excel VBA - Clear Cells

Sub clear()
Range("a1:a10").ClearFormats
Range("a1:a10").ClearComments
Range("a1:a10").ClearHyperlinks
Range("a1:a10").clear
End Sub

14 Excel VBA - Delete Cells

Sub delete_cells()
Range("b3").Delete
Range("a1:a10").Delete
 
 
Ranage("b6").EntireRow.Delete
Ranage("c6").EntireColumn.Delete
End Sub

15 Excel VBA - Rows & Columns Insert

Sub insert_row_column()
'在C列前添加一列
Range("c:c").Insert
Range("1:1").Insert

Range("b5").EntireRow.Insert
Range("b5").EntireColumn.Insert

Range
End Sub