EXCEL VB简单使用

逃离我推掉我的手 2021-06-24 14:37 493阅读 0赞

Excel启用vb编辑，要在选项中自定义功能区把开发工具加上。  
上次简单使用vb处理一些excel数据，现学了一下。把代码存着，以后备用。

' 定义按钮点击事件
    Private Sub CommandButton1_Click()
        ' 选择当前工作簿1
        Worksheets(1).Select
        ' 如果处理多数据时，就false掉excel的实时更新
        Application.ScreenUpdating = False
    
        ' 定义变量int型
        Dim line As Integer
        Dim col As Integer
        ' 赋值变量
        line = 1
        col = 2
        Dim high As Integer
        ' 获取表格最大行数
        'high = Application.WorksheetFunction.CountA(Columns(1).EntireColumn)
    
        ' 循环遍历从1到1000
        For i = 1 To 1000  'Step 3
    
            ' 定义字符串
            Dim str As String
            ' 定义数组
            Dim arr1() As String
            'Dim arr2() As String
            Dim arrLength1 As Integer
            'Dim arrLength2 As Integer
    
            ' 获取表格值，并对表格赋值
            'Cells(line, 2).Value = Cells(i, 1)
    
            str = Cells(i, 1)
            ' if then else endif判断语句
            If str = "" Then
                line = line + 1
                col = 2
            Else
                ' 划分字符串
                arr1() = Split(str, " ")
                ' 获取字符串长度
                arrLength1 = UBound(arr1)
                For j = 0 To 1 'arrLength1
                    ' 表格赋值
                    Cells(line, col).Value = arr1(j)
                        col = col + 1
                ' For循环结束标志
                Next
                    Cells(line, col).Value = "v"
                   col = col + 1
                    Cells(line, col).Value = "-"
                   col = col + 1
            End If
            'arr1() = Split(str, " ")
            'arrLength1 = UBound(arr1)
            'If 0 = i Mod 2 Then
             ' For j = 0 To 1 'arrLength1
             ' Cells(line, col).Value = arr1(j)
             ' col = col + 1
             ' Next
            'End If
    
            'str = Cells(i + 2, 1)
            'arr2() = Split(str, " ")
            'arrLength2 = UBound(arr2)
            'If 0 = i Mod 2 Then
            ' For m = 0 To arrLength2
            ' Cells(line, 2 + arrLength1 + 1 + m + 1).Value = arr2(m)
            ' Next
            'End If
    
            'Cells(line, 2 + arrLength1 + 1 + arrLength2 + 1 + 1).Value = Cells(i + 3, 1)
            'line = line + 1
    
        ' For循环结束标志 
        Next
        ' 当所有数据处理完后，使excel刷新表格
        Application.ScreenUpdating = True
    
    ' 函数结束 
    End Sub