How can this VBA function go faster??

santiperez

New Member
Joined
Jan 8, 2009
Messages
3
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jan 8, 2009
Messages
3
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
Joined
Aug 23, 2006
Messages
149
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jan 8, 2009
Messages
3
Exactly, that was why I was asking for.

Thank you very much again for your answer.
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top