0

First and foremost I'm an amateur when it comes to coding, and some of the code I have, I've figured out how to do by googling, so if what I'm doing is not intuitive, inefficient, or unfeasible, then please let me know.

I've been working making a macro that allows me to quickly write a "narrative" from preselected text from a survey. I have a few worksheets within the workbook, 10 out of which I reference in my code. In order to facilitate my coding, I found out how to name the worksheets via the "set" function so that I could simply call the worksheet without having to write out "Thisworkbook.Worksheets("Name").Range()" every time I have to access data on the worksheets, and instead, I simply use Name.Range() (where Name is the name I've given the worksheet via the Set function). I recently discovered how to set code to run when the workbook opens, which I set up to clear any data I may have forgotten to erase in the Template, however, Only some of the workbooks are named and remembered in the code later on. These are GI, PF, and TF. For example, I could open the Immediate window and write "GI.Activate" and it will activate the worksheet (this also works for the PF and TF worksheets), however, "WSPSS.Activate" (or any of the other worksheets) gives me 424 Run time error. I was wondering why this was. Am I doing something different/wrong that doesn't allow VBA to remember the other names, or is there a limit to how much could be done in the workbook open subroutine? Heres my code:

Option Explicit
Public GI, PF, WSFA, WSCom, WSSC, WSSoc, GenInfo, TF, WSNarrative, RTables, WSTSS, WSTGAC, WSPSS, WSPGAC As Worksheet

Private Sub Workbook_Open()

Set GI = ThisWorkbook.Worksheets("General Information"): Set PF = ThisWorkbook.Worksheets("Parent Form")
Set TF = ThisWorkbook.Worksheets("Teacher Form"): Set WSFA = ThisWorkbook.Worksheets("FA")
Set WSCom = ThisWorkbook.Worksheets("Com"): Set WSSC = ThisWorkbook.Worksheets("SC")
Set WSSoc = ThisWorkbook.Worksheets("Soc"):
Set WSPSS = ThisWorkbook.Worksheets("Parent SS"): Set WSPGAC = ThisWorkbook.Worksheets("Parent GAC")
Set WSTSS = ThisWorkbook.Worksheets("Teacher SS"): Set RTables = ThisWorkbook.Worksheets("Tables")


If ThisWorkbook.Path = "" Then 'This is so that only "new" files are cleared, but saved ones are left alone
    GI.OptionButtonF.Value = False: GI.OptionButtonM.Value = False
    Range("SFN").ClearContents: Range("SMN").ClearContents: Range("SLN").ClearContents: Range("DoB").Value = ""
    Range("PFN").ClearContents: Range("PLN").ClearContents: Range("PEvalDate").ClearContents
    Range("TFN").ClearContents: Range("TLN").ClearContents: Range("TEvalDAte").ClearContents
    PF.Range("B3:K28").ClearContents: PF.Range("B30:K30").ClearContents: PF.Range("E37:H40").Value = ""
    TF.Range("B3:K28").ClearContents: TF.Range("B30:K30").ClearContents: TF.Range("E37:H40").Value = ""
End If

End Sub

Edit:

Ok, as Tim Williams noted in a comment below, I seem to have changed the names of the worksheets in the VBA project explorer tree, and they don't match the names of the worksheets as I'm trying to set them, with the exception of GI,TF and PF. So that's probably the issue there. I'll go back into the explorer tree and rename them to what I'm using in the code.

Side question, if I already named the worksheets via the explorer tree, is there a point to using the set function to set the names of the worksheets?

Phoenix559
  • 1
  • 1
  • Did you change the codename for any of your sheets (in the VBA project explorer tree) such that it matches the names you're using for (eg) the `GI` sheet? If other sheets are not being "remembered" that's either because something in your code later cleared that variable, or the whole project got reset due to (eg) an unhandled exception. See also: https://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba/7043901#7043901 – Tim Williams Feb 03 '20 at 22:16
  • 1
    It looks like you are confusing workbook and worksheet in your question, can you go over it and check? Side note: `Public GI, PF, WSFA, ... WSPGAC As Worksheet` should be `Public GI as Worksheet, PFas Worksheet, ... ` etc... – cybernetic.nomad Feb 03 '20 at 22:16
  • 1
    Also, you declared a lot of worksheets but you never actually use them with sheet objects. You should have something like `GI.RANGE("A....")` – urdearboy Feb 03 '20 at 22:31
  • Style note - using`:` like that to save space (?) is counter-productive and will lead to other folks (and possibly also you) mis-reading your code and make it difficult to maintain. – Tim Williams Feb 04 '20 at 01:28
  • Tim- Thanks for the style note, I'll correct it, also, I don't know if I changed the codenames, however, it may be possible. I'm not at work to check it, so I'll update tomorrow to let you know. – Phoenix559 Feb 04 '20 at 05:37
  • Urdearboy, I don't use all those worksheets in the Workbook_Open sub but rather in the other subs and functions in a separate module. I could add all of my code, however, it's long and, IMO, somewhat convoluted as I use a lot of abbreviations for the different categories of answers in the survey as the variables. I would gladly upload the module for additional comments and suggestions on how to improve. – Phoenix559 Feb 04 '20 at 05:41
  • The main reason that I'm trying to set the workbook names in the Workbook_Open sub is so that I don't have to do add the code every time I need it (a short cut I guess), and I found it rather amusing, and annoying, that it only worked for a few of the worksheets, so as I said in the main post, if it's unfeasible, I'll just do it the other way. – Phoenix559 Feb 04 '20 at 05:44
  • Cybernetic.Nomad, I'm not sure if I did. It could be possible, as it has been edited by braX. Also is having to name 'as worksheet' after every name something that needs to be done? again it could be a style issue that may cause confusion (similar to the using ':' that Tim noted), however, I've never had an issue with just listing them with a ',' separating the names and ' as X' at the end (where X is the type). – Phoenix559 Feb 04 '20 at 05:49

0 Answers0