Similar to a previous thread*, when I step into a VBA sub in Excel 2016 and then step into the statement:
Workbooks.Open Filename:= "C:\file2"
the file opens okay but on return the remainder of the sub just executes to the end (I lose step into mode).
I’m running Windows 10 on a 64 bit machine, and a 32 bit version of Office professional Plus 2016
*The same issue is described in following link: Excel 2013 VBA Workbooks.Open Loses Debug Step Mode? but the 'ideal' solution is to add a breakpoint after the workbooks.open line.This does not work in Excel 2016 - the routine stops but on the first press of F8 the routine completes to end.
The same issue is describe here:
http://blog.contextures.com/archives/2014/09/04/excel-vba-problem-with-step-into-f8/ but the solution (requiring changes to the registry) applies to earlier versions of Excel and does not work for Windows 10/Office 2016.
This is a frustrating issue preventing effective debugging. Microsoft Pro support have directed me to the Excel fora. So here we are.. :)
Any ideas to fix warmly welcomed.
I've experienced the problem you have described, and for me the following worked:
1) rather than Workbooks.Open Filename:= "C:\file2" use variables such as:
Dim wk as Workbook, sh as worksheet, path as string
path = "C:\file2"
Set wk = Workbooks.Open(path)
Set sh = ActiveSheet
'etc...
This allows you to put break points before and after the line which opens the file and that way you can step through the code again. There's something about doing file input/output lines that causes Excel macros to just run the code rather than stepping after those lines. Also, assigning variable is a good practice since it allows you to troubleshoot problems in the code. But be aware that with several workbooks open you need to keep track of which is which -- so it's also a good idea to assign a variable to ThisWorkbook (the one where the code is). Finally, if you use ActiveSheet as I show above and you are in the midst of debugging, then whatever sheet is active will be the ActiveSheet -- which can be confusing. Let me know if you have questions.