Excel VBA - Read range into Variant while maintaining index same as column numbers

Question

I am writing a vba macro in Excel 2013. I have the below code to read a range into a Variant,

Dim MyBuffer As Variant

With MyWorkSheet
    MyBuffer = .Range(.Cells(1, NAME_COL), .Cells(10, AGE_COL)).Value
End With

'Here NAME_COL = 5, AGE_COL = 9

Now MyBuffer is a 2 dimensional array with indices ranging from (1, 1) to (10, 5). I was wondering whether there is a way to maintain the second part of the indices same as that of the column numbers. ie from (1, 5) to (10, 9) so that I can use the constants NAME_COL, AGE_COL etc while accessing the Variant too. This is primarily for readability (so that the other programmer can easily see which entry is that I am accessing) and maintainability (cases where we add/delete/swap columns on the sheet). Appreciate any help in figuring out the same. Kindly note that I am not considering further copying into another array with modified index, or having another set of constants for the Variant positions (NAME_POS, AGE_POS etc).

score 1 · Accepted Answer · answered Mar 07 '15 at 12:48

1

Just redim it.

With MyWorkSheet
    MyBuffer = .Range(.Cells(1, NAME_COL), .Cells(10, AGE_COL)).Value
    ReDim Preserve MyBuffer(LBound(MyBuffer) To UBound(MyBuffer), NAME_COL To AGE_COL)
End With

answered Mar 07 '15 at 12:48

GSerg

76,472
17
159
346

Thanks GSerg. As I look up on ReDim, what I understand is that it creates a new array behind the scenes and copies the data into it (or will it not in the above case since there is no size change?). I have multiple such buffers and much more data volume (hence performance) to be considered (ie why I avoided explicit copying to another array). – programmist Mar 07 '15 at 13:03
1

@mpathi As far as I can see, this directly [manipulates](http://stackoverflow.com/a/11713643/11683) the SAFEARRAY descriptor, simply changing the dimension bounds. You can verify that by looking at `VarPtr(MyBuffer(LBound(MyBuffer, 1), LBound(MyBuffer, 2)))` - it does not move. Also it only works because `MyBuffer` is declared as a single `Variant`. If you declare it as an array of Variants it will fail to redim - because in this case it will try to copy and for that the new bounds must properly overlap the old bounds, which they do not in this case (although that I'm kinda speculating here). – GSerg Mar 07 '15 at 14:11
Note that this is what I have observed, I didn't know this from the theory. You should really measure the time it takes for you to rebase a huge array, see if takes unnoticeably small amount of time regardless of array size. – GSerg Mar 07 '15 at 14:14
I will try out both VarPtr & time measurement and get back soon. Thanks for that VarPtr especially (I am new to vba). – programmist Mar 07 '15 at 14:32
1

Please find my answer below with the results. They prove you right. Thanks once again. – programmist Mar 08 '15 at 05:08

score 0 · Answer 2 · answered Mar 08 '15 at 05:06

Based on GSerg's suggestion, I did the following test to measure performance as well as changes in memory address.

// Shift column indices, without increasing number of columns
1000R x 20C     -->  1000R x 20C      =  4.7 micro secs   (address remains same)
100,000R x 20C  -->  100,000R x 20C   =  6.3 micro secs   (address remains same)

// Shift column indices, and increase number of columns
1000R x 20C     -->  1000R x 21C      =  80 micro secs    (address changes)
100,000R x 20C  -->  100,000R x 21C   =  13.5 milli secs  (address changes)

Following is the code, in case anyone interested

Option Explicit

Declare Function GetFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (Frequency As Currency) As Long
Declare Function GetTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (TickCount As Currency) As Long

Sub Test()
    Dim MyWorkSheet As Worksheet
    Dim MyBuffer As Variant

    Dim FirstRow As Long
    Dim FirstCol As Long
    Dim LastRow As Long
    Dim LastCol As Long

    Dim Message As String

    Set MyWorkSheet = Sheets("Test")
    FirstRow = 1
    FirstCol = 1
    LastRow = 100000
    LastCol = 20

    ' Read the range into buffer
    With MyWorkSheet
        MyBuffer = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)).Value
    End With

    ' Check the address before ReDim
    Message = "Value At " & VarPtr(MyBuffer(FirstRow, FirstCol)) & " = " & MyBuffer(FirstRow, FirstCol)

    ' Shift the column indices
    FirstCol = FirstCol + 100
    LastCol = LastCol + 100   ' Modify this to change column count as well

    ' ReDim the buffer to shifted column indices and measure time taken
    Timer
    ReDim Preserve MyBuffer(FirstRow To LastRow, FirstCol To LastCol)
    Timer

    ' Check the address after ReDim
    Message = Message & Chr(10) & "Value At " & VarPtr(MyBuffer(FirstRow, FirstCol)) & " = " & MyBuffer(FirstRow, FirstCol)

    MsgBox Message
End Sub

Sub Timer()
    Dim TickCount As Currency
    GetTickCount TickCount

    Static Frequency As Currency
    If Frequency = 0 Then
        GetFrequency Frequency
    End If

    Static FirstTime As Double

    If Frequency Then
        If FirstTime <> 0 Then
            MsgBox "Elapsed : " & (TickCount / Frequency) - FirstTime
            FirstTime = 0
        Else
            FirstTime = TickCount / Frequency
        End If
    End If
End Sub

Excel VBA - Read range into Variant while maintaining index same as column numbers

2 Answers2