Microsoft 365 VBA Mauvaise déclaration pour des fonctions créées qui affichent parfois "#VALEUR"

[Villard17]

Bonjour
j'ai créé deux fonctions dans Excel 2016 et 365, la deuxième appellant la 1ère.
J'utilise ces fonctions dans plusieurs onglets du classeur.
Les deux fonctions fournissent les bons calcul, mais quand j'ouvre le classeur et modifie des données, y compris dans des onglets non concernées par ces deux fonctions, les cellules qui utilisent ces deux fonctions affichent "#VALEUR".
Pour réafficher les valeurs calculées, je dois me placer dans le champ de saisie et juste valider le champ. Le calcul se réaffiche.
C'est la 1èrefois que je créée des fonctions et j'ai donc sans doute mal déclaré les deux fonctions qui sont placées dans un module VBA du classeur.
Je vous copie ci-dessous les deux fonctions que j'ai simplifiées, car je ne pense pas que ce soit le code VBA du calcul de la fonction qui pose problème, pusque le résulat est correct quand il s'affiche.

Merci d'avance pour votre aide

Public Function NB_jours_activite(A As Date, B As Date, C As Date, D As Date)
Application.Volatile
Dim S As Date

If (D = A) And (B > S Then
NB_jours_activite = 0
Else
NB_jours_activite = D-A
End If

End Function

Public Function NB_jours_activite_Periode(A As Date, B As Date, C As Date, D As Date)
Application.Volatile
Dim PPD, PPF As Date
PPD = Worksheets("Aparamètres").Cells(4, 2).Value
PPF= Worksheets("Aparamètres").Cells(5, 2).Value

If (A> PPF) Or (D< PPD) Then
NB_jours_activite_Periode = 0
Else
If (A< PPD) And (D <= PPF) Then
A = WorksheetFunction.Max(A, PPD - 1)
NB_jours_activite_Periode = NB_jours_activite(A, B, C, D)
Else
NB_jours_activite_Periode = 99999
End If
End If
End Function

 

[sylvanu]

Bonjour Villard,
Un point me chiffonne, dans :

Dim S As Date
If (D = A) And (B > S Then

( il manque une ")" après S mais je pense qu'il s'agit d'un oubli )
La variable S n'est jamais initialisée, donc S vaut 0, donc (B>S) est toujours vrai.
A quoi sert S ?
( mais ce n'est pas l'origine du pb, je pense. )

 

[Villard17]

Bonjour et merci Sylanu
désolé, j'ai fait des erreurs de recopie en recopiant et simplifiant la macro !
Dans ma macro complète, S est initialisé et la ) est bien là.
le pb est ailleurs !

Dim S As Date S = Worksheets("Aparamètres").Cells(18, 3).Value If (D = A) And (B > S) Then

Bien à vous
Martin

 

[sylvanu]

Sorry, je n'arrive pas à reproduire le phénomène. Voir PJ.
Si #Valeur apparait à l'ouverture et qu'une validation de donnée remet tout en place, c'est que les fonctions ne sont pas évaluées. XL attend une validation ou modification de valeur pour tout ré évaluer.
Pour quelle raison ? ...

Pouvez vous essayer dans une cellule vide de la feuille mettre =alea() ?
Ca oblige XL à recalculer à l'ouverture du fichier. Juste pour voir si ça a un impact.
ou encore
dans une cellule possédant la formule, la remplacer par =NB_jours_activite_Periode(xx;xx;xx;xx)+0*ALEA()
Le 0*alea() vaut 0 mais oblige XL à ré évaluer la formule à l'ouverture du fichier. Encore une fois, juste pour comprendre, le Volatile normalement devrait suffire.

 

[Villard17]

merci
je viens d'essayer mais cela ne résout pas le problème.
J'ai fait réapparaitre le phénomène #VALEUR en copiant une cellule fixe (juste un texte sans formule) de mon classeur vers un autre classeur !! Ce qui est qand même bizarre
je pensais aussi que l'instruction Application.volatile suffisait.
Bon, si Volative suffit, c'est qu'il doit y avoir malgré tout dans la formule elle-même de ma fonction personnalisée qui comporte plusieurs If imbriqués , un bug. Je vais essayer de relire à tête reposée.

Je les dépose, mais c'est vraiment abscons ! et sans doute mal écrit.
Merci si vous repérez une grosse bourde.
Je ne pourrai pas répondre ou vérifier ce soir.
Bien à vous
Martin

Public Function NB_jours_activite(Arrivée_Jour As Date, Arrivée_Heure As Date, _
Départ_Jour As Date, Départ_Heure As Date)

   Application.Volatile

Dim sans_nuitée_test_soirée As Date
Dim nuitée_1j_Arr, nuitée_1j_Dep As Date
Dim nuitée_2j_Arr, nuitée_2j_Dep As Date
Dim nuitée_0j_Arr, nuitée_0j_Dep As Date
sans_nuitée_test_soirée = Worksheets("Aparamètres").Cells(18, 3).Value
nuitée_1j_Arr = Worksheets("Aparamètres").Cells(20, 3).Value
nuitée_1j_Dep = Worksheets("Aparamètres").Cells(20, 5).Value
nuitée_2j_Arr = Worksheets("Aparamètres").Cells(21, 3).Value
nuitée_2j_Dep = Worksheets("Aparamètres").Cells(21, 5).Value
nuitée_0j_Arr = Worksheets("Aparamètres").Cells(22, 3).Value
nuitée_0j_Dep = Worksheets("Aparamètres").Cells(22, 5).Value


If (Départ_Jour = Arrivée_Jour) And (Arrivée_Heure > sans_nuitée_test_soirée) Then
        NB_jours_activite = 0
Else
    If (Départ_Jour = Arrivée_Jour) Then 'Si VRAI
    NB_jours_activite = 1
    Else
        If (Départ_Jour > Arrivée_Jour) And (Arrivée_Heure > nuitée_1j_Arr) And (Départ_Heure > nuitée_1j_Dep) Then
        NB_jours_activite = Départ_Jour - Arrivée_Jour
        Else
            If (Départ_Jour > Arrivée_Jour) And (Arrivée_Heure <= nuitée_2j_Arr) And (Départ_Heure > nuitée_2j_Dep) Then
            NB_jours_activite = Départ_Jour - Arrivée_Jour + 1
            Else
                If (Départ_Jour > Arrivée_Jour) And (Départ_Heure <= nuitée_0j_Arr) And (Arrivée_Heure > nuitée_0j_Dep) Then
                NB_jours_activite = Départ_Jour - Arrivée_Jour - 1
                Else
                NB_jours_activite = 999
                End If
            End If
        End If
    End If
End If
   

 
 
End Function

Public Function NB_jours_activite_Periode(Arrivée_Jour As Date, Arrivée_Heure As Date, _
Départ_Jour As Date, Départ_Heure As Date)

   Application.Volatile
 
Dim Période_programme_début, Période_programme_fin As Date
Période_programme_début = Worksheets("Aparamètres").Cells(4, 2).Value
Période_programme_fin = Worksheets("Aparamètres").Cells(5, 2).Value


If (Arrivée_Jour > Période_programme_fin) Or (Départ_Jour < Période_programme_début) Then
NB_jours_activite_Periode = 0
Else
    If (Arrivée_Jour < Période_programme_début) And (Départ_Jour <= Période_programme_fin) Then
    Arrivée_Jour = WorksheetFunction.Max(Arrivée_Jour, Période_programme_début - 1)
    NB_jours_activite_Periode = NB_jours_activite(Arrivée_Jour, Arrivée_Heure, Départ_Jour, Départ_Heure)
    Else
        If (Arrivée_Jour < Période_programme_début) And (Départ_Jour > Période_programme_fin) Then
        NB_jours_activite_Periode = 99999
        Else
            If (Arrivée_Jour <= Période_programme_fin) And (Départ_Jour <= Période_programme_fin) Then
            NB_jours_activite_Periode = NB_jours_activite(Arrivée_Jour, Arrivée_Heure, Départ_Jour, Départ_Heure)
            Else
                If (Arrivée_Jour <= Période_programme_fin) And (Départ_Jour > Période_programme_fin) Then
                Départ_Jour = WorksheetFunction.Min(Départ_Jour, Période_programme_fin)
                NB_jours_activite_Periode = NB_jours_activite(Arrivée_Jour, Arrivée_Heure, Départ_Jour, Départ_Heure)
                End If
            End If
        End If
    End If
End If
End Function

 

[sylvanu]

Vous avez testé ma PJ ? Elle marche ?

 

[Villard17]

Oups de oups ! désolé je pensais que la pj reprenait l'idée du+0*ALEA()
Je viens de tester en fermant mon classeur et j'ai le meme phénomène.
J'ai fermé Excel, puis ouvert votre classeur.
Quand je copie la cellule marqué "PPD" vers un nouveau classeur vide et reviens ensuite dans le classeur, votre formule s'affiche en #VALEUR"
Brr
Bon, donc ce n'est pas forcément lié au contenu de ma macro, mais je sèche ...
est-ce un problème d'environnement ?
merci sylvanu et encore désolé pour ma bourde

 

[sylvanu]

Alors je vais avoir du mal à vous aider.
Cette PJ marche parfaitement sur mon PC. ( Win10, XL2007 )
Je ne vois pas de où ça peut venir.

Une autre idée ( absurde ), mettez ceci dans ThisWorkbook :

Private Sub Workbook_Open()
    PPD = Worksheets("Aparamètres").Cells(4, 2): Worksheets("Aparamètres").Cells(4, 2) = 0
    PPF = Worksheets("Aparamètres").Cells(5, 2): Worksheets("Aparamètres").Cells(5, 2) = 0
    Calculate
    Worksheets("Aparamètres").Cells(4, 2) = PPD: Worksheets("Aparamètres").Cells(5, 2) = PPF
    Calculate
End Sub

A l'ouverture du fichier j'efface PPD, PPF je recalcule la page, puis je remet les bonnes valeurs et recalcule la page.
Peut être cela sera t-il suffisant. ( même si c'est absurde )

 

[Villard17]

Merci
Je regarde demain..je dois partir en reunion virtuelle

 

[Villard17]

Bonjour Sylvanu
j'ai testé votre macro sur votre fichier mais cela ne change pas. Quand je copie colle une cellule, ne serait que la cellule du libellé PPD par exemple dans un classeur vierge, la fonction affiche #VALEUR
j'ai aussi ouvert votre fichier-exemple essai.xlsm sur un autre PC avec MS365 et sur un autre PC avec Excel 2013 et j'ai le même souci. pourtant votre macro est simple !

J'ai donc encore simplifié pour essayer de circonscrire le problème :
mon test consiste toujours à copier une cellule de texte et coller sur un classeur vierge, et en revennat à la feuille initiale à vérifier si la cellule calculée affiche #VALEUR ou le calcul.

J'ai supprimé la deuxième fonction et n'ai utilisé que la 1ère fonction NB_jours_activite et j'ai le même problème.
j'ai supprimé la condition If Then . la macro qui reste est très simple ! Et j'ai encore le problème;
Le problème doit donc être lié à la définition de la variable S qui ne sert même pas dans cette macro réduite à l'extrême !
S = Worksheets("Aparamètres").Cells(18, 3).Value
D'ailleurs quand je remplace les deux lignes de définition de S dans la macro par S as Long et S= 4428, le problème disparait. Quand je remplace S as Long et conserve S = Worksheets("Aparamètres").Cells(18, 3).Value, le problème demeure.

Public Function NB_jours_activite(A As Date, B As Date, C As Date, D As Date)
    Application.Volatile
    Dim S As Date
    S = Worksheets("Aparamètres").Cells(18, 3).Value
        NB_jours_activite = D - A
End Function

 

[sylvanu]

Bonjour Villard,
Alors je ne peux guère vous aider, car chez moi ça marche tout le temps.

A noter cependant que cela me rappelle un fil récent où le fichier marchait chez moi mais pas chez le demandeur.
Il s'avère que depuis XL2013, MS a resserré la sécurité.

XL 2010 - Macro pour mot de passe à l'ouverture d'Excel

Bonsoir le forum, Je rencontre un problème pour protéger l'ouverture de mon fichier Excel. En effet j'aimerai qu'à l'ouverture de mon fichier, Excel me demande un mot de passe avant de montrer les onglets. J'ai essayé avec les options présentes dans Excel mais le problème c'est qu'elles...

www.excel-downloads.com

Regardez les commentaires de Patrice ( post #66 )

Les sécurités d'Excel sont parfois contradictoires, depuis Excel 2013 le mode protégé, protège le PC des fichiers douteux, par exemple ceux qui proviennent d'Internet.

Ceci dit, si tu veux tester les fichiers (ils fonctionnent tous depuis le début), il faut les exécuter dans un contexte qui autorise l'exécution des macros sans restriction. Au niveau des options de sécurité des macro, depuis l'apparition du mode protégé, il manque une précision importante : ces choix ne concernent que les fichiers situés dans un emplacement de confiance.

Essaies de mettre le fichier dans le répertoire temporaire Internet, tu devrais reproduire son problème (à condition de ne pas avoir désactivé le mode protégé). ( post #69 )

Peux être une piste, car ça ressemble diablement à ce problème. Tout marche impeccable chez moi, Patrice et Staple, mais jamais chez le demandeur.

 

[Villard17]

Merci beaucoup Sylvanu
oui cela pourrait ressembler.
j'ai créé un emplacement approuvé , y ai placé votre fichier essai.xlsm du post #4 sans le modifier.
J'ouvre et refais mon test de copier une cellule quelconque dans un nouveau classeur vierge. Et quand je reviens sur la classeur essai.xlsm, le #VALEUR est affiché !
Donc je suis encore avec le pb.
J'ai réessayé aussi (en, bureau à distance) sur un autre PC avec Excel 2013. Le problème demeure même après avoir créée un emplacement approuvé sur ce PC et y avoir placé votre fichier esai.xlsm.

C'est vraiment bizarre. Ca marche pour vous et pas pour mes deux PC.
Brrr, cela m'échappe
Je vais essayer d'autres recherches
Martin

 

[Villard17]

Bonjour
je vais arrêter cette discussion. je reposerai peut-être une question plus générale dans quelques jours car même avec des fonctions très simples, j'ai des soucis de rafraichissement. Si je n'arrive pas à résoudre le problème, j'ouvrirai un fil de discussion plus précis.
Merci à Sylvanu !
Martin