UDF programming... PLEASE HELP!!!

[anna]

I am trying to write my first UDF and am hitting a snag. I need to pass a
worksheet name as one of the arguments (referenced in a cell). Function keeps
giving me zeros! I have multiple worksheets with result data and in a summary
sheet I want to use my UDF to "gather" this info in a clear concise manner.
The worksheet function I had was waaaayyy to big, but worked. That's why I
was trying the UDF approach. Here's the UDF:

Function gather(cond As String, HI As Integer, mode As Integer) As Double

'-----------------------------------------------------------------
' Purpose: Collect data from multiple results sheets
'
'
' Arguments:
'
' cond ... Engine condition
'
' HI ... Harmonic Index
'
' mode ... mode number
'
' Assumptions/Restrictions:
'
' 1. Assumes the results are stored in separate worksheets named
' as the conditions.
'
' 2. Named reference NB = number of blades in 360 deg ring.
'
' Notes:
'
' Revision History:
'
' Date By Description
'
'-----------------------------------------------------------------
Dim nrow As Integer

If HI = 0 Or HI = NB Then

nrow = Application.WorksheetFunction.Match(1,
(Worksheets(cond).Range("B2:B200") = HI) *
(Worksheets(cond).Range("A2:A200")= mode), 0)

gather =
Application.WorksheetFunction.Index(Worksheets(cond).Range("C2:C200"), nrow,0)

ElseIf HI > 0 And HI < NB Then

nrow =
Application.WorksheetFunction.Match(1,(Worksheets(cond).Range("B2:B200") =
HI) * (Worksheets(cond).Range("A2:A200")= (mode * 2)), 0)

gather =
Application.WorksheetFunction.Index(Worksheets(cond).Range("C2:C200"), nrow,
0)

End If

End Function

Any suggestions??
Thanks in advance,
Anna

 

[Randy Harmelink]

' 2. Named reference NB = number of blades in 360 deg ring.

If HI = 0 Or HI = NB Then

If NB is a named reference, I think that latter statement needs to be
something like:

If HI = 0 Or HI = Range("NB")
....or:

If HI = 0 Or HI = Worksheets(cond).Range("NB")

....depending on how you need to reference it. Why not just pass it as
a parameter to the function as well, rather than assume it's going to
be available?

 

[Bob Phillips]

Haven't tested it, don't know what NB is, but try this

Function gather(cond As String, HI As Integer, mode As Integer) As Double
'-----------------------------------------------------------------
' Purpose: Collect data from multiple results sheets
'
'
' Arguments:
'
' cond ... Engine condition
'
' HI ... Harmonic Index
'
' mode ... mode number
'
' Assumptions/Restrictions:
'
' 1. Assumes the results are stored in separate worksheets named
' as the conditions.
'
' 2. Named reference NB = number of blades in 360 deg ring.
'
' Notes:
'
' Revision History:
'
' Date By Description
'
'-----------------------------------------------------------------
Dim nrow As Integer

If HI = 0 Or HI = NB Then
nrow = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200"), nrow, 0)

ElseIf HI > 0 And HI < NB Then

nrow = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200"), nrow, 0)

End If

End Function

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[anna]

Thank you!

Anna

If NB is a named reference, I think that latter statement needs to be
something like:


....depending on how you need to reference it. Why not just pass it as
a parameter to the function as well, rather than assume it's going to
be available?

 

[anna]

Thanks so much!

Anna

Haven't tested it, don't know what NB is, but try this

Function gather(cond As String, HI As Integer, mode As Integer) As Double
'-----------------------------------------------------------------
' Purpose: Collect data from multiple results sheets
'
'
' Arguments:
'
' cond ... Engine condition
'
' HI ... Harmonic Index
'
' mode ... mode number
'
' Assumptions/Restrictions:
'
' 1. Assumes the results are stored in separate worksheets named
' as the conditions.
'
' 2. Named reference NB = number of blades in 360 deg ring.
'
' Notes:
'
' Revision History:
'
' Date By Description
'
'-----------------------------------------------------------------
Dim nrow As Integer

If HI = 0 Or HI = NB Then
nrow = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200"), nrow, 0)

ElseIf HI > 0 And HI < NB Then

nrow = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200"), nrow, 0)

End If

End Function

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)