2

I have been developing a VBA, UserForm1 program for the past few weeks and I have performed numerous test and never once had this problem. The code, shown below, is part of the Initialization routine. I made some enhancements to the program, far deeper in the code than the code under discussion. Now, I can't get past this point in the program. I replaced "Activate" with "Select", but this did not affect the outcome, i.e., an aborted run.

Please, can someone suggest what I am doing wrong? Or, how can a program that been tested dozens of times, suddenly develop a fault out of thin air? 

    '======================================================================
    Dim WPA() As Variant             'Workbook path for Category Class
    Dim WBA() As Variant             'Workbook name for Category Class
    Dim WSA() As Variant             'Worksheet name for Category Class
    '======================================================================
    Dim WBK() As Workbook

    ..........
    Set WBK(S) = Workbooks.Open(WPA(S))
    Workbooks(WBA(S)).Worksheets(WSA(S)).Activate   'Activate Worksheet
    NWS(S) = CheckSheetExists(WSA(S))               'Check for Worksheet
    ..........

   Function CheckSheetExists(SheetName) As Boolean
   CheckSheetExists = Evaluate("ISREF('" & SheetName & "'!A1)")
   If CheckSheetExists = False Then
       MsgBox "Worksheet " & SheetName & " does not exist. Run aborted."
       End                        '<==================Run aborts
   End If
   End Function
Teamothy
  • 2,000
  • 3
  • 16
  • 26
Gggggggg
  • 31
  • 4
  • 6
    You need to replace `End` with `Exit Function`, see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/end-statement – Storax Sep 30 '19 at 12:35
  • workbook path, workbook name, and worksheet name are all accessible from the worksheet object (`.Parent.Path`, `.Parent.Name`, and `.Name`). I don't see any reason to capture these in three separate arrays, when a single array or collection of worksheet object should suffice. While this is likely not the root of your problem, it could help to simplify your code. – David Zemens Sep 30 '19 at 13:18
  • Please include [mcve]. As of right now, it is not clear that the sheet "obviously" exists (does it, really? in which workbook(s)? are you sure there's no leading/trailing whitespace in the sheetname which causes false result? etc.) – David Zemens Sep 30 '19 at 13:22
  • Thank you for your valuable advice, however, I can assure you I have checked, but I do appreciate your skepticism. I'm new to VBA David, so how do I demonstrate in this forum the confidence of my assertion that the sheet exists. Let's work on the premis that the sheet exists, what set of circumstances could cause the problem? – Gggggggg Sep 30 '19 at 18:30
  • With regards to your comment on workbook path, workbook name, and worksheet, it just seemed an easy solution at the time, as I said I'm a newbie to VBA. None of the information is hardcoded in the program, all file names, and procedure names are kept on a control sheet called Template. However, now that you have enlightened me with (.Parent.Path, .Parent.Name, and .Name). I will investigate that for version 2 of the program. So thank you. – Gggggggg Sep 30 '19 at 18:42

2 Answers2

1

It looks like you are using global variables?

If so, you should be aware that they are not as permanent as you might expect. There are a number of things which cause global variables to reset and become undefined.

See this answer for more info: https://stackoverflow.com/a/7043901/1473412

Using "End"

An unhandled runtime error

Editing code

Closing the workbook containing the VB project

Is it possible that the "enhancements to the program, far deeper in the code than the code under discussion", causes one of these things to happen, and so resets the global variables?

If you have to use global variables, you could store them safely in a worksheet. Or, everytime you try and use one, you could check that it is defined, and if not, redefine it?

Community
  • 1
  • 1
Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • Thank you for your advice, however all global variables, if that is what I am using,(New to VBA), are kept on various worksheets in a workbook called Template. The initialization routine reads all pathnames, workbook names and sheet names into the various arrays, see sample code: – Gggggggg Sep 30 '19 at 18:05
  • But that is what I mean. The arrays ARE the global variables. They could be getting reset between the initialization routine running and you actually using them. I am not saying that this is the problem, but it is certainly one of the first things I would check. Add a breakpoint just before your code breaks and see if your arrays contain what you think they should. – Gravitate Oct 01 '19 at 08:09
  • Thank you, I will check that next time it goes wrong. However, just to give you feedback, I closed all the excel workbooks, then restarted excel and, wouldn't you know it, it worked perfectly. But I have received some really useful tips, and I thank everybody in this community for their advice. – Gggggggg Oct 01 '19 at 12:25
0

That is a good question, Storaxs' answer should have fixed this though to my knowledge.

However when something like this happens I try to create a whole new workbook and paste all of this code into it (just for the sheet in question) and then copy all the cells data into the new sheets.

This has saved me once before and it may help you in this case as well.

I also recommend not deleting your old workbook in case you don't port everything over.

If this still doesn't work let us know and we will continue to look into this.

Dasheno
  • 1
  • 4