Solved

code only works in debugger

Posted on 2012-03-14
7
199 Views
Last Modified: 2012-11-21
Hi Experts,

This is my VBA code. A part of it only works in debugger and not when run normally. That part has been bolded.

PFA the code.
I am trying to copy from 1 sheet to another.
Please help.

Thanks

Shilpi
Sub-bootstrap.docx
0
Comment
Question by:camper12
7 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Put a DoEvents line after the first Selection.Copy and if that doesn't work put one after the second Selection.Copy
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
Comment Utility
Hello,

No, you do not need a Do Events in your code anywhere.  You should, however, explicitly reference your worksheets, and I wouldn't rely on selecting anything, as it's not needed at all.  I've amended your code with such, but have no data to test with....


Sub bootstrap_new()
'
' bootstrap_new Macro
'
    Dim iRow                    As Integer
    Dim wsHistData              As Worksheet
    Dim wsInterZero             As Worksheet
    Dim wsBoot                  As Worksheet

    Set wsHistData = Sheets("Hist_DATA")
    Set wsInterZero = Sheets("Interpolated_Zero_Rate_new")
    Set wsBoot = Sheets("Bootstrapping")

    Application.ScreenUpdating = False
   
    For iRow = 5 To wsHistData.UsedRange.Rows.Count
   
        wsHistData.Cells(iRow, 1).Copy Destination:=wsBoot.Range("Date_Bootstrap")
        Calculate
        Range("P21:P43").Copy

        wsInterZero.Range("A5:A75").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        wsInterZero.Range("A5:A75").Copy
        wsInterZero.Cells(iRow, 2) = wsBoot.Range("Date_Bootstrap")
        wsInterZero.Cells(iRow, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
       
    Next iRow
   
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.CutCopyMode = False
   
End Sub

HTH

Regards,
Zack Barresse
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
The ranges here have no worksheet references.
Range("P21:P43").Select
Selection.Copy

Sheets("Interpolated_Zero_Rate_new").Select
Range("A5:A75").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False

Open in new window

Which sheet(s) are you copying from/pasting to?

If you are copying from 'BootStrapping' to 'Interpolated_Zero_Rate_new' try this.
Sheets("BootStrapping").Range("P21:P43").Copy

Sheets("Interpolated_Zero_Rate_new").Range("A5:A75").PasteSpecial Paste :=xlPasteValuesAndNumberFormats

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Dear Mr. firefytr, it's yet to be seen if a DoEvents() is needed (note it's not Do Events), but in case you're not aware, what DoEvents does, among other things, is to temporarily yield to the processor within an event procedure. This allows the processor to catch up when it has to do external processing and it is not given enough time to complete the external process before the code moves on. It is often the reason that code will work when run in Debug but will not work when run normally.
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
I know what DoEvents does,Martin, and how it's spelled.  Thanks.  :)  While DoEvents (I have taken my misplaced space out, just for you! :) ) might be preferrable in some cases, this is not one of them.  It's just a simple copy/paste routine.  Better to turn off some application attributes and let it run.  We're not letting other processes run outside the scope of the application and need to wait for the code to finish, so it's not an issue here, thus DoEvents is not needed.

Regards,
Zack Barresse
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Just so the whole back of 'tricks' is on the table :P

I can't say I disagree with anyone's comments.  I've found occassions when DoEvents/Wait works in special cases, as opposed to normal steps that shouldn't require it.

As I recently had this experience, I would suggest one more thing to do, and that's run Code Cleaner on the project.  Not only did my debugger kick in but when I hit F5 it was executing different lines than what was highlighted yellow, lol.

I don't know if Code Cleaner is need, but thought to tee it up if the other suggestions didn't get you there:
http://www.appspro.com/Utilities/CodeCleaner.htm

Dave
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Hi Dave.  I think it's a safe a assumption that 90%+ of the code we all see that *magically works* when you use DoEvents is poorly written code, and not actually due to what is being processed at that time.

In addition to Bovey's CodeCleaner, MZ-Tools is another fantastic add-in for the VBIDE (http://www.mztools.com/index.aspx).

In the above code, since it is a simple loop, copy, paste, we are not waiting on other processes or applications, thus DoEvents would not be warranted.  In fact, you'd probably see a performance hit instead.  Because we want the code to execute as efficiently as possible (I hope that's what we all want!), we'll want these processes and commands to go quickly, thus execute and move on, and not yield any execution to any process.  We don't want to yield to the operating system, we want control, as we're not opening any outside processes which may take up any CPU speed/efficiency.  It might be advantageous if the OP were doing extreme multi-tasking, running multiple forms/code at once, or accessing other applications/processes which needed to run/load during run-time.  But as I said, this is not the case here.  In short - leave DoEvents out of it.  :)

Regards,
Zack Barresse
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now