storing worksheet usedrange into an Array Class

[sifar786]

Hi,

i am importing a workbook and storing each worksheets UsedRange into an public array. but once the code ends, the array contents are lost. i need to make sure the contents donot get lost as these array contents would be used later for some calculations.

what i understand is that a class can be used to store these arrays into it and maintain their state as long as this workbook is open. so the array contents wont get lost.

does anyone know how to write such a class?

In Worksheet button code:

Public Sub ImportWorksheets()
Filename=GetOpenFileName(""....)
....
....
....
arrInfo = ImportWorksheetFromExcel(Filename, "WkSheet_Infomation")
....
....
....
End Sub

In a Module:

Public arrInfo() as Variant

Function ImportWorksheetFromExcel(sWorkbookPath As String, Optional sSheetName As String = "") As Variant
'Importing data from a particular sheet in a workbook into an array

    
    On Error Resume Next
    'Check if file exists
    If Len(Dir$(sWorkbookPath)) > 0 Then
            On Error GoTo ErrFailed
            
            Set oWorkbook = Application.Workbooks(wkName)
            
            'Add sheet to store results
            If Len(sSheetName) > 0 Then
                Set oWkSheet = oWorkbook.Sheets(sSheetName)
            Else
                'Just use first sheet
                Set oWkSheet = oWorkbook.Sheets(1)
            End If
            
            'Get used range after resetting UsedRange
            Dim a
            a = oWkSheet.UsedRange.Rows.Count
            Call RangeToArray(oWkSheet.UsedRange, avValues)
    
    End If
    
    ImportWorksheetFromExcel = avValues
    Exit Function

ErrFailed:
    Debug.Assert False
    Debug.Print Err.Description
    On Error GoTo 0
End Function

Function RangeToArray(rngInput As Object, avValues As Variant) As Boolean
'Reads used range values into an array
    On Error GoTo ErrFailed
    avValues = Empty
    avValues = rngInput.Value
    RangeToArray = True
    
    Exit Function

ErrFailed:
    'Failed
    Debug.Print "Error in RangeToArray: " & Err.Description
    Debug.Assert False
    RangeToArray = False
    On Error GoTo 0
End Function

The above code puts the worksheets UsedRange into an array. but the arrays go empty as soon as the ImportWorksheets() procedure ends.

Any ideas how to implement the above code into a class for storing/retrieval of worksheet data?

Any help would be most appreciated.