1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
Sub main()

     ' wb：工作簿
    Dim wb As Workbook
    
    ' ws：工作表
    Dim ws As Worksheet
    
    ' file：打开的路径
    Dim file As String
    file = "C:\Users\25276\Desktop\test.xlsx"
    
    ' 打开工作簿
    Set wb = Workbooks.Open(file)
    ' 打开【指定】工作表
    Set ws = Worksheets("JasonQian")
    ' 激活工作表
    ws.Activate
        
    ' 调用函数
    ' Dim totalCount As Long
    ' totalCount = rowsCount(ws)
    handleElement ws
    
End Sub
   


' Sub不返回值 操作表中的一些值
Sub modifyElement(ByRef ws As Worksheet)
    ' 修改表名字
    ws.Name = "JasonQian"
    
    
    ' Celss属性
    
    ' 返回表中的一个单元格，可以进行操作
    ws.Cells(2, 3).Value = "Cells(2, 3)"
    
    
    ' Range属性
    ' 单个值
    ws.Range("A1").Value = "Range('A1')"
    ' 区域内的值
    ws.Range("A2:B4").Value = "Range('A2:B4')"
    
    ' UsedRange属性
    Dim rng As Range
    Set rng = ws.UsedRange
    ' 显示以及使用过的单元格数
    ' MsgBox rng.Cells.Count
    
    ' Rows和Columns属性
    ws.Rows(2).Hidden = False
    ws.Columns(3).Delete
    
    ' ClearContents方法
    ws.Cells.ClearContents  ' 清除所有
    ws.Range("A3:B3").ClearContents  ' 清楚指定区域
    
End Sub


' Function返回值 返回第一列的长度
Function rowsCount(ByVal ws As Worksheet) As Long
    Dim rc As Long
    rc = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row  ' 获取工作表中A列的最后一个有效单元格位置
    ' End(xlUp)用于从列底部向上找到第一个非空单元格
    
    
    ' 如果是行，使用End(xlToLeft)查找第一行
    ' ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    
    ' MsgBox rc
    
    rowsCount = rc  ' 返回行数
End Function

' for循环操作
Sub forOperator(ByVal ws As Worksheet)
    Dim rc As Long
    rc = rowsCount(ws)
    
    For i = 1 To rc
        Debug.Print i
    Next i
        
End Sub

' if操作
Sub IfOeprator(ByVal ws As Worksheet)
    Dim rc As Long
    rc = rowsCount(ws)
    If rc > 10 Then
        Debug.Print "G"
    Else
        Debug.Print "GG"
    End If
End Sub



Function CountDots(cellValue As String) As Integer
    Dim dotCount As Integer
    dotCount = Len(cellValue) - Len(Replace(cellValue, ".", ""))
    
    ' 返回层级 (点号的数量 + 1)
    CountDots = dotCount + 1
End Function