I've written a user-defiend function, meant to be called from a cell via "=",
have put it in an .xla file, have loaded the .xla via Tools | Add-Ins, but it
is not visible to the cell unless I include the module's name. The formula
box "sees" it but doesn't interact with it:

Doesn't Work: =AverageB(rRng,sLower,sUpper).
Works: =mdlAverageB.AverageB(rRng,sLower,sUpper).

I don't think this is a coding issue, but in the following, the declarations
haven't been included for brevity's sake:

Public Function AverageB(rRng As Range, sLower As Single, sUpper As Single)
As Single
' TPAC.J(254).CSR():20080123W1807E:20080123w1842e
For Each r In rRng
' Standard Average for future reference - is not returned by fnc
intCount = intCount + 1
sglSum = sglSum + r.Value

' Bound Average
If r.Value >= sLower And r.Value <= sUpper Then
intAvgBCount = intAvgBCount + 1
sglAvgBSum = sglAvgBSum + r.Value
End If
Next

' Standard Average for future reference - is not returned by fnc
sglAvg = sglSum / intCount 'Returns std Avg()'s value

AverageB = sglAvgBSum / intAvgBCount
End Function

Grazie,
--
Trent Argante
[DC.J(254)]

On Wed, 13 Feb 2008 05:19:01 -0800, Trent Argante
<(E-Mail Removed)> wrote:

>I've written a user-defiend function, meant to be called from a cell via "=",
>have put it in an .xla file, have loaded the .xla via Tools | Add-Ins, but it
>is not visible to the cell unless I include the module's name. The formula
>box "sees" it but doesn't interact with it:
>
> Doesn't Work: =AverageB(rRng,sLower,sUpper).
> Works: =mdlAverageB.AverageB(rRng,sLower,sUpper).
>
>I don't think this is a coding issue, but in the following, the declarations
>haven't been included for brevity's sake:
>
>Public Function AverageB(rRng As Range, sLower As Single, sUpper As Single)
>As Single
>' TPAC.J(254).CSR():20080123W1807E:20080123w1842e
> For Each r In rRng
> ' Standard Average for future reference - is not returned by fnc
> intCount = intCount + 1
> sglSum = sglSum + r.Value
>
> ' Bound Average
> If r.Value >= sLower And r.Value <= sUpper Then
> intAvgBCount = intAvgBCount + 1
> sglAvgBSum = sglAvgBSum + r.Value
> End If
> Next
>
> ' Standard Average for future reference - is not returned by fnc
> sglAvg = sglSum / intCount 'Returns std Avg()'s value
>
> AverageB = sglAvgBSum / intAvgBCount
>End Function
>
>Grazie,

I presume when you write "Tools/AddIns" that you are referring to the menu
selection in Excel.

That being the case, in my limited experience, the reason for the behaviour you
see is that there is a naming conflict. Perhaps there is another AverageB
function defined in some other open Workbook.
--ron

Along the same vein as what Ron has mentioned - and this always
catches me out - have you named the module in which the function
resides to be the name of the function? You want a distinct name for
the module, otherwise this error results.

Richard


On 13 Feb, 14:11, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Wed, 13 Feb 2008 05:19:01 -0800, Trent Argante
>
>
>
>
>
> <TrentArga...@discussions.microsoft.com> wrote:
> >I've written a user-defiend function, meant to be called from a cell via "=",
> >have put it in an .xla file, have loaded the .xla via Tools | Add-Ins, but it
> >is not visible to the cell unless I include the module's name. *The formula
> >box "sees" it but doesn't interact with it:
>
> > * Doesn't Work: =AverageB(rRng,sLower,sUpper).
> > * * * * * * Works: =mdlAverageB.AverageB(rRng,sLower,sUpper).
>
> >I don't think this is a coding issue, but in the following, the declarations
> >haven't been included for brevity's sake:
>
> >Public Function AverageB(rRng As Range, sLower As Single, sUpper As Single)
> >As Single
> >' TPAC.J(254).CSR():20080123W1807E:20080123w1842e
> > * For Each r In rRng
> > * * *' Standard Average for future reference - is not returned by fnc
> > * * *intCount = intCount + 1
> > * * *sglSum = sglSum + r.Value
>
> > * * *' Bound Average
> > * * *If r.Value >= sLower And r.Value <= sUpper Then
> > * * * * intAvgBCount = intAvgBCount + 1
> > * * * * sglAvgBSum = sglAvgBSum + r.Value
> > * * *End If
> > * Next
>
> > * ' Standard Average for future reference - is not returned by fnc
> > * sglAvg = sglSum / intCount 'Returns std Avg()'s value
>
> > * AverageB = sglAvgBSum / intAvgBCount
> >End Function
>
> >Grazie,
>
> I presume when you write "Tools/AddIns" that you are referring to the menu
> selection in Excel.
>
> That being the case, in my limited experience, the reason for the behaviour you
> see is that there is a naming conflict. *Perhaps there is another AverageB
> function defined in some other open Workbook.
> --ron- Hide quoted text -
>
> - Show quoted text -

