0845 643 64 63

Macro

Excel crashes when enabling macros

I’ve had a problem with Excel 2013 crashing a number of times when opening/editing spreadsheets with macros. When I click ‘Enable Content’ to enable macros, the Excel window fades out, goes unresponsive and then crashes with a glorious “Microsoft Excel has stopped working, Windows is checking for a solution to the problem” message. Unfortunately Windows never seems to be able to find the elusive solution that we need.

excelmacrocrash1excelmacrocrash2

I’ve found that the spreadsheets tend to stay working whilst you’re looking at them (if you don’t click ‘Enable Content’), however it crashes when you try and do anything useful such as copy/paste, etc. Sometimes Excel just crashes when opening the file, even without enabling macros. This is seemingly caused by a corrupt compile of the macros. Yes I know it’s VBA code which is interpreted and not compiled, but I’m over-simplifying here.

There a a couple of solutions to this:

1) Before enabling the macros try adding a new blank sheet, and then save the spreadsheet, reload, and sometimes it corrects itself. But not always.

2) Recompile the macro

  • Open the Excel spreadsheet (but don’t enable macros yet)
  • In the ‘Developer’ ribbon, click ‘Visual Basic’

excelmacrocrash3

  • On the ‘Tools’ menu, click ‘Options’
  • Then on the ‘General’ tab, untick ‘Compile On Demand’, then click OK

excelmacrocrash4

  • Then scroll down in the ‘Project’ window, right click on ‘Modules’, then Insert a new module

excelmacrocrash5

 

  • Then just close the Visual Basic window, save the spreadsheet and close Excel.
  • Hey presto, when you then load your spreadsheet you’ll be able to enable macros, and Excel won’t crash.

Thanks to Paulina from Microsoft for helping me find this solution – it’s saved my bacon a few times now!

Frog-Blog-Out

Clearing SSRS Query cache

When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data.

A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The next time you preview the report SSRS will be forced to requery the source.

To save time, I use the following macro to take care of it.

Press ALT+F8 to open the Macro Explorer, and add a new module called “RemoveRDLDataFiles” under MyMacros. Edit the file and add the following code to the file. (This is for SQL Server 2008, you may need to tweak the references for 2005).

Imports System Imports EnvDTE Imports EnvDTE80 Imports EnvDTE90 mports System.Diagnostics Imports System.IO Public Module RemoveRDLDataFiles Sub RemoveRDLDataFiles() Dim project As Project Dim Folder As String project = DTE.ActiveSolutionProjects(0) Dim fi As New FileInfo(project.FullName.ToString) Folder = fi.DirectoryName For Each FileFound As String In Directory.GetFiles(Folder, "*.rdl.data") File.Delete(FileFound) Next End Sub End Module

You can then run it by either double clicking on the macro, or assigning a keyboard shortcut to it (via Tools, Customize, Keyboard).

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon