Home Redim Preserve a 2D Array from Excel Table
Reply: 0

Redim Preserve a 2D Array from Excel Table

JJB Published in 2018-02-14 18:42:48Z

Try as I might I cannot correct a "Subscript out of Range" error that the code below is throwing on the last line of code below when I try to expand the size of an array with Redim Preserve. Any ideas as to what might be wrong?

Sub Demo()

'Set Excel variables
    Dim wb As Workbook:                 Set wb = ActiveWorkbook
    Dim wsCluster As Worksheet:         Set wsCluster = wb.Worksheets("Cluster")
    Dim initialK As ListObject:         Set initialK = wsCluster.ListObjects("k")

'Set Array variables
    Dim kArray() As Variant
        ReDim kArray(1 To initialK.DataBodyRange.Rows.Count, 1 To initialK.DataBodyRange.Columns.Count)
            kArray = initialK.DataBodyRange

'Set Other variables
    Dim coordResults As Variant, kResults As Variant, addKs As Variant, dupeArray As Variant
    Dim transferLat As Double, transferLong As Double, potDupeCount As Long, expandK As Long, nextAvailK As Long

'Run the assignment function once, then loop through average/assign functions
    coordResults = Assign(coordArray, kArray)
        For i = 1 To 3
            kResults = Average(coordArray, kArray, coordResults)
            coordResults = Assign(coordArray, kArray)
        Next i

'Run the radius function
addKs = AdjustForRadius(coordArray, kResults, coordResults)

'Add the new, deduped centroid data to the kResults array
    expandK = UBound(addKs, 1) + UBound(kArray, 1)
    ReDim Preserve kArray(1 To expandK, 1 To 3)

End Sub

The functions are working properly. The "expand" variable is good.

I have tested everything I know.


You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299586 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO