Debug fonctionne après erreur "application defined or object defined"

Version imprimable

Bonjour!

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

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.

Excel VBA Error 1004 Application defined or object defined error

Bonjour Isabelle86.

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

Citation:

Envoyé par Isabelle86

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:

.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:

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:

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" 8O avec comme fichier demandé "DATA" puis "CRCY". Cela doit être un effet de VLOOKUP dans ma version d'Excel. ;)

25/07/2011, 14h05
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?
25/07/2011, 14h47
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:

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:

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:

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.

Citation:

Envoyé par Isabelle86

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.