Working For months, now broken

[JPARKHURST]

Did I miss an update, something with excel or ...?

This code is (all stored in a module, save for the workbook_open):

Public Function CnSwS(wb101 As Workbook, ws101 As Worksheet, sws101 As String)  'Check to see if Worksheet variables have been established, and if not, establish them!
    If ws101 Is Nothing Then
        Set ws101 = wb101.Sheets(sws101) 'this is the line erring
    End If
End Function

has been working for months, and is now not working. "Run-time error 9" Subscript out of range

It is called here:

Public Sub FireOpen()
    'Check & See if things need to be reset
    'Set wbThis = CnSwB(wbThis)
    Call CnSwB(wbThis)
    Call CnSwS(wbThis, wsDashboard, "Dashboard")
    Call CnSwS(wbThis, wsWorkingDispatch, "Working_Dispatch")
    Call CnSwS(wbThis, wsJobDispatch, "Job_Dispatch")
    Call CnSwS(wbThis, wsShortages, "Shortages")
    Call CnSwS(wbThis, wsShippingRequirements, "Shipping_Requirements")
    Call CnSwS(wbThis, wsResprayReport, "Respray_Report")
End Sub

and it is called on doc open.

Public Sub Workbook_Open()
 Call FireOpen
End Sub

Here is the workbook variable set

Public Function CnSwB(wb100 As Workbook)
  'Check to see if Workbook is established, if not, establish it!
    If wb100 Is Nothing Then
        Set wb100 = ThisWorkbook
    End If
    'CnSwB = wb100
End Function

I am at a loss. Any help or direction would be appreciated.

 

[RoryA]

I don't know why you're using functions when they don't return anything (they could, and probably should, as they are weirdly written in all honesty), but you must have an incorrect sheet name. What are the variable values when the error occurs?

 

[JPARKHURST]

No good answers for you there. I noticed that when I was pasting it in here. Old code, and i was new[er] then. I have corrected it. I am guessing i was initially going to try to return an object, but learned i did not have to.

I'm also guessing that the file is just somehow corrupted. I opened up the same file from two days ago and it works fine (I archive it daily). There were no changes that I can think of in the past two days, and definitely not to this bit of code, as it is the first few lines i had ever written in vba or excel.

So, question solved, I guess. It seems excel files get corrupted awfully easy, I wish that could be avoided or at least I understood why.

Thanks for the help anyway.

Also, you say they are weirdly written, do you have any suggestions? I'm just production and understaffed, so I have to automate things to be able to get work done. If there is a better way to do what I need, I'm all ears.

Jon

 

[RoryA]

Are you sure someone didn't accidentally alter a sheet name? That would cause the error you mention.

The last function seems fairly pointless - why not just use Thisworkbook?

 

[JPARKHURST]

LOL - well, yeah, that seems VERY obvious now that you point it out. It was a misunderstanding of referencing workbooks and some of excel's internal variables (like ThisWorkbook). That's a good point, and I will incorporate it. In general this bit of code was simply set to reduce the possibilities of typos, but that one is really redundant.

I probably need to make time to run through this and see what other ideas i have which are in need of cleanup.

And no, no sheet names were altered. Same same on both sheets, same old, bad code on both. Works on the one two days ago, not on this one. head scratcher, but i have had a LOT of problems with corrupt excel files. Sometimes they blow up to several megs, sometimes they just toss errors when they did not before. i haven't figured out the logic in it.

Thank you

Jon

 

[RoryA]

You aren't using Shared Workbooks are you?

 

[JPARKHURST]

No, sir. I'll double check and make sure nobody changed it.

I had something very strange happen yesterday, now that I think of it. When I would save, it would tell me my files my have been changed by another user. It's a fairly small shared folder, so I don't know who would have had access to been in it. But it asked every time if i wanted to save a copy or overwrite. After 2-3 times of this, I immediately saved, then re-opened, and it did not tell me the file was already opened, so I'm not sure what was going on there. I would say i can't believe i forgot that, but ... it was one of those days.

 

[RoryA]

That sounds a lot like the workbook is shared. That would explain both bloat and corruption.

 

[JPARKHURST]

By shared, do you mean on a shared drive? I didn't realize Excel specified, if so. I can easily save this locally.

If shared you mean do other people use it? Not to my knowledge. I send a copy out daily, but the location - while shared between 6 people or so - is not widely used. I can check to see, though.

 

[RoryA]

No, I mean the "Share workbook" option on the Review tab.