Discussion :

[Isabelle86]

Bonjour!

J'ai un problème avec la ligne de code suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
With Worksheets(3)
For i = 2 To LastLig 'ligne
    If Sheets(2).Cells(i, 12).Value = "OK" Then
    x = Sheets(2).Cells(i, 15).Value
    For j = 1 To x
    For k = 1 To LastYear 'colonne
        If .Cells(i, k).Value = 0 Then
        .Cells(i, k).FormulaR1C1 = "=IF(AND(YEAR(DATE(YEAR(DATA!RC10),month(DATA!RC10)+(12*" & j - 1 & "),day(DATA!RC10)))=YEAR(R1C),MONTH(DATE(year(DATA!RC10),month(DATA!RC10)+(12*" & j - 1 & "),day(DATA!RC10)))=month(R1C)),DATA!RC8*DATA!RC7/100,0)*(VLOOKUP(DATA!RC6,'CRCY'!R1C1:R14C3,3,0))"
        End If
    Next k
    Next j
next i
end with

La macro vba se bloque à la formule R1C1 mais seulement à partir de la ligne 51 (il y a 52 lignes, donc LastLig=52) colonne 6 (LastYear = 228). Avant ça, tout se calcule parfaitement.

Or, quand je clique sur "Debug", le calcul se fait sans problème, sauf que je dois faire ça sur toute la boucle k et j...

Comment expliquer ce bloquage qui n'en est apparemment pas vraiment un?

Merci d'avance pour vos réponses.

[MattChess]

Bonjour Isabelle86.

En résumé, le contexte du bug est : i = 51, j = ?, k = 6

quand je clique sur "Debug", le calcul se fait sans problème, sauf que je dois faire ça sur toute la boucle k et j...

Une fois l'erreur atteinte, le débogueur met en jaune la très très longue ligne de l'affectation de la cellule cible par une méga formule géante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Cells(i, k).FormulaR1C1 = "=IF(AND...

Vous voulez dire que vous pouvez continuer à exécuter en pas à pas alors que l'erreur 1004 Application defined or object defined error n'a pas été résolue ?

Dans le fragment de code VBA fourni, dans le 1er For par ligne, il manque le End If du test sur "OK". On ne devrait pas pouvoir exécuter ou déboguer sans cette fermeture du If.

Voici une version avec constantes nommées pour décrire les feuilles de calcul Source et Cible ainsi qu'une meilleure indentation du code VBA pour mieux voir la structure de la procédure. Les noms des variables sont sur plus d'une seule lettre ce qui permet de les renommer plus facilement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Option Explicit
 
' Sheet source
Const indSheetSource = 2
Const rowSrcStart = 2
Const rowSrcEnd = 52
Const colSrcOk = 12
Const colSrcMax = 15
 
' Sheet target
Const indSheetTarget = 3
Const colLastYear = 228
 
Const formulaIf1 = "=IF(AND(YEAR(DATE(YEAR(DATA!RC10),month(DATA!RC10)+(12*"
Const formulaIf2 = "),day(DATA!RC10)))=YEAR(R1C),MONTH(DATE(year(DATA!RC10),month(DATA!RC10)+(12*"
Const formulaIf3 = "),day(DATA!RC10)))=month(R1C)),DATA!RC8*DATA!RC7/100,0)*(VLOOKUP(DATA!RC6,'CRCY'!R1C1:R14C3,3,0))"
 
Sub SetFormulaYear()
Dim indRow As Integer, indColSrc As Integer, indColEnd As Integer, indColYear As Integer
Dim strFormula As String, strPrm As String
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For indRow = rowSrcStart To rowSrcEnd ' ligne
        If Sheets(indSheetSource).Cells(indRow, colSrcOk) = "OK" Then
            indColEnd = Sheets(indSheetSource).Cells(indRow, colSrcMax)
            For indColSrc = 1 To indColEnd
                For indColYear = 1 To colLastYear ' colonne
                    With Worksheets(indSheetTarget)
                        If .Cells(indRow, indColYear) = 0 Then
                            strPrm = CStr(indColSrc - 1)
                            strFormula = formulaIf1 + strPrm + formulaIf2 + strPrm + formulaIf3
                            .Cells(indRow, indColYear).FormulaR1C1 = strFormula
                        End If
                    End With
                Next
            Next
        End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

J'ai posé un point d'arrêt en début de procédure.
J'ai créé une 2ème feuille de calcul nommée "Source" avec Cells(2, 12) = "OK" et Cells(2, 15) = 3 et une troisième feuille nommée "Target".

Dans la fenêtre d'Exécution immédiate (Ctrl+G) du VBE, j'ai validé par ENTER l'appel de la fonction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SetFormulaYear

Dans le VBE en mode debug j'avance pas à pas dans la 1ere itération de chaque boucle.
Etrangement l'affectation de la 1ere formule dans la cellule de la feuille cible me fait apparaître la boite de dialogue d'ouverture de fichier "File Not Found" avec comme fichier demandé "DATA" puis "CRCY". Cela doit être un effet de VLOOKUP dans ma version d'Excel.

[Isabelle86]

Bonjour MattChess!

Merci pour ta réponse.

En effet, le code se bloque pour j = 9 et je peux continuer à exécuter le code pas à pas alors qu'à chaque pas, l'erreur 1004 apparaît.

J'ai en effet oublié de recopier le dernier "End if", mais celui-ci est bien dans mon code.

DATA et CRCY sont les noms de deux autres feuilles de mon classeur. C'est pour cela que ton code ne peut pas fonctionner.

Par contre, je ne comprends pas trop le but de ton code. C'est pour déterminer l'endroit de la formule qui pose problème?

[MattChess]

Reproduire le bug

Le but de SetFormulaYear() est de reproduire le bug et de présenter le même code avec une indentation et une convention de nom plus explicite facilitant la compréhension.

Etant donné que le code fonctionne jusqu'à i = 51, j = 9, k = 6, ces valeurs d'indices devraient permettre de :

• Localiser les cellules qui posent problème lors de l'affectation de la méga formule.
• Déterminer la formule réelle dans ce contexte.
• Donner la valeur attendue.

Ainsi on peut tenter de reproduire le bug en saisissant manuellement la formule dans la première cellule où le problème est apparu.

Suspendre le mode calcul automatique

Si ce n'est pas déjà fait, on peut essayer également de temporairement suspendre le calcul automatique des formules :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Application.Calculation = xlCalculationManual

Faire l'affectation des cellules avec la méga formule.

Puis rétablir le calcul automatique des formules uniquement en fin de toutes les boucles par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Application.Calculation = xlCalculationAutomatic

Le but est d'identifier le bug

Il faut savoir si le problème est dans le code VBA (ce que je ne crois pas) ou dans l'exécution par Excel de la méga formule uniquement dans certaines cellules dans un contexte de plusieurs feuilles de calcul Source, Cible, DATA et CRCY.

Gestion d'erreur et mise au point

Le fait de pouvoir continuer le pas à pas alors qu'il y a une erreur (telle que l'erreur 1004) à partir d'un certain contexte montre seulement que l'erreur n'est pas explicitement gérée dans le code par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
If Err.Number <> 0 Then
    ' Gestion d'erreur
End If

Il y a une erreur, mais cela n'empêche pas le débogueur de poursuivre le pas à pas si on lui demande.

je peux continuer à exécuter le code pas à pas alors qu'à chaque pas, l'erreur 1004 apparaît.

L'erreur 1004 n'est donc pas unique et réapparaît plusieurs dans la boucle la plus interne à moins que ce soit la même erreur qui n'a pas été traitée.