VBA 知識

weixin_34194087發表於2018-06-01
  • Excel sheets和worksheets的區別
    Sheets集合代表當前工作簿中的所有工作表，包括圖表、工作表、對話方塊工作表和巨集表。
    Worksheets集合僅代表當前工作簿中的所有工作表。
4241417-6714b8081206901b.png
Sheet.png

區別：worksheets只代表工作表，僅第一個。sheets包括全部

  • 遍歷工作表六種方法
1.Sub 遍歷工作表()
For Each sh In Worksheets    '陣列
    sh.Select
    Call 設定A1格式
Next
End Sub
--------------------------------------
2.Sub 遍歷工作表()
For Each sh In Worksheets    '陣列
    if sh.name <> 表名1 and sh.name <>表名 2 then
            sh.Select
            Call 設定A1格式
    end if 
Next
End Sub
--------------------------------------
3.Sub 迴圈工作表()
    For Each sh In Worksheets
        If sh.Index > 2 Then    '限定工作表範圍
       sh.Select
              Call 設定A1格式
        End If
    Next
End Sub
--------------------------------------
4.Sub 遍歷工作表()
For Each sh In Worksheets    '陣列
    If sh.Name Like "*" & "表" & "*" Then     '如果工作表名稱包含“表”
        sh.Select
        Call 設定A1格式
    End If
Next
End Sub
---------------------------------
5.Sub 遍歷工作表()
For Each sh In Worksheets    '陣列
    If Not sh.Name Like "\*" & "表" & "*" Then     '如果工作表名稱不包含“表”
        sh.Select
        Call 設定A1格式
    End If
Next
End Sub
-------------------------
6.Sub 遍歷工作表()
For Each sh In Worksheets    '陣列
    If sh.Name <> "價格表" And sh.Name <> "人員表" Then    '指定不參與迴圈的工作表名稱，可一個或多個，自行增減
        sh.Select
        Call 設定A1格式
    End If
Next
End Sub
  • 刪除重複項
  1. VBA去重複的幾種方法
Sub 刪除重複項()
 
Application.ScreenUpdating = False
 
Dim rga, rgb As Range
Dim a, b As String
 
For Each rga In Range("c2:f25")
For Each rgb In Range("h30:z55")
 
a = rga.Value
b = rgb.Value
 
If a = b And rgb <> "" Then
rga.ClearContents
rgb.Interior.ColorIndex = 3
End If
 
Next rgb
Next rga
 
Application.ScreenUpdating = True
 
End Sub

'使用 ParamArray 關鍵字可以使函式接收數目可變的引數
Function CalcSum(ByVal FirstArg As Integer, ParamArray OtherArgs())
    Dim i As Integer
    CalcSum = FirstArg
    For i = 0 To UBound(OtherArgs)
        CalcSum = CalcSum + OtherArgs(i)
    Next
End Function
'如果用如下程式碼呼叫該函式：
Sub Example1()
    Dim ReturnValue
    '區域性變數賦予以下值：FirstArg = 4，
    'OtherArgs(0) = 3，OtherArgs(1) = 2，OtherArgs(2) = 1，等等。
    ReturnValue = CalcSum(4, 3, 2, 1)
    MsgBox ReturnValue
End Sub
  • 集合 Collection
Sub test()
'    Dim s As Collection '定義s變數為集合物件
'    Set s = New Collection '初始化集合物件s (否則無法使用)

    Dim s As New Collection '推薦這句程式碼，直接初始化，可以不用再Set了

    '集合s中新增元素的方法
    For i = 1 To 10
        s.Add i '對於集合s，用Add方法可以加入集合元素
            '當然事實上你可以新增任意內容來代替本例中的i
    Next

    '讀取集合中元素的方法    
    For i = 1 To s.Count '可以用Count屬性返回集合中元素總個數
        t = s(i) '讀取集合s中第i個變數存入臨時變數t中
        t = s.Item(i) '正規的程式碼寫法應該是這樣子(效果一樣，推薦用上一句更簡明)
        Debug.Print s(i) '在立即視窗中觀察s(i)的值
    Next
    
    '下面是集合s中元素刪除的方法
    For i = 1 To s.Count '遍歷集合元素
        s.Remove (1) '每次刪去第1個……直至全部刪除完畢 或直接簡寫為： s.Remove 1
