How can this VBA function go faster??

santiperez

New Member
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.

Code:
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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Jonmo1

MrExcel MVP
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

New Member
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

Board Regular
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

MrExcel MVP
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

New Member
Exactly, that was why I was asking for.

Replies
5
Views
284
Replies
5
Views
98
Replies
2
Views
195
Replies
4
Views
113
Replies
1
Views
96

1,136,434
Messages
5,675,834
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back