none
Store and retrieve VBA array RRS feed

  • Question

  • Fellow sufferers!

    My Excel sheet loads a lot of information into arays (via an API to a tool called Confluence), some of which is then used to display an analytical stacked bar graph, when you select a certain cell. So when someone selects the cell, the event handler builds the graphs based on the figures stored in the arrays.

    My problem: when I close the sheet, the arrays (stored in memory) are lost. How can I save/reload an array? I will of course reload it by the open file event, but where/how is it best stored? I will store it at the file close event.

    Thank you all.

    BW Peter Bunde Hansen
    Monday, September 25, 2017 3:31 PM

Answers

  • You could store the array values in a worksheet. Name the range for the array to match the array name and then you can restore the values into the arrays in the WorkBook Open event.

    Something like the following example that creates 2 arrays and stores them into named ranges and then retrieves the data and stores in arrays when the workbook is opened.

    I do not know of any method of dynamically naming arrays to match the named range on the restore part so you need to know all of the array names and match the named ranges to the correct array with the Select Case.

    Not sure of your expertise so rather than go into a lengthy explanation, I will leave it to you to have a look at the code and then ask if you have any queries.

    'Following code in standard module

    Option Explicit

    Public arrFirst()   'Need to be public variables at the top of a standard module
    Public arrSecond()  'Need to be public variables at the top of a standard module

    Sub CreateAndStoreArray()
        Dim wbthis As Workbook
        Dim wsArrays As Worksheet
        Dim nme As Name
        Dim a As Long
        Dim b As Long
        Dim i As Long
        Dim j As Long
        Dim lngNextRow As Long
       
        a = 10
        b = 4
       
        'Dimension and create first test array
        ReDim arrFirst(1 To a, 1 To b)   'Two dimensional one based array
       
        For i = 1 To a
            For j = 1 To b
                arrFirst(i, j) = "First " & Chr(i + 64) & "_" & Chr(j + 64)
            Next j
        Next i
       
        'Following for testing if required
        'For i = 1 To a
        '    For j = 1 To 3
        '        Debug.Print arrFirst(i, j) & ", ";
        '    Next j
        '    Debug.Print
        'Next i
       
        'Dimension and create second test array
        ReDim arrSecond(1 To a, 1 To b)   'Two dimensional one based array
       
        For i = 1 To a
            For j = 1 To b
                arrSecond(i, j) = "Second " & Chr(i + 64) & "_" & Chr(j + 64)
            Next j
        Next i
       
        'Following for testing if required
        'Debug.Print 'Adds line feed
        'For i = 1 To a
        '    For j = 1 To 3
        '        Debug.Print arrSecond(i, j) & ", ";
        '    Next j
        '    Debug.Print
        'Next i
       
        Set wbthis = ThisWorkbook
        Set wsArrays = wbthis.Worksheets("Sheet2")
       
        With wsArrays
            .Cells.Clear    'Clears all data
            'Delete any existing names in the worksheet
            For Each nme In wbthis.Names
                If nme.RefersToRange.Parent.Name = wsArrays.Name Then
                    nme.Delete
                End If
            Next nme
           
            'First array saves at cell(1,1). (Resized range to fit the array)
            .Cells(1, 1).Resize(UBound(arrFirst, 1), UBound(arrFirst, 2)).Name = "arrFirst"
            Range("arrFirst").Value = arrFirst()
       
            lngNextRow = LastRowOrCol(True, .Cells) + 2 'Starting row for next array (spaced one row)
           
            'Next array saves 2 rows below previous array
            .Cells(lngNextRow, 1).Resize(UBound(arrSecond, 1), UBound(arrSecond, 2)).Name = "arrSecond"
            Range("arrSecond").Value = arrSecond()
        End With
    End Sub

    Function LastRowOrCol(bolRowOrCol As Boolean, Optional rng As Range) As Long
        'Finds the last used row or column in a worksheet
        'First parameter is True for Last Row or False for last Column
        'Third parameter is optional
            'Must be specified if not ActiveSheet
       
        Dim lngRowCol As Long
        Dim rngToFind As Range
       
        If rng Is Nothing Then
            Set rng = ActiveSheet.Cells
        End If
       
        If bolRowOrCol Then
            lngRowCol = xlByRows
        Else
            lngRowCol = xlByColumns
        End If
       
        With rng
            Set rngToFind = rng.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngRowCol, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
        If Not rngToFind Is Nothing Then
            If bolRowOrCol Then
                LastRowOrCol = rngToFind.Row
            Else
                LastRowOrCol = rngToFind.Column
            End If
        End If
       
    End Function

    'Following code in ThisWorkbook module

    Option Explicit

    Private Sub Workbook_Open()
        Dim wbthis As Workbook
        Dim wsArrays As Worksheet
        Dim nme As Name
       
        Set wbthis = ThisWorkbook
        Set wsArrays = wbthis.Worksheets("Sheet2")
       
        For Each nme In wbthis.Names
            If nme.RefersToRange.Parent.Name = wsArrays.Name Then
                Select Case nme.Name
                    Case "arrFirst"
                        arrFirst() = Range(nme.Name)
                    Case "arrSecond"
                        arrSecond = Range(nme.Value)
                End Select
            End If
        Next nme
       
        'Following is for testing purposes
        Dim i As Long
        Dim j As Long
       
        Debug.Print "arrFirst"
        For i = 1 To UBound(arrFirst, 1)
            For j = 1 To UBound(arrFirst, 2)
                Debug.Print arrFirst(i, j) & ", ";
            Next j
            Debug.Print 'Line feed
        Next i
       
        Debug.Print 'Line feed between array outputs
        Debug.Print "arrSecond"
        For i = 1 To UBound(arrSecond, 1)
            For j = 1 To UBound(arrSecond, 2)
                Debug.Print arrSecond(i, j) & ", ";
            Next j
            Debug.Print
        Next i
    End Sub


    Regards, OssieMac

    • Proposed as answer by Terry Xu - MSFT Tuesday, September 26, 2017 2:45 AM
    • Marked as answer by PeterBHansen Friday, October 27, 2017 5:36 PM
    Tuesday, September 26, 2017 2:12 AM

