Unable to access VBA editor after running macro

[marcusblackcat]

Right - here's the strangest thing I have ever seen in Excel (despite using it since it was invented!)

I have Excel 2016 64bit and have created a file which copies and pastes data when prompted to refresh reports.

I have created reports running from this data which, on my office 2016 64bit machine work absolutely superbly.

However, I have sent this file to 2 people who are using 32 bit version of excel 2010 and it appears to have corrupted their vba.

Both users now get a blank grey screen without as much as a project explorer (or menus) when trying to access the VBA editor. This happens on all workbooks (including a brand new blank one) and, due to this, they can;t access and code to create new or update existing.

The strange thing is - this happened on a single macro. The macro works fine on my computer but seems to have corrupted something on theirs. I have tried repairing the personal.xlsb but I can't find it on their machines (or mine for that matter) (Windows 10). I have been coding for a while but I am still using the "way I know" method which is potentially not the best. I don't really want to attach the workbook as I don't want anyone on here downloading it and breaking their Excel - this is more in hope that someone has seen this before and knows how/why it happened and how to repair it.

There are 3 buttons on the main page - one which imports the data, builds the columns required for the reporting and refreshes the reports (this works fine even without access to the vba editor), one which is a simple navigate to the dashboard which works. The third one rebuilds reports on 5 pages depending upon selections in dropdowns on the control panel. It's the third one which crashed their vba.

'Macro called on button click
Sub Reporting()
ThisWorkbook.RefreshAll
hidesheetsREPORT
Dim slItem As SlicerItem, wb As Workbook, LocCa(1 To 3) As SlicerCache, _
    PCNCa(1 To 3) As SlicerCache, i As Byte, Loc As String, PCN As String, a As Long
Set wb = ThisWorkbook
Loc = Worksheets("Control").Range("WFTLocality")
PCN = Worksheets("Control").Range("WFTPCN")
Highlights1
Highlights3
BuildWFPg1
NewSymptomsReport
DoSReport
Worksheets("Weekly WF Report Pg 1").Activate
For i = 1 To 3
    If i = 1 Then
    Set LocCa(i) = wb.SlicerCaches("Slicer_Locality")
    Set PCNCa(i) = wb.SlicerCaches("Slicer_PCN")
    Else
    Set LocCa(i) = wb.SlicerCaches("Slicer_Locality" & i - 1)
    Set PCNCa(i) = wb.SlicerCaches("Slicer_PCN" & i - 1)
End If
Next i

For i = 1 To 3
a = 0
PCNCa(i).ClearManualFilter
LocCa(i).ClearManualFilter
If Loc <> "All" Then
    For Each slItem In LocCa(i).SlicerItems
        If slItem.Name <> Loc Then
            slItem.Selected = False
            Else
            slItem.Selected = True
        End If
    Next slItem
End If
If PCN <> "All" Then
    For Each slItem In PCNCa(i).SlicerItems
        If slItem.Name <> PCN Then
            slItem.Selected = False
            Else
            slItem.Selected = True
        End If
    Next slItem
End If
Next i
End Sub

Let me know if you want the called macros adding and I'll put them on - just didn't want the post to be massive!!

 

[rlv01]

I imagine those 2010 people you sent the file to are not very happy with you right now. What you have posted is a top-level macro and it's not really possible to tell much, because there is not much there. Most of the action is taking place in the six macros being called from your top-level macro, and one of those is the likely culprit. Are you making any windows API calls anywhere in your code? That might be a place to start looking. Though I suspect what you really need is your own copy of Excel 2010 so you can start testing to find the bit of code blowing things up. Ideally that would be on a VM install so that when it blows up, you can quickly reset for the next test run. Do you know if it will also blow up a 32-bit install of Excel 2016?

 

[Joe4]

Also note this.

When writing VBA code for other people who will be using it, if possible, it is always best to create the code in the oldest version of Excel owned by your user group.
So if you have users using Excel 2010, it is best to create your VBA code in Excel 2010. That way, you are sure to not use any new functionality in 2016 that won't work on 2010.

 

[marcusblackcat]

Thanks both - I am actually about to get an office 2010 laptop temporarily as a testing machine so will let you know how I get on. Luckily those people are working fine today with everything except this file so fingers crosses all works. The code all worked fine until the people I am coding for asked me to hide worksheets so users could not navigate incorrectly. I hid the worksheets and "unhid" them in the code and this is when it crashed.

Luckily, those I sent the files to do not code anything. My team does that and all the macros run fine except this one.

 

[marcusblackcat]

Well it's a strange one.

All the VBA came back after the PCs were restarted and all worked except this one macro.
Turns out that one of the macros was activating a worksheet which, on activate, had a "hide worksheets" macro as part of it. In the 2016 version it seems you have to tell the code to enable events whereas in 2010 you have to tell it not to!

I took the code out which was activating the worksheet in question and all is 100%.

I'm no high level programmer. only been doing it for a couple of years but trying to learn more by the day! (And I love it too!!). Every day is a school day!!

Edit: As I'm also learning this message board thing, Do I need to close this thread now? Can;t seem to find anywhere to do it!

 

[Joe4]

In the 2016 version it seems you have to tell the code to enable events whereas in 2010 you have to tell it not to!

You shouldn't need to have to explicitly tell your code to eneable events in any version of Excel, unless you told it to disable them earlier. Where I see people often get caught up is that there is a "disable events" line in their code and an "enable events" line later on in the code, and there code gets interrupted between the two (due to error or stepping through code), so the "enable events" is not run, so they need to explicitly re-enable events.

Or possibly you mean "enabling macros and vba". Depending on the individual user's security settings and whether or not the file is in a Trusted Location, they may need to explicitly enable macros and vba.

Do I need to close this thread now? Can;t seem to find anywhere to do it!

No, just posting back that you problem is solved is sufficient.

 

[marcusblackcat]

You shouldn't need to have to explicitly tell your code to eneable events in any version of Excel, unless you told it to disable them earlier. Where I see people often get caught up is that there is a "disable events" line in their code and an "enable events" line later on in the code, and there code gets interrupted between the two (due to error or stepping through code), so the "enable events" is not run, so they need to explicitly re-enable events.

Or possibly you mean "enabling macros and vba". Depending on the individual user's security settings and whether or not the file is in a Trusted Location, they may need to explicitly enable macros and vba.


No, just posting back that you problem is solved is sufficient.

Same code worked fine in 2016 but didn't in 2010 so not exactly sure how that happened but at least it's working in all versions now.