XL 2010 Une fonction personnalisée qui pose un problème en VBA

[Magic_Doctor]

Bonsoir,

J’ai bricolé une fonction qui permet, suivant son paramétrage, de connaître :
- le nombre de fuseaux qui séparent 2 méridiens
- le décalage horaire entre 2 méridiens
- la distance, à une latitude donnée, séparant 2 méridiens

Function Decalage(lat As Double, long1 As Double, long2 As Double, Optional fuseau As Boolean = False, Optional distance As Boolean = False) As String
'Renvoie le décalage horaire entre 2 méridiens si "fuseau" & "distance" sont omis
'La latitude et les longitudes sont en degrés DÉCIMAUX
'- lat : latitude de l'un des 2 points
'- long1 : longitude du premier point
'- long2 : longitude du deuxième point
'- fuseau = True ("distance" omis) --> nombre de fuseaux qui séparent les 2 méridiens
'- distance = True ("fuseau" omis) --> distance, à la latitude "lat", séparant le méridien du point à la longitude "long1" du méridien du point à la longitude "long2"
'Magic_Doctor

Dim wf As WorksheetFunction, ValLongit As Double, dif As Double, circ As Double, LongArc As Double, nbf As Double, h As Byte, mn, s As Byte

    Set wf = Application.WorksheetFunction
   
    '************************************* Valeur, en km, d'1° de longitude suivant la latitude où l'on se trouve *************************************
    ValLongit = 6371.009 * wf.Acos(Sin(wf.Radians(long1)) ^ 2 + Cos(wf.Radians(long1)) ^ 2 * Cos(wf.Radians(1))) '(loi des sinus)
   
    dif = Abs(long1 - long2) 'nombre de degrés séparant les méridiens des 2 points
    circ = 2 * wf.Pi * 6371.009 * Cos(wf.Radians(lat)) 'circonférence du parallèle à la latitude "lat"
    LongArc = dif * circ / 360 'distance entre les longitudes des 2 points (la latitude "lat1" étant celle de l'un des 2 points)
    nbf = LongArc / ValLongit / 15 'nombre de fuseaux horaires séparant les 2 Points
     
    h = Int(nbf)                         'heures
    mn = Int((nbf - h) * 60)             'minutes
    s = Int(((nbf - h) * 60 - mn) * 60)  'secondes
   
    Decalage = IIf(distance = True, Format(Round(LongArc, 3), "#,##0.000 km"), IIf(fuseau = True, Format(Round(nbf, 3), "0.000"), IIf(h < 10, "0" & h, h) & ":" & IIf(mn < 10, "0" & mn, mn) & ":" & IIf(s < 10, "0" & s, s)))

End Function

Apparemment, cette fonction marche correctement quand on l’utilise directement sur la feuille. En revanche, quand on l’utilise dans une macro, uniquement pour le nombre de fuseaux qui séparent 2 méridiens il y a un problème. Si c’est, par exemple (voir PJ), 7,228 j’obtiendrai 7.228. Comment résoudre ce problème ?

 

[Dranreb]

Bonsoir.
Rappelez vous ce que je vous avais dit dans l'autre discussion :

il suffit soit de mettre préalablement à la cellule un format de texte soit de le faire précéder d'une apostrophe pour qu'Excel ne tente plus de convertir un String selon une règle d'interprétation toujours internationale dans les macros.

 

[pierrejean]

Bonjour Magic-doctor
Salut Dranreb
Une autre solution

 

[Magic_Doctor]

Bonjour Dranreb, pierrejean, le forum,

En fait, c'est très curieux cette histoire.
J'applique la solution de Dranreb : avec la macro ça marche ; problème avec la fonction sur feuille.
Avec la solution de pierrejean : c'est l'inverse.

Entre-temps j'ai simplifié le paramétrage de la fonction et j'ai rajouté d'autres résultats que l'on peut tirer de la fonction, dont le nombre de degrés sexagésimaux qui séparent les 2 méridiens. Ça marche très bien via macro mais pas avec la fonction sur feuille. Très excelien tout ça...

 

[Dranreb]

