+ Reply to Thread
Results 1 to 3 of 3

Copy values from Excel Range to VBA array.

  1. #1
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345

    Copy values from Excel Range to VBA array.

    I'm pretty new to VBA programming, but have managed the basics. Now I have been struggeling with a problem today, and try to ask here for some help.

    I have a range in a worksheet named "rngInput". This is currently a 85 row, 20 column range containing text and numbers. This range can vary in size in future versions, so I need the code to be flexible regarding the size and location of the array.

    What I need is some code to make a backup of the values in this range, stored in an array with VBA. Then I also need some code to restore these values back into the worksheet range.

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    VBA variables will lose thier value when they pass out of scope (like when you close a workbook) so they are not suitable for backing up data. (Just in case that is what you meant).

    For quickly loading arrays from spreadsheet ranges. Variant arrays can be used

    Please Login or Register  to view this content.
    will set myArray to a 2-Dimensional 100 X 3 array of the values in A1:C100

    Please Login or Register  to view this content.
    will put the values of myArray in AA1:AC100.

    If the size of myArray is not certain

    Please Login or Register  to view this content.
    will adjust the size of the range to match the size of myArray.

    I hope that helped.
    Last edited by mikerickson; 12-29-2007 at 04:58 PM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    Thank You, this works fine.
    This was not supposed to be a long time backup, as I know I can't do this way. This was actualy a part of trying to make a workaround for the Undo stack beeing wiped out when I execute some VBA code to update cell formatting.

    I now experienced somthing new. This first code, which I made with help from you actually does not flush the Undo stack:

    Please Login or Register  to view this content.
    But this code gives med the same result, but flushes the Undu stack:

    Please Login or Register  to view this content.
    Both codes makes a copy of the Values in "rngInput" in Sheet1 and put these values in the same cells in Sheet3. The Sub is run from a Worksheet_Change sub in the Sheet1 Code.

    Both subs make changes to cells in the Workbook, but the first one does not flush the Undo stack. Do you know why ? I tought all changes to cells in the Workbook with VBA would flush the Undo stack.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1