Workbook with a series of errors

[darkwulf]

I represent a group of RPG gamers who have for a number of years used an excel file to create characters for the Star Wars Saga Edition system. The creator of the file no longer supports it and we have a number of issues we are looking to fix. However none of us are programmers or at least none of us work with VBA. This was the first place that we came across looking for help to fix this file. The majority of the issues are located on an issues tab in the file. I am sure that makes it convenient. While I can't help with the code itself I can explain what we are looking to have happen with each issue. Is there anyone who would be willing to help us tackle this?
Where I can't place a link or attach the file, PM me or post here and I'll pm you the link.

[p45cal]

Yes, PM me the link, but even though you haven't sufficient count of posts here at the moment (momentarily currently 1) you can still post everything but the http:// part of a link. People will understand.

[darkwulf]

Here is the link minus the http part for anyone who is interested in looking at the file.
mediafire.com/download/exwbv7c3i1899ih

[SamT]

http://mediafire.com/download/exwbv7c3i1899ih

*.xlsm File is 3.4mb

[SamT]

Doing a little step by step cleanup on your Book.

First I found a bunch, (350,) of Names in the book that reference Worksheets that no longer exist. They probably don't affect the operation of the Book, but they might and they do get in the way and increase the size of the book without benefit. The attached file has a Worksheet with a list of Names with #REF Errors in the Sheet reference part of the Refers To formula. It also has a macro that reads this list and deletes those names from a copy of "SagaSheet 1.4.9 CHARACTER GENERATOR.xlsm."

This copy MUST BE NAMED "Copy of SagaSheet 1.4.9 CHARACTER GENERATOR.xlsm"

Please Download the attached and open both it and the "Copy of SagaSheet 1.4.9 CHARACTER GENERATOR.xlsm." Click the button on the Worksheet in the attached to remove those Names, then thoroughly test "Copy of SagaSheet 1.4.9 CHARACTER GENERATOR.xlsm" to insure that no negative changes were made.

This is the Code in the Attached.

Option Explicit

Private Sub CommandButton1_Click()
RemoveNamesForDeletedSheets
End Sub

Sub RemoveNamesForDeletedSheets()
Dim Cel As Range
For Each Cel In Me.Range("$A$2:$A$350")
  With Workbooks("Copy of SagaSheet 1.4.9 CHARACTER GENERATOR.xlsm")
    .Names(Cel.Text).Delete
  End With
Next Cel
End Sub

How much knowledge does your group have with programming, in general and VBA in specific?

[Paul_Hossler]

Interesting problem

1. The WB and macros seem to be pretty well written: comments, good use of Excel objects, etc. Named ranges (lots of named ranges)

2. No Option Explicit used so in things like this, I can't tell where the 'x' is coming from

Public Sub ArmourDetails()
    
    If Armour.Range("ArmourName" & x).Value <> "" Or NewDroid.Range("NewDroidArmour").Value > 0 Then
        ArmourCycle
        Call DisplayArmour(False)
    Else
        Call DisplayArmour(True)
    End If
    
End Sub

3. I did like SamT and cleared the 'bad Names' except I did it brute manual force and sorted by RefersTo and Value and just deleted the #REF ones


4. To look at the sheets I make some engineering S/W macros

Option Explicit
Sub HideSome()
    Beast.Visible = xlSheetHidden
    BlankSheet.Visible = xlSheetHidden
    Customs.Visible = xlSheetHidden
    Data.Visible = xlSheetHidden
    Droid.Visible = xlSheetVeryHidden
    DroidData.Visible = xlSheetVeryHidden
    Export.Visible = xlSheetHidden
    ExportArmor.Visible = xlSheetHidden
    ExportSpecies.Visible = xlSheetHidden
    ExportWeapon.Visible = xlSheetHidden
    Force.Visible = xlSheetHidden
    HouseRules.Visible = xlSheetHidden
    NewDroid.Visible = xlSheetHidden
    OtherSpecies.Visible = xlSheetHidden
    SubTalents.Visible = xlSheetHidden
