How to avoid using Select/Activate

Question

How do I avoid using select/activate in my macro (to help speed it up)?

The macro goes through each row on a worksheet; if the QTY is greater than zero (in column C), then it calls another macro to open a specific workbook (workbook name in column A), makes some changes and then closes that workbook.

Sub Update_All_Workbooks()
    
    Dim LastRow As Long
    Dim DataRange As Range
    Dim WB As Workbook
    Dim WS As Worksheet
    
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Set DataRange = Sheets("TestA").Range("A3:A" & LastRow)
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    WB.Sheets("TestA").Activate
    Range("C3").Select
    
    For Each Row In DataRange
        If ActiveCell > 0 Then
            Call Open_Update_Close_WB
            WB.Sheets("TestA").Activate
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Next Row
        
    WS.Activate
        
End Sub

It would appear that your Open_Update_Close_WB also depends on the current active cell, in which case you need to fix them both at once. — GSerg
While we are waiting for the other code, if you turn off ScreenUpdating and DisplayAlerts in your code, you will want to turn them back on before ending. — Darrell H

freeflow freeflow · Accepted Answer · 2021-03-15T10:26:15

Its quite a change in perspective to move from using select to using references but in the long run, code is much better when using references.

I hop the code below is useful to you.

Option Explicit

Sub Update_All_Workbooks()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim myWB As Workbook
    Set myWB = ActiveWorkbook
    
    ' We set myWS on the basis of the unqualified Cell method used in th original code
    Dim myWS As Worksheet
    Set myWS = myWB.ActiveSheet
    
    Dim LastRow As Long
    LastRow = myWS.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Pull the filenames into a VBA array
    ' So we don't keep having to refder to a Worksheet
    ' The transpose method is used to convert the pseudo 2D array
    ' to a correct 1D array
    Dim myWbNames As Variant
    Set myWbNames = myWB.Application.WorksheetFunction.Transpose(myWS.Range("A3:A" & LastRow).Value)
    
    
    ' Similar to above, you can extract the QTY values in
    ' column C to a VBA array
    Dim myQTY As Variant
    Set myQTY = myWB.Application.WorksheetFunction.Transpose(myWS.Range("C3:C" & LastRow).Value)
    
    ' Because we are processing two arrays (col a and col c)
    ' its easier to use a standard for loop with an index than a for each loop
    Dim myIndex As Variant
    For myIndex = LBound(myWbNames) To UBound(myWbNames)
        If myQTY(myIndex) > 0 Then
        
            Open_Update_Close_WB myWbNames(myIndex)
            
        End If
        
    Next
        
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
        
End Sub

' Underscores have significance in Method names as they are used in
' interface and event declarations
' Therefore it is good practise to get used to NOT using underscores
' for Method names that do not involve an interface

Public Sub OpenUpdateCloseWB(ByVal ipWbName As String)

End Sub

How to avoid using Select/Activate

1 Answers