VBA conks out on very simple code

[Sebastian H]

After many years of working without problem, my macros suddenly stop working on basic commands, such as the following:

Selection.Cells(1, 1).FormulaR1C1 = "foo"
Debug.Print "done"
This example code writes "foo" in the cell, but then stops execution without any warning or error message, and never executes Debug.Print. Setting On Error Resume Next doesn't help.

This doesn't happen consistently, though. When I tried the example in a completely new file, it worked just fine. So I recreated one of the problematic .XLSM files by copying the sheets individually to a new file, and then importing the VBA code into that file, but that didn't help: The problem still exists, even with this simple two-liner.

[Paul_Hossler]

If something other than a Range is selected (i.e. a Shape, Chart, etc.), it won't work




Option Explicit


Sub test()


If TypeOf Selection Is Range Then
Selection.Cells(1, 1).FormulaR1C1 = "foo"
Debug.Print "done"

Else
Debug.Print "not done"
End If

End Sub

[SamT]

After many years of working without problem, my macros suddenly stop working
What, in around or about, your computing system recently changed

Network? Patchcord? OS? Office version? Ram? Keyboard? Desk, chair? et al.

[Sebastian H]

If something other than a Range is selected (i.e. a Shape, Chart, etc.), it won't work
Good idea. But sadly, this is not an explanation here. What I posted here was actually just a sample, which I wrote for testing, and with which I could still repro the issue, and of course I selected a range when testing it. The context of the original code, which always worked, did not change. And I experienced it in at least 4 different macros, which all suddenly exhibit this strange behavior. I say “strange” because I don't think VBA should ever conk out just like that.



What, in around or about, your computing system recently changed ...
Both Windows (10) and Office (2007) are changing constantly, thanks to MS's automatic updates. Other than that, nothing changed since the last time at least one of the macros ran without a problem (some 4 days ago).

[Paul_Hossler]

1. Export the module
2. Remove from project
3. Import file from step 1

This should 'clean' the invisible garbage that collects in a heavily edit module


Also

https://www.add-ins.com/macro-products-for-Microsoft-Excel/ways-to-repair-vba-code/how-to-fix-vba-code.htm

[Sebastian H]

Thanks, this should be just the right solution, but isn't it basically what I already did?

I recreated one of the problematic .XLSM files by copying the sheets individually to a new file, and then importing the VBA code into that file ...
If anything, what I did should be safer, since I started from a new, empty file.

[Paul_Hossler]

Depends -- did you

a. Export the module from the original, and Import into to the new workbook

or

b. Just drag the modules between the workbooks?

[SamT]

MS Auto up date was 4 days ago.

I'm not saying this is definitely your issue, but it is one reason I choose to be Notified when updates are available, so I can make Restore Points of the Registry.

[Sebastian H]

SamT: You identified a likely cause, thank you! Unfortunately I don't have a restore point to which I could return. (For anyone else reading this, here (https://www.tomshardware.com/news/how-to-restore-windows-10-earlier-restore-point,36411.html) is a good description how to go about it.) Fortunately, though, this did not come up in a paid project, so I can wait till the next MS update; maybe they'll fix the regression. Or is there a faster or more proactive way?

Paul: b. That ‘import’ goes with ‘export’ seems obvious to me, but I see your point: Here we meet people from many different backgrounds; it's wise not to take one's own assumptions for granted.

[snb]

Did you check in the VBEditor tools/references ?

[snb]

@SamT

Since you have such warm relationships with MS, why don't you suggest them to create a restore point in the registry automaticaally everytime a 'down'date is taking place ?

[Paul_Hossler]

Paul: b. That ‘import’ goes with ‘export’ seems obvious to me, but I see your point: Here we meet people from many different backgrounds; it's wise not to take one's own assumptions for granted.


True, and just to make certain that I've expressed myself clearly, I meant 1, 2, and 3

27288


The code cleaner adds a menu item that does all modules all at once

27289

[Sebastian H]

Did you check in the VBEditor tools/references ?
Check for what? The two-line test code of my OP only uses basic built-in functionality (which I don't think can be overloaded); so what possible problem do you see? Just to be absolutely sure, I removed the references that are not needed for the test code, but no surprise, it still repros.


... I meant 1, 2, and 3
As I wrote, I started from a new, empty file. That means, your step 2 doesn't apply, as there is nothing to remove.

[Sebastian H]

Paul: b. ....
Oops, I meant a. But it seems Paul understood what I meant.

[Sebastian H]

Apparently this regression bug got fixed with one of the recent updates.

[SamT]

One thing I always try

Sub ResetApp()
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Caclulation = xlCalculationAutomatic
'and any others I run into
End With
End sub