VBA 代码中的 Excel 公式

Excel公式的应用及案例代码

vba
Sub SumExample()
    Dim rng As Range
    Dim sumResult As Double
    
    Set rng = Sheet1.Range("A1:A10") '假设需要计算A1到A10单元格的数值之和
    sumResult = WorksheetFunction.Sum(rng)
    
    MsgBox "数值之和为：" & sumResult
End Sub

vba
Sub IfExample()
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Sheet1.Range("A1:A10") '假设需要判断A1到A10单元格的数值是否大于10
    
    For Each cell In rng
        If cell.Value > 10 Then
            cell.Offset(0, 1).Value = "大于10" '如果数值大于10，将在相邻单元格写入"大于10"
        Else
            cell.Offset(0, 1).Value = "小于等于10" '如果数值小于等于10，将在相邻单元格写入"小于等于10"
        End If
    Next cell
End Sub

vba
Sub VlookupExample()
    Dim lookupValue As Variant
    Dim lookupRange As Range
    Dim result As Variant
    
    lookupValue = Sheet1.Range("A1").Value '假设需要查找的值为A1单元格的数值
    Set lookupRange = Sheet2.Range("A1:B10") '假设查找范围为Sheet2中的A1到B10区域
    
    result = WorksheetFunction.VLookup(lookupValue, lookupRange, 2, False)
    
    MsgBox "查找结果为：" & result
End Sub