EXCEL 2010 VBA Macro Stopping

Question

I have created an EXCEL 2010 workbook (e.g., workbook1) which has a macro that (1) opens up another workbook (e.g., workbook2), (2) extract data from workbook2 and puts information into an array of the workbook1 VBA code, (3) closes workbook2, and then continues running the macros in workbook1.

Macro works if (1) you run macro from VBA or (2) you run macro from developer/macros selection.

Macro opens workbook2 and then macro stops if I run macro through shortcut (SHIFT_CNTL_U)?

If shortcut just calls macro, any thoughts on why it would stop?

Simple Example of VBA code below (macro stops succesfully executing Workbooks.Open command:

Sub TESTER()
Dim DataMatrix(5) as integer, i as integer
Dim Analysis(5) as integer
Dim ws As Worksheet, wb As Workbook
'
    Set wb = Workbooks.Open("c:\workbook2.xlsx")
    Sheets("Data").select
    Range("A5").select
    For i = 0 to 4
       DataMatrix(i) = activecell.offset(0,i).value
    Next i
    wb.close
'    
    For i = 0 to 4
       Analysis(i) = DataMatrix(i) * 2
    Next i
'
end sub

Have you confirmed location is correct? You also need to qualify those objects, especially when more than 1 book/sheet is in scope — urdearboy
Does the workbook you are opening have any events that want to run automatically like Workbook_Open ? — HackSlash
@Martin Feinstein Your description is ambiguous. You say both "macro stops succesfully executing Workbooks.Open command" and "Macro opens workbook2 and then macro stops". Only one of these can be true but we would certainly also like to know if there is an error message and which. You also convey the impression that the sample you give us is a mockup of the code that stops to execute. Please test the mockup to make sure that it does demonstrate the fault you describe (too vaguely). — Variatus
Have you tried to do a search to see if anyone else has ask the question? I found over 4k SO questions concerning excel stopping when using a shortcut. This SO Question should be a good start. — GMalc

user9601310 user9601310 · Accepted Answer · 2020-08-30T21:59:24

I believe the problem is using the Shift key in the shortcut. I was able to replicate the behaviour in Excel 2016. When I removed the Shift key from the shortcut, it worked fine. See the link referred to by GMalc above. The issue is that when you hold down the Shift key when opening any Excel workbook, it prevents macros running. This behaviour apparently carries over to invoking macros via hotkey.

EXCEL 2010 VBA Macro Stopping

1 Answers