Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

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
0
camper12
Asked:
camper12
1 Solution
 
Martin LissRetired ProgrammerCommented:
Put a DoEvents line after the first Selection.Copy and if that doesn't work put one after the second Selection.Copy
0
 
Zack BarresseCEOCommented:
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
 
NorieCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
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
 
Zack BarresseCEOCommented:
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
 
dlmilleCommented:
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
 
Zack BarresseCEOCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now