How can this VBA function go faster??

[santiperez]

Hi,
I've been working with this formula and, although it gives the correct result, it is very slow.
When I use it many times in the same workbook it losses functionality.

The formula calculates monthly averages.
Date and numbers must be arranged in columns (the date and the correspondient number must be in the same row) .
It compares dates in the column with an specified month and makes the average of the correspondient data.

Is there any way of reducing steps or changing something so as to make it go faster?
I would appreciate any comment.
Thanks.

Function PMES(R As Range, F As Range, D As Date) As Double
Dim n As Integer
Dim b As Integer
Dim sump As Double
Dim mes As Date
 
mes = DateSerial(Year(D), Month(D), 1)
n = 0
sump = 0
b = R.Column
 
For Each x In F
    If DateSerial(Year(x), Month(x), 1) = mes Then
    sump = sump + Cells(x.Row, b)
    n = n + 1
    End If
Next x
 
PMES = sump / n
 
End Function

 

[Jonmo1]

Welcome to the board...

for startes, it will be faster if you do it with built in functions, rather than vba code.

Try something like

=AVERAGE(IF(TEXT(A1:A100,"mmmyyyy")="Jan2009",B1:B100))

Where column A is your range of dates
Jan2009 is month and year to average
Column B is values to average based on date

IPORTANT NOTES
Ranges cannot be entire column refs like A:A, must use Row#s like A1:A100 (except in xl2007)
Ranges must be same size
This is an array formula entered with CTRL + SHIFT + ENTER
After entering formula, highlight cell and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, formula will be enclosed in {brackets}

Hope that helps..

 

[santiperez]

Thank you very much for the answer.
It works perfectly well and much more faster.
And additional question.. is it possible to use another function instead of "TEXT".
I tried with EOMONTH function but it gave me an error.
Is there a reason why this function isn't working or am i making a mistake?

Thank you very much. It was of great help.

 

[KWMSeattle]

I believe EOMONTH is part of the analysis toolpak. You can enable this by going to Tools --> Add-ins... and checking the box for Analysis Toolpak. Restart Excel and try that formula again.

 

[Jonmo1]

Are you asking so that you can put a cell reference in for your date, rather than hard coding Jan2009 in the formula?

If so, try like this

=AVERAGE(IF(TEXT(A1:A100,"mmmyyyy")=Text(C1,"mmmyyyy"),B1:B100))
Where C1 holds the date you would have used as the 3rd variable you would have used in your VBA function.

 

[santiperez]

Exactly, that was why I was asking for.

Thank you very much again for your answer.