Comme je l'ai dit dans le titre, je me demande si quelqu'un peut m'aider à comprendre pourquoi mon code fonctionne si lentement (Ran pendant une heure sans résultat). Je suis très nouveau quand il s'agit d'écrire en VBA, mais je ne vois pas pourquoi cela prendrait autant de temps. Voici le code en question:

Sub fast()
Application.ScreenUpdating = False

Dim prices As Worksheet
Dim stockreturns As Worksheet
Dim index As Worksheet
Dim stockprices As Range

Set index = Worksheets("IndexPrices")
Set prices = Worksheets("HistPrices")
Set stockreturns = Worksheets("Sheet1")

index.Range("A:B").Copy stockreturns.Range("A:B")

For col = 1 To 975
    For n = 2 To 260
        prices.Range("A:A").Offset(0, col).Copy stockreturns.Range("A:A").Offset(0, 2 * col + 1)
        If stockreturns.Cells(n + 1, 2 * col).Value = Null Or IsEmpty(stockreturns.Cells(n + 1, 2 * col).Value) Then
            stockreturns.Cells(n, 2 * col + 1) = Null
        Else
            stockreturns.Cells(n, 2 * col + 1).Formula = Cells(n, 2 * col) / Cells(n + 1, 2 * col) - 1
            stockreturns.Cells(n, 2 * col + 1).NumberFormat = "0.00%"
        End If
     Next n
Next col

Application.ScreenUpdating = True
End Sub

Je serais heureux de publier le classeur si quelqu'un veut voir ce que j'essaie d'accomplir dans la feuille et éventuellement suggérer une manière différente ou plus efficace de le faire. Merci.

0
Joe 10 août 2017 à 01:08

2 réponses

Avant de passer à l'optimisation, assurons-nous au moins que cela fonctionne. Veuillez vérifier où je commente. Supposons que le code ci-dessus est correct. Et dans votre code, vous modifiez simplement à 260 lignes pour que je mette la dernière ligne à 260. Je pense que cela nécessitera un débogage plus profond pour fonctionner. mais si vous suivez cette méthode, votre programme se terminera beaucoup plus rapidement (comme des centaines de fois plus vite que toutes les méthodes normales ci-dessus). Le concept est similaire. 1. Vider toutes les données en mémoire (tableau "stockdata et" pricedata ") 2. Jouer avec les données en mémoire 3. réécrire dans le fichier 4. ajouter le format si nécessaire.

