How to pass range to Excel formula from VBA function

Question

I'm writing a vba function to generate moving averages for smoothing a graph. I need to use quartile ranges to adjust the smoothing formula. How do I pass the QuartileRange to the Evaluate function to return the quartile value of that range so I may use it in my function?

The function is called as follows

=MovingAverageSmoothQuartile( A1, 4, B1:b10 )

Where

• A1 is the value to smooth,
• 4 is the number of values to use and

• B1:B10 is a column of samples used to calculate the quartile value.

  Function MovingAverageSmoothQuartile(r As Range, ByVal m As Integer, QuartileRange As Range)
  ' returns a smoothed average using the 'rectangular' method
  Dim q1 As Double, q2 As Double, q3 As Double        
  q1 = Evaluate("Quartile( " + QuartileRange.Text + ", 1") ' <--- Stuck here
  

Answer 1

Like this:

q1 = WorksheetFunction.quartile(QuartileRange, 1)

Answer 2

You can use QUARTILE directly in VBA without EVALUATE.

For example, to return the first quartile from A1:A10:

Sub Calltest()
MsgBox Test([a1:a10])
End Sub

Function Test(rng1 As Range)
Test = Application.WorksheetFunction.Quartile(rng1, 1)
End Function

Answer 3

Not quite sure why you would use Evaluate here? I think you need to do this instead:

ql = WorksheetFunction.Quartile(QuartileRange, 1)

Answer 4

q1 = Evaluate("Quartile(INDIRECT(" + QuartileRange.Text + "), 1")

I am sorry, I don't know the use of Quartile function & am assuming that the QuartileRange.Text contains a range address in string form.