Excel VBA Run-time Error '32809' - Trying to Understand it

Question

A colleague at work made some changes to one of our macro workbooks and now on my PC only I receive the dreaded Run-time Error '32809' when I attempt to run it. This latest version runs fine on his PC and another colleague's PC that we tested it on. The previous version runs fine on all of our PC's, all of which are running Excel 2010.

The error is thrown when the macro attempts to Select the Worksheet index 1, named "Info". I know that Select/Activate is not required but am just working with this Workbook for now and am trying to work out why I alone would receive this error.

I have tried:

1. Reboot/Power Cycle
2. Saving a Copy of the Workbook
3. Cleaning out Temp Files with CCleaner
4. Researching online
5. Checking for ActiveX Controls (Uses Form Controls)

All with no success. I then had a bit of a mess around in the immediate window and discovered that even a simple:

Debug.Print ThisWorkbook.Worksheets(1).Name

would throw the run-time error which lead me to believe that somehow that Worksheet had broke. I added a couple of events to the Worksheet including _Activate and _Change but none would fire even after confirming that:

Application.EnableEvents = True

I added a simple Test Sub as follows:

Public Sub Test()

    Dim ws As Worksheet
    Dim sheetNum As Integer

    For Each ws In ThisWorkbook.Worksheets
        ws.Select           ' Selects all Sheets Without Error
        Debug.Print ws.Name ' Prints All Worksheet Names Fine
    Next ws

    Set ws = ThisWorkbook.Worksheets(1)
    ws.Select               ' Selects Sheet 1 Without Error

    ' Prints all but sheetNum = 1, Run-time Error 32809
    For sheetNum = 7 To 1 Step -1
        Debug.Print ThisWorkbook.Worksheets(sheetNum).Name
    Next sheetNum

    ' Run-time Error 32809
    ThisWorkbook.Worksheets(1).Select

End Sub

Has anyone run into anything similar to this or know of what causes this error to occur only on some PC's?

Highspirited

That is because the worksheet is corrupted. Try this. Create a new sheet and copy every thing from that to the new sheet and then finally delete that sheet. It will work. just tested and tried it. – Vyner

Hi Siddharth - thanks for your response. This is the short-term solution I have opted with but unfortunately attempting to delete the existing sheet causes Excel to crash so I just renamed it. The question remains though: why is it only corrupt when opened on my PC? – Highspirited

Sorry, I don't have an answer to that. In fact, I tested it with THIS FILE and I was able to reproduce the issue. Even after renaming the subs, the problem was still there. Deleting and re-creating it fixed the issue. – Vyner

In addition to @SiddharthRout, soloution. Lastly, if that doesn't solve it, you can also export your modules and remove them / Save / Close / Open, Re-Import, Save. Lastly, I'd reinstall Excel also as a last ditch effort only if all the other solutions failed and it's still only happening on your machine. – Chino

Answer 1

In my case following helped:

1. Save file as .xlsx (macro-free) - all macros would be erased while saving;
2. Open source file with macros and copy modules to the .xlsx file;
3. Save file as .xlsm - full recompile performed.

Afterwards everything started working normally. I had file with 200+ sheets and 50+ macros and posting comments in each module didn't help, but this solution worked.

Hajji

this worked well for me, one note though. Don't close the xlsx file before you re save-as xlsm, this will keep all the vba code without needing to copy and paste it. – Affinal

Answer 2

I've been struggling with this for awhile too. It actually occurred due to some Microsoft Office updates via Windows Update starting in December. It has caused quite a bit of a headache, not to mention hours of lost productivity due to this issue.

One of the updates breaks the forms, and you need to clear the Office cache as stated by UHsoccer

Additionally, another answer thread here: Suddenly several VBA macro errors, mostly 32809 has a link to the MS blog with details.

Another of the updates causes another error where if you create or modify one of these forms (even as simple as saving the form data) it will update the internals of the spreadsheet, which, when given to another person without the updates, will cause the error above.

The solution (if you are working with others on the same spreadsheet)? Sadly, either have everyone you deal with use the office updates, then have them clear the office cache, or revert back to pre Dec '14 updates via a system restore (or by manually removing them).

I know, not much of a solution, right? I'm not happy either.

Just as a back-story, I updated my machine, keeping up with updates, and one of the companies I dealt with did not. I was pulling out my hair just before Christmas trying to figure out the issue, and without any restore points, I finally relented and reformatted.

