-1

I have an Excel file in which a VBA macro loads data into a Userform.

It will sometimes crash and close Excel if I run it without the editor open.

How to fix my file so I don't need to first open the editor?

Sample code from the macro:

Public Sub LoadButton_Click()  

    'Policy Information  
    ZoneLatitudeTextBox.Text = Sheets("Saved Policy Values").Cells(2, 2)  
    ZoneLongitudeTextBox.Text = Sheets("Saved Policy Values").Cells(3, 2)  
    TownClassComboBox.Text = Sheets("Saved Policy Values").Cells(4, 2)
Community
  • 1
  • 1
offcenter35
  • 145
  • 2
  • 11
  • 1
    Is the vbaproject password protected? – findwindow Apr 19 '16 at 19:53
  • 1
    There is absolutely no reason that having the VBE open would affect the way your macro is working. A more plausible reason would be that you have some kind unqualified calls to sheets and cells which are defaulting to the `ActiveWorkbook` object. Without seeing all of your code though it's impossible to tell. – SierraOscar Apr 19 '16 at 19:57
  • Nope it is not password protected. – offcenter35 Apr 19 '16 at 19:57
  • @MacroMan What do you mean by 'defaulting to the ActiveWorkbook object'? It sounded like an explanation for why the code runs when the editor is open, but I don't completely understand. – offcenter35 Apr 19 '16 at 20:12
  • If you use something like `Range` or `Cells` without appending it to a worksheet object, the code will execute on whichever sheet is active on whichever workbook is active at that point in time. This is a commonly overlooked issue in VBA. – SierraOscar Apr 19 '16 at 20:54

2 Answers2

5

This thread provided me the answer to my question:

http://www.xtremevbtalk.com/excel/229325-excel-crashes-unless-vba-editor.html

Apparently Excel runs into memory issues when there is a Userform that has too many controls. Opening VBA editor somehow bypasses the memory issues and allows the associated macro to run properly.

To automate this process, one simply has to add the following lines of code to the beginning of the macro:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.Visible = False

offcenter35
  • 145
  • 2
  • 11
1

I found the solution for this!! For months opening a UserForm without first having the form open in VBA editor would tank the entire program.

Another thread pointed out that Excel changed to loading forms in parallel, so when one piece finishes before the other it causes the whole thing to crash. Almost like your friend texting you "here" when they're still 3 blocks away, and if you head outside before they get to your house you die. Anyways.

If you call your UserForm with a button, add this to the Button_click() sub.

ThisWorkbook.VBProject.VBComponents("UserForm").Activate

It tells Excel to load the form as soon as you click the button, instead of loading everything that goes into the form first. This does essentially the same thing as opening a VBA window.

Jamie
  • 51
  • 3