Arrays vs. Ranges

Following on from the previous post, I’ve had a look at the VBA WorksheetFunction methods DSum (as an example of the database functions) and Transpose.

The signature of DSum is:

Function DSum(Arg1 As Range, Arg2, Arg3) As Double

The arguments are the database table, the column to sum and the criteria. This suggests that the criteria might be something other than a Range. However, on investigation, this doesn’t seem to be the case. The function throws a Type Mismatch error of you give it a Range.Value array. Is there something else it could take?

So it seems that you need a criteria range out on a worksheet (you can’t create a new Range object, independent of a worksheet). However, you can still change the Value (array) of the criteria range from within the code. For example:

Function TestDsum() As Integer
        'pre: Range "table" exists
        '   with columns "id" and "value" (numeric)
        'pre: Range "criteria" exists, with 2R, 1C

    Dim criteria As Range
    Dim val As Variant        'value array for criteria
    ReDim val(1 To 2, 1 To 1) As Variant

    Set criteria = Range("criteria")

    val(1, 1) = "id"        'column name
    val(2, 1) = 3           'id = 3;
                            'could be a calculated variable
    criteria.Value = val

    TestDsum = WorksheetFunction.DSum(Range("table"), _
                                "value", criteria)

End Function

However, this is only saving us having a DSUM formula out on a worksheet, so I’m not sure it’s hugely useful.

The WorksheetFunction Transpose is even more disappointing: I can’t get it to do anything. The array function TRANSPOSE (in a worksheet formula) takes a worksheet range and switches rows and columns (e.g. 2R x 3C –> 3R x 2C). It might be useful if we could apply similar transposition to VBA arrays.

The signature is just:

Function Transpose(Arg1)

This is odd, because it doesn’t return anything! This implies that is modifies Arg1, which makes it a sub, not a function. Also, there’s no type declaration for Arg1, implying that it is not restricted to (say) a Range.

However, I can’t get it to do anything with either a Range or an array (such as Range.Value). It’s also not clear how it could modify a Range in situ. If anybody knows what it does, please let me know!


5 Responses to “Arrays vs. Ranges”

  1. 1 dougaj4 April 7, 2009 at 11:02 am

    I’m not sure if I missed your point, but this short sub:

    Sub TestTranspose()
    Dim TRange As Variant

    TRange = Range(“c5:c9”).Value
    TRange = WorksheetFunction.Transpose(TRange)
    Range(“d5:h5”).Value = TRange
    End Sub

    transposed the 5×1 array to a 1×5 array.

    But it’s better to “roll your own” VBA transpose function, because using Worksheetfunction calls is really slow (especially in XL2007).

    There’s a VBA Transpose function included in the file I recently posted here:

  2. 2 roymacleanvba April 8, 2009 at 10:12 am

    Ok, thanks. I was confused by the incorrect signature in the Object Browser, where no return type is given (this is done for several functions; why?).

    Do you have any performance metrics to support your preference for a DIY VBA transpose function, versus the worksheetfunction? I’ve always assumed that the latter were far more efficient, as they are presumably fully compiled code, rather than interpreted.

  3. 3 roymacleanvba April 8, 2009 at 2:32 pm

    Having nothing better to do this afternoon 🙂 I thought I’d check out the performance issue mentioned by dougaj4. I’ll describe the experiment and the results in a proper post, rather than here.

  4. 4 next page June 6, 2013 at 8:55 pm

    Howdy! Do you know if they make any plugins to protect against hackers?
    I’m kinda paranoid about losing everything I’ve worked hard
    on. Any recommendations?

  1. 1 Worksheet vs. Custom VBA Functions « Roy MacLean’s VBA Blog Trackback on April 8, 2009 at 3:08 pm

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

March 2009
« Feb   Apr »

%d bloggers like this: