Worksheet vs. Custom VBA Functions

In a previous post, I mentioned experimenting with the Transpose worksheet function. Dougaj4 clarified its usage, and mentioned that it was “really slow” compared with a custom VBA function (his comment included a link to his own blog, and a downloadable workbook containing such a function). So I thought I’d check this out.

I’ve written a sub that successively transposes square arrays: 2 x 2, 3 x 3, 4 x 4, …, 256 x 256. The reason for stopping at 256 is because I initially tried this in Excel 2000 (of which more shortly). Here it is:

Sub TestTranspose()
    Dim harr As Variant
    Dim varr As Variant    'value arrays
    Dim ro As Integer
    Dim co As Integer      'row, column offsets
    Dim hrng As Range      'source range
    Dim vrng As Range      'target range

    Debug.Print Time

    ro = 1
    co = 1

    Dim i As Integer
    For i = 1 To 255
        Set hrng = Range(Range("Hstart"), _
                    Range("Hstart").Offset(ro, co))
        Set vrng = Range(Range("Vstart"), _
                    Range("Vstart").Offset(ro, co))
        harr = hrng.Value

        'Comment out one of the following
        varr = DougTranspose(harr)
        varr = WorksheetFunction.Transpose(harr)

        vrng.Value = varr

        ro = ro + 1
        co = co + 1

    Debug.Print Time
End Sub

The source ranges contain row numbers (h for horizontal), the target ranges transpose these (v for vertical).

In Excel 2000 this worked until iteration 74, when it threw Run Time Error 13: Type Mismatch. Seriously weird! Anyway it seems to work Ok in Excel 2007. And the results are … (sound of envelope being opened) …

  • WorksheetFunction: 32, 33, 32, 32 seconds
  • Doug’s VBA function: 25, 30, 31, 30 seconds

(times just to nearest second, but you get the overall idea).

So, remarkably similar. I thought the WS function would get it; Doug thought his would; end-result, pretty much the same. Now, it could be that the time taken for the other operations in the test procedure (Range and Offset) swamp the time taken for the transpose, but somehow I doubt it. Anyway, there doesn’t seem to be a prima facie case against using the worksheet function.


5 Responses to “Worksheet vs. Custom VBA Functions”

  1. 1 dougaj4 April 17, 2009 at 1:14 pm

    Roy – I’ll post more details later, but it is the other parts of the function that take all the time, specifically reading the data from the spreadsheet and writing it back. The actual transpose function is about twice as fast using the VBA as using worksheetfunction with Excel 2007. Worksheetfunction is quicker in earlier versions, but still generally significantly slower than VBA in my experience. Also the matrix functions pre 2007 are very limited in the maximum size they will handle, as you discovered.

    This only applies to working in VBA by the way. On the spreadsheet a built in function will normally be way quicker than a UDF that does the same thing.

  2. 2 dougaj4 April 22, 2009 at 11:58 am

    Roy – I have given details of my timings on the worksheetfunction vs UDF question on my blog:

    In summary, the UDF is about twice as fast as the worksheetfunction at doing the actual transpose, but if there are a large number of operations reading and writing between the spreadsheet and VBA these take much longer than the transposing, so the saving on the transpose operation is not so significant.

  3. 3 roymacleanvba April 24, 2009 at 1:43 pm

    Thanks, Doug. I have to admit that handling large arrays, and any attendant performance issues, is not something I deal with normally, although clearly there are people who do. As you say, it’s probably the worksheet–VBA transfer of data that’s significant. If people really do start using (in xl2007) 1 million row * 10K column worksheets then I can see this being relevant.

  1. 1 5 Excel Blogs You May Not Have Known About » Code For Excel And Outlook Blog Trackback on January 6, 2010 at 12:15 pm
  2. 2 dallas cpa Trackback on June 13, 2014 at 9:39 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

April 2009
« Mar   May »

%d bloggers like this: