Problème lorsque j'étends une fonction VBA

Bonjour à tous,

Je crée actuellement un planning de production et je souhaite créer une fonction : dureeprod qui me calcule la durée de la production suivant la date (de début ou de fin au choix), la vitesse de la machine en fonction de la référence produit et de l'engagement de la machine ( nbr d'heures par jours de travail disponible).

Je rencontre deux problème dans la réalisation de ma fonction.

le premier est que, lorsque j'étends ma fonction à d'autres cases, ma fonction me recalcule toutes mes valeurs dans toutes les autres cases. Par exemple, j'ai une boucle qui me permet de trouver le numéro de la colonne dans mon calendrier qui correspond à ma date de début ou à ma date de fin (variable appelée i).

Dim i As Integer
        i = 15

    While Cells(3, i) <> casejour.Value And Cells(3, i).Value <> ""
        i = i + 1
    Wend 'le but est de trouver le numéro de la colonne ou la date du calendrier correspond à la date mise en variable dans ma fonction

Je voudrais savoir si c'est normale que, lorsque je rentre ma fonction dans une nouvelle cellule et que je choisie une nouvelle valeur pour ma variable, ma fonction me relance le calcul pour toute les cases ou j'ai déjà rentré cette fonction?

(on peut le voir car je renvoie pour les tests la valeur de i et je devrais avoir une seule msgbox qui apparaisse car je ne lance qu'une fonction (celle dans la cellule active lorsque j'appuie sur Entrée)

mon second problème est le suivant: dans la boucle While plus bas, je veux récupérer le nombre d'heure disponible correspondant à la date de ma production. Je veux commencer à produire le 10/04, je prends le nombre d'heure dispo ce jour que je multiplie par la vitesse et j'obtiens ma distance parcouru dans ma journée. Je regarde si la distance parcouru est inférieure à la distance totale à faire et je passe au jour suivant en prenant le nombre d'heure du 11/04 cette fois ci etc...

Ma formule fonctionne lorsque je fixe la cellule avec mon heure (cells (5,20)) mais lorsque je veux la faire varier avec un cells(5,j), la formule retourne #valeur# dans la case de mon classeur.

While prod < prodtot                                    
                        heure = Cells(5, 20) 'Range("r5") 'Cells(5, j)
                        prod = prod + vitesse * heure
                        JourFin = JourFin + 1
                        dur = dur + 1
                        j = j + 1
                    Wend

Je vous mets en pièce jointe le fichier.

l'onglet s'appelle F6K1 et la formule se met en colonne I

Je vous remercie si vous avez eu le courage d'arriver jusque là et encore plus si vous pouvez m'aider.

Bonjour,

Je voudrais savoir si c'est normale que, lorsque je rentre ma fonction dans une nouvelle cellule et que je choisie une nouvelle valeur pour ma variable, ma fonction me relance le calcul pour toute les cases ou j'ai déjà rentré cette fonction?

Rien de plus normal ! Si tu construis une fonction à utiliser dans une feuille de calcul, c'est pour qu'elle te fournisse un résultat toujours à jour, donc qu'elle soit recalculée comme toutes les formules que tu insères dans ta feuille, lorsqu'un changement quelconque déclenche le recalcul automatique.

C'est pour que ce recalcul se fasse que tu as mis la commande : Application.Volatile.

Pour ta 2e question, je n'ai pas le temps de plonger dans les arcanes de ton calcul, mais il me semble que tu fais chercher à ta fonction les éléments sur lesquels elle doit calculer au lieu de les lui fournir en arguments...

Bases-toi sur la façon dont tu procèdes pour faire le calcul manuellement : les éléments que tu sais où trouver de façon fixe, tu les fournis à la fonction dans les mêmes conditions, ceux que tu dois chercher dans une zone délimitée, tu fournis la zone et le critère de recherche...

Cordialement.

Bonsoir,

Je vois que tu as mis en résolu. Tu as donc sans doute vu que ton erreur #VALEUR! en I6 provenait du fait que à partir de la semaine 19 la capacité horaire journalière est en erreur.

Sinon je te livre un essai d'amélioration, pas vraiment satisfaisant car il ne traite pas tous les cas d'erreurs, mais elle te donne déjà une valeur fractionnée pour le dernier jour.

Remarque sur la rédaction des procédures (Function ou Sub, c'est pareil) : on place toujours toutes les déclarations de variables en tête de procédure, comme en tête de module pour les variables module, de façon que VBA les place immédiatement en mémoire.

While... Wend est une boucle ancienne, qui fonctionne certes toujours, mais que microsoft a remplacée depuis longtemps par Do... Loop, jouant le même rôle (dans sa version Do While... Loop) mais offrant plus de possibilité avec des conditions de sorties de boucle sur Do While..., Do Until, Loop While..., Loop Until..., et par l'instruction Exit Do. Possibilité utile dans ton cas.

Function DureeProd(prodtot, vitesse As Single, Optional JourDeb, Optional JourFin) As Single
    Dim i%, J%, Jour As Date, heuretot!, heure!, dur!
    Application.Volatile
    If Not IsMissing(JourDeb) Then
        Jour = JourDeb
        J = 1
    ElseIf Not IsMissing(JourFin) Then
        Jour = JourFin
        J = -1
    Else
        DureeProd = CVErr(xlErrNA)
        Exit Function
    End If
    heuretot = prodtot / vitesse
    i = 15
    With ActiveSheet
        Do
            If .Cells(3, i) = Jour Then
                Exit Do
            ElseIf .Cells(3, i) = "" Then
                DureeProd = CVErr(xlErrNA)
                Exit Function
            End If
            i = i + 1
        Loop
        heure = .Cells(5, i)
        Do
            If heure >= heuretot Then
                dur = dur + heuretot / heure
                Exit Do
            Else
                heuretot = heuretot - heure
                dur = dur + 1
                i = i + J
                heure = .Cells(5, i)
            End If
        Loop
    End With
    DureeProd = dur
End Function

Cordialement.