End Sub

Sub UnHideAll()
    Dim ws
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next
End Sub

5. Lots of commented out code and possibly unused macros. The Sub in #2 might never be called (even though it's in other Subs) so the undefined x might not be a problem

6. This is an AWFULLY big project, but what might help you get some fixes is to re-post a version with data, and some specific steps and information about what to do to cause it to fail

[SamT]

I found some 766 Names with sheet references, but they no longer Refer To any cells. In the attached File you will find a list of the names and the Worksheets they belong to. These "bad" names can be a real source of trouble.

It is some major drudge work, and requires someone very familiar with the Worksheets, but someone has to go thru all these names and either, find and replace the Refers TO value (a Cell Address) or they should be deleted if and only if it can be determine that the actual cell is no longer used.

Guess who we at VBA Express elect to do the drudge work

BTW, be sure and use the Workbook that you ran my previous sub on. We are trying to achieve a clean workbook to troubleshoot. Or at least as clean as possible.

[SamT]

Better add this to your "Engineering Macros."

Sub ShowEverything()
With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .Calculation = xlCalculationManual
End With

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
        .Visible = xlSheetVisible
        .UsedRange.Columns.AutoFit
        .UsedRange.Rows.AutoFit
        End With
    Next ws
With Application
  .ScreenUpdating = True
  .EnableEvents = True
  '.Calculation 'Leave it off while investigating and engineering
End With

End Sub

@All; is there an easy way to save the hidden Properties and sizes of all the Rows and Columns in Excel => 2007? The OP will need to hae the book returned to the original appearance after all the work is done.

[p45cal]

@All; is there an easy way to save the hidden Properties and sizes of all the Rows and Columns in Excel => 2007? The OP will need to have the book returned to the original appearance after all the work is done.

Perhaps something along these lines in a separate workbook:

Sub blah()
Set NewSht = Sheets.Add
NewSht.Cells(1, 1).Resize(7).Value = Application.Transpose(Array("Name", "Used Range", "Columns Hidden", "Column Widths", "Rows Hidden", "Row Heights", "Sheet Visible Property"))
Set xxx = Workbooks("vbaxpress54022SagaSheet 1.4.9 CHARACTER GENERATOR.xlsm")
With NewSht
  DestColm = 2
  For Each sht In xxx.Sheets
    .Cells(1, DestColm) = sht.Name
    .Cells(2, DestColm) = sht.UsedRange.Address(0, 0)
    cw = "": ch = ""
    For Each colm In sht.UsedRange.Columns
      ch = ch & ", " & colm.Hidden
      cw = cw & ", " & colm.Width
    Next colm
    .Cells(3, DestColm).Value = Mid(ch, 3)
    .Cells(4, DestColm).Value = Mid(cw, 3)
    rhd = "": rht = ""
    For Each rw In sht.UsedRange.Rows
      rhd = rhd & ", " & rw.Hidden
      rht = rht & ", " & rw.Height
      If Len(rht) > 32768 Then Exit For
    Next rw
      .Cells(5, DestColm).Value = Mid(rhd, 3)
      .Cells(6, DestColm).Value = Mid(rht, 3)
    .Cells(7, DestColm).Value = sht.Visible
    DestColm = DestColm + 1
  Next sht
End With
End Sub

It takes some time to run as the Languages sheet has a usedrange of A1:IU65536 (edit post posting - rectified), but it shouldn't be too difficult to convert the strings in some of these cells to arrays to restore the original sheets' appearance.
Will also need to know what this sheet does, and what's Star Wars?

[SamT]

