Passing a named range into a user defined function

[Simon Shaw]

Hi,

i have written my first user defined function as follows:

Function Budget_Amount(Spread_Method As String, _
Annual_Amount As Currency, _
Budget_Month As Integer, _
Manual_Percent As Currency, _
Manual_Amount As Currency, _
Manual_Override As String, _
Rounding_Month As Integer, _
Rounding_Factor As Integer)

Dim Source_Cell As Range

If Annual_Amount = 0 Then
Budget_Amount = 0
Else
If Rounding_Month = Budget_Month Then
Dim x As Integer
Set Source_Cell = Application.Caller
Budget_Amount = Annual_Amount
For x = 1 To 12
If x <> Rounding_Month Then
Budget_Amount = Budget_Amount - _
Source_Cell.Cells(1, x + 1 - Rounding_Month).Value
End If
Next x
Else
Select Case UCase(Spread_Method)
Case "MP"
Budget_Amount = Annual_Amount * Manual_Percent
Case "MA"
Budget_Amount = Manual_Amount
Case ""
Budget_Amount = "Spread Required"
Case Else
If Left(UCase(Manual_Override), 1) = "Y" _
And Manual_Amount <> 0 Then
Budget_Amount = Manual_Amount
Else
With WorksheetFunction
Budget_Amount = Annual_Amount * _
.Index(Range("Spread_Percent_" & Budget_Month), _
.Match(Spread_Method, _
Range("Spread_IDs"), 0))
End With
End If
End Select
Budget_Amount = WorksheetFunction.Round(Budget_Amount,
Rounding_Factor)
End If
End If

End Function
-----------------------------------
the function works when I enter it into a cell as follows:

=budget_amount($G13,$H13,I$4,AS13,BH13,BG13,Rounding_Month,Rounding_factor)

the named ranges Rounding_Month and Rounding_factor are single cell named
ranges. However, I want to be able to use named ranges for all other
variables being passed to the function, i.e. $G13, $H13, etc... where I would
replace $G13 with the named range Selected_Spread_Method which = "B:B" and
$H13 => Annual_Amount = "C:C"

If I use regular excel functions, I can reference a named range like
Annual_Amount, and it knows to use the amount from the same row as the
formula. How can I get my function to act this way?

things I have tried:
- changing the variable types from String to Range
- then I tried finding the intersection of the range and the caller cell.

Ideas?

thanks

Simon

 

[Tom Ogilvy]

You would have to write your function to do that - there isn't anything
built in that will do it for you.

 

[Simon Shaw]

I wrote the following code to achieve my goal:

Function Budget_Amount(rngSpread_Method As Range, _
rngAnnual_Amount As Range, _
Budget_Month As Integer, _
rngManual_Percent As Range, _
rngManual_Amount As Range, _
rngManual_Override As Range, _
Rounding_Month As Integer, _
Rounding_Factor As Integer)

Dim Spread_Method As String
Dim Annual_Amount As Currency
Dim Manual_Percent As Currency
Dim Manual_Amount As Currency
Dim Manual_Override As String
Dim Source_Cell As Range

Set Source_Cell = Application.Caller

Spread_Method = rngSpread_Method _
.Cells(Source_Cell.Row - rngSpread_Method.Row + 1, 1).Value
Annual_Amount = rngAnnual_Amount _
.Cells(Source_Cell.Row - rngAnnual_Amount.Row + 1, 1).Value
Manual_Percent = rngManual_Percent _
.Cells(Source_Cell.Row - rngManual_Percent.Row + 1, 1).Value
Manual_Amount = rngManual_Amount _
.Cells(Source_Cell.Row - rngManual_Amount.Row + 1, 1).Value
Manual_Override = rngManual_Override _
.Cells(Source_Cell.Row - rngManual_Override.Row + 1, 1).Value

'remaining code stayed the same...