Speed up simple user defined functions

Question

User defined functions writen in Excel VBA seem to run much slower than functions simply written in the worksheet cells. Is there a way to run them faster? As an example I have a very simple user defined function:

Function myweekday(mydate As Double)
    myweekday = Weekday(mydate)
End Function

Basically it does the same thing as the built in =weekday() function. However running this on 50,000 cells takes about 5 seconds to calculate, whereas simply using the built in function takes a fraction of a second.

What can I do to make user defined functions like this run faster?

Why would you create a UDF that offers less functionality than either the worksheet WEEKDAY function or the VBA Weekday function? — user4039065
@Jeeped My only guess is that Alex eventually wants to make the UDF more complex, but is seeing if it's even possible to get the UDF speed where they want it to be on something very basic. — rwilson
@Jeeped Yes, rwilson is right on the mark. The UDF is pointless of course, but I want to see if something even as simple as that can run at a decent speed. — Alex Wilding

rwilson rwilson · Accepted Answer · 2015-09-02T03:18:12

You can instead pass in the values as a range and have it return all the values at once in an array. I just tried this on 50,000 rows and it returned all the values instantly. You will need to enter the UDF with CTRL + SHIFT + ENTER.

Function myweekday(mydate As Range) As Variant

Dim vMydate As Variant
Dim vMyWeekDay As Variant
Dim i As Long

vMydate = mydate.Value2
ReDim vMyWeekDay(1 To UBound(vMydate), 1 To 1)

    For i = 1 To UBound(vMydate)
        vMyWeekDay(i, 1) = Weekday(vMydate(i, 1))
    Next i

myweekday = vMyWeekDay

End Function

Speed up simple user defined functions

1 Answers