We help IT Professionals succeed at work.

code only works in debugger

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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Put a DoEvents line after the first Selection.Copy and if that doesn't work put one after the second Selection.Copy
CERTIFIED EXPERT
Commented:
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
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
CERTIFIED EXPERT

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
CERTIFIED EXPERT

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.