VBA Code vanishing

reacher14

New Member
Joined
Jul 14, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
I was unable to find someone with the same issue that has a answer so I am posting it here in case it happens to anyone else.
It appears something happen to a saved excel file because when I open the file the VBA code has vanished I open to the vb window and I can see all my modules listed. However when I try to click on any module or worksheet to see the code all that comes up is an empty white box (no code). See attached 'VBA gone image of what I mean.
I will continue to try and find a solution, just wondering if this has happened to anyone else?
The weird thing is if I copy the same file to another machine the code in this same file reappears and works fine ??? but on the other hand if I try and save the file when it is in VBA blank state I get a message Removed one or more invalid formulas. see Error message Image, the formulae it removes are a Stock market addin from Gaiersoftware (Gaier Software - Excel Quotes Add-In) which has worked fine for many years.

Frustrating as I am not an experienced coder at all, the code I have rejigged from websites but all the code does is copy 1 column numbers to an history sheet where it adds the column of figures as values to the next blank column. Also why the formulae issue after that it has worked for Years
Any clues to this bug Excel Version 2019 -Thanks
VBa gone.jpg

Thanks!
 

Attachments

  • Error message.jpg
    Error message.jpg
    41.3 KB · Views: 11

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What file format are you saving the file as....xls, xlsm ?
It needs to be saved as an .xlsm file format
 

reacher14

New Member
Joined
Jul 14, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi yes thanks - it is saved as xlsm , the file has worked fine for year or more if you look at the images of the modules and file attached you can see the file extension.
One other thing if I open the file I then can not save it, I get a message saying error detected then proceeds to strip out formula with a message "Removed some invalid formulas." how can they have worked for a year or more and no be invalid plus i have further sheets working fine with same formulae.

One odd thing that I did not notice before if I move a module around it leaves a shadow see module
I also when I go back from the VBA editor to the Excel Sheet "An unexpected error has occurred. "Auto recovery has been disabled for this session of excel"
I have a similar files with identical VBA code - same data set but different mix which opens fine see Barry this shows what should be there .
It is frustrating
 

Attachments

  • Module-0000.png
    Module-0000.png
    101.7 KB · Views: 8
  • Barry (2).png
    Barry (2).png
    129.2 KB · Views: 8

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Can you check to make sure you haven't installed any new addins OR recently upgraded your version of Excel.....
Have you tried the file on another machine....if you have the same issue, your file may be corrupted !!
 

reacher14

New Member
Joined
Jul 14, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Nothing added other than windows updates nothing else,
I have copied the File over to USB and then got my partners to open it at her works laptop which is a powerful CAD Workstation Core i7 (my Laptop which has a XEON processor if it is relevant) also that Cad station runs Excel 2013. In this opens and all works fine. So she saved it as a new name on to the USB.
Now it gets strange on my laptop opening from the USB I can open do all the VAB code etc so the saved and renamed file and it is fine.
Now I thought I would see what the original did and strangely, I can open the original copy of the file that is on my USB and it works fine - but not the identical file that sits on my Hard drive. So does that mean the file itself is not corrupt ? Is there something else going on with my hard drive/Virus Security or any ideas.

Thanks all
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Weird !!
The only thing I can suggest is you have something on your PC / Laptop that removes VBA code....check your AntiVirus settings AND your Excel Trust Center settings. They are at File>>Options>>trust Center>>trust center settings>>macro settings
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
try to no work from any USB drive : copy first the file on a local drive, open it, work, save & close it​
then if necessary copy the local file back to the USB drive …​
 

Forum statistics

Threads
1,148,189
Messages
5,745,243
Members
423,937
Latest member
The Great Dane

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top