Need UDF help; cycling through various sheets using VBA name property

[Keith R]

I haven't been able to get the normal worksheet functions to do what I
need, so I think I'm stuck making my own.

I have 7 sheets, each with identical tables. On an eight sheet, I have
another table (same format) in which I need to generate unweighted averages
of the relative cell from each source page. I would have done this with an
array formula, but cells without values in the source tables are =N/A (and
I need to keep them that way for graphing purposes), and apparently isna
and iserror don't work in array formulas across multiple pages.

So, in cell C3 of my roll-up table, I need to cycle through the values of
cells C3 on each source worksheet, and if the value is something other than
N/A, include that value in my unweighted average.

Where I'm stuck is how to reference the other sheets in the UDF: it stops
execution on the "choose" line, without giving any errors

Public Function Consolidated(SourceCell As Range) As Variant
Dim userSheet As Worksheet
UseRow = SourceCell.Row 'I'm the only one using this, so I know I'll
only have one cell selected
UseCol = SourceCell.Column

For i = 1 To 7
Set userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16",
"Sheet17", "Sheet18", "Sheet19")
'I also tried:
'ActivePull = Choose(i, Sheet9, Sheet12, Sheet15, Sheet16, Sheet17,
Sheet18, Sheet19) ' without quotes didn't work either
'Set userSheet = ActivePull

'do something here like :
'if userSheet.Range(UseCol & UseRow).value <> NA then
' TotalValue = TotalValue + userSheet.Range(UseCol & UseRow).value
' DivCount = DivCount+1
'end if
' then divide TotalValue/DivCount to get the average

MsgBox userSheet.Range("A2").Value 'it never gets to this line
Next
End Function


Any and all help is welcome and appreciated-
Thanks,
Keith R
XL97

 

[Mark Bigelow]

Your problem is with this line:

Set userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16",
"Sheet17", "Sheet18", "Sheet19")

Change it to:

userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16",
"Sheet17", "Sheet18", "Sheet19")

The problem is that you were using the Set keyword, which would be
useful if you wanted to make userSheet an array. In this case, you just
want it to be a single variable.

Let me know if you have questions.

Mark

---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

Public Function Consolidated() As Variant
Application.Volatile
Dim userSheet As Worksheet
Dim i As Long
Dim UseRow As Long
Dim UseCol As Long
Dim TotalValue As Double
Dim DivCount As Double
Dim rng As Range
Set rng = Application.Caller
UseRow = rng.Row
UseCol = rng.Column
For i = 1 To 7
Set userSheet = Worksheets(Choose(i, "Sheet9", "Sheet12", _
"Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19"))
' do something here like :
If Not IsError(userSheet.Cells(UseRow, UseCol).Value) Then
If Not IsEmpty(userSheet.Cells(UseRow, UseCol).Value) Then
If IsNumeric(userSheet.Cells(UseRow, UseCol).Value) Then
TotalValue = TotalValue + _
userSheet.Cells(UseRow, UseCol).Value
DivCount = DivCount + 1
End If
End If
End If
Next
If DivCount > 0 Then
Consolidated = TotalValue / DivCount
Else
Consolidated = 0
End If
End Function

You want to reference the cell containing the formula, but this would cause
a circular reference error.

Since you don't but references in the argument of the cell, excel doesn't
know what cells this formula is dependent on, so you need to use
Application.Volatile to make it calculate on each recalculation.

Tested in Excel 97.

Regards,
Tom Ogilvy

 

[Keith R]

Thanks to Tom, Bob, and Mark for your responses. I had forgotten about the
application.volatile issue, so I went ahead and decided to pass the source
ranges directly. Here's the final product, in case anyone is interested
Keith R
XL97
========================================================
Public Function Consolidated(SC1 As Range, SC2 As Range, SC3 As Range, _
SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range) As Variant
'each input range will be a single cell

Dim i As Long
Dim ws As Range
Dim TotalValue As Double
Dim DivCount As Double

For i = 1 To 7
Set ws = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7))
If Not IsError(ws.Value) Then
If Not IsEmpty(ws.Value) Then
If IsNumeric(ws.Value) Then
TotalValue = TotalValue + ws.Value
DivCount = DivCount + 1
End If
End If
End If
Next
If DivCount > 0 Then
Consolidated = TotalValue / DivCount
Else
Consolidated = CVErr(xlErrNA)
End If

End Function