Now, a month later, the company's IT department updated their workstations. And, without surprise, they began having issues similar to this as well (not to mention when I received their spreadsheets, I had the same issue).

Now, we are all up on the same updates, and everything is well as can be.

Adabelle

I Installed the Microsoft security update here and everything was fine for Excel 2010. – Emlen

Answer 3

I have encountered similar (nearly unexplainable) behavior

Found a reference to deleting .exd files under the directory C:\Users\username\AppData\Local\Temp Located one in each of the directory Excel8.0 and VBE. Typical name is MSForms.exd

Google "Excel exd" or "KB 2553154" From my perspective, it is a completely unacceptable situation which has been there for at least a month now.

Ultrasonic

Answer 4

I suffered this problem while developing an application for a client. Working on my machine the code/forms etc worked perfectly but when loaded on to the client's system this error occurred at some point within my application.

My workaround for this error was to strip apart the workbook from the forms and code by removing the VBA modules and forms. After doing this my client copied the 'bare' workbook and the modules and forms. Importing the forms and code into the macro-enabled workbook enabled the application to work again.

Tidy

Answer 5

This worked for me using excel 2010 and getting the same error when opening a macro-enabled .xlsm file.

-after dismissing the error dialog, do "save as" tab-delimited .txt file. click OK for

...only active sheet.

...functions not saved.

