Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Why does my VBA macro end after opening file?


12 Dec 2013   #1

Windows 7 Home (x64)
 
 
Why does my VBA macro end after opening file?

Hi,

I made a macro 5 years ago that I use everyday at work to compile reports. A couple of years ago, I copied the macro and made minor modifications for a very similar project. Both worked fine until I got a new PC with Windows 7. Now, my first macro just stops (without any error message) when it reaches the line to open my report file (it opens the file but doesn't continue). However, if I add stop points (maroon bullets) on the faulty line of code and the following line, I can manually make the macro continue on. While the second macro, with the similar lines of code works just find and doesn't stop or require stop points to continue.

Here is a portion of the code of the first macro that stops:

Code:
''' some code before '''
    
''' Creates the reports
    '' Copies and pastes the data in the XLSX report file
LastCol = [F1].End(xlToRight).Column
Range(Cells(2, 1), Cells(LastRow, LastCol)).Select
Selection.Copy
xlFileName = "P:\folder\Clients\folder\Rapports\20" & CurrentYear & " - " & FullMonth & "\projet1_20" & CurrentYear & FullMonth & ".xlsx"
xlFile = "projet1_20" & CurrentYear & FullMonth & ".xlsx"
Workbooks.Open FileName:=xlFileName ' <------- ***THIS IS THE LAST LINE THE MACRO EXECUTES AND THEN STOPS. ***
Sheets("TANGUAY_IVR1").Select
If Cells(2, 1) <> "" Then
Cells(1, 1).End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Else
Cells(2, 1).Select
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets(FullMonth).Select
Application.Workbooks(xlFile).Save

''' some code after '''
While in my second macro that works fine:

Code:
''' some code before '''

    '' Copies and pastes the data in the XLSX report file
Range(Cells(1, 1), Cells(LastRow, LastCol)).Select
Selection.Copy
xlFileName = "P:\folder\Clients\folder\Rapports\20" & CurrentYear & " - " & FullMonth & "\BEMECO_IVR1_20" & CurrentYear & FullMonth & ".xlsx"
xlFile = "BEMECO_IVR1_20" & CurrentYear & FullMonth & ".xlsx"
Workbooks.Open FileName:=xlFileName ' <------- ***THE MACRO DOESN'T STOP HERE LIKE THE PREVIOUS ONE, IT CONTINUES ON. ***
Sheets("BEMECO_IVR1").Select
If Cells(2, 1) <> "" Then
Cells(1, 1).End(xlDown).Select
ActiveCell.Offset(1, 0).Select
RowSelect = ActiveCell.Row
Else
Cells(2, 1).Select
RowSelect = 2
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
LastRow = [A1].End(xlDown).Row
Cells(RowSelect, 1).Select

''' some code after '''
Any idea on what causes this and how to remedy?

Thanks,
Peatawn

My System SpecsSystem Spec
.

23 Dec 2013   #2

XP/7x32-x64/8x64
 
 

Are you declaring variables ?
Have you declared Option Explicit ?

If not it will be impossible to trace the source of the problem.
Have you got Error capture turned on in VBEditor. ?
My System SpecsSystem Spec
23 Dec 2013   #3

Microsoft Community Contributor Award Recipient

Microsoft Windows 7 Home Premium 64-bits 7601 Multiprocessor Free Service Pack 1
 
 

just before: Workbooks.Open FileName:=xlFileName ' <------- ***THIS IS THE LAST LINE THE MACRO
Msgbox "before"

and after that:
Msgbox "after"

And maybe on a few other places. Now you know where it really halts in code.
My System SpecsSystem Spec
.


23 Dec 2013   #4

XP/7x32-x64/8x64
 
 

Or use the debug window to step through each expression.
My System SpecsSystem Spec
23 Dec 2013   #5

Microsoft Community Contributor Award Recipient

Microsoft Windows 7 Home Premium 64-bits 7601 Multiprocessor Free Service Pack 1
 
 

Quote   Quote: Originally Posted by GrayGhost2 View Post
Or use the debug window to step through each expression.
He already did (using breakpoints). I think it's problem with which window has the focus
My System SpecsSystem Spec
09 Jan 2014   #6

Windows 7 Home (x64)
 
 

Quote:
Are you declaring variables ?
Have you declared Option Explicit ?

If not it will be impossible to trace the source of the problem.
Have you got Error capture turned on in VBEditor. ?
Hi Greyghost. Yes, I declared all my variables. The code I provided is only a portion of the entire macros. I am, however, unaware of the Option Explicit and what it does. I am also unaware of how to turn on Error Capture, but I will play around VBEditor and find out. Nonetheless, when executed, the macro doesn't return any error. It just simply stops as if it was the end of the macro.

Quote:
He already did (using breakpoints). I think it's problem with which window has the focus
Hi Kaktussoft. Upon opening my file (in either macro) it automatically gains focus.

On the other hand, I have gained more info on the issue. If I am working from home and connect to my work computer via TeamViewer, now both macros have more bugs. They pass through the line of code SELECTION.COPY but nothing is actually copied and in the clipboard. So when it's time to paste, the macro generates an error. Now for some reason, this leads me to believe that it's due to using computer resources to run TeamViewer and for some reason my macro doesn't like that. I don't get it though, my previous PC (that was on XP) was far less powerful and these macros were never an issue. My new PC is Windows 7, Intel Xenon CPU E5-1620 0 @3.60GHz with 16 GB of RAM.

Thanks!
My System SpecsSystem Spec
09 Jan 2014   #7

Windows 7 Home (x64)
 
 

And recently I made changes to other macros, which are all part of the PERSONAL.XLSA file, and updated the file on my colleagues' PCs. I just tested them and they all jam at the same place, Windows XP and Windows 7. I even plugged in my old PC and tried, now it jams at the same place. It's not an issue of system resources. There is definitely something going on in the macro. I'm starting to think corrupted file... ?
My System SpecsSystem Spec
09 Jan 2014   #8

XP/7x32-x64/8x64
 
 

Option Explicit statement at top of each module makes error tracking easier during development.
Code complies faster too.

Variables are best declared as data types
eg,
Dim var As String (words and text)
Dim var As Integer (non decimal numerals -32,768 to 32,767)
rather than
Dim var (implies a "Variant" variable, which can be anything object or data)

When reading data cells to a variable VBA has to make assumptions about the data type.
Without data-type declarations, sometimes VBA throws a curve ball, and at times identifying the cause can be a challenge, particularly with some obscure methods based in unavailable Reference libraries.

Cell Text and Value are not the same.
eg, 3.00 might not be 3 nor "3.00" ... all subject to cell formatting and variable data types

Without more code to view I can only guess.

My VBEditor settings
It is so many years since I needed to touch these settings I have forgotten about "state loss".
Enable it and see what transpires, I write code with it "disabled"
Name:  VBEditor settings.jpg
Views: 9
Size:  49.7 KB
-----
Code:
1 Workbooks.Open FileName:=xlFileName ' <------- ***THIS IS THE LAST LINE THE MACRO EXECUTES AND THEN STOPS. ***
2  Sheets("BEMECO_IVR1").Select
Line #1 assumes the workbook is not open.
- I would programaticaly first check
• if the book exists
• is it already open
• if not - open the workbook
• activate the workbook
• check if the worksheet exists
• activate the sheet (if you want to watch progress)
The open workbook can be assigned to a workbook object (but not if it is closed)
The target worksheet can be assigned to a worksheet object in the workbook object
NB: Sheets(<name/index>) refers to the active workbook.
Line #2 assumes the ActiveWorkbook (not necessarily the book you just opened)
-----
Corrupted code.
A strong contender especially with VBA.
As modules are edited, code enabled, disabled, commented -- VBA bloats.
Some of my larger VBA projects became unstable/corrupted after a lot of code changes.
(Maybe why MS have dumped VBA after Office 2010)
I got fed up with manually rebuilding VBA projects (Excel) so I have written a Rescue/rebuild addin. (Excel 2003)
Some of my projects (xls, xlt, xla) have sized down to 60% of the original after rebuilding a project.
(My RSQ.xla has options to also strip out all full-line comments, which can really downsize a project)


My System SpecsSystem Spec
10 Jan 2014   #9

Windows 7 Home (x64)
 
 

In an attempt to determine if a corrupt file could be the cause, I created a brand new PERSONAL.XLSA and copied my macros in VB. That didn't fix the issue so I ruled out corrupted file.

Then I decided to separate my macros in different modules as to alleviate the amount of code in a single module. That didn't fix the issue so I ruled out module being too heavy.

Then I used "Step by step detailed" (not sure about the precision of my translation, but it's the F8 key) and then everything went on smoothly! The macro worked just fine!

So I tried it normally again and the issue came back. So I decided to try again executing the macro manually through the macros menu instead of my shortcut keys. And the macro worked just fine again.

Therefore the issue seems to be when I activate the macro using the shortcut keys I've set up. I searched the web to see if my shortcut keys affected anything (CTRL + SHIFT + D) and found a place listing all Excel shortcuts. I tried a different combo not used in Excel (CTRL + SHIFT + E) but that doesn't work either. So I looked for a simpler combo shortcut that isn't used in Excel (CTRL + e) and then it worked. Problem solved. Can't believe it was this stupid. And the even more surprising fact, is that my second macro that works fine was also executed with CTRL + SHIFT + D seeing as that shorcut actually activated a third macro which would analyze my file and only then direct to my first faulty macro or my second okay macro.

Lesson learned, don't make your shortcut combos too complicated because it confuses Mr. Excel here.

I'd like to thank everyone for your help with this issue. Especially GrayGhost with your elaborate replies. Very appreciated!

Nice day to everyone!
My System SpecsSystem Spec
10 Jan 2014   #10

XP/7x32-x64/8x64
 
 

Good news

Thanks for posting back here too. It will help others. You have taught me something also.

I no how frustrating it can be ... having spent days trying to find ... what and where.

One tip:
Rebuilding VBA projects by copy pasting code.
For some weird reason MS Clipboard copies bloat too.
I discovered this when problems persisted in rebuilt projects.
Then I installed a clipboard extender (ClipX - free) because I could copy multiple items as text and paste as text, without laborious back and forth with MS Clipboard between old modules and new modules.
Then one project a monster 68 modules (including classes) I just had to find an easier way.
With code I can rebuild all modules and forms in <3 seconds, manually 45 minutes ... 20 or so worksheets about 2 minutes (manually days !#! )
(I assume because MS Clipboard is generic, it copies images, files, ... everything under the sun ... it copies the underlying code that represents what we see on the module. My take on it anyway. )
My System SpecsSystem Spec
Reply

 Why does my VBA macro end after opening file?




Thread Tools



Similar help and support threads for2: Why does my VBA macro end after opening file?
Thread Forum
Opening a WindowsImageBackup VHD file Backup and Restore
Opening a .CDR file ? Software
BSOD - Opening or attaching file using "File Open" menu BSOD Help and Support
opening .car file extensions Software
File opening General Discussion

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 05:13 PM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33