VB 代码和 Excel 的单独文件

使用VB代码和Excel文件进行数据处理

vba
Public Sub ReadExcelData()
    Dim excelApp As Excel.Application
    Dim workbook As Excel.Workbook
    Dim worksheet As Excel.Worksheet
    Dim rangeData As Excel.Range
    Dim row As Long
    Dim column As Long
    
    ' 创建Excel对象
    Set excelApp = New Excel.Application
    
    ' 打开Excel文件
    Set workbook = excelApp.Workbooks.Open("C:\data.xlsx")
    
    ' 选择要读取的工作表
    Set worksheet = workbook.Sheets("Sheet1")
    
    ' 获取数据范围
    Set rangeData = worksheet.UsedRange
    
    ' 逐行逐列读取数据并输出到控制台
    For row = 1 To rangeData.Rows.Count
        For column = 1 To rangeData.Columns.Count
            Debug.Print rangeData.Cells(row, column).Value
        Next column
    Next row
    
    ' 关闭Excel文件
    workbook.Close
    
    ' 退出Excel应用程序
    excelApp.Quit
End Sub

vba
Public Sub CalculateSum()
    Dim excelApp As Excel.Application
    Dim workbook As Excel.Workbook
    Dim worksheet As Excel.Worksheet
    Dim rangeData As Excel.Range
    Dim column As String
    Dim sumResult As Double
    
    ' 创建Excel对象
    Set excelApp = New Excel.Application
    
    ' 打开Excel文件
    Set workbook = excelApp.Workbooks.Open("C:\data.xlsx")
    
    ' 选择要处理的工作表
    Set worksheet = workbook.Sheets("Sheet1")
    
    ' 获取数据范围
    Set rangeData = worksheet.UsedRange
    
    ' 设置要计算总和的列
    column = "A"
    
    ' 计算总和
    sumResult = Application.WorksheetFunction.Sum(rangeData.Columns(column))
    
    ' 将结果输出到单元格
    worksheet.Cells(1, rangeData.Columns.Count + 2).Value = sumResult
    
    ' 关闭Excel文件
    workbook.Close SaveChanges:=True
    
    ' 退出Excel应用程序
    excelApp.Quit
End Sub

vba
Public Function Average(ByVal num1 As Double, ByVal num2 As Double) As Double
    Average = (num1 + num2) / 2
End Function