VBA 代码逐句解释：用户界面初始化、数据加载及列表视图操作

Public SqlOperation As SqlOperation Public ListViewOperation1 As ListViewOperation Public ListViewOperation2 As ListViewOperation Public ListViewOperation3 As ListViewOperation

' 声明四个变量，分别为公共的 SqlOperation 类、ListViewOperation 类的实例 ' Get_Code 函数返回一个字符串，该字符串由输入的 Number、当前时间、和四位随机数构成 Public Function Get_Code(ByVal Number As String) As String Get_Code = Number & WorksheetFunction.Text(Now, "-yymmdd-hhssmm-") & WorksheetFunction.RandBetween(1000, 9999) End Function

' UserForm1Show 子过程用于初始化用户界面和 ListViewOperation 类的实例 Public Sub UserForm1Show() Set SqlOperation = New SqlOperation SqlOperation.ExcelConnectionString = ThisWorkbook.FullName

' 使用 With 语句引用 UserForm1 及其子控件，便于代码书写
With UserForm1
    ' 创建 ListViewOperation 类的实例，并设置其属性
    Set ListViewOperation1 = New ListViewOperation
    ListViewOperation1.ListView = .ListView1
    ListViewOperation1.HomePage = .CommandButton7
    ListViewOperation1.Left = .CommandButton8
    ListViewOperation1.Right = .CommandButton9
    ListViewOperation1.LastPage = .CommandButton10
    ListViewOperation1.Query = .CommandButton1
    ListViewOperation1.Reset = .CommandButton2
    ListViewOperation1.ListControls.Add .TextBox1
    ListViewOperation1.ListControls.Add .TextBox2
    ListViewOperation1.QueryOnAction = "AddListView1"
    ListViewOperation1.DisplayCount = 39
    
    ' 创建两个 ListViewOperation 类的实例，并设置其属性
    Set ListViewOperation2 = New ListViewOperation
    ListViewOperation2.ListView = .ListView2
    ListViewOperation2.HomePage = .CommandButton11
    ListViewOperation2.Left = .CommandButton12
    ListViewOperation2.Right = .CommandButton13
    ListViewOperation2.LastPage = .CommandButton14
    ListViewOperation2.Query = .CommandButton3
    ListViewOperation2.Reset = .CommandButton4
    ListViewOperation2.StartDate = .TextBox7
    ListViewOperation2.EndDate = .TextBox8
    ListViewOperation2.ListControls.Add .ComboBox1
    ListViewOperation2.ListControls.Add .TextBox3
    ListViewOperation2.ListControls.Add .TextBox4
    ListViewOperation2.ListControls.Add .TextBox7
    ListViewOperation2.ListControls.Add .TextBox8
    ListViewOperation2.QueryOnAction = "AddListView2"
    ListViewOperation2.DisplayCount = 39
    
    Set ListViewOperation3 = New ListViewOperation
    ListViewOperation3.ListView = .ListView3
    ListViewOperation3.HomePage = .CommandButton15
    ListViewOperation3.Left = .CommandButton16
    ListViewOperation3.Right = .CommandButton17
    ListViewOperation3.LastPage = .CommandButton18
    ListViewOperation3.Query = .CommandButton5
    ListViewOperation3.Reset = .CommandButton6
    ListViewOperation3.StartDate = .TextBox10
    ListViewOperation3.EndDate = .TextBox9
    ListViewOperation3.ListControls.Add .ComboBox2
    ListViewOperation3.ListControls.Add .TextBox5
    ListViewOperation3.ListControls.Add .TextBox6
    ListViewOperation3.ListControls.Add .TextBox9
    ListViewOperation3.ListControls.Add .TextBox10
    ListViewOperation3.QueryOnAction = "AddListView3"
    ListViewOperation3.DisplayCount = 39
    
    ' 将 Sheet4 中的数据加载到 ListView 中
    NewlyListView .ListView1, Sheet4.ListObjects("ListView1").DataBodyRange.value
    NewlyListView .ListView2, Sheet4.ListObjects("ListView2").DataBodyRange.value
    NewlyListView .ListView3, Sheet4.ListObjects("ListView3").DataBodyRange.value
    
    ' 调用 AddListView1、AddListView2、AddListView3 过程
    AddListView1
    Addlistview2
    Addlistview3
    
    ' 显示用户界面
    .Show 0