Bonjour.
Je ne parlais pas de faire renvoyer à la function un résultat commençant par une apostrophe, si vous tenez absolument à ce qu'elle renvoie un String au lieu d'un Double.
Je parlais d'en concaténer une devant son invocation pour son utilisation par affectation à valeur de cellule de format standard. Ou sinon de mettre préalablement à celle ci .NumberFormat = "@": ça interdit aussi à Excel d'essayer de l'interpréter selon la norme internationale, où la virgule est un séparateur de milliers.

Cela dit je ne comprends pas vos 7,772 fuseaux: =ABS(D6-D3)*24/360 me donne 4,653037067 À moins que vous le vouliez pour deux points de même distance mais à l'équateur ?

 

[pierrejean]

Re
Autre solution
NB: Vous aurez pu remarquer que mon type préféré pour les variables est Variant
Excel s'en depatouille très bien
Nb2: on me reproche souvent de ne pas declarer mes variables mais on oublie qu'Excel les déclare pour moi (en Variant)

 

[pierrejean]

Re
Encore une autre solution
Edit : Je n'avais pas vu le pb de D16

 

[Magic_Doctor]

Bonjour pierrejean, Dranreb,

pierrejean, voilà un moment déjà que nous nous croisons sur ce forum, et j'avais remarqué que vous ne mettiez aucun attribut aux variables déclarées (certains pousseraient des cris d'orfraie...). C'est curieux comme un "As Variant" peut changer les choses. Bref, votre solution marche, que la fonction soit utilisée directement sur la feuille ou via macro. Mais dans mon application, contre toute attente, je suis obligé dans la macro de formater la cellule réceptrice. ¡Caramba! En tout cas, merci pour votre aide.

Dranreb, je réponds à votre question. N'étant ni marin ni pilote, c'est la première fois que je bidouille des coordonnées géographiques ; j'ai donc improvisé la résolution de ces problèmes à ma manière, en glanant des formules à droite et à gauche...
Je suis à telle latitude, donc sur un parallèle donné. Une formule me donne la circonférence (circ) de ce parallèle, et une autre la distance (arc de cercle : LongArc) entre les 2 méridiens. Une autre formule (ValLongit) me donne la valeur, en km, de 1° de longitude à la latitude où l'on se trouve (autrement dit du parallèle). Divisant l'arc de cercle par cette dernière valeur, on obtient le nombre de degrés qui séparent les 2 méridiens. Sachant qu'un fuseau horaire correspond à 15°, divisant le nombre de degrés séparant les 2 méridiens par 15, on obtient le nombre de fuseaux.
Dans l'exemple de la PJ (coordonnées tout à fait arbitraires), on apprend :
- que le méridien passant par un des 2 points est distant de 6.798,228 km du méridien passant par l'autre point ;
- que le méridien passant par l'autre point (en plein antarctique) est distant de 2,258 km du méridien passant par l'autre mpoint.
- le décalage, entre ces 2 méridiens est de 7,772 fuseaux horaires (ce qui n'est pas très scientifique), autrement dit, exactement : 7 heures 46 mn 20 s. Ce qui, dans la réalité pragmatique, correspondrait plutôt à 7 heures de décalage.
Ma foi, à moins que je me sois planté quelque part (ce ne serait pas la première fois...), ça a l'air de tenir la route.
Entre Brest et Menton, il y a 0,532 fuseau ; autrement dit, 31' 55'' d'écart. Qui l'eût cru ? Voilà pourquoi Menton est une ville de vieux qui se couchent tôt !

 

[Dranreb]

Bonjour.
En tout cas la différence des longitudes divisée par 15 donne 4,653037067 et non pas 7,772.

 

[Magic_Doctor]

Re,

Trouvé l'erreur d'australopithèque. Dans la formule :

ValLongit = 6371.009 * wf.Acos(Sin(wf.Radians(lat)) ^ 2 + Cos(wf.Radians(lat)) ^ 2 * Cos(wf.Radians(1)))

j'avais mis une longitude. Maintenant nos résultats concordent. Et entre Brest et Menton, c'est encore pire, il y a en fait 47' et 56'' d'écart !