Solved

code only works in debugger

Posted on 2012-03-14
7
200 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 46

Expert Comment

by:Martin Liss
ID: 37722488
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
ID: 37722535
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
ID: 37722552
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 37722955
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
ID: 37723080
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
ID: 37723337
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
ID: 37725105
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 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