End With

End Sub

' NewlyListView 过程用于创建 ListView 控件，并设置其属性 Public Sub NewlyListView(ByVal ListView As ListView, ByVal Arr As Variant) Dim i As Integer With ListView .View = lvwReport .Gridlines = True .FullRowSelect = True .LabelEdit = lvwManual .AllowColumnReorder = True .ColumnHeaders.Clear For i = 1 To UBound(Arr) .ColumnHeaders.Add Arr(i, 1), , Arr(i, 2), Arr(i, 3), 0 Next End With End Sub

' SetWidth 过程用于设置 ListView2 中列的宽度 Public Sub SetWidth() Dim i As Integer With UserForm1.ListView2.ColumnHeaders For i = 1 To .Count Sheet4.ListObjects("ListView2").ListRows(i).Range(, 3).value = .Item(i).Width Next End With End Sub

' AddListView1 过程用于向 ListView1 中添加数据 Public Sub AddListView1() Dim sql As String, Arr As Variant With UserForm1 sql = "select ID,货号,名称,厂家,规格型号,存放温度,单位,入库数量,出库数量,库存数量 from [试剂$] where 1=1" If .TextBox1.Text <> "" Then sql = sql & " and 名称 like '%" & .TextBox1.Text & "%'" End If If .TextBox2.Text <> "" Then sql = sql & " and 厂家 like '%" & .TextBox2.Text & "%'" End If SqlOperation.SelectCommand = sql If SqlOperation.GetRstDataBoolean(Arr) Then ListViewOperation1.DataSource = Arr Else ListViewOperation1.CommandButtonEnabled_False End If End With End Sub

' Addlistview2 过程用于向 ListView2 中添加数据 Public Sub Addlistview2() Dim sql As String, Arr As Variant With UserForm1 sql = "select ID,货号,名称,厂家,规格型号,存放温度,单位,批号,有效期,入库人员,数量,日期,出库数量,库存数量 from [入库$] where 1=1" If .TextBox3.Text <> "" Then sql = sql & " and 名称 like '%" & .TextBox3.Text & "%'" End If If .TextBox4.Text <> "" Then sql = sql & " and 厂家 like '%" & .TextBox4.Text & "%'" End If If .TextBox7.Text <> "" Then sql = sql & " and 日期>=#" & .TextBox7.Text & "#" End If If .TextBox8.Text <> "" Then sql = sql & " and 日期<=#" & .TextBox8.Text & "#" End If If .ComboBox1.Text <> "" Then sql = sql & " and 入库人员='" & .ComboBox1.Text & "'" End If SqlOperation.SelectCommand = sql & " order by 日期 desc" If SqlOperation.GetRstDataBoolean(Arr) Then ListViewOperation2.DataSource = Arr Else ListViewOperation2.CommandButtonEnabled_False End If End With End Sub

' Addlistview3 过程用于向 ListView3 中添加数据 Public Sub Addlistview3() Dim sql As String, Arr As Variant With UserForm1 sql = "select ID,货号,名称,厂家,规格型号,存放温度,单位,批号,有效期,出库人员,数量,日期,备注 from [出库$] where 1=1" If .TextBox5.Text <> "" Then sql = sql & " and 名称 like '%" & .TextBox5.Text & "%'" End If If .TextBox6.Text <> "" Then sql = sql & " and 厂家 like '%" & .TextBox6.Text & "%'" End If If .TextBox10.Text <> "" Then sql = sql & " and 日期>=#" & .TextBox10.Text & "#" End If If .TextBox9.Text <> "" Then sql = sql & " and 日期<=#" & .TextBox9.Text & "#" End If If .ComboBox2.Text <> "" Then sql = sql & " and 出库人员='" & .ComboBox2.Text & "'" End If SqlOperation.SelectCommand = sql & " order by 日期 desc" If SqlOperation.GetRstDataBoolean(Arr) Then ListViewOperation3.DataSource = Arr Else ListViewOperation3.CommandButtonEnabled_False End If End With End Sub