Microsoft 365 Fonction personnalisée ne se recalcule pas

[xUpsilon]

Chers confrères,

J'ai un problème assez basique. Dans un fichier Excel, j'extrais les données d'une BDD SQL, qui ressort donc dans un tableau structuré.
Suite à diverses manipulations, je souhaite pousser dans une colonne additionnelle de ce tableau une formule personnalisée, qui se comporte comme ceci :

Const sFeuil_DB As String = "DB_SOR"
Const nCol_SOR As Integer = 1
Const nCol_Dlv As Integer = 2
Const nCol_Combi As Integer = 3
Const nCol_Long As Integer = 4
Const nPremLig As Integer = 2

Function TrouveLongBrute(Section, Longueur, Dlv, SOR)
Dim oWs_DB As Excel.Worksheet
Set oWs_DB = ThisWorkbook.Worksheets(sFeuil_DB)

Combinaison = Left(Section, 8) & "/" & Longueur
nDerLig = oWs_DB.Range("A" & Rows.Count).End(xlUp).Row

If InStr(1, Combinaison, "+") Then
    For nLig = nPremLig To nDerLig
        DateDlv = DateSerial(Right(oWs_DB.Cells(nLig, nCol_Dlv), 2), Mid(oWs_DB.Cells(nLig, nCol_Dlv), 4, 2), Left(oWs_DB.Cells(nLig, nCol_Dlv), 2))
        If oWs_DB.Cells(nLig, nCol_SOR) = SOR And oWs_DB.Cells(nLig, nCol_Combi) = Combinaison And DateDlv = Dlv Then
            TrouveLongBrute = oWs_DB.Cells(nLig, nCol_Long)
            Exit Function
        End If
    Next nLig
End If
TrouveLongBrute = Longueur
End Function

A priori, rien de bien méchant : après avoir créé ma fonction, je l'insère dans ma colonne supplémentaire de mon tableau structuré, qui se remplit correctement et calcule correctement tous les résultats.
Sauf qu'après ça, je décide de changer une valeur dans ma feuille "DB_SOR", et je me rends compte que le recalcul automatique ne se fait pas. Rien de très surprenant vous allez me dire, vu qu'il s'agit d'une UDF, il suffit de forcer le calcul avec Shift + F9. Sauf que c'est bien là le problème : même en utilisant "Calculer la feuille", le calcul ne s'effectue pas, je suis obligé de retourner dans chaque cellule l'une après l'autre et de valider par entrée pour que le recalcul se fasse.

S'agit-il d'un bug connu ? Y a t'il une alternative ? (pas de Application.Volatile, le fichier risque de devenir trop lourd pour l'emploi souhaité)

Merci et bonne soirée,

 

[TooFatBoy]

Bonjour,

23 affichages, pas de réponse... à mon avis il manque un fichier.

 

[Deadpool_CC]

Bonsoir,
vu que t'es un Accro je suppose que t'as pensé à vérifier les options dans Excel

 

[Dranreb]

Bonsoir.
Normalement dans une fonction personnalisée il faut prévoir un argument pour tous les antécédents qu'elle utilise, sinon Excel ne peut pas détecter la nécessité de la réévaluer.

 

[xUpsilon]

Bonjour,

De ce que j'ai lu de manière générale, il semblerait que ce soit un problème lié à la structure même de Excel mais voici mon fichier pour l'exemple.
La colonne contenant l'UDF est la colonne O de la feuille "SOR Azure". Ce qui m'intéresse est de faire ressortir sur la feuille "SOR Azure" les données de la feuille "DB_SOR", elle même renseignée par un USF.

@Deadpool_CC le calcul automatique n'est qu'un gestionnaire d'itérations de recalcul, donc a priori si j'arrive à recalculer via Shift + F9 ça devrait fonctionner aussi avec le recalcul auto. Toujours est-il que ni l'un ni l'autre ne fonctionnent malheureusement.

@Dranreb a priori tous les arguments sont renseignés, je ne suis pas sûr de comprendre ce que tu entends par là.

Bonne journée,

 

[Dranreb]

