Reading contents of sheet into array before saving workbook

[tiredofit]

My workbook contains a single worksheet with data in cells A1 to A3.

This code reads the data into an array:

    Dim MyArray() As Variant
   
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value

The problem occurs if the above Sub is saved in the Workbook_BeforeSave event.

So I have:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim MyArray() As Variant
   
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
End Sub

This is Module1:

Option Explicit

Sub S()

    Dim FileSelector As FileDialog
           
    Set FileSelector = Application.FileDialog(fileDialogType:=msoFileDialogSaveAs)
   
    With FileSelector
       
        .FilterIndex = 1
       
        .InitialFileName = ThisWorkbook.Path & "\"
        
        .Title = "Please Type A Filename"
       
        Dim FileSelected As Boolean
   
        FileSelected = .Show

    End With
   
    If FileSelected <> False Then
   
        Application.DisplayAlerts = False
       
        ThisWorkbook.SaveAs Filename:=FileSelector.SelectedItems(1), _
                            FileFormat:=51

        Application.DisplayAlerts = True
       
    End If
   
    Set FileSelector = Nothing
           
End Sub

When I try to save the workbook, the program crashes on this line:

MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value

Can someone tell me what is wrong?

It seems the program cannot "see" the data and read it into MyArray.

Thanks

 

[JackDanIce]

What are you trying to achieve? I believe the array variable will lose it's contents when the workbook is saved, regardless of reading into an array or not.

There doesn't seem to be any direct connection between the sub in Module1 (some kind of file save prompting the user to provide a file name) and saving contents of A1:A3 into a variable.
If that data is stored into an array and then the file is closed, what is the purpose of saving that data into a variable?

Can you describe (simplified) what is in A1:A3 and what the end result should be?

 

[tiredofit]

What are you trying to achieve? I believe the array variable will lose it's contents when the workbook is saved, regardless of reading into an array or not.

There doesn't seem to be any direct connection between the sub in Module1 (some kind of file save prompting the user to provide a file name) and saving contents of A1:A3 into a variable.
If that data is stored into an array and then the file is closed, what is the purpose of saving that data into a variable?

Can you describe (simplified) what is in A1:A3 and what the end result should be?

I have only posted the relevant parts of the code to show it doesn't read the contents of the array.

The purpose of the entire program is to check if certain conditions in teh array are met.

If not, then don't save.

 

[JackDanIce]

That doesn't answer why you're trying to save data into an array as you close the file, that variable will lose it's contents once the file closes (I believe) so there appears to be a disconnect.

Difficult to answer without knowing all necessary detaills/info. It may be the '()' after MyArray in the second line and if the range is fixed as A1:A3, try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim MyArray() As Variant
   
    MyArray = Sheet1.Range("A1:A3").Value
   
End Sub

 

[tiredofit]

That doesn't answer why you're trying to save data into an array as you close the file, that variable will lose it's contents once the file closes (I believe) so there appears to be a disconnect.

Difficult to answer without knowing all necessary detaills/info. It may be the '()' after MyArray in the second line and if the range is fixed as A1:A3, try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim MyArray() As Variant
  
    MyArray = Sheet1.Range("A1:A3").Value
  
End Sub

No, I tried removing () but it still crashed.

My point is that the code seems to NOT like it when the Sub S() is run.

But if Sub S() were removed and I save the workbook (by simply clicking on the Save icon), the contents of the array are read.