1

I have already looked at these two posts:

Closing a Userform with Unload Me doesn't work

Error when closing an opened workbook in VBA Userform

They both suggest that when you want to close a file from Form code, you need to Unload the Form first (using Unload Me). However, if I Unload, I have a global array that's getting dereferenced.

Take a look at my code below though (which crashes on assigning global_int(0,0) to test). I can't Unload the Form unless I remove the array. Is this really the only solution to this problem?

Create a fresh excel file. In it, create a Userform. On that, create a Command Button with the following Click event code and global declaration:

Private global_int(2, 10) As Integer

Private Sub CommandButton1_Click()
    global_int(0, 0) = 23

    Dim filename As String
    Dim opened_workbook As Workbook

    filename = Application.GetOpenFilename()    ' User selects valid Excel file
    Set opened_workbook = Application.Workbooks.Open(filename)
    ' File operations would occur here
    Unload Me
    opened_workbook.Close    ' Exception thrown here

    Dim test As Integer
    test = global_int(0, 0)

    MsgBox "If you got here, it worked!"

End Sub

I'm just adapting someone else's code to work on a Mac, so I'd like to avoid completely refactoring if possible.

Thanks.

Community
  • 1
  • 1
colej1390
  • 157
  • 4
  • 11

1 Answers1

0

based on what I can understand is you have a userForm and the code is inside there. You can't unload the user form from inside the userForm code and expect the rest of the code to work. One option would be to write the code in a separate module. Call the user form to run from there

Math4123
  • 1,267
  • 4
  • 12
  • 23
  • This is more or less what I ended up doing. I used a module to declare all global variables so they weren't in the scope of the userForm. – colej1390 Feb 20 '14 at 12:21