Sub fast()
Dim stockdata,pricedata As Variant

    Application.ScreenUpdating = False
    ' Stop Excel from recalculating the workbook every time a cell value changes
    Application.Calculation = xlCalculationManual
    Dim prices As Worksheet, stockreturns As Worksheet, index As Worksheet
    ' Fully qualify your sheets by specifying the workbook
    With ThisWorkbook
        Set index = .Sheets("IndexPrices")
        Set prices = .Sheets("HistPrices")
        Set stockreturns = .Sheets("Sheet1")
    End With
    ' Assign some last row number so you don't have to be copying the value of tens of thousands of rows
    ' Previously every values copy was on the entire column, wasting a lot of time!
    ' Could get this value by a cleverer, more dynamic method, but that depends on needs.
    Dim lastrow As Long: lastrow = 260

    ' Assign values, don't use copy/paste. Avoiding the clipboard speeds things up
    stockreturns.Range("A1:B" & lastrow).Value = index.Range("A1:B" & lastrow).Value
    pricedata = prices.Range("A1",prices.Cells(lastrow,975))
    redim stockdata(1 to lastrow, 1 to 1952)    
    For col = 1 To 975
        'stockreturns.Range("A1:A" & lastrow).Offset(0, 2 * col + 1).Value = prices.Range("A1:A" & lastrow).Offset(0, col).Value
        for n = 1 to lastrow
        'offset so that +1
            stockdata(n,col*2+1+1)  = pricedata(n,col+1)
        next n
    next col
    'done with that

    'check value and change if need
    For col = 1 To 975

        For n = 2 To 260
            If stockdata(n + 1, 2 * col) = Null Or IsEmpty(stockdata(n + 1, 2 * col)) Then
                stockdata(n, 2 * col + 1) = Null
            Else
                stockdata(n, 2 * col + 1).Formula = stockdata(n, 2 * col) / stockdata(n + 1, 2 * col) - 1
                'stockdata(n, 2 * col + 1).NumberFormat = "0.00%"
            End If
         Next n
    Next col
    stockreturns.Range("A1",stockreturns.Cells(lastrow,1952)) = stockdata
    Dim rng As Range
    Set rng = stockreturns.Range("B1:B" & lr)
    For col = 2 To 975
        Set rng = Union(rng, Range(stockreturns.Cells(1,2*col + 1),stockreturns.Cells(lr,2*col + 1),)
    next n
    rng.NumberFormat = "0.00%"
    ' Reset Application settings
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
0
Duc Anh Nguyen 11 août 2017 à 07:57

Problème principal:

Votre boucle imbriquée s'exécute 975 * 260 = 253.500 fois:

For col = 1 To 975
    For n = 2 To 260
        prices.Range("A:A").Offset(0, col).Copy stockreturns.Range("A:A").Offset(0, 2 * col + 1)
        If stockreturns.Cells(n + 1, 2 * col).Value = Null Or IsEmpty(stockreturns.Cells(n + 1, 2 * col).Value) Then
            stockreturns.Cells(n, 2 * col + 1) = Null
        Else
            stockreturns.Cells(n, 2 * col + 1).Formula = Cells(n, 2 * col) / Cells(n + 1, 2 * col) - 1
            stockreturns.Cells(n, 2 * col + 1).NumberFormat = "0.00%"
        End If
     Next n
Next col

Résumé de ce que vous faites, selon le code de la question:

Fondamentalement, ce que vous faites est d'obtenir les colonnes B, C, D, etc. et de les copier dans D, E, G, etc. en utilisant le décalage. Ensuite, vous vérifiez dans la feuille de calcul Stockreturns quelle est la valeur de la cellule copiée dans la ligne suivante (par exemple, vous vérifiez D3, puis D4 etc.) et en fonction de cela, remplissez E2, E3, etc. avec des valeurs nulles, ou, alternativement, vous prenez ( (D2 / D3) - 1) comme valeur ici. Le contrôle initial est d'éviter la division par zéro erreur, je suppose.

Remarque: Dans ces lignes de votre code, vous faites référence à Cells(n, 2 * col) de sorte que ce serait toujours le ActiveSheet, alors que je suppose que vous voulez remplir la feuille de calcul stockreturns avec ces valeurs. C'est à dire. si vous exécutez la formule avec la feuille de calcul prices activée, la formule ne donnera pas le résultat souhaité.

Vers une solution:

Bien sûr, il serait bien plus rapide de ne pas faire 253.500 boucles, mais de tout peupler en même temps le plus possible. Étant donné que le numéro de colonne varie à chaque fois, nous laisserons cette boucle, mais les 260 boucles imbriquées dont nous pouvons facilement nous débarrasser:

Optimisation pour faire 975 boucles au lieu de 253.500:

With stockreturns
    For col = 1 To 975
        prices.Range("A:A").Offset(0, col).Copy .Range("A:A").Offset(0, 2 * col + 1)
        'Now we fill up the entire 260 rows at once using a relative formula:
        .Range(.Cells(2, 2 * col + 1), .Cells(260, 2 * col + 1)).FormulaR1C1 = "=IF(R[+1]C[-1]="""","""",(RC[-1]/R[+1]C[-1])-1)"
        'If you want a value instead of a formula, we replace the formula's with the value. If calculation is set to manual, you'll have to add an Application.Calculate here.
        .Range(.Cells(2, 2 * col + 1), .Cells(260, 2 * col + 1)).Value = .Range(.Cells(2, 2 * col + 1), .Cells(260, 2 * col + 1)).Value
        .Range(.Cells(2, 2 * col + 1), .Cells(260, 2 * col + 1)).NumberFormat = "0.00%"
    Next col
End With

Cela économisera déjà un temps d'exécution majeur. Cependant, nous pouvons également nous épargner 975 actions de calcul, en désactivant les calculs et en ne remplaçant les formules que par les valeurs à la toute fin:

Deuxième optimisation pour éviter les calculs lors de l'exécution:

Application.Calculation = xlCalculationManual
With stockreturns
    For col = 1 To 975
        prices.Range("A:A").Offset(0, col).Copy .Range("A:A").Offset(0, 2 * col + 1)
        'Now we fill up the entire 260 rows at once using a relative formula:
        .Range(.Cells(2, 2 * col + 1), .Cells(260, 2 * col + 1)).FormulaR1C1 = "=IF(R[+1]C[-1]="""","""",(RC[-1]/R[+1]C[-1])-1)"
        .Range(.Cells(2, 2 * col + 1), .Cells(260, 2 * col + 1)).NumberFormat = "0.00%"
    Next col
End With
Application.Calculate
stockreturns.UsedRange.value = stockreturns.UsedRange.value

Cette dernière version fonctionne en quelques secondes. S'il est acceptable pour vous de modifier la disposition de la feuille de calcul stockreturns et d'utiliser une plage continue pour copier en une seule fois, vous n'aurez pas non plus besoin de ces 975 boucles, mais vous pouvez obtenir le résultat souhaité avec les actions suivantes:

  • Copier la fourchette de prix
  • Ajouter la formule dans une autre plage
  • Calculer
  • Remplacer les formules par des valeurs
  • Définir le format numérique

J'espère que cela t'aides.

0
Rik Sportel 10 août 2017 à 09:22