User Defined functions

[Basharat A. Javaid]

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.

 

[Gareth]

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]

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,SchB_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

 

[Gareth]

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

 

[Gareth]

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