Comment améliorer la vitesse du code macro VBA?

Je n'ai pas beaucoup d'expérience avec l'écriture de macros, et donc besoin de l'aide de la communauté pour le problème suivant rencontrés:

Ma macro copie une plage de valeurs saisies dans une plage verticale dans une feuille de calcul, puis colle les valeurs horizontalement (transpose) dans une autre feuille de calcul. Il serait, en théorie, coller les valeurs de la première feuille de la première ligne de la deuxième feuille de calcul qui n'ont pas de contenu. Depuis les cinq premières lignes du contenu, il colle les valeurs de la sixième ligne.
Le problème que j'ai avec l'exécution de la macro, c'est que j'ai l'impression que c'est trop lent et je tiens donc à courir plus vite.

J'ai la même macro de faire la même chose mais au lieu de cela colle les valeurs d'une autre feuille de calcul à la première ligne, et il fonctionne parfait.

Ma meilleure supposition est donc que la deuxième macro est lent car il doit commencer à coller sur la sixième ligne et il peut y avoir de contenu sur les 5 premières lignes qui prennent beaucoup de temps pour que la macro passer à travers (il y a beaucoup de références de cellule à d'autres classeurs) pour déterminer où la ligne suivante pour le collage doit être. C'est ma meilleure supposition bien et depuis je ne sais rien à propos des macros, je ne peux pas dire à coup sûr que le problème est.

Par la présente, je vous fournir le code de ma macro et espère sincèrement que quelqu'un peut me dire qu'est-ce que faire de ma macro de ralentir et de me fournir une solution quant à la façon de le faire fonctionner plus rapidement. Je pense qu'une solution pourrait potentiellement être que la macro ne doit pas considérer les cinq premières lignes de données et commencer à coller immédiatement sur la ligne 6 pour la première entrée. Ensuite, sur la ligne 7, la prochaine fois, et etc. Ce pourrait être une solution mais je ne sais pas comment écrire le code d'une manière qu'il le ferait.

Merci de prendre le temps et de m'aider à trouver une solution, voici le code:

Sub Macro1()
Application.ScreenUpdating = False

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myCopy As Range
    Dim myTest As Range

    Dim lRsp As Long

    Set inputWks = wksPartsDataEntry
    Set historyWks = Sheet11

      'cells to copy from Input sheet - some contain formulas
      Set myCopy = inputWks.Range("OrderEntry2")

      With historyWks
          nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With

      With inputWks
          Set myTest = myCopy.Offset(0, 2)

          If Application.Count(myTest) > 0 Then
              MsgBox "Please fill in all the cells!"
              Exit Sub
          End If
      End With

      With historyWks
          With .Cells(nextRow, "A")
              .Value = Now
              .NumberFormat = "mm/dd/yyyy hh:mm:ss"
          End With
          .Cells(nextRow, "B").Value = Application.UserName
          oCol = 3
          myCopy.Copy
          .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
          Application.CutCopyMode = False
      End With

      'clear input cells that contain constants
      With inputWks
        On Error Resume Next
           With myCopy.Cells.SpecialCells(xlCellTypeConstants)
                .ClearContents
                Application.GoTo .Cells(1) ', Scroll:=True
           End With
        On Error GoTo 0
      End With

Application.ScreenUpdating = True
End Sub

source d'informationauteur Miles

  1. 7

    Juste en répétant ce qui a déjà été dit:

    Option Explicit
Sub Macro1()
'turn off as much background processes as possible
With Excel.Application
.ScreenUpdating = False
.Calculation = Excel.xlCalculationManual
.EnableEvents = False
End With
Dim historyWks As Excel.Worksheet
Dim inputWks As Excel.Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Excel.Range
Dim myTest As Excel.Range
Dim lRsp As Long
Set inputWks = wksPartsDataEntry
Set historyWks = Sheet11
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry2")
With historyWks
nextRow = .Cells(.Rows.Count, 1).End(Excel.xlUp).Offset(1, 0).Row
End With
With inputWks
Set myTest = myCopy.Offset(0, 2)
If Excel.Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
GoTo QuickExit
End If
End With
With historyWks
With .Cells(nextRow, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, 2).Value = Excel.Application.UserName
oCol = 3
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=Excel.xlPasteValues, Transpose:=True
Excel.Application.CutCopyMode = False
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(Excel.xlCellTypeConstants)
.ClearContents
Excel.Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
Calculate
QuickExit
With Excel.Application
.ScreenUpdating = True
.Calculation = Excel.xlAutomatic
.EnableEvents = True
End With
End Sub

    J'avais étape grâce à la macro, ligne par ligne, pour essayer de trouver la ligne qui est lent.

    Une autre alternative, même si vous ne savez pas si ça va accélérer les choses - est pour éviter le presse-papiers et de perdre la copy/paste donc, si vous voulez appliquer une méthode comme suit pour déplacer les données:

    Option Explicit
Sub WithoutPastespecial()
'WORKING EXAMPLE
Dim firstRange As Range
Dim secondRange As Range
Set firstRange = ThisWorkbook.Worksheets("Cut Sheet").Range("S4:S2000")
With ThisWorkbook.Worksheets("Cutsheets")
Set secondRange = .Range("A" & .Rows.Count).End(Excel.xlUp).Offset(1)
End With
With firstRange
Set secondRange = secondRange.Resize(.Rows.Count, .Columns.Count)
End With
secondRange.Value = firstRange.Value
End Sub
  2. 7

    Meilleure façon d'améliorer la performance, basée sur mon expérience est de travailler sur des variables dans le code plutôt que d'accéder à la feuille de calcul à chaque fois que vous voulez à la recherche d'une valeur.
    Enregistrer de n'importe quelle plage vous souhaitez travailler dans une variable(variante), puis itérer à travers elle comme si c'était la feuille.

        dim maxRows as double
dim maxCols as integer.
dim data as variant
with someSheet
maxRows = .Cells(rows.count, 1).end(xlUp).row             'Max rows in sheet
maxCols = .Cells(1, columns.count).end(xlToLeft).column   'max columns in sheet
data = .Range(.Cells(1,1), .Cells(maxRows, maxCols))      'copy range in a variable
end with

    À partir d'ici vous pouvez accéder à la variable de données comme si c'était la feuille de calcul comme - données(ligne, colonne) avec BEAUCOUP plus rapidement de la vitesse de lecture.

  3. 5

    Veuillez jeter un oeil à cet article.
    Comment accélérer la vitesse de calcul et d'améliorer les performances...

    Par tous les moyens, de l'Application.calculation= xlCalculationManual est généralement le coupable. Mais nous pouvons remarquer que la volatilité des feuille Excel fonctions peuvent tuer votre application à grande échelle de traitement de données et l'aspect fonctionnel.

    Aussi, pour votre code actuel post suivant, peut-être pas directement pertinents. Je trouve ça utile pour des trucs et conseils sur tous les Excel/VBA optimisation de la performance.

    75 Excel accélérer les conseils

    PS: je n'ai pas assez de réputation de faire des commentaires sur votre post. Donc ajouté comme une réponse..

  4. 1

    Comme suggéré par quelques autres dans les commentaires, vous devriez certainement de la Demande de changement.Calcul de xlCalculationManual et rememeber à définir à xlcalculationAutomatic à la fin. Aussi, essayez de régler une Application.Screenupdating = False (et tournant le dos à nouveau trop). Aussi, gardez à l'esprit que .La copie est une voie très inefficace pour copier les valeurs des cellules - si vraiment vous voulez juste les valeurs, boucle à travers le réglage de la plage .La valeur de l' .Les valeurs dans la plage ancienne. Si vous avez besoin de toute la mise en forme, vous êtes probablement coincé avec .Copie.

    Lorsque vous éteignez le calc/de rafraîchissement de l'écran drapeaux, n'oubliez pas de les activer en toutes circonstances (même lors de vos sorties de programme en un autre point, ou provoque une erreur d'exécution). Sinon, toutes sortes de mauvaises choses vont arriver. 🙂

  5. 1

    Quelques suggestions (aurait posté un commentaire mais je suppose que je n'ai pas de rep):

    1. Essayer en référence à la cellule d'adresses au lieu de plages nommées (doute que ce serait la cause, mais peut être à l'origine de certains frappé à la performance)

    2. Faire votre classeur formules contiennent des liens vers d'autres classeurs? Essayez de tester le code sur un fichier avec des liens brisés pour voir si ça améliore les performances.

    3. Si aucune de ces sont les question, ma conjecture est que si les formules sont trop complexes, il y a probablement une surcharge de traitement en cours d'ajout. Essayez le code sur un fichier contenant uniquement les valeurs pour voir si il n'y a aucune amélioration de la performance.

  6. 0

    Vous pouvez améliorer la vitesse par l'arrêt de calcul pendant le changement de la valeur de la cellule et après que vous pouvez activer. veuillez suivre le lien.
    http://webtech-training.blogspot.in/2013/10/how-to-stop-heavy-formula-calculation.html

  7. 0

    .Les cellules(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Application.CutCopyMode = False

    Je ne le ferais pas. Couper,copier & les opérations de collage sont les plus coûteuses opérations, dans les conditions d'utilisation du processeur, dans un système d'exploitation.

    Au lieu de cela, vous pourriez juste assigner la valeur d'une cellule ou plage de cellules /plage, comme dans

        Cells(1,1) = Cells(1,2)  or Range("A1") = Range("B1")

    Espère que vous avez compris mon point de vue..