All replies

  • You could store the array values in a worksheet. Name the range for the array to match the array name and then you can restore the values into the arrays in the WorkBook Open event.

    Something like the following example that creates 2 arrays and stores them into named ranges and then retrieves the data and stores in arrays when the workbook is opened.

    I do not know of any method of dynamically naming arrays to match the named range on the restore part so you need to know all of the array names and match the named ranges to the correct array with the Select Case.

    Not sure of your expertise so rather than go into a lengthy explanation, I will leave it to you to have a look at the code and then ask if you have any queries.

    'Following code in standard module

    Option Explicit

    Public arrFirst()   'Need to be public variables at the top of a standard module
    Public arrSecond()  'Need to be public variables at the top of a standard module

    Sub CreateAndStoreArray()
        Dim wbthis As Workbook
        Dim wsArrays As Worksheet
        Dim nme As Name
        Dim a As Long
        Dim b As Long
        Dim i As Long
        Dim j As Long
        Dim lngNextRow As Long
       
        a = 10
        b = 4
       
        'Dimension and create first test array
        ReDim arrFirst(1 To a, 1 To b)   'Two dimensional one based array
       
        For i = 1 To a
            For j = 1 To b
                arrFirst(i, j) = "First " & Chr(i + 64) & "_" & Chr(j + 64)
            Next j
        Next i
       
        'Following for testing if required
        'For i = 1 To a
        '    For j = 1 To 3
        '        Debug.Print arrFirst(i, j) & ", ";
        '    Next j
        '    Debug.Print
        'Next i
       
        'Dimension and create second test array
        ReDim arrSecond(1 To a, 1 To b)   'Two dimensional one based array
       
        For i = 1 To a
            For j = 1 To b
                arrSecond(i, j) = "Second " & Chr(i + 64) & "_" & Chr(j + 64)
            Next j
        Next i
       
        'Following for testing if required
        'Debug.Print 'Adds line feed
        'For i = 1 To a
        '    For j = 1 To 3
        '        Debug.Print arrSecond(i, j) & ", ";
        '    Next j
        '    Debug.Print
        'Next i
       
        Set wbthis = ThisWorkbook
        Set wsArrays = wbthis.Worksheets("Sheet2")
       
        With wsArrays
            .Cells.Clear    'Clears all data
            'Delete any existing names in the worksheet
            For Each nme In wbthis.Names
                If nme.RefersToRange.Parent.Name = wsArrays.Name Then
                    nme.Delete
                End If
            Next nme
           
            'First array saves at cell(1,1). (Resized range to fit the array)
            .Cells(1, 1).Resize(UBound(arrFirst, 1), UBound(arrFirst, 2)).Name = "arrFirst"
            Range("arrFirst").Value = arrFirst()
       
            lngNextRow = LastRowOrCol(True, .Cells) + 2 'Starting row for next array (spaced one row)
           
            'Next array saves 2 rows below previous array
            .Cells(lngNextRow, 1).Resize(UBound(arrSecond, 1), UBound(arrSecond, 2)).Name = "arrSecond"
            Range("arrSecond").Value = arrSecond()
        End With
    End Sub

    Function LastRowOrCol(bolRowOrCol As Boolean, Optional rng As Range) As Long
        'Finds the last used row or column in a worksheet
        'First parameter is True for Last Row or False for last Column
        'Third parameter is optional
            'Must be specified if not ActiveSheet
       
        Dim lngRowCol As Long
        Dim rngToFind As Range
       
        If rng Is Nothing Then
            Set rng = ActiveSheet.Cells
        End If
       
        If bolRowOrCol Then
            lngRowCol = xlByRows
        Else
            lngRowCol = xlByColumns
        End If
       
        With rng
            Set rngToFind = rng.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngRowCol, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
        If Not rngToFind Is Nothing Then
            If bolRowOrCol Then
                LastRowOrCol = rngToFind.Row
            Else
                LastRowOrCol = rngToFind.Column
            End If
        End If
       
    End Function

    'Following code in ThisWorkbook module

    Option Explicit

    Private Sub Workbook_Open()
        Dim wbthis As Workbook
        Dim wsArrays As Worksheet
        Dim nme As Name
       
        Set wbthis = ThisWorkbook
        Set wsArrays = wbthis.Worksheets("Sheet2")
       
        For Each nme In wbthis.Names
            If nme.RefersToRange.Parent.Name = wsArrays.Name Then
                Select Case nme.Name
                    Case "arrFirst"
                        arrFirst() = Range(nme.Name)
                    Case "arrSecond"
                        arrSecond = Range(nme.Value)
                End Select
            End If
        Next nme
       
        'Following is for testing purposes
        Dim i As Long
        Dim j As Long
       
        Debug.Print "arrFirst"
        For i = 1 To UBound(arrFirst, 1)
            For j = 1 To UBound(arrFirst, 2)
                Debug.Print arrFirst(i, j) & ", ";
            Next j
            Debug.Print 'Line feed
        Next i
       
        Debug.Print 'Line feed between array outputs
        Debug.Print "arrSecond"
        For i = 1 To UBound(arrSecond, 1)
            For j = 1 To UBound(arrSecond, 2)
                Debug.Print arrSecond(i, j) & ", ";
            Next j
            Debug.Print
        Next i
    End Sub


    Regards, OssieMac

    • Proposed as answer by Terry Xu - MSFT Tuesday, September 26, 2017 2:45 AM
    • Marked as answer by PeterBHansen Friday, October 27, 2017 5:36 PM
    Tuesday, September 26, 2017 2:12 AM
  • Hi PeterBHansen,

    I can see that you are using an 3rd party api called Confluence.

    you are using the same api to load the data in to the sheet.

    I suggest you to refer the documentation of Confluence api.

    from your description it is not clear that the data get loss or only array get loss.

    so based on your issue you can try to find the solution form that documentation.

    on this forum, we only provide support for VBA object model related issue.

    Microsoft does not provide any support for 3rd party api.

    so for further support you may contact the developers of that api.

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 19, 2017 9:11 AM
    Moderator