Excel VBA Editor crashes

Question

Something's causing VBA Editor to crash in Excel for Mac.

All used to be fine. Then I started to get a perpetual freeze, or a complete crash and exit, when typing (often, I think, but wouldn't swear, towards the end of a line). It would usually survive a few (but not many) keystrokes. I hadn't been trying to do anything out of the ordinary or especially advanced.

I assumed some corruption in the file. I tried deleting large areas of it, and also porting to a different file. With no improvement, I suspected Excel might have become corrupted, so uninstalled and reinstalled. The problem persists. I had taken to writing code in word, copying it in, and correcting it - with regular saves.

It's now refusing to save the file, claiming either that 'errors were detected' and that it would 'minimally save the file' (but where it said it was saving it doesn't exist) or claiming to be unable to save at all.   All snail-like limping on has ground to a halt.

Clearly something went awry somewhere at some stage and needs to be put right.

I'm out of ideas and am completely stuck.  What might the problem be, and what do I need to do to get the VBA editor working like a dream again?

Thank you

Answer 1

I've had an update e-mail from the Excel team. They've 'finally cracked' (their words) the cause of the problem, fixed it, and it will be in a release in October.

For the record, when I first contacted them (after about six weeks of trying to identify what *I'd* done wrong) I thought I'd probably not get very far or even noticed. They were actually very receptive and human. Full credit to them.

All should be well in the foreseeable future.

Answer 2

Just a courtesy update:

I heard back from the Excel team (after a prompt) and was told that VBA Editor was working okay for them in my file, but that if they opened it with Macros Enabled, it crashed, and could I try it with older versions.

Buoyed up by reassurances here, I reinstalled 16.62 (same problem) and then 16.61.1 - and that works like a dream. That echoes what Mukesh_465 said. It still seems odd that more (than Mukesh and me) aren't commenting on it, or that Microsoft didn't give any hint that my problem wasn't entirely unexpected to them.

For now, I have a working version at 16.16.1, but asked them for further guidance beyond that. I'll update if something definitive comes in on that.

Answer 3

Thank you Jim

I am using Microsoft Excel for Mac version 16.63.1 and Visual Basic version 7.1

I tried what you suggest and it's still doing it. Since the export/import I've had a couple of attempts with various outcomes.

The first time I typed:

For v_count = 0 to 2

... and when I was doing tabs or backspace or whatever to indent the next line, it crashed

Time 2:

For v_count = 0 to 2 [try to save, it said it had problems and couldn't, but I carried on, without indents etc....]

if v_DateArray [and when I went to type ( it hung, needing me to hard reboot again]

In general, the hangs / crashes etc. have happened in multiple different places that I somehow manage to limp past, often by copying similar stuff from elsewhere to minimise keystrokes on that line. They usually happen such that it's 'what exactly did I just do?' I *think* it's particularly likely when I'm doing something which requires parameters or the object model. At one stage I suspected one or all of " ( ) , but wouldn't swear to it. Having said that, that For v_count line was causing problems before!

I've also tried setting off to retype the whole shebang into a new file. That went so far, through module-wide constant declarations, a simple subroutine, into a more complicated one with Dim rows and comment lines, and I *think* went at

v_LastDate = Sheets [crashed on ( or maybe the following " ]

If anything, you know how the parser kind of looks over your shoulder and prompts or corrects... it's maybe when I do something that prompts it to do that, and that 'that' isn't 100% simple.

It was always fine before this.

m

Answer 4

Hi Mark,

Please let us know what version of Excel you have. To find out the version, use the Menu Bar and choose Excel > About Microsoft Visual Basic. It will display the version (the current version is 7.1. Then, on a worksheet, use the Menu Bar and choose Excel > About Excel to display the Excel version. The current version is 16.63.

The most common causes of VB Editor crashes are coding errors or bad fonts. Does the editor crash when coding something specific?

There is an easy way to fix corruption in a VBA code module. In the VB Editor, select the module in the Project panel, then use the File menu and choose Export File. Make sure the file was saved. Then delete the code module and save the workbook. Quit Excel. Then re-open the workbook. Restore the module using the VBE Editor's File > Import File feature.