title: how to assing the value of a range to a function in vba
body: <p>Hi I am using a function which takes two of the inputs from specific cells which are defined as ranges.
  When I run the code the function takes the name of the range but not the value in it.
  The range location of those inputs is manually assigned and later on store there the outcome of a formula.
  Below the code I have highlighted the part with the problem
  Any idea about how I can apply the range value into the function?</p>
  
  <pre><code>   Sub my_range()
  
     Dim RngWidth As Long, RngHeight As Long
  
     Dim MyAverage As Variant, MyStDev As Variant, MyMax As Variant, MyMin As
       Variant, MyCount As Variant
  
  Dim StckReturns As Range
  
  Dim MyAverage1 As Variant, MyStDev1 As Variant
  
  
  Selection.End(xlToLeft).Select
  
  Selection.End(xlUp).Select
  
  Range(Selection, Selection.End(xlToRight)).Select
  
  Range(Selection, Selection.End(xlDown)).Select
  
  RngWidth = Selection.Columns.Count
  
  RngHeight = Selection.Rows.Count
  
  ActiveCell.Offset(0, RngWidth).Range("a1").Select
  
  ActiveCell.FormulaR1C1 = "Daily Return"
  
  ActiveCell.Offset(2, 0).Range("a1").Select
  
  ActiveCell.FormulaR1C1 = "=StockReturn(R[-1]C[-1],RC[-1])"
  
  Range(ActiveCell, ActiveCell).Select
  
  Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(RngHeight - 3, 0))
  
  Set StckReturns = Range(ActiveCell, ActiveCell.Offset(RngHeight - 3, 0))
  
  MyAverage = InputBox("Where do you want the Average?")
  
  MyStDev = InputBox("Where do you want the Standard Deviation?")
  
  Range(MyAverage).Value = WorksheetFunction.Average(StckReturns.Value)
  
  Range(MyStDev).Value = WorksheetFunction.StDev(StckReturns.Value)
  
  MyAverage1 = Range(MyAverage).Value
  
  MyStDev1 = Range(MyStDev).Value
  
  ActiveCell.Offset(-2, 1).Range("a1").Select
  
  ActiveCell.FormulaR1C1 = "Scaled Return"
  
  ActiveCell.Offset(2, 0).Range("a1").Select
  
  ***ActiveCell.FormulaR1C1 = "=ScaledReturns(RC[-1],myaverage1,MyStDev1)"***
  
  Range(ActiveCell, ActiveCell).Select
  
  Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(RngHeight - 3, 0))
  
  
  End Sub
  
  Function StockReturn(IniPrice As Double, FinPrice As Double) As Double
  
  StockReturn = (FinPrice - IniPrice) / IniPrice
  
  End Function
  
  Function ScaledReturns(MyReturn As Variant, AverageReturn As Variant, StDevReturn As Variant) As Double
  
  ScaledReturns = (MyReturn - AverageReturn) / StDevReturn
  
  End Function
  </code></pre>