Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined functions
I created an xla file with a lot of user defined functions and went though
the AddIn process. I see all of my functions under the Paste functions - User defined functions' list. But all of the cells in my workbook using the various functions have the #Name? error. -- Basharat Javaid. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined functions
Firstly, check the workbook in which your using your functions has a
reference to the AddIn containing them. If it has, I suggest you post an example of one of your functions and a cell formula using it. HTH, Gareth Basharat A. Javaid wrote: I created an xla file with a lot of user defined functions and went though the AddIn process. I see all of my functions under the Paste functions - User defined functions' list. But all of the cells in my workbook using the various functions have the #Name? error. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined functions
Yes I did - without that I would be able to see my functions.
Here is a simplest of the formulas: =ReturnOnAssets (EOY_Assets_LY,LY_Contb,Distributions,Expenses,Sch B_S412Assets) and here is the function Function ReturnOnAssets(BOYAssets, Contrib, Distrib, Expense, EOYAssets) Gain = (EOYAssets - BoYAssets) + Distrib + Expense - Contrib Devisor = (BOYAssets + EOYAssets - Gain) If Devisor = 0 Then ReturnOnAssets = 0 Else ReturnOnAssets = Gain / (Devisor/2) End If End Function ---------------------------------------- Basharat. "Gareth" wrote in message ... Firstly, check the workbook in which your using your functions has a reference to the AddIn containing them. If it has, I suggest you post an example of one of your functions and a cell formula using it. HTH, Gareth Basharat A. Javaid wrote: I created an xla file with a lot of user defined functions and went though the AddIn process. I see all of my functions under the Paste functions - User defined functions' list. But all of the cells in my workbook using the various functions have the #Name? error. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined functions
Well it works ok for me. In fact I couldn't break it. I did however,
have to declare the variables before it ran (since I always have Option Explicit set). See below. I've modified a second version that should report an error number, depending on where it's failing. You could try that and report back. HTH \Gareth Function ReturnOnAssets(BOYAssets As Long, _ Contrib As Long, _ Distrib As Long, _ Expense As Long, _ EOYAssets As Long) As Variant Dim Gain As Long Dim Devisor As Long Dim myErrNo As Byte Gain = (EOYAssets - BOYAssets) + Distrib + Expense - Contrib Devisor = (BOYAssets + EOYAssets - Gain) If Devisor = 0 Then ReturnOnAssets = 0 Else ReturnOnAssets = Gain / (Devisor / 2) End If If Err.Number = 0 Then Exit Function ReturnOnAssets = "#Error Occurred#" End Function Function ReturnOnAssetsTemp(BOYAssets As Long, _ Contrib As Long, _ Distrib As Long, _ Expense As Long, _ EOYAssets As Long) As Long Dim Gain As Long Dim Devisor As Long Dim myErrNo As Byte On Error GoTo ErrorHandler: myErrNo = 1 Gain = (EOYAssets - BOYAssets) + Distrib + Expense - Contrib myErrNo = 2 Devisor = (BOYAssets + EOYAssets - Gain) myErrNo = 3 If Devisor = 0 Then ReturnOnAssetsTemp = 0 myErrNo = 4 Else ReturnOnAssetsTemp = Gain / (Devisor / 2) myErrNo = 5 End If Exit Function ErrorHandler: ReturnOnAssetsTemp = "Err" & myErrNo End Function Basharat A. Javaid wrote: Yes I did - without that I would be able to see my functions. Here is a simplest of the formulas: =ReturnOnAssets (EOY_Assets_LY,LY_Contb,Distributions,Expenses,Sch B_S412Assets) and here is the function Function ReturnOnAssets(BOYAssets, Contrib, Distrib, Expense, EOYAssets) Gain = (EOYAssets - BoYAssets) + Distrib + Expense - Contrib Devisor = (BOYAssets + EOYAssets - Gain) If Devisor = 0 Then ReturnOnAssets = 0 Else ReturnOnAssets = Gain / (Devisor/2) End If End Function ---------------------------------------- Basharat. "Gareth" wrote in message ... Firstly, check the workbook in which your using your functions has a reference to the AddIn containing them. If it has, I suggest you post an example of one of your functions and a cell formula using it. HTH, Gareth Basharat A. Javaid wrote: I created an xla file with a lot of user defined functions and went though the AddIn process. I see all of my functions under the Paste functions - User defined functions' list. But all of the cells in my workbook using the various functions have the #Name? error. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined functions
Correction. Error reporting formula should read as follows (I changed
function to return variant type, rather than Long. Function ReturnOnAssetsTemp(BOYAssets As Long, _ Contrib As Long, _ Distrib As Long, _ Expense As Long, _ EOYAssets As Long) As variant Dim Gain As Long Dim Devisor As Long Dim myErrNo As Byte On Error GoTo ErrorHandler: myErrNo = 1 Gain = (EOYAssets - BOYAssets) + Distrib + Expense - Contrib myErrNo = 2 Devisor = (BOYAssets + EOYAssets - Gain) myErrNo = 3 If Devisor = 0 Then ReturnOnAssetsTemp = 0 myErrNo = 4 Else ReturnOnAssetsTemp = Gain / (Devisor / 2) myErrNo = 5 End If Exit Function ErrorHandler: ReturnOnAssetsTemp = "Err" & myErrNo End Function Gareth wrote: Well it works ok for me. In fact I couldn't break it. I did however, have to declare the variables before it ran (since I always have Option Explicit set). See below. I've modified a second version that should report an error number, depending on where it's failing. You could try that and report back. HTH \Gareth Function ReturnOnAssets(BOYAssets As Long, _ Contrib As Long, _ Distrib As Long, _ Expense As Long, _ EOYAssets As Long) As Variant Dim Gain As Long Dim Devisor As Long Dim myErrNo As Byte Gain = (EOYAssets - BOYAssets) + Distrib + Expense - Contrib Devisor = (BOYAssets + EOYAssets - Gain) If Devisor = 0 Then ReturnOnAssets = 0 Else ReturnOnAssets = Gain / (Devisor / 2) End If If Err.Number = 0 Then Exit Function ReturnOnAssets = "#Error Occurred#" End Function Function ReturnOnAssetsTemp(BOYAssets As Long, _ Contrib As Long, _ Distrib As Long, _ Expense As Long, _ EOYAssets As Long) As Long Dim Gain As Long Dim Devisor As Long Dim myErrNo As Byte On Error GoTo ErrorHandler: myErrNo = 1 Gain = (EOYAssets - BOYAssets) + Distrib + Expense - Contrib myErrNo = 2 Devisor = (BOYAssets + EOYAssets - Gain) myErrNo = 3 If Devisor = 0 Then ReturnOnAssetsTemp = 0 myErrNo = 4 Else ReturnOnAssetsTemp = Gain / (Devisor / 2) myErrNo = 5 End If Exit Function ErrorHandler: ReturnOnAssetsTemp = "Err" & myErrNo End Function Basharat A. Javaid wrote: Yes I did - without that I would be able to see my functions. Here is a simplest of the formulas: =ReturnOnAssets (EOY_Assets_LY,LY_Contb,Distributions,Expenses,Sch B_S412Assets) and here is the function Function ReturnOnAssets(BOYAssets, Contrib, Distrib, Expense, EOYAssets) Gain = (EOYAssets - BoYAssets) + Distrib + Expense - Contrib Devisor = (BOYAssets + EOYAssets - Gain) If Devisor = 0 Then ReturnOnAssets = 0 Else ReturnOnAssets = Gain / (Devisor/2) End If End Function ---------------------------------------- Basharat. "Gareth" wrote in message ... Firstly, check the workbook in which your using your functions has a reference to the AddIn containing them. If it has, I suggest you post an example of one of your functions and a cell formula using it. HTH, Gareth Basharat A. Javaid wrote: I created an xla file with a lot of user defined functions and went though the AddIn process. I see all of my functions under the Paste functions - User defined functions' list. But all of the cells in my workbook using the various functions have the #Name? error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User defined functions without using VBA. | New Users to Excel | |||
User defined functions without using VBA. | Excel Discussion (Misc queries) | |||
User defined functions without using VBA. | Excel Worksheet Functions | |||
User Defined Functions - Help Text - Make it Easy for the User | Excel Programming | |||
excel functions and User defined functions | Excel Programming |