-then "save as" again, but this time select macro-enabled .xlsm format. (to another file or overwrite original doesn't matter, but save as another feels safer.)

-close out excel.

-open the newly saved .xlsm file. In my cases, the error messages went away and the macros are working.

Knawel

Strangely this method worked for me. I followed the steps that James outlined on the machine that was breaking the code and afterwards it started working. Caution! You loose the digital signature of the project so you need to enable all macros or have to deal with the security warning every time you open the workbook. If you don't want to go this route, the office update also solves the problem. Use windows update to install every office update you find – Stacistacia

Answer 6

It seems that 32809 is a general error message. After struggling for some time, I found that I had not clicked on the "Enable Macros" security button at the below the workbook ribbon. Once I did this, everything worked fine.

Vanillic

Answer 7

In my case, the error occurred executing a macro in: Sheets("own sheet one").Select

copy the sheet into another with other name, ie. "oso", then delete the original sheet and renamed the new one as "own sheet one"

Excel 2013

Labialized

Answer 8

My solution ( may not work for you)

Open the application on a machine that is flagging the error. Change the VB code in some way. ( I added one comment line of code of no consequence into one of the macros)

Sheets(sheetName).Select 'comment of no consequence

and save it. This causes a recompile. Close and re-open - all fixed.

Hope this makes sense and helps

Grant

Precipitancy

Answer 9

Ok, this might be weird. Anyway one of my colleagues had this error and we tried the edit VBA compile whatever. But the thing is, just copy the excel file to the desktop. And it worked. The Excel file was originally in a network drive. This worked, this is my answer to this issue.

Soothe

Answer 10

I have removed all ActiveX controls from sheet and now it works smoothly without any error messages. That's my solution.

Crore

I had an ActiveX button on the sheet causing problems. Trying to delete it crashed Excel. Instead, I deleted the formula below it =EMBED(etc...) and used "Cut" for the button, instead of delete. It stopped crashing, the macros started working again, and after closing and reopening I was able to recreate the button. Something must have been corrupted... – Tylertylosis

Answer 11

I did the following and worked like a charm:

1. Install Office 2013 (I haven't tried with 2010 but I think it would work too).
2. Install Office 2013 SP1.
3. Run Windows Updates and install all Office and Windows updates.
4. Reboot computer.
5. Done.

This worked for me in two different computers. I hope this will work in yours too!

Collected

Answer 12

I exported the VBA Module - resaved the file, then imported the module again and all is well

Puma

Answer 13

I have found the solution. Just download the following Office update: https://support.microsoft.com/en-us/kb/2920754

Choose between 32-bit or 64-bit and install.

Worked for me, hope it works for you.

Regards

Ong

Answer 14

I have the same problem and found that this is the problem of Microsoft vulnerabilities.

It works for me when I install these update patches. You can find these patches on www.microsoft.com .

1. If your office 2010 version is SP1, you need download and install office SP2 pack first. Update patch name is KB2687455.

2. Install update patch KB2965240.

   This security update resolves vulnerabilities in Microsoft Office that could allow remote code execution if an attacker convinces a user to open or preview a specially crafted Microsoft Excel workbook in an affected version of Office. An attacker who successfully exploited the vulnerabilities could gain the same user rights as the current user.

3. Install update patch KB2553154.

   A security vulnerability exists in Microsoft Office 2010 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability.

Invincible

Answer 15

I got this problem after adding a combobox with VBA-code in a particular sheet. Testing the code etc was no problem at all, until I opened the sheet again. Stackoverflow and Microsoft comes with many work arounds, but no real solution. I use excel 2010 (dutch version) with W10 (upgraded from W7). I think the problem is in Excel 2010. In my case, I got an error on the line to unprotect a sheet by VBA, in a module which wasn't changed for a long time.

Ok, this is how it is in my opinion: There was a security issue in FM20.DLL, for whic MS had an update in Q1 2015. This update installs a new FM20.DLL, however the language packages (FM20NLD.DLL and FM20ENU.DLL) were not updated. Possibly, if you don't use a language pack, you don't have this error. In my opinion, the language parts should have been updated as well (but there is no update available)

Ok, deleting the .exd-files works for a moment. This is a temporary work around. MS doesn't has a real solution, but recompiling the code 'solves' the problem.

That is why some people said: 'Add a comment and the problem is solved'. Yes, adding a comment forces a recompilation.

I agree, this is still a work-around, but not a temporary work around. So: 1. check in which part of the VBA-code the error exists 2. add a comment by which a recompile is forced. 3. save the project again

that's all

Newmann

Answer 16

I have similar problem. The VBA (ActiveX) code had been working fine on 20+ computers for a few years, the problem suddenly surfaced out when one new colleague joined, the code doesn't work on his new laptop although the Excel version is same, it showed Run-time Error '32809'. I have checked all security settings of ActiveX and Macro, all correct. I did some experiments. I found the Code created in my computer will not work on my colleague's new laptop. However if I create code in my colleague's new laptop, it works in my computer. Once I saved this code in my computer, it won't be able work in my colleague's laptop any more.

By checking the error in details, the problem is that all code written in my computer, my colleague's Excel won't recognize them. Debug>>Compile will show compile error, even "DIM ..." is not recognized.All the ActiveX Controls , Comboboxs, Buttons... the property-name were randomly assigned a new one. For example, I have a button name as [AddNew], once open in my colleague's new laptop, it is re-assigned as [commandbutton54] . It even can not recognize the existing worksheet name.

After researched all the solutions , I found Delete "*.exd" file doesn't work(Even empty the recycle bin".). 'Add comments' doesn't work...

In the end the solution is:

Step1: Make a copy of the sheets with codes(sheets w/o code is not required),

Step2: Delete the original sheets,

Step3: Rename copied sheet to their original name,

It starts to work. I found all ActiveX controls get back their original name in their property. It just took a minute. Hope it help those who are facing the same problem. Note: No need to save the file to *.txt or rename to xlsx...

Rhaetic

Answer 17

Error 32,809: Copying the corrupt sheet to a new sheet and changing the name or deleting the corrupt sheet works for me. Additionally, I went to the SHEET Module of the corrupt sheet and removed the coding from the sheet Module associated with the corrupt sheet. That ALSO cured the problem for me. [ The sheet modules can have routines that are triggered by events specific to that worksheet.] So in my case, I think it was a corrupt Sheet Module, not corrupt data on the worksheet itself.

Parted

Answer 18

I was trying to resolve error/corrupted file. I tried multiple times, folllowed some articles in google, nothing worked, restarted, tried to renamed, nothing resolved, finally I copied file to desktop location and opened the file, renamed it, that worked fine..

Wald

Answer 19

I know this is an old thread but since there isn't an accepted answer yet, here is another solution that worked for me. This is my go-to fix for most VBA issues that don't have an obvious fix.

1. Before hitting "Enable Content", open VBA and go to Tools > References
2. Uncheck "Microsoft Visual Basic for Applications Extensibility 5.3"
3. Save the file, recheck the above reference, and save the file again
4. Close the file, open it back up, and hit "Enable Content".

Pengelly

Answer 20

Deleting all instances of *.exd resolved it for me.

Wigwam