title: How to use the Resize function with the offset function in VBA code for a User Defined Function in Excel
body: <p>I have 10 years worth of monthly returns of an asset. I am trying to create a function that can give me the annualised returns of this series of monthly return data. I would like to use the Offset function to go to the last monthly returns date, select an array of periods that I choose (3 months back, 6 months back, 12 months back, 24 months back etc) so that I can get the annualised return of a series of returns from the end date back to a specific period.</p>
  
  <p>The goal is to calculate the monthly average monthly returns of the last 3 months, 6 months, 12 months etc.</p>
  
  <p>However, the parameters for the Offset function in VBA are different to those in normal Excel functions. I think I need to use the .Resize function to resize my Offset function.</p>
  
  <p>Here is how the function works in Excel</p>
  
  <pre><code>=GEOMEAN(1+OFFSET($W$4,MATCH(LARGE($V$4:$V$10485,1),$V$4:$V$10485,0)-1,0,-$AJ17,1))-1
  </code></pre>
  
  <p>Rewritten so that you can understand my variables</p>
  
  <pre><code>=GEOMEAN(1+OFFSET(ref,MATCH(LARGE(Dates,1),Dates,0)-1,0,-period,1))-1
  </code></pre>
  
  <p>Here is the code that I have written so far...</p>
  
  <pre><code>Function Returns(ref As Range, Dates As Range, Period As Integer) As Variant
  
      With Application.WorksheetFunction
  
          Returns = .Geomean(1 + ref.Resize(-Period, 1).Offset(.Match(.Large(Dates, 1), Dates, 0) - 1, 0))-1
  
  
  
      End With
  
  
  
  End Function
  </code></pre>
  
  <p>Please, can someone help?</p>