Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a named range into a user defined function
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,Roundi ng_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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a named range into a user defined function
You would have to write your function to do that - there isn't anything
built in that will do it for you. -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... 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,Roundi ng_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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a named range into a user defined function
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... "Tom Ogilvy" wrote: You would have to write your function to do that - there isn't anything built in that will do it for you. -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... 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,Roundi ng_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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing an array in a user defined function | Excel Programming | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
passing named range to a UDF user defined function | Excel Programming | |||
passing a range to a user defined function using a form | Excel Programming | |||
Passing an Array of User-Defined Type to an Argument of a Function | Excel Programming |