using a range from multiple spreadsheets

[Engywook]

This is one of those how-do-you-do-it type questions, where I hope the answer is (relatively) simple.

I'm working on a user-defined function that takes averages between cells. The cells need to be from multiple worksheets. So to call the function within the workbook, I'd use

=myAverage(Sheet1!A1, Sheet2!A1, Sheet3!A1)

...and the function:

Public Function myAverage(cellSet As Range) 
'etc
End Function

I would think this should work, but it returns a #VALUE error.

Note that if I use

myAverage(A1:A5)

I do not get an error message.

What am I missing here? I see plenty of examples of UDF's where the range is contiguous cells, but nothing online that handles exactly what I'm trying to do.

 

[SkipVought]

Hi,

Assuming that the sheets are CONTIGUOUS, then ON THE SHEET...
[tt]
=AVERAGE(Sheet1:Sheet3!A1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Engywook]

Not quite, Skip. That does work for the native AVERAGE function, but I need to get this to work for a user defined function. The function myAverage does something rather different (in this case, taking letter grades and rendering out a grade point average).

So there's got to be something I'm doing wrong in the function declaration

Public Function myAverage(cellSet As Range) As Double

 

[SkipVought]

Well then, pilgrim, don't you think someone who might want to help you, might need something more than 'etc???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Engywook]

I suppose that's fair enough... although it doesn't do much yet, and it does do what it's supposed to if I give it contiguous cells:

Public Function compareGrades(cellSet As Range) As Double
Dim grades(13) As String
Dim scores(13) As Double
Dim gpa As Double

'set of letter grades
grades(1) = "A+"
grades(2) = "A"
grades(3) = "A-"
grades(4) = "B+"
grades(5) = "B"
grades(6) = "B-"
grades(7) = "C+"
grades(8) = "C"
grades(9) = "C-"
grades(10) = "D+"
grades(11) = "D"
grades(12) = "D-"
grades(13) = "F"

'matching set of scores
scores(1) = 4.33
scores(2) = 4
scores(3) = 3.67
scores(4) = 3.33
scores(5) = 3
scores(6) = 2.67
scores(7) = 2.33
scores(8) = 2
scores(9) = 1.67
scores(10) = 1.33
scores(11) = 1
scores(12) = 0.67
scores(13) = 0

'for now just print pi to keep debugging simple
gpa = 3.14

compareGrades = gpa

End Function

 

[SkipVought]

Use 3 arguments:
[tt]
ALL grades must be in adjacent sheets in the same cell on each sheet.
FirstSheetNbr - if your first grade is on the second sheet then enter 2
LastSheetNbr - if your last grage is on sheet 12, enter 12
CellRef - if your grades are in C3, enter "C3"
[/tt]

Public Function compareGrades(FirstSheetNbr As Integer, LastSheetNbr As Integer, CellRef As String) As Double
Dim grades(13) As String
Dim scores(13) As Double
Dim gpa As Double, iSht As Integer, i1 As Integer, iCnt As Integer

'set of letter grades
grades(1) = "A+"
grades(2) = "A"
grades(3) = "A-"
grades(4) = "B+"
grades(5) = "B"
grades(6) = "B-"
grades(7) = "C+"
grades(8) = "C"
grades(9) = "C-"
grades(10) = "D+"
grades(11) = "D"
grades(12) = "D-"
grades(13) = "F"

'matching set of scores
scores(1) = 4.33
scores(2) = 4
scores(3) = 3.67
scores(4) = 3.33
scores(5) = 3
scores(6) = 2.67
scores(7) = 2.33
scores(8) = 2
scores(9) = 1.67
scores(10) = 1.33
scores(11) = 1
scores(12) = 0.67
scores(13) = 0

iCnt = 0
For iSht = FirstSheetNbr To LastSheetNbr
    
    For i1 = 1 To 13
        If Sheets(iSht).Range(CellRef) = grades(i1) Then
            gpa = gpa + scores(i1)
            iCnt = iCnt + 1
        End If
    Next
Next

compareGrades = gpa / iCnt

End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Engywook]

Thanks, Skip! That did the trick.

 

[electricpete]

Here is something that is perhaps a little more flexible in terms of number of arguments and their location.

Function myaverage(ParamArray arglist())
    Dim tempsum As Double
    Dim counter As Long ' counts number of items in arglist
    tempsum = 0  ' Initialize counter
    ' loop through all items in arglist. 
    For counter = LBound(arglist) To UBound(arglist) - 1
        tempsum = tempsum + arglist(counter)
    Next counter
    myaverage = tempsum / counter
End Function

The call to myaverage is exactly as you identified in your original post.

The indexing of arglist is a little tricky. If you let it go to ubound you get an error, but if you stop at ubound-1 you get the right answer. Maybe some smart person here can explain why that is.

 

[electricpete]

Here is corrected version

Function myaverage(ParamArray arglist())
    Dim tempsum As Double
    Dim counter As Long ' counts number of items in arglist
    tempsum = 0  ' Initialize counter
    ' loop through all items in arglist.
    For counter = LBound(arglist) [b]To UBound(arglist) [/b]
        tempsum = tempsum + arglist(counter)
    Next counter
    myaverage = tempsum / counter
End Function

I was thrown off before because I had an extra comma in the function call from the spreadsheet:
=myAVERAGE(Sheet1!A1,Sheet2!B2,Sheet3!C3, )
So the last argument was null and caused a problem during my testing.

With the corrected code in current post and the comma deleted in the function call ...
=myAVERAGE(Sheet1!A1,Sheet2!B2,Sheet3!C3 )
...then everything works as expected.