Array Storage Problem: Array Elements Deleted on 'End' Command

Hi guys,

So I'm building an application for the company I work for. The application is quite large now, so I won't post the code, but I'll do my best to explain the issue.

Basically, the application is set up so that users input most of the information directly into the cells of a spreadsheet. The type of information inputted is for HR purposes, and includes things like the role an employee will fill, their pay rate, phone number, etc. However, there is a second information gathering area the application uses. One column of the spreadsheet is labeled 'Safety Tickets,' and when a cell in this column is selected it triggers a user form to appear. This form is then used to collect a potentially large amount of additional information, (it was chosen over the alternative of cluttering up the spreadsheet). Once the user clicks the 'Confirm Inputs' button on the userform, the form's inputs are stored into a series of arrays (from now on referred to as the 'Safety Ticket Arrays') for the selected employee and the userform is unloaded. This form is then re-used each time a cell in the Safety Tickets column is selected for a different employee. Therefore, these Safety Ticket Arrays are updated dynamically with each new entry. The rest of the spreadsheet data, however, is not collected into arrays until a button is clicked and the application runs to completion.

However, before I let the application run to completion, I have some validation checks on the data. If the user has forgotten to input a required field, or there are row gaps in the spreadsheet, then the application lets the user know what the issue is and ends the sub so that they can make the required fixes. To do this, I am using the 'End' command to prematurely exit the sub if validation check conditions are not met. The problem I'm having is that every time the code gets to one of these 'End' commands, all the data (elements) in the Safety Ticket Array's are deleted/emptied. This is obviously not a desired result, as the user now has to re-enter all that data. So my question is whether anyone knows a way around this issue? Perhaps there is an alternative to the End command?

Here's some additional information:

When the user clicks the button to run the main sub of the application, the subs are set up so that 1) Validation checks are done, 2) Data storage to arrays for all spreadsheet data - except Safety Ticket information - is done, (i.e., Safety Ticket data has already been collected into arrays previous to the main sub being run) 3) Array data is used to automatically fill out paperwork using MS Word templates, documents are saved to appropriate folders, and any required e-mails are sent.

Currently, the data validation checks are done largely in the same sub as the array information gathering process, (so steps 1 & 2 are done in the same subroutine). I've done this because both processes require the application to first identify how many employee records have been created on the spreadsheet. Personally, I don't think this is an issue, since the arrays being affected (emptied) are the 'Safety Ticket' arrays which have been populated prior to the user running the main sub.

I'd appreciate any input you might have. Please let me know if any additional information is needed!

Thanks,
Aiden

Just checking to see if anyone has any more ideas today. I'd appreciate any help

Thanks guys!

Yeah, I guess the 'End' command is true to its name and pretty much ends everything haha.

My initial problem with using the 'Exit Sub' command was that since my application was so large, I had a lot of subroutine 'Calls' within a Main subroutine. Therefore, if the code reached an Exit Sub within one of these called subroutines, the application would just exit to the main sub, (which would then just call the next subroutine in the Main sub, which was not the desired result).

I got around this issue with a fairly simple solution, with a variable I named 'exitSub'. Right before a line containing 'Exit Sub' coding was reaching, I coded for the exitSub variable to = 1. The application would then exit the sub to the Main sub, but before the next sub was called I had the program check whether exitSub equaled 1 with an If statement. If exitSub = 0, all was good and the program would continue. However, if exitSub = 1, another Exit Sub would result and the application would also exit the Main sub.

Another option might have been to play around with the 'GoTo' command.

I guess the solution wasn't all that complicated, but it sure had me pulling out my hair when my arrays kept emptying!

Thanks again for everyone's help, I defiantly learned a lot about the End command with this trial!