Anybody finding perfectly working xlsm files suddenly finding the code cannot be reached and the code unviewable despite the modules showing up in the Project Viewer?
It probably isn’t an XLS Padlock issue but any thoughts welcomed.
For clarity the security settings do allow macros to be run and on opening the files you do not get the “Macros blocked” warning. What appears to happen is that when the code tries to run it is then being stopped as being unreachable.
After closing the file it is then hit and miss as to whether on opening again you are given the option to open the file in safe mode - which may give you the option to “recover” the file - but more often than not the file becomes worthless. It is not even possible to remove the troublesome modules and re-load them from your backups - as the attempt to remove the module simply performs no action - and if you rename the module with a view to importing from backup then the process of importing just triggers the wonderfully named “Microsoft Not Responding” dialogue.
Have reported the issue via the MS Admin account but not hopefully for an explanation…
From technical perspective, I think it’s a good strategy, to:
1.) check if the specific (.xlsm) file is blocked by Windows. In this case, before you open this file, you may right click on it, select “Property”, check “Unblock” checkbox, click on “Apply” and then on “OK” button.
2.) check your V.B.A. code (especially Load events and public constants). Sometimes, I have faced some issues similar to yours, when I’m using outdated references or references blocked from Windows services (TSR (Terminate and Stay Resident) utilities, Antivirus, standalone .exe (running in parallel), …etc.)
3.) check user settings of your project path and give permissions to everyone. You may use the following command from CMD to do so: ICACLS “[xlsm path]*” /grant “Everyone”:F /T
Item 1) is on my list but usually this is checked if needed and not an issue. On loading the typical blocking or enable message is not displayed. So I can generally rule this one out.
Issue 2) is definately a possibility as I use the Ribbon Callback method to load my specific ribbon menu which is then used to run the required VBA. I do occassionally get issues here but that inevitable leads me back to a wong configuration in the Office RibbonX Editor. Sometimes I can get a restore point file and continue successfully. Once the file is loaded there are none of these issues. So presumably whatever is causing it is happening between the closing / saving of the file to the opening of the file again. However on loading I notice that the defined ribbon menu has been loaded although not operational. I can check whether the Open/Close Workbook are performing efficiently. Thus any code executed before the file is “ready for use” is something I’ll look closely at.
With regard to outdated references – what specifically did you mean – for example named ranges being called or something completely different?
I presume by 3) you mean user permissions over file directories? The files in this instance are not being shared. Thus if so, probably not my issue in this instance.
If I am correct in believing the error is causing the VBA project code to be disabled and irretrievably so, then I feel justified in asking Microsoft Support to provide an error log of what is causing it. I doubt they’ll help!
I did wonder whether it was worth de-installing and re-installing Excel – presumably not?
Thank you for your prompt response!
As “outdated reference”, I mean the loaded library (.ocx, .dll, …etc.) to your xlsm file (you can find it under the default menu: VBE=>Tools=>References) which is disabled, or visible as a strike-through text. In this case, you have to re-install the specific library and/or register the respective ActiveX controls.
Furthermore, I would like to ask you if you unload any part of declared variable or pointer before you close the workbook or all it’s instances. I’m asking you, because it’s crucial to know if you are trying to open the specific workbook without any previous (non-visible Excel-based) conflict.
Hi Dimitris
By opening from a restore point I can see the following – and only those visible as checked are actually checked. All others on the scrolled list are unchecked. Nothing is greyed out or struck-through.
Typically the BEFORE CLOSE routine would hide all sheets bar a Blank one. I have less of a need for this with XLS Padlock so tend to remove this. On OPEN I tend to call a routine to make only desired sheets visible. There is no specific reference to declared variables in these routines. Are you suggesting that a variable may have been declared and is still in memory on closing that might be causing an issue?
Appreciate your guidance.
Thank you,
Dave
These 5 references looks great without any conflict - so, I arrive into conclusion that (at this point) I don’t observe something weird!
Regarding V.B.A. code, many times when we are trying to set a value (e.g. 0) into a variable (e.g. Foo) by typing: set Foo = 0
we forget to unload it using the following tactic: set Foo = nothing
My suggestion is:
1.) Ensure that your variables are unloaded (including pointers).
2.) If you are using any UserForm, check that they are unloaded too (using Unload function).
3.) Check for COM Add-Ins for any suspicious entry (you can find it on Developer tab in your ribbon).
4.) Use watch expressions during debugging (F8) in order to catch variables and pointers status. It is very helpful, because you can see any value throughout V.B.A. process.
5.) Try to avoid statements like: On Error Resume Next or GoTo. If so, replace them with error handlers using “If” statements.
Thank you for your suggestions which look sensible even if not always obvious.
I am not specifically recognising what you mean by “pointers” – I may well have them. Do you have an example of what you would call a “pointer” as this would help me identify the like in mine?
Presumably with 1) and 4) you are suggesting that any variable (or pointer) created through any module at any time during its runtime should be checked to see when and if it is released – and if it isn’t then fix it. 2) is usually easily spotted and 5) is generally adhered to. 3) is noted.
Apologize for the technical terminology about pointers!
Kindly, allow me to use the following example to describe the meaning of pointers (as elements of an array):
Dim ii, j As Integer
Dim MyArray(1 To 10)
j = 1 ’ j is a (memory) pointer.
For ii = LBound(MyArray) To UBound(MyArray)
MyArray(j) = ii ’ j is used as an array element.
Next ii
MsgBox MyArray(j) ’ The value of the last declared pointer in the specific array (MyArray).
Erase MyArray ’ Using this method we unload the specific array (MyArray) with it’s pointers which is very important in order to avoid any memory conflict. This is what I was trying to describe in 1.) and 4.) topics.
Understood. I tend to class them all as variables in my mind – but yes will have plenty of arrays gathering data from various sources.
I think the “erase Array” has normally been confined to instances where the array is being populated several times from different sources in a single routine thus cleared between each unique source.
With my scenario issues, if I further follow it suggests that the contents of an array (or any variable) may remain in scope, despite closing gracefully or otherwise, through to closing the workbook then re-opening and executing the OnLoad Ribbon callbacks and then, and only then, hitting an issue which cause the VBA code to be completely disabled and hidden from view.
With some well placed break clauses I should be able to test some of this out.
Thank you for sharing your insights they are very helpful.
Hey Dave. May be going a little off-piste so hear me out. I have experienced a lot of crashes on my application - my xlsm not xls Padlock this time
I will be working on it, no problems, code compiles fine, I close and when I re-open it just crashes. It seems to occur in phases, happened once today, but can happen 8 or 9 times in a day… Take a look at these 2 reddits: https://www.reddit.com/r/excel/comments/1axe4rq/any_advice_for_several_macroenabled_workbooks/
and https://www.reddit.com/r/vba/comments/1b0pi0j/excel_vba_corruption_all_over_the_place_wondering/ for further details, along with a comment from a user called droans in particular. There appears to have been 4 or 5 updates all trying to “fix” to excel crashing, but so far no Excel updates have resolved the issue for me.
The most reliable way I have found to recover is some-what hit and miss, but :
1) I close excel,
2) create a copy of the xlsm,
3) open it with "open and repair", but keep the the vba locked (I do not enable it at the warning prompt),
4) open the editor and compile the code.
5) save and close,
6) copy the xlsm to another new filename,
7) finally, open it normally.
Its a real pain in the a**e, but works almost everytime. Am happy to provide any more details I can, if it helps, but accept this may be unrelated - I just know I have been tearing my hair out with this!
All best wishes, Paul
EDIT: ADDED FOLLOWING to save you going on a search of what I was referring to. At this point the registry settings appeared to have resolved it for me:
This solution has worked for me and all the users I support:
Open the start menu and type “reg” and select the “Registry Editor”
Navigate to: Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\options
On the Edit menu, point to New, and then click DWORD Value.
Type ForceVBALoadFromSource, and then press Enter.
In the Details pane, right-click ForceVBALoadFromSource, and then click Modify.
In the Value data box, type 1, and then click OK.
Or make a text file and load this: [I have not tried this]
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options] “ForceVBALoadFromSource”=dword:00000001
Reload the ‘corrupted excel file’ and no corruption detected.
Hi David,
I mentioned this problem many years ago to MS, but vba projects still gets corrupted frequently.
The solution I developed is crazy simple and unexpected.
First thing you should do is: do not enable macros to run (rename the file, move it to another location will usually display again the Enable Content message to request your permission for macros to run.)
If macro’s are not allowed, you should be able to see your code as usual.
Next step is to make any change in code. Does not matter what you do: just add a space, or a new line in any module.
Close the file and save the changes.
Next time you open the file you can enable macros, codes will run normally.
Hi Paul
Thank you for reply. Generally have an aversion to updating registry entries but good to know it has worked for yourselves. How do you load/execute it as a text file please?
Are you finding this fix a permanent one or one being employed as and when needed?
Had you tried a de-instal and re-instal of Excel - I wonder whether that would have reset the registry entries? Although the jury is out having one file open only may reduce the incidence.
Appreciate your help.
Dave
Presumably MS response was to ignore it and hope it goes away.
So far restore points have saved the day. At least I have some better options to try now. Thanks
Dave
Hey Dave, Sorry I missed the message at first, then clearly failed to respond! So far, and touching wood, the registry update has resolved my issues permanently, and across multiple users
