Excel VBA 编程练习

最近做了一个VBA的小case，用于方便excel数据的处理，主要的功能代码记录如下。

1. 根据表单名称从workbook中查找出特定表单：

    For Each sitem In ThisWorkbook.Worksheets
        If sitem.Name = sname Then
            ' sitem is the object that we wants
            Exit For
        End If
    Next

2. 复制表单m的特定内容到表单n：

Sheets(m).Range("A10:C11").Copy Sheets(n).Cells(1, 1)

3. 删除表单特定区域或者是特定区域的数据验证逻辑规则：

Sheets(m).Range("A10:C11").Delete
Sheets(m).Range("A10:C11").Validation.Delete

4. 添加新的worksheet并更改其名称：

    ThisWorkbook.Worksheets.Add
    ActiveSheet.Name = sname 'ActiveSheet is the new one

5.具体代码

    r = ActiveSheet.UsedRange.Rows.Count
    c = ActiveSheet.UsedRange.Columns.Count
    Dim i As Integer
    Dim j As Integer
    Dim sname As String
    Dim sperson As String
    Dim rgtemp As String
    sname = ActiveSheet.Cells(1, 2).Text
    sperson = ActiveSheet.Cells(1, 4).Text
    If Sheet3.Cells(r, c).Text <> "" Or IsEmpty(sname) Then
        
        MsgBox ("A new sheet (Rig.: " + sname + "; Resp. person: " + sperson + ";) is about to be created.")
        Worksheets.Add
        ActiveSheet.name = sname
        Sheet2.Cells.Copy ActiveSheet.Cells(1, 1)
        rgtemp = "B3:E" + Trim(Str(r))
        Sheet3.Range(rgtemp).Copy ActiveSheet.Cells(18, 5)
        ActiveSheet.Cells(5, 3).Value = sname