Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add-In Visibility
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 ' TPA:DC.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)] |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add-In Visibility
On Wed, 13 Feb 2008 05:19:01 -0800, 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 ' TPA:DC.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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add-In Visibility
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 wrote: On Wed, 13 Feb 2008 05:19:01 -0800, 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 ' TPA:DC.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 - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add-In Visibility
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 ' TPA:DC.J(125).CSR(105):20080123W1807E:20080123w184 2e 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)] |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add-In Visibility
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 ' TPA:DC.J(125).CSR(105):20080123W1807E:20080123w184 2e 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add-In Visibility
On Wed, 13 Feb 2008 11:26:03 -0800, 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)] Glad you've got it working. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox text visibility | Excel Discussion (Misc queries) | |||
Custom Toolbar Visibility | Excel Discussion (Misc queries) | |||
Toggling Visibility of items with VBA | Excel Worksheet Functions | |||
Visibility of active cell. | Excel Discussion (Misc queries) | |||
Is it possible to change visibility of Trendlines on a Scatter Gra | Charts and Charting in Excel |