Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function returns #VALUE! when working on another book
I create a function called BassDiff1()
I put it in General/Standard module:VBAProject\Modules\Module1 I an trying to use it in a worsheet formula: example: =BassDiff1(L37) It works fine except when I start working on another workbook and come back, it displays #VALUE! this is the function: Any help greatly appreciated thanks Option Explicit Public NDataPoints As Integer Dim Vara, Varb, Varc As Long Public InputDataCell1, SummaryOutput, YVar As Range Function BassDiff1(num) 'VARIABLES Dim m1 As Variant Dim p1 As Variant Dim q1 As Variant NDataPoints = Range("NDataPoints").Value Set YVar = Range(Range("YVarCell1"), Range("YVarCell1").Offset(NDataPoints - 1, 0)) Vara = Range("Vara").Value Varb = Range("Varb").Value Varc = Range("Varc").Value m1 = Application.WorksheetFunction.Sum(YVar) p1 = Varc / m1 q1 = p1 + Vara 'FUNCTION BassDiff1 = p1 * (m1 - num) + (q1 * (num / m1) * (m1 - num)) End Function -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function returns #VALUE! when working on another book
Here are some suggestions:
Dim Vara, Varb, Varc As Long defines Vara and Varb as variant not long, which is probably not what you meant It's not a good idea to reference Defined Names or ranges inside a UDF when they have not been passed in as parameters: you may get unexpected results because Excel's calculation engine does not expect this. I suggest you make all the ranges the UDF references into parameters for the UDF. I don't know why you are declaring some of the variables as Public, some as Module scope and others inside the function, but I suggest you move all your declarations to inside the function. Using Application.WorksheetFunction.SUM inside a UDF will sometimes return #Value the first time its recalculated: try pressing Ctrl/Alt/F9. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "caroline" wrote in message ... I create a function called BassDiff1() I put it in General/Standard module:VBAProject\Modules\Module1 I an trying to use it in a worsheet formula: example: =BassDiff1(L37) It works fine except when I start working on another workbook and come back, it displays #VALUE! this is the function: Any help greatly appreciated thanks Option Explicit Public NDataPoints As Integer Dim Vara, Varb, Varc As Long Public InputDataCell1, SummaryOutput, YVar As Range Function BassDiff1(num) 'VARIABLES Dim m1 As Variant Dim p1 As Variant Dim q1 As Variant NDataPoints = Range("NDataPoints").Value Set YVar = Range(Range("YVarCell1"), Range("YVarCell1").Offset(NDataPoints - 1, 0)) Vara = Range("Vara").Value Varb = Range("Varb").Value Varc = Range("Varc").Value m1 = Application.WorksheetFunction.Sum(YVar) p1 = Varc / m1 q1 = p1 + Vara 'FUNCTION BassDiff1 = p1 * (m1 - num) + (q1 * (num / m1) * (m1 - num)) End Function -- caroline |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function returns #VALUE! when working on another book
yuor function explicitly looks up named ranges. Do these exist on the active
sheet of the other workbooks? this line is worng: Dim Vara, Varb, Varc As Long should be Dim Vara As Long Dim Varb As Long Dim Varc As Long or Dim Vara As Long, Varb As Long, Varc As Long Also, your function doesn't use Varb...is that an oversight? What is the purpose of this function? "caroline" wrote: I create a function called BassDiff1() I put it in General/Standard module:VBAProject\Modules\Module1 I an trying to use it in a worsheet formula: example: =BassDiff1(L37) It works fine except when I start working on another workbook and come back, it displays #VALUE! this is the function: Any help greatly appreciated thanks Option Explicit Public NDataPoints As Integer Dim Vara, Varb, Varc As Long Public InputDataCell1, SummaryOutput, YVar As Range Function BassDiff1(num) 'VARIABLES Dim m1 As Variant Dim p1 As Variant Dim q1 As Variant NDataPoints = Range("NDataPoints").Value Set YVar = Range(Range("YVarCell1"), Range("YVarCell1").Offset(NDataPoints - 1, 0)) Vara = Range("Vara").Value Varb = Range("Varb").Value Varc = Range("Varc").Value m1 = Application.WorksheetFunction.Sum(YVar) p1 = Varc / m1 q1 = p1 + Vara 'FUNCTION BassDiff1 = p1 * (m1 - num) + (q1 * (num / m1) * (m1 - num)) End Function -- caroline |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function returns function statement rather than result | Excel Worksheet Functions | |||
Work book with working days only | Excel Programming | |||
Newly created Get Function is not working when I copied the syntax from a working function | Excel Programming | |||
Paste into new Excel book not working | Excel Programming | |||
Array not working correctly Returns FALSE on second part | Excel Discussion (Misc queries) |