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

Redim Preserve a 2D Array from Excel Table

user47077
1#
user47077 Published in September 20, 2018, 4:39 pm

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.

Jake

share|improve this question
  • 2
    You cannot redim preserve any dimension but the last one. – Scott Craner Feb 14 at 18:44
  • 2
    You can only ReDim the last dimension of the array. You are trying to do it to the first dimension. – braX Feb 14 at 18:44
  • Sorry, I am not understanding. I am trying to go from array(1 to 150, 1 to 3) to array(1 to 159, 1 to 3). This is not possible? – JJB Feb 14 at 18:47
  • 1
    NO. do it like from array(1 to 3, 1 to 150) to array(1 to 3, 1 to 159) and then in the end transpose. You cannot change rows in an array. – cyboashu Feb 14 at 18:50
  • OK. That makes sense. Thanks to you all. – JJB Feb 14 at 20:15

active oldest votes

Your Answer

StackExchange.ifUsing("editor", function () { StackExchange.using("externalEditor", function () { StackExchange.using("snippets", function () { StackExchange.snippets.init(); }); }); }, "code-snippets"); StackExchange.ready(function() { var channelOptions = { tags: "".split(" "), id: "1" }; initTagRenderer("".split(" "), "".split(" "), channelOptions); StackExchange.using("externalEditor", function() { // Have to fire editor after snippets, if snippets enabled if (StackExchange.settings.snippets.snippetsEnabled) { StackExchange.using("snippets", function() { createEditor(); }); } else { createEditor(); } }); function createEditor() { StackExchange.prepareEditor({ heartbeatType: 'answer', convertImagesToLinks: true, noModals: false, showLowRepImageUploadWarning: true, reputationToPostImages: 10, bindNavPrevention: true, postfix: "", onDemand: true, discardSelector: ".discard-answer" ,immediatelyShowMarkdownHelp:true }); } });
 
StackExchange.ready( function () { StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f48794138%2fredim-preserve-a-2d-array-from-excel-table%23new-answer', 'question_page'); } );

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged arrays vba or ask your own question.

StackExchange.ready(function(){$.get('/posts/48794138/ivc/af20');});
StackExchange.ready(function () { StackExchange.responsiveness.addSwitcher(); }) (function(i, s, o, g, r, a, m) { i['GoogleAnalyticsObject'] = r; i[r] = i[r] || function() { (i[r].q = i[r].q || []).push(arguments) }, i[r].l = 1 * new Date(); a = s.createElement(o), m = s.getElementsByTagName(o)[0]; a.async = 1; a.src = g; m.parentNode.insertBefore(a, m); })(window, document, 'script', 'https://www.google-analytics.com/analytics.js', 'ga'); StackExchange.ready(function () { StackExchange.ga.init({ sendTitles: true, tracker: window.ga, trackingCodes: [ 'UA-108242619-1' ] }); StackExchange.ga.setDimension('dimension2', '|arrays|vba|'); StackExchange.ga.setDimension('dimension3', 'Questions/Show'); StackExchange.ga.trackPageView(); }); /**/ var _qevents = _qevents || [], _comscore = _comscore || []; (function() { var ssl = 'https:' == document.location.protocol, s = document.getElementsByTagName('script')[0], qc = document.createElement('script'); qc.async = true; qc.src = (ssl ? 'https://secure' : 'http://edge') + '.quantserve.com/quant.js'; s.parentNode.insertBefore(qc, s); _qevents.push({ qacct: "p-c1rF4kxgLUzNc" }); /**/ var sc = document.createElement('script'); sc.async = true; sc.src = (ssl ? 'https://sb' : 'http://b') + '.scorecardresearch.com/beacon.js'; s.parentNode.insertBefore(sc, s); _comscore.push({ c1: "2", c2: "17440561" }); })();
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO