User-Defined Functions


Posted by Chris on September 10, 2000 7:02 PM

I am having problems getting a user-defined function to execute properly when referencing a range in another workbook. Any suggestions???

Posted by Celia on September 12, 0100 1:36 AM

Chris
I've just looked at this again. I think what I posted only gives the simple average.
I can't follow your code. What is "Weight"? Is it the range of cells that contains the weighting for each cell in "Notional"? If so, I think the following does it :-

Option Explicit
Dim Notional As Range
Dim Weight As Range
Function WAverage(Notional, Weight)
Application.Volatile True
With Application.WorksheetFunction
WAverage = .SumProduct(Notional, Weight) / .Sum(Weight)
End With
End Function

This is the equivalent of :-
=SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5)

The function would be entered as :-
=WAverage(A1:A5,B1:B5)

Perhaps I'm misunderstanding what you need?

Celia


Posted by Chris on September 12, 0100 7:51 AM


You are correct. "Weight" is the range of cells that contains the weighting for each cell in "Notional". The caveat, however, is that "Weight" will contain text that I need to convert. Thus, my rational for using "Select Case".

The most recent code is exactly what I am looking for with the exception of creating the flexibility to reference text, which then cross-references a constant.

You have been EXTREMELY helpful. I greatly appreciate your assistance.

Posted by Chris on September 11, 0100 12:05 PM

I am receiving the #VALUE! error, when the arguments are referencing a range in a closed file. Below is the code. I'm simply trying to write a weighted average function. Any suggestions would be greatly appreciated. Thanks in advance for your time and consideration.

Option Explicit
Dim Notional()
Dim Weight()

Function WAverage(Notional, Weight)

Application.Volatile True

Dim i As Integer
Dim x As Long
Dim y As Long

Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4

x = 0
y = 0
WAverage = 0

For i = 1 To Notional.Cells.Count
x = x + Notional(i)
Select Case Weight(i)
Case Is = "a"
y = y + Notional(i) * a
Case Is = "b"
y = y + Notional(i) * b
Case Is = "c"
y = y + Notional(i) * c
Case Is = "d"
y = y + Notional(i) * d
Case Else
y = y + Notional(i) * 0
End Select

Next i

WAverage = y / x

End Function


Posted by Celia on September 12, 0100 2:27 PM

Chris

Option Explicit
Dim Notional As Range
Dim Weight As Range
Function WAverage(Notional, Weight)
Application.Volatile True
Dim i As Integer, x As Integer, y As Integer
Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4
For i = 1 To Weight.Cells.Count
Select Case Weight(i)
Case Is = "a"
x = x + a
y = y + Notional(i) * a
Case Is = "b"
x = x + b
y = y + Notional(i) * b
Case Is = "c"
x = x + c
y = y + Notional(i) * c
Case Is = "d"
x = x + d
y = y + Notional(i) * d
End Select
Next
WAverage = y / x
End Function

Celia

Posted by Celia on September 11, 0100 5:42 PM

Chris
I think the following eliminates the #VALUE error.
I have indicated where changes have been made (only in two places).

Function WAverage(Notional, Weight)

Application.Volatile True

Dim i As Integer
Dim x As Long
Dim y As Long

Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4

x = Notional.Cells.Count 'CHANGED
y = 0
WAverage = 0

For i = 1 To Notional.Cells.Count
Select Case Weight "CHANGED
Case Is = "a"
y = y + Notional(i) * a
Case Is = "b"
y = y + Notional(i) * b
Case Is = "c"
y = y + Notional(i) * c
Case Is = "d"
y = y + Notional(i) * d
Case Else
y = y + Notional(i) * 0
End Select

Next i

WAverage = y / x

End Function

Post again if it still doesn't work.
Celia




Posted by Celia on September 10, 0100 7:50 PM

Chris
If you are getting the error #VALUE, post your code so it can be checked.
If you are getting the error #NAME, the UDF is not beinng called properly. Is this the error you are getting?
Celia