0

I am trying to create a UDF to minimize the lines of data in a calculation. Rather than include the stated calculation of specific cell references, I want to create a variable called Rent_Rate and set it equal to Base_year_rent * ((1 + Rent_escalator)^Compound_period) so that the formula in the spread sheet reads "= Rent_Rate * Units (a static cell reference)"

I've tried defining the variables and setting each variable to the reference cell that I want (on a separate sheet) to reference.

Public Function Rent_Rate(Base_year_rent As Long, Rent_escalator As Long, Compound_period As Long, Rent_Rate As Long) As Long

    Base_year_rent = SH_Assumptions.Cells("C11")
    Rent_escalator = SH_Assumptions.Cells("C12")
    Compound_period = Year(Range(ActiveCell, ActiveCell.End(xlUp)).Select) - Year(SH_Assumptions.Cells("C4"))

    Rent_Rate = Base_year_rent * ((1 + Rent_escalator) ^ Compound_period)

End Function

I expect that the output would be a specific value equal to the values entered into the cells being referenced.

BigBen
  • 46,229
  • 7
  • 24
  • 40
RT09BSBA
  • 1
  • 2
  • Functions need parameters when used. Thus, =Rent_Rate(year,escalator,compound)*Units. Also, you can't have the function itself be a parameter in the function. – mooseman Jun 06 '19 at 14:28
  • 1
    Debug >> Compile VBAProject. The `Rent_Rate` parameter can't have the same name as the function it's defined in, that's a duplicate declaration. Rename the parameter or the function. – Mathieu Guindon Jun 06 '19 at 14:59

1 Answers1

1

First off - a Function in Excel needs to have brackets, e.g. =Rent_Rate()

You can bypass that by setting up a Named Range with the name you want, which calls your UDF (e.g. a Named Range called RentRate which is =Rent_Rate())

The next issue is that you have given your UDF 3 arguments, but aren't passing anything to them. In fact, you are immediately overwriting them - just Dim the variables instead!

Public Function Rent_Rate() AS Long
    Dim Base_year_rent As Long, Rent_escalator As Long, Compound_period As Long, Rent_Rate As Long

    Base_year_rent = SH_Assumptions.Cells("C11").Value
    Rent_escalator = SH_Assumptions.Cells("C12").Value

    'What is this abomination supposed to do???
    Compound_period = Year(Range(ActiveCell, ActiveCell.End(xlUp)).Select) - Year(SH_Assumptions.Cells("C4"))

    Rent_Rate = Base_year_rent * ((1 + Rent_escalator) ^ Compound_period)

End Function

However, you still have a big problem here - why are you using ActiveCell and Select? Especially in a UDF, you should Avoid Using Select. If you are trying to use the cell that is calling the function, look up Application.Caller

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • Does the `Rent_Rate = ...` instruction assign to the function's return value or to the `Rent_Rate` parameter? That's a "duplicate declaration in current scope" compile error. – Mathieu Guindon Jun 06 '19 at 14:53
  • Thanks to both for quick commentary. I'll try to answer them in order. For background. This is for a forecasting model calculating rental income by month for 3-5 year hold period. The Compound period is SUPPOSED to identify the Date (a column header), and extract the year, then subtract the year of the acquisition date (which resides on a separate sheet "Assumptions" in order to calculate the future Gross Rent on future periods. – RT09BSBA Jun 06 '19 at 16:51
  • I'm trying to get it to the point where the Rent_Rate = the value of those cells being multiple...in short I want to be able to use this is a function to simplify the length of a the longer formula (essentially running in the background) for ease of reading for some of my coworkers) who tend to get lost when the formula gets beyond 1 row. Sorry...This is the first thing I've tried to do in VBA... – RT09BSBA Jun 06 '19 at 17:10