Long, long ago, (summer of '83,) in a Carnival far, far away I went to a members only, after hours party at the Arcade Emporium, where that was a brand new Star Wars Machine.After a few minutes, I got my chance to fight the Death Star...

No the thing to remember in this story is that these after hours meets lasted fro at least two hours.

Where was I? ... Oh yeah, Killing the Death Star... Well, sometime in during the third level, I surrendered to The Force, Luke, The Force. The next thing that I remembered then and still the only thing I remember after that, is losing 4 lives in as many seconds.

WTH! I never lost a game that fast in my entire life!

Exasperated, I turned away from the Machine and and immediately noticed that all the lights, but one, were out and all the other machines were turned off.

"What is going on?!? What is wrong?!? " I thought.

The second thing, which really should have been the first, that I saw, was the crowd of other members standing around watching me. Then they started applauding my performance. It seems I had been playing on the same session for 2 hours and had scored 3 times the previous high score.

WOW!

Too bad I can't remember even one second of that time. Darn "The Force," anyway.

Star Wars games, of which there are many, are based on the Star Wars fantasy movies, of which there are also many.

[snb]

@All; is there an easy way to save the hidden Properties and sizes of all the Rows and Columns in Excel => 2007? The OP will need to hae the book returned to the original appearance after all the work is done.

- saving a copy of the workbook as csv
- open the csv-file and performing the things you have to do.
- copy the results of the csv-file into the oiriginal xls* file.

[SamT]

Multiple sheets. I saved it as an xlsx file for investigative purposes.

[darkwulf]

I apologize, for some reason I didn't get a notification that you had responded to my query.
About our understanding of programming: to be honest most of us know very little especially when it comes to VBA.
I will post again once I have everything cleared. Again thank you for your help.

Basically this sheet breaks down all the "rules" of the RPG and helps you put together a character to play with. It includes the different abilities or equipment you can pick from along with the prerequisites needed for those abilities/equipment. It isn't designed to replace the books written for the game entirely mere to help make creating characters faster, easier, and in a less complicated way.

EDIT: I have run the cleaner from your first post SamT. I am in the process of making sure the workbook works properly before moving on to the other task you assigned me.

EDIT 2: With the 766 Names with sheet references I understand that the sheet you gave me shows which sheet those belong to. Problem is I can't find where those names are? Where should I be looking to find those to fix the issue?

[SamT]

With the 766 Names with sheet references I understand that the sheet you gave me shows which sheet those belong to. Problem is I can't find where those names are? Where should I be looking to find those to fix the issue?

You will have to use your knowledge of the Worksheet to determine which cell the Name Refers To. Whoever initially designed the Workbook did a good job Defining Names. For Example, the Name "AFewManeuvers" an the "Feats" Worksheet is almost identical to "A Few Maneuvers" found in Cell C8. The "Refers To" will be Cell K8. In cells C21 and D21 are two terms that add together to produce a Name, "ArmorProficiencyHeavy," which Refers To Cell K21.

Change the "Refers To:" part for only one name at a time, (unless there are two similar name such as TalentSpaceBaseClasses & TalentSpacePrestigeClasses2,) then Close the Defined Names Dialog and test the Workbook in a manner that uses that (those) Name(s.)

Look in one book (Workbook) on sheet (Worksheet, Tab) "Feats" for "Talent Space Base Classes." If, (when, I looked for this Name,) you don't find it, go to the VBA Editor and click the mouse anywhere in the code, then Press Ctrl + F to bring up the "Find" Dialog. Enter "TalentSpaceBaseClasses" in the "Find What:" box, Check the "Current Project," Radio Button, and Press "Find Next." If, (when, I checked this name,) the name is not found, you can safely delete the Name.

If you find the Name in the code, but cannot determine what Rage it Refers To, then make a list of those name, maybe we can determine the solution.

BTW:

Basically this sheet breaks down all the "rules" of the RPG

The correct nomenclature is Workbook, not Sheet. Sheet refers to a single Tab in Excel.

[snb]

@SamT

what Rage it Refers To

Is this a new Excel Property ?

[SamT]

@ snb,

Absolutely, it is a User.Property invoked when Ecxel doesn't perform as Expected.

The User.HammerIntoSubmission.Method is closely related

[snb]

I have to amend my website .....