UDF calculates but when switching workbooks gives #ref error

[balla506]

Hi,

I am having problems with a UDF I found. This sums values across different sheets (the ones you pick) in the workbook. The problem I am having is that when I do a full calc the function works but if i open a new workbook or worksheet I get a #ref error. I need to reference this in another worksheet and am unable to. I am a beginner in VBA and have no idea why this isn't working. Thanks for any help in advance.

Public Function SumOfSheets(RangeAddress As String, _        ParamArray SheetNames() As Variant) As Variant


Dim Total As Double
Dim N As Long
Dim R As Range
Dim WS As Worksheet
Dim FirstWS As Long
Dim LastWS As Long
On Error Resume Next
' ensure SheetNames is present
If IsError(LBound(SheetNames)) Then
    SumOfSheets = CVErr(xlErrValue)
    Exit Function
End If
' ensure RangeAddress is included
If RangeAddress = vbNullString Then
    SumOfSheets = CVErr(xlErrValue)
    Exit Function
End If
' ensure RangeAddress is a valid range
Set R = Worksheets(1).Range(RangeAddress)
If Err.Number <> 0 Then
    SumOfSheets = CVErr(xlErrRef)
    Exit Function
End If
If SheetNames(0) = ":" Then
    ' working with range of sheets. ensure we
    ' have both start and end sheets
    If IsError(SheetNames(1)) Or _
        IsError(SheetNames(2)) Then
            SumOfSheets = CVErr(xlErrRef)
    End If
    
    ' get the first sheet index
    FirstWS = Worksheets(SheetNames(1)).Index
    If Err.Number <> 0 Then
        SumOfSheets = CVErr(xlErrRef)
        Exit Function
    End If
    ' get the last sheet index
    LastWS = Worksheets(SheetNames(2)).Index
    If Err.Number <> 0 Then
        SumOfSheets = CVErr(xlErrRef)
        Exit Function
    End If
    
    ' ensure first sheet is <= last sheet
    If FirstWS > LastWS Then
        SumOfSheets = CVErr(xlErrRef)
        Exit Function
    End If
    
    'loop and sum
    For N = FirstWS To LastWS
        Set R = Worksheets(N).Range(RangeAddress)
        If Err.Number <> 0 Then
            SumOfSheets = CVErr(xlErrRef)
            Exit Function
        End If
        Total = Total + Application.Sum(R)
    Next N
    ' return the result
    SumOfSheets = Total
    Exit Function
Else
    ' we have a list of sheets. loop and sum.
    For N = LBound(SheetNames) To UBound(SheetNames)
        Total = Total + Application.Sum( _
            Worksheets(SheetNames(N)).Range(RangeAddress))
        If Err.Number <> 0 Then
            SumOfSheets = CVErr(xlErrRef)
            Exit Function
        End If
    Next N
    ' return the result
    SumOfSheets = Total
End If
End Function

 

[shg]

Try this:

Public Function SumOfSheets(sAddr As String, _
                            ParamArray avsWks() As Variant) As Variant
    ' UDF Only

    ' sum from left sheet to right sheet:
    ' =SumOfSheets("A1:A10", ":", "Sheet1", "Sheet4")

    ' sum random sheets:
    ' =SumOfSheets("A1:A10", "Sheet1", "Sheet3", "Sheet5", "Sheet7")

    Dim wkb         As Workbook
    Dim sht         As Object
    Dim r           As Range
    Dim iSht        As Long         ' sheet index
    Dim iShtL       As Long         ' index of left worksheet
    Dim iShtR       As Long         ' index of right worksheet

    Application.Volatile True
    Set wkb = Application.ThisCell.Worksheet.Parent

    On Error Resume Next

    ' ensure sAddr is a valid range
    Set r = wkb.Worksheets(1).Range(sAddr)
    If Err.Number Then
        SumOfSheets = CVErr(xlErrRef)
        Exit Function
    End If

    If avsWks(0) = ":" Then
        iShtL = wkb.Worksheets(avsWks(1)).Index
        If Err.Number Then
            SumOfSheets = CVErr(xlErrRef)
            Exit Function
        End If
        iShtR = wkb.Worksheets(avsWks(2)).Index
        If Err.Number Then
            SumOfSheets = CVErr(xlErrRef)
            Exit Function
        End If

        ' ensure first sheet is <= last sheet
        If iShtL > iShtR Then
            SumOfSheets = CVErr(xlErrRef)
            Exit Function
        End If

        'loop from left to right
        For iSht = iShtL To iShtR
            Set sht = wkb.Sheets(iSht)
            Debug.Print wkb.Name & "." & sht.Name
            If TypeOf sht Is Worksheet Then
                SumOfSheets = SumOfSheets + Application.Sum(sht.Range(sAddr).Value)
            End If
        Next iSht

    Else
        ' loop over listed sheets
        For iSht = LBound(avsWks) To UBound(avsWks)
            Set sht = wkb.Sheets(avsWks(iSht))
            
            If TypeOf sht Is Worksheet Then
                SumOfSheets = SumOfSheets + Application.Sum(sht.Range(sAddr).Value)
            Else
                SumOfSheets = CVErr(xlErrRef)
                Exit Function
            End If
        Next iSht
    End If
End Function

 

[shg]

The Debug.Print statement can be (should be) removed.

 

[balla506]

This works perfectly. Thanks so much!

 

[shg]

You're welcome, good luck. It was only lightly tested, but does (now) accommodate interspersed chart sheets.

Unsolicited comment: Needing a UDF like this would make me question the design of my workbook.