Ron & Richard,
Thanks for your inputs. As far as I researched, I couldn't find another
function or sub named "AverageB", but in thinking that anything starting with
"Average" was reserved, I renamed my function to "AvgBound", and it worked.
Thanks, dudes!
--
Trent Argante
[DC.J(125).CSR(105)]


"Trent Argante" wrote:

> I've written a user-defiend function, meant to be called from a cell via "=",
> have put it in an .xla file, have loaded the .xla via Tools | Add-Ins, but it
> is not visible to the cell unless I include the module's name. The formula
> box "sees" it but doesn't interact with it:
>
> Doesn't Work: =AverageB(rRng,sLower,sUpper).
> Works: =mdlAverageB.AverageB(rRng,sLower,sUpper).
>
> I don't think this is a coding issue, but in the following, the declarations
> haven't been included for brevity's sake:
>
> Public Function AverageB(rRng As Range, sLower As Single, sUpper As Single)
> As Single
> ' TPAC.J(125).CSR(105):20080123W1807E:20080123w1842e
> For Each r In rRng
> ' Standard Average for future reference - is not returned by fnc
> intCount = intCount + 1
> sglSum = sglSum + r.Value
>
> ' Bound Average
> If r.Value >= sLower And r.Value <= sUpper Then
> intAvgBCount = intAvgBCount + 1
> sglAvgBSum = sglAvgBSum + r.Value
> End If
> Next
>
> ' Standard Average for future reference - is not returned by fnc
> sglAvg = sglSum / intCount 'Returns std Avg()'s value
>
> AverageB = sglAvgBSum / intAvgBCount
> End Function
>
> Grazie,
> --
> Trent Argante
> [DC.J(125).CSR(105)]

Did you have any names called AverageB--or a module named AverageB?

Trent Argante wrote:
>
> Ron & Richard,
> Thanks for your inputs. As far as I researched, I couldn't find another
> function or sub named "AverageB", but in thinking that anything starting with
> "Average" was reserved, I renamed my function to "AvgBound", and it worked.
> Thanks, dudes!
> --
> Trent Argante
> [DC.J(125).CSR(105)]
>
> "Trent Argante" wrote:
>
> > I've written a user-defiend function, meant to be called from a cell via "=",
> > have put it in an .xla file, have loaded the .xla via Tools | Add-Ins, but it
> > is not visible to the cell unless I include the module's name. The formula
> > box "sees" it but doesn't interact with it:
> >
> > Doesn't Work: =AverageB(rRng,sLower,sUpper).
> > Works: =mdlAverageB.AverageB(rRng,sLower,sUpper).
> >
> > I don't think this is a coding issue, but in the following, the declarations
> > haven't been included for brevity's sake:
> >
> > Public Function AverageB(rRng As Range, sLower As Single, sUpper As Single)
> > As Single
> > ' TPAC.J(125).CSR(105):20080123W1807E:20080123w1842e
> > For Each r In rRng
> > ' Standard Average for future reference - is not returned by fnc
> > intCount = intCount + 1
> > sglSum = sglSum + r.Value
> >
> > ' Bound Average
> > If r.Value >= sLower And r.Value <= sUpper Then
> > intAvgBCount = intAvgBCount + 1
> > sglAvgBSum = sglAvgBSum + r.Value
> > End If
> > Next
> >
> > ' Standard Average for future reference - is not returned by fnc
> > sglAvg = sglSum / intCount 'Returns std Avg()'s value
> >
> > AverageB = sglAvgBSum / intAvgBCount
> > End Function
> >
> > Grazie,
> > --
> > Trent Argante
> > [DC.J(125).CSR(105)]

--

Dave Peterson

On Wed, 13 Feb 2008 11:26:03 -0800, Trent Argante
<(E-Mail Removed)> wrote:

>Ron & Richard,
>Thanks for your inputs. As far as I researched, I couldn't find another
>function or sub named "AverageB", but in thinking that anything starting with
>"Average" was reserved, I renamed my function to "AvgBound", and it worked.
>Thanks, dudes!
>--
>Trent Argante
>[DC.J(125).CSR(105)]
>


Glad you've got it working.
--ron