宏VBA中的公式

Sub InsertSimpleFormula()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 在单元格 C1 中插入公式 =A1+B1
    ws.Range("C1").Formula = "=A1+B1"
End Sub

Sub InsertSumFormula()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 在单元格 C1 中插入公式 =SUM(A1:A10)
    ws.Range("C1").Formula = "=SUM(A1:A10)"
End Sub

Sub InsertR1C1Formula()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 在单元格 C1 中插入公式 =A1+B1，使用 R1C1 参考样式
    ws.Range("C1").FormulaR1C1 = "=R1C1+R1C2"
End Sub

Sub InsertDynamicFormula()
    Dim ws As Worksheet
    Dim rowNum As Integer
    rowNum = 5 ' 假设用户输入的行号是 5
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 在单元格 C1 中插入公式 =A5+B5
    ws.Range("C1").Formula = "=A" & rowNum & "+B" & rowNum
End Sub

Sub InsertArrayFormula()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 在单元格 C1 中插入数组公式 =A1:A10*B1:B10
    ws.Range("C1").FormulaArray = "=A1:A10*B1:B10"
End Sub

Sub UseWorksheetFunction()
    Dim ws As Worksheet
    Dim result As Double
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 使用 WorksheetFunction 对象调用 SUM 函数
    result = Application.WorksheetFunction.Sum(ws.Range("A1:A10"))
    
    ' 将结果放入单元格 C1
    ws.Range("C1").Value = result
End Sub

Sub InsertFormulaWithErrorHandling()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    On Error GoTo ErrorHandler
    
    ' 在单元格 C1 中插入公式 =A1/B1
    ws.Range("C1").Formula = "=A1/B1"
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Error inserting formula: " & Err.Description
End Sub