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

Should you not use & to concatenate the strings rahter than + ? — Dan
– Dan, Commented Oct 5, 2012 at 7:33
Thank you to all who answered. When I get back to work I'll implement your suggestions. — Martlark
– Martlark, Commented Oct 5, 2012 at 9:20

Dan · Accepted Answer · 2012-10-05 07:44:01Z

2

Like this:

q1 = WorksheetFunction.quartile(QuartileRange, 1)

edited Oct 5, 2012 at 7:44

answered Oct 5, 2012 at 7:38

Dan

45.8k20 gold badges98 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2012-10-05 07:39:49Z

1

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

answered Oct 5, 2012 at 7:39

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Comments

markblandford · Accepted Answer · 2012-10-05 07:41:52Z

1

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

ql = WorksheetFunction.Quartile(QuartileRange, 1)

answered Oct 5, 2012 at 7:41

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Comments

shahkalpesh · Accepted Answer · 2012-10-05 07:26:06Z

0

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.

answered Oct 5, 2012 at 7:26

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Collectives™ on Stack Overflow

How to pass range to Excel formula from VBA function

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related