none
speeding up vba code RRS feed

  • Question

  • I've been trying to understand object coding with the goal of speeding up my macros but i learn better by seeing examples.

    Below is my code:  Can someone give me some alternate coding for copying these two fiels between these two different workbooks?

    Thanks!

     Sub AE_03_Infeasiblilty_Chk_Sensitivity_Testing()
    '
    ' AE_Chk_infeasible Macro
    '
    '    ActiveWindow.WindowState = xlMinimized
    '    ActiveWindow.DisplayWorkbookTabs = False
        MstrTemplate = ThisWorkbook.Name
    '
    '   Clear manual input range in infeasibiltiy matrix
        Windows("RA 2.0.2 Infeasibility Matrix - schedule -Sensitivity.xlsx").Activate
        Worksheets("working").Activate
    '    Range("B2").Select
        Sheets("working").Range("b2").ClearContents
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        SensTemplate = ThisWorkbook.Name
    '
        Workbooks(MstrTemplate).Activate
        Sheets("RA Input - Sensitivity Test").Select
        Range("Bv4:Bv1582").Select
        Application.CutCopyMode = False
        Selection.ClearContents
        Dim i As Integer
        Dim j As Integer
            j = Range("bw4").Value
        Range("BU5").Select
        For i = 1 To j
            Selection.Copy
            Windows("RA 2.0.2 Infeasibility Matrix - schedule -Sensitivity.xlsx").Activate
    '    requires that this workbook have the correct sheet as active
            Range("B1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Range("D2").Select
            Application.CutCopyMode = False
            Selection.Copy
            Workbooks(MstrTemplate).Activate
    '          Windows("AE UW Template - Master - 2017-07-01 - RA v2_0.xlsm").Activate
            ActiveCell.Offset(0, 1).Select
    '        Range("BV2").Select
    '        Selection.End(xlDown).Select
    '        ActiveCell.Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            ActiveCell.Offset(1, -1).Select
            Application.CutCopyMode = False
        Next i
    '    ActiveWindow.WindowState = xlMaximized
        Application.DisplayStatusBar = True
        Application.ScreenUpdating = True
    '    ActiveWindow.DisplayWorkbookTabs = True
    End Sub
    Wednesday, July 25, 2018 10:15 PM

All replies

  • Try this; please make sure you have backup copies of the workbooks in case it doesn't work as intended.

     Sub AE_03_Infeasiblilty_Chk_Sensitivity_Testing()
     '
     ' AE_Chk_infeasible Macro
     '
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim i As Integer
        Dim j As Integer
       
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
       
        Set wb1 = ThisWorkbook
        Set ws1 = wb1.Worksheets("RA Input - Sensitivity Test")
        ws1.Range("BV4:BV1582").ClearContents
        Set wb2 = Workbooks("RA 2.0.2 Infeasibility Matrix - schedule -Sensitivity.xlsx")
        Set ws2 = wb2.Worksheets("working")
        ws2.Range("B2").ClearContents
       
        j = ws1.Range("BW4").Value
        For i = 1 To j
            ws1.Range("BU" & i + 4).Copy
            ws2.Range("B1").PasteSpecial Paste:=xlPasteValues
            ws2.Range("D2").Copy
            ws1.Range("BV" & i + 4).PasteSpecial Paste:=xlPasteValues
        Next i
       
        Application.DisplayStatusBar = True
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
     End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 26, 2018 9:36 AM
  • Thanks for the example code Hans!

    1.  It worked (i.e. both routines returned same results)

    but

    2.  There was actually a slight increase in the total time using the new code.

    I was surprised because everything I've read suggested that using objects would speed the process.

    Any thoughts?

    Friday, July 27, 2018 6:24 PM
  • I'm sorry, I can't explain that.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 27, 2018 7:36 PM