Bonjour
J'entends par là que personnellement je n'utilise jamais dans une fonction personnalisée de cellule ne faisant pas partie d'un argument Range spécifié en paramètres.

 

[xUpsilon]

Re,

Et comment fais-tu pour exploiter ton argument Range du coup ? Tu le passes dans un array ?

Bonne journée,

 

[Dranreb]

D'habitude je le passe ByVal BdD As Variant, mais je fais au début de la fonction :
If TypeOf BdD Is Range Then BbD = BdD.Value
Remarque: Je préfère quand c'est possible travailler directement avec un tableau plutot qu'avec un Variant contenant un tableau. Mais là c'est le plus simple.

 

[xUpsilon]

Re,

Du coup j'ai essayé en passant le Range directement en argument et on dirait bien que ça fonctionne.
J'ai par contre un nouveau problème de fermetures intempestives du fichier que je ne m'explique pas.

Mais quoi qu'il en soit, visiblement, les UDF ne se recalculent pas si elles emploient un Range qui n'est pas passé en argument, merci @Dranreb !

Bonne journée,

 

[Deadpool_CC]

@Deadpool_CC le calcul automatique n'est qu'un gestionnaire d'itérations de recalcul, donc a priori si j'arrive à recalculer via Shift + F9 ça devrait fonctionner aussi avec le recalcul auto. Toujours est-il que ni l'un ni l'autre ne fonctionnent malheureusement.

C'était au cas ou l'option de calcul auto était activé mais hors des tableau de données ... option que je n'ai jamais testée pour ma part et cela pouvait peut-être bloquer. Mais bon t'as testé les différentes options et c'est pas cela ... je retourne à mes propres soucis

 

[job75]

Bonsoir,

Pour qu'une fonction VBA se recalcule par la touche F9 il faut la rendre volatile avec au début du code :

Application.Volatile

A+

 

[Laurent78]

Bonsoir,

Pour qu'une fonction VBA se recalcule par la touche F9 il faut la rendre volatile avec au début du code :

Application.Volatile

A+

Merci Job, depuis tout à l'heure je cherchais dans ma tête ce truc (que je connaissais, sans prétention) mais impossible de m'en souvenir. Merci, je vais mieux dormir

 

[xUpsilon]

Bonsoir,

Pour qu'une fonction VBA se recalcule par la touche F9 il faut la rendre volatile avec au début du code :

Application.Volatile

A+

Bonjour job, le fil,

Rendre la fonction volatile va forcer son recalcul à chaque changement de donnée du classeur non ? Sur un fort nombre de données, avoir une UDF avec une boucle comme ce que j'ai risque de rendre le classeur indigeste je pense, c'est pour ça que je n'ai pas essayé.
D'autant plus que la solution de Dranreb me permet de gagner en temps d'exécution puisque le tableau passe en array plutôt que de faire une itération sur une feuille Excel, ce qui est généralement plus lent.

Bonne journée,

 

[job75]

Bonjour xUpsilon, le forum,

Rendre la fonction volatile va forcer son recalcul à chaque changement de donnée du classeur non ?

Bien sûr, j'ai proposé cette solution parce que vous parliez de la touche F9.

Mais il y a d'autres solutions pour forcer le recalcul, par exemple cette macro :

Private Sub Worksheet_Activate()
[A1].Select
With ListObjects(1).Range.Find("=TrouveLongBrute(", , xlFormulas, xlPart)
    .FormulaArray = .Formula 'formule matricielle
End With
End Sub

A placer dans le code de la feuille "SOR Azure".

Edit : j'ai mis FormulaArray parce que chez moi sur Excel 2019 votre formule apparaît comme validée matriciellement (avec les accolades).

A+

 

[xUpsilon]

Re,

Cette solution fonctionne également à merveille.
Je ne suis pas sûr de comprendre en quoi forcer la réinsertion de la formule déclenche son calcul, mais ça fait le boulot !
Pas de formule matricielle de mon côté mais c'est quelque chose que j'ai déjà observé sur le pc de collègues, est-ce la conversion d'une formule dans un tableau structuré qui déclenche ça ?

Bonne journée,