I'm using Excel 2021. I have a VBA macro that can be started either by menu command or keyboard shortcut.
It had previously been working fine both ways. But while adding to the macro, I found that when starting it by keyboard command, it stopped partway through with no error message, and also didn't go into the error handler. It still works as expected if I start the macro with the menu command.
If I put a breakpoint on the beginning and step through, it works fine even if I start it by the keyboard command.
By putting a breakpoint near the end and testing, and then moving the breakpoint further up and testing each time, I found that it was stopping on a line that opens an Excel workbook.
The workbook is very small and simple. Text data only; no formulas, no objects. Ten columnns by 12 rows. I open it with UpdateLinks:=False just on general principle; it has no links.
It just stops on that line with no error message, and doesn't go into the error handler. Again, that line procesess as expected if I start the macro by the menu command.
Any suggestions?
UPDATE:
I now have a much simplified test case, and the problem happens with a new workbooks. Here are the simple repro steps:
- Create a new default workbook and save it as Book1.xlsx. Close Book1.xlsx.
- In the same folder, create a new macro-enabled workbook called Text.xlsm.
- Open the VB editor. Add a standard module to Test.xlsm. Allow it to be named the default Module1.
- Add this code to Module1:
.
Sub Main()
Call Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\Book1.xlsx")
MsgBox "Hi"
End Sub
Go to Developer > Macros > Options, and add this keyboard shortcut to Main:
Now, making sure Book1.xlsx is closed, press Ctrl+Shift+R. Note that the msgbox does NOT run.
Close Book1 again. Put a breakpoint on Sub Main. Run the keyboard shortcut again. Note that this time you DO get the msgbox.
If Book1 is already open when you run the keyboard shortcut, you do get the msgbox.
Any suggestions?
