Referencing an array (within an array)

Question

What my sub is trying to do:
Take an array (e.g. (1 to 100, 1 to 36), contained in an array of arrays)
Take only some of the columns (e.g. I only want columns 2,5,7 etc.)
Replace the original array with one containing only those columns (so it will go from (1 to 100, 1 to 36) to e.g. (1 to 100, 1 to 5)).
I do this by copying the columns I want to a new array, then erasing the original and re-creating it with only the new information.

N.B. all my arrays are dimmed as variants first dim arrVariable as Variant with arrVariable = Array() I have an array of arrays arrAggregatedArrays(1 to 8)

The relevant part of my sub goes:

FilterSheetArrayForColumns (ArrAggregatedArrays(i))


Private Sub FilterSheetArrayForColumns(ByRef arrSource As Variant)

'/======================================================================================================================================================
'/  Author:  Zak Armstrong
'/  Email:   [email protected]
'/  Date:    12/August/2015
'/
'/  Description:    Takes Sheet arrays, finds the columns from the colAllHeadings, recreates the array with just that data (and empty columns for the ones not found)
'/======================================================================================================================================================
Dim i                                           As Long
Dim j                                           As Long
Dim k                                           As Long

Dim lngFinalRow                                 As Long
Dim lngFinalColumn                              As Long

Dim arrTempArray                                As Variant      '/  Temporarily holds the filtered information
    arrTempArray = Array()

Dim arrHeadingsRow                              As Variant      '/  Holds the top (headings) row for application.match
    arrHeadingsRow = Array()

Dim varColumnPosition                           As Variant      '/  Holds the position of the relevant column

Dim strHeading                                  As String       '/  The current heading to search for
'/======================================================================================================================================================

        AssignArrayBounds arrSource, UB1:=lngFinalRow, UB2:=lngFinalColumn

    '/==================================================
    '/ Recreate Headings Row
    '/==================================================
        ReDim arrHeadingsRow(1 To lngFinalColumn)

        For i = 1 To lngFinalColumn
            arrHeadingsRow(i) = arrSource(1, i)
        Next i

'/==================================================
'/ Find Columns, put in array
'/==================================================
    ReDim arrTempArray(0 To lngFinalRow, 0 To ColAllHeadings.Count)
    arrTempArray(0, 0) = arrSource(0, 0)

    Dim lngDestinationColumn As Long
    Dim lngSourceColumn As Long

        For i = 1 To ColAllHeadings.Count
            strHeading = ColAllHeadings(i)
            varColumnPosition = Application.Match(strHeading, arrHeadingsRow, 0)

                If IsError(varColumnPosition) _
                    Then
                        MissingDataHeadingsHandler arrSource, strHeading
                    Else
                        lngDestinationColumn = i
                        lngSourceColumn = varColumnPosition

                        CopyArrayColumn2d arrSource, arrTempArray, lngSourceColumn, lngDestinationColumn
                End If
        Next i

CopyArrayContents2d arrTempArray, arrSource

End Sub

But, at the end of this sub, arrAggregatedArrays(i) still contains the original array, not the filtered one.

I imagine my code might just be erasing the reference to the array, as opposed to the array itself. If that's the case, how do I reference it properly?

If that's not the case, where is the variable referencing going wrong?

paul bica · Accepted Answer · 2015-08-28 12:48:35Z

1

Function FilterSheetArray() is not using the parameter "arrSource"

Try this:

FilterSheetArray(arrAggregatedArrays(i))

Sub FilterSheetArray(ByRef arrSource as variant)

    dim arrTemp as variant

        arrTemp = arrSource        ' <----------------------

        '/ fill arrTemp with specific columns from arrSource
        '/ Erase arrSource, ReDim and copy contents of arrTemp

       arrSource = arrTemp
End Sub

and make sure you assign "arrTemp" back to "arrSource" at the end

(or just use "arrSource" and remove "arrTemp")

edited Aug 28, 2015 at 12:48

answered Aug 28, 2015 at 12:40

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kaz Over a year ago

I'm not sure you've understood what I'm trying to accomplish, I'll add an edit clarifying it.

paul bica Over a year ago

The answer should address the issue: arrSource must be updated

Kaz Over a year ago

It's not doing it. arrSource has the correct data now, but arrAggregatedArrays(i) is still referencing the original array, instead of arrsouce

Kaz · Accepted Answer · 2015-08-28 13:41:46Z

0

I found the problem. I was calling my sub using this syntax:

FilterSheetArrayForColumns (ArrAggregatedArrays(i))

When I should have been using:

FilterSheetArrayForColumns ArrAggregatedArrays(i)

In VBA, whenever you use parentheses around a variable, it forces VBA to evaluate it. So, in this instance, it was not passing the variable arrAggregatedArrays(i), but rather an evaluation (effectively a local copy).

So the sub was doing what it should, but rather than referencing the original variable, it was only changing a local copy, which disappeared as soon as the sub ended, leaving the original unchanged.

answered Aug 28, 2015 at 13:41

Kaz

1,3329 silver badges21 bronze badges

2 Comments

skkakkar Over a year ago

Like your correct and analytic diagnosis of the problem. BTW which practical situations may require such type of jagged arrays.

Kaz Over a year ago

In this case, It's different sets of data but once I have them in arrays, everything elsei do to them is standardised, so I'd rather iterate through them than call each one individually

Collectives™ on Stack Overflow

Referencing an array (within an array)

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related