'        s.Remove (s.Count) '或者每次刪除最後一個，直至全部刪除完畢(推薦用上一句，不容易錯)
'        或簡寫為 s.Remove s.Count       
        Debug.Print s.Count '在立即視窗中觀察集合s中剩餘元素個數
    Next
    
End Sub
  • 在 VBA 中可以通過 Font 物件的 Name.Size 等屬性設定字型的格式。
  1. 方法一：
    在 VBA 視窗中輸入以下程式碼即可一步實現：
Sub 設定字型格式()

  Range("A1").Select With Selection .Font.Name = "黑體" .Font.Size = 16 .Font.ColorIndex = 5 .Interior.ColorIndex = 39 End With

  Range("A2:G2").Select With Selection.Font .Name = "微軟雅黑" .Size = 14 End With

  Range("A3:A21,C3:C21,G3:G21").Select With Selection.Font .Name = "TimeS New Roman" .Size = 11 End With

  Range("A2:G21").Select With Selection .HorizontalAl ignment = xlCenter .VerticalAlignment = xlCenter End With

  Range("A1").Select

End Sub
  1. 方法二：
    通過錄制巨集的方法獲取為表格新增邊框的程式碼，但由巨集錄製器生成的程式碼會分別設定單元格的每個邊框，因此程式碼過多且效率低。實際上，使用 Range 物件的 Borders 集合可快速對單元格區域的每個邊框應用相同格式，且 Range 物件的 BorderAround 方法可以快速為單元格區域新增一個外邊框。

例如，在 Excel 2010 的工作表中新增邊框，只要輸入以下 VBA 程式碼即可快速實現：

Sub 新增邊框()

  Dim i As Range Set i = Range("A1:G21") '設定物件變數為單元格區域"A1：G21"

  With i.Borders(xlInsideVertical) 
    .LineStyle = xlContinuous '設定邊框的線條樣式

    .Weight = xlThin '設定邊框線條的精細

    .ColorIndex = 55 '設定邊框的顏色

  End With

  With i.Borders(xlInsideHorizontal) 
    .LineStyle = xlDash .Weight = xlThin .ColorIndex = 55
  End With

  i.BorderAround xlContinuous, xlMedium, 55 Set i = Nothing '從記憶體中釋放物件變數，與Set相對應

End Sub

當在執行該程式後，即可為 Excel 2010 表新增邊框。

操作步驟

  • 提示：另外，Borders(index) 屬性返回單個 Border 物件，其中引數 index 的取值及意義如下表所示。
'常量--值--說明
xlDiagonaIDown ：5　斜下邊框
xlDiagonalUp ：6　斜上邊框
xlEdgeLeft ：7　左邊框
xlEdgeTop ：8　頂部邊框
xlEdgeBottom ：9　底部邊框
xlEdgeRight ：10　右邊框
xllnsideVertical ：11　內部垂宜
xllnsideHorizontal ：12　內部水平

應用 Range 物件的 BorderAround 方法向單元格區域新增整個區域的外邊框，其語法格式為：BorderAround(LineStyle, Weight, ColorIndex, Color)。其中 Colorlndex 引數可以指定當前調色盤中的顏色，Color 引數以 RGB 值指定邊框的顏色，可以是當前調色盤以外的顏色，但是不能同時指定這兩個引數。

  • 行和列 Row Column
'vba 取行數
Sheets（1）.[A65536].End(xlUp).Row '''該列最後一行
'vba 取列數
Cells(14, 256).End(xlToLeft).Column'''該14行最後一列</pre>
'當前工作表總行數
k=activesheet.usedrange.rows.count
'指定sheet1工作表總列數
j=sheets("sheet1").usedrange.columns.count
'前提是頂部和左側無空行空列

相關文章