在Excel中使用VBA上一个或下一个工作表添加值

Option Explicit

Function SumBySheetName(InputRange As Range, Optional SheetName As Variant)

'Declaring variable

Dim SheetIn As Worksheet

'Checking whether value is assigned to optional parameter

'IF optional parameter is missing then generate sum for range in the active sheet

If IsMissing(SheetName) Then

SumBySheetName = WorksheetFunction.Sum(InputRange)

Exit Function

End If

'Looping through sheets in the worksheets collection

For Each SheetIn In Worksheets

'Checking whether specified sheet exist in the worksheets collection

If SheetName = SheetIn.Name Then

'Calculating the sum for specified range of the specified sheet

SumBySheetName = WorksheetFunction.Sum(Worksheets(SheetName).Range(InputRange.Address))

Exit Function

End If

Next

SumBySheetName = "Specified Sheet doesn't exist in the workbook"

End Function

Function SumBySheetNumber(InputRange As Range, Optional SheetIndex As Integer = 0)

'Checking whether optional parameter is missing or sheet index is assigned zero value

If SheetIndex = 0 Then

SumBySheetNumber = WorksheetFunction.Sum(InputRange)

'Checking whether sheet index is greater than number of sheets in the workbook

ElseIf SheetIndex > Sheets.Count Then

SumBySheetNumber = "Sheet Index is greater than number of sheets in the workbook"

Else

SumBySheetNumber = WorksheetFunction.Sum(Worksheets(SheetIndex).Range(InputRange.Address))

End If

End Function

Function SumByOffsetSheetNumber(InputRange As Range, Optional SheetOffset As Integer = 0)

'Error Handling

On Error GoTo Last

'Calculating sum

SumByOffsetSheetNumber = WorksheetFunction.Sum(Worksheets(InputRange.Worksheet.Index + _

SheetOffset).Range(InputRange.Address))

Exit Function

Last:

SumByOffsetSheetNumber = "Referred Sheet not exists"

End Function