Variable de texte dans une Function

Bonjour.

Voici mon souci.

Parmi d'autres, j'utilise ce bout de code, dans une fonction, pour la création d'un distancier Excel usant des API de Google Maps

Function AversB(A As String, B As String) As deAaB

Dim Depart As String, Arrivee As String, Site As String
Dim Json As Object, Elem As Object, Elem1 As Object
Dim ok As Boolean

    With Sheets("Distancier_via_GoogleMaps")
        Depart = SupprCaracSpe(A)
        Arrivee = SupprCaracSpe(B)

        On Error Resume Next
        Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=XxXXx_XXXxX_xxXXx_XxXxX["
        Set Json = oRecordSet(Site)

        For Each Elem In Json.rows
            For Each Elem1 In Elem.elements
                ok = Not (Elem1.status = "ZERO_RESULTS")
                AversB.dist = Elem1.distance.Value / 1000
                AversB.duree = Elem1.duration.Value / 24 / 60 / 60
            Next Elem1
        Next Elem

        ScriptControl.AddCode "Object.prototype.item=function( i ) { return this[i] } ; "
        AversB.ptA = Json.origin_addresses.item(0)
        AversB.ptB = Json.destination_addresses.item(0)

        If Not ok Then
            AversB.dist = 0
            AversB.duree = 0
        End If

        Set Json = Nothing

    End With

End Function

Ce code, associé à d'autres FUNCTION et à une SUB, fonctionne à merveille... et même encore il y a quelques jours si on supprimait la clé API personnelle (key=)

Mais depuis quelques jours, les API Google Maps sont (presque) plus gratuites, et mon outil doit être utilisé par plusieurs personnes, dans des lieux différents.

Je n'ai pas envie de partager ma clé API perso avec tous et payer pour les autres lol... et l'utilisation en mode "anonyme" ne fonctionne plus donc.

Dans mon fichier Excel, j'ai donc ajouter une cellule dans une autre feuille, où il est demandé à l'utilisateur de saisir sa clé API.

Sauf que je n'arrive pas à l'intégrer dans mon code Function que je vous ai présenté... J'avais pensé à l'ajout d'une variable :

(...)
Dim API_Perso As String
API_Perso = Range("A1").Value   'ou Cells(1, 1).Value

(...)

Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=" & API_Perso

(...)

Mais cela ne fonctionne pas... Je débute dans les macros, mais je crois bien que je ne peux pas déclarer la valeur d'une cellule dans une variable, et l'utiliser comme dans un Sub, non ?

J'avais pensé à une macro qui copie-coller le contenu "texte" d'une cellule directement au sein de la macro suivante (genre j'insère ma ligne 'site = "https://......' avec la clé en cellule A1 et une première macro me copie le texte en ligne 12 de la macro suivante, qui contient le code 'FUNCION', dans le Module 1 VBA... Mais je ne sais pas si c'est possible.

Y a t-il une solution concrète ? Laquelle ? Comment ?

Merci mille fois !

Bonjour,

Electro02 a écrit :

Dans mon fichier Excel, j'ai donc ajouter une cellule dans une autre feuille, où il est demandé à l'utilisateur de saisir sa clé API.

(...)
Dim API_Perso As String
API_Perso = Range("A1").Value   'ou Cells(1, 1).Value

(...)

Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=" & API_Perso

(...)

Mais cela ne fonctionne pas... Je débute dans les macros, mais je crois bien que je ne peux pas déclarer la valeur d'une cellule dans une variable, et l'utiliser comme dans un Sub, non ?

Appelle ta zone API_Perso et ensuite mets dans ton code [API_Perso] avec crochets ou Range("API_Perso").Value

Bonjour,

Je n'ai pas lu jusqu'au bout et j'espère que je n'ai rien loupé... Mais typiquement, si une clé est nécessaire, si elle n'est pas en dur dans le code de ta fonction, il faut qu'elle soit passée en argument à la fonction.

Tu rajoutes donc un argument du type de données de la clé, et tu l'utilises dans la fonction !

Cordialement.

Steelson a écrit :

Appelle ta zone API_Perso et ensuite mets dans ton code [API_Perso] avec crochets ou Range("API_Perso").Value

Bonjour Steelson.

Si j'ai bien tout suivi, j'ai supprimé le DIM et la ligne suivante, j'ai nommé ma cellule (on va dire A1), qui contient la clé, en "API_Perso" au lieu de A1, et j'ai mis dans ma FUNCTION :

(...)
Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=" & [API_Perso]
(...)

J'ai aussi essayé :

(...)
Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=" & Range("API_Perso").Value
(...)

Mais aucune de ces possibilités ne fonctionnent... J'ai toujours des résultats à 0.00

Je crois que c'est parce que je suis dans un FUNCTION et non dans un SUB...

MFerrand a écrit :

Tu rajoutes donc un argument du type de données de la clé, et tu l'utilises dans la fonction !

Bonjour MFerrand

Oui justement le but est d'ajouter un argument qui est variable selon le contenu d'une cellule de la feuille, mais je n'arrive pas à l'intégrer en tant que tel dans un FUNCTION (je ne suis pas dans un SUB sinon je n'aurai même pas cherché ^^).

Comment faire ?

Le principe habituel :

Dim API_Perso

Site = "https://.........." & API_Perso

ne fonctionne pas dans un FUNCTION

Si ça peut aider : le code complet dans mon Module 1 :

Option Explicit

Public ScriptControl As Object

Public Type deAaB
    ptA As String
    ptB As String
    dist As Single
    duree As Single
End Type

Sub Serie()
Dim Tdata As Variant, lg As Long, i As Long
Dim Trajet As deAaB

Range("J1").Value = "Veuillez patienter svp..."

    With Sheets("Distancier_via_GoogleMaps")
        lg = .Cells(rows.Count, "A").End(xlUp).row
        Tdata = .Range(.Cells(2, "A"), .Cells(lg, "F")).Value

        For i = 1 To lg - 1
            Trajet = AversB(SupprCaracSpe(Tdata(i, 1)), SupprCaracSpe(Tdata(i, 2)))
            Tdata(i, 5) = Trajet.ptA
            Tdata(i, 6) = Trajet.ptB
            Tdata(i, 3) = 1 * Format(Trajet.dist, "# ###.00")
            Tdata(i, 4) = Trajet.duree
        Next i

        .Range("A2").Resize(UBound(Tdata, 1), UBound(Tdata, 2)) = Tdata
    End With

    Range("J1").Value = "Cliquez sur ETAPE 2 svp"

End Sub

Function SupprCaracSpe(Sv As Variant) As String

Dim S As String

    S = CStr(Sv)
    S = Replace(S, "'", " ")
    S = Replace(S, "L ", "")
    S = Replace(S, "â", "a")
    S = Replace(S, "à", "a")
    S = Replace(S, "ä", "a")
    S = Replace(S, "ê", "e")
    S = Replace(S, "é", "e")
    S = Replace(S, "è", "e")
    S = Replace(S, "ë", "e")
    S = Replace(S, "ï", "i")
    S = Replace(S, "ô", "o")
    S = Replace(S, "ö", "o")
    S = Replace(S, "û", "u")
    S = Replace(S, "ù", "u")
    S = Replace(S, "ü", "u")

    SupprCaracSpe = S

End Function

Function AversB(A As String, B As String) As deAaB

Dim Depart As String, Arrivee As String, Site As String
Dim Json As Object, Elem As Object, Elem1 As Object
Dim ok As Boolean

    With Sheets("Distancier_via_GoogleMaps")
        Depart = SupprCaracSpe(A)
        Arrivee = SupprCaracSpe(B)

        On Error Resume Next
        Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=XXXXX_XXXXX_XXXXX_XXXXX"
        Set Json = oRecordSet(Site)

        For Each Elem In Json.rows
            For Each Elem1 In Elem.elements
                ok = Not (Elem1.status = "ZERO_RESULTS")
                AversB.dist = Elem1.distance.Value / 1000
                AversB.duree = Elem1.duration.Value / 24 / 60 / 60
            Next Elem1
        Next Elem

        ScriptControl.AddCode "Object.prototype.item=function( i ) { return this[i] } ; "
        AversB.ptA = Json.origin_addresses.item(0)
        AversB.ptB = Json.destination_addresses.item(0)

        If Not ok Then
            AversB.dist = 0
            AversB.duree = 0
        End If

        Set Json = Nothing

    End With

End Function

Function oRecordSet(txt As String, Optional www As Boolean = True) As Object

Dim Html As Object, Obj As Object
Dim S As String

    Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")
    ScriptControl.Language = "JScript"

    If www Then
        Set Html = CreateObject("MSXML2.XMLHTTP")
        With Html
        .Open "GET", txt, False
        .send
        S = .responsetext
        End With
    Else
        S = txt
    End If

    Set Obj = ScriptControl.Eval("(" & S & ")")
    Set oRecordSet = Obj
    Set Obj = Nothing

End Function

Je ne te suis pas !

Steelson pourra peut-être t'éclairer mieux que moi compte tenu de la matière...

Function ou Sub, la distinction ne présente en principe pas d'importance. Une Function peut juste renvoyer en plus un résultat, ce qu'une Sub ne fait pas.

Je ne vois pas ce qui constitue ta clé perso dans la fonction que tu cites (mais je n'ai pas trop regardé, il faut scroller... ), mais tu parles à son propos d'une donnée de type String. Je ne vois pas ce qui l'empêche d'être affectée à une variable de type String ! Le texte renvoyé sera le même.

Il ne s'agit pas de déclarer une variable locale dans la fonction mais de la doter d'un argument supplémentaire en sus des deux qu'elle possède déjà, afin de lui passer l'élément manquant qu'elle pourra utiliser.

Après que tu mettes ce texte dans une cellule, c'est autre chose. C'est lors de l'appel de la fonction que tu feras référence à la cellule qui contient le texte à passer au lieu de le passer sous forme texte, c'est indifférent.

Il y a peut-être des éléments que j'ignore, notamment ton type de données deAaB renvoyé par la fonction qui ne peut être que personnalisé...

Cordialement.

C'est sûr que pour bien comprendre ce que je demande, où ça se trouve et pouvoir m'aider plus avant, c'est mieux de lire tout ce que j'ai mis

En tout cas je pense avoir bien compris tout ce que tu m'as expliqué et je suis d'accord avec toi sur le principe.

J'ai essayé d'appliquer une variable String, dont la valeur est celle d'une cellule, et j'ai mis ça au bout de ma ligne, comme je le fais habituellement dans un Sub, mais ça ne marche pas, et je ne comprends pas pourquoi.

Encore une fois je parle d'argument de la fonction, pas de variable interne !

Function AversB(A As String, B As String, K As String) As deAaB

Un argument se place dans la déclaration de procédure. Si K représente la clé passée par l'utilisateur, tu utilises K dans ton code de la fonction comme clé...

Bonjour MFerrand

@ Electro02 : un exemple d'appel d'une variable dans une sub et une function

si ta macro fonctionne avec ta clé en dur dans la macro, elle fonctionnera avec la clé dans la zone nommée API_Perso

Merci à vous deux.

Je vais analyser votre dernière réponse et je vais surtout essayer de comprendre la mécanique d'interactivité Sub<>Function.

Débutant en VBA, ne maîtrise pas le sujet pour le moment...

J'espère que cette fois je vais y arriver

N'hésite pas à revenir ... c'est pour cela que j'ai voulu faire un exemple simplissime. Du reste, dans cet exemple, la variable prise dans la fonction ne sert à rien, le but était d'illustrer la récupération de la clé google.

Bon eh bien cela ne marche pas et je ne comprends toujours pas pourquoi :

Function AversB(A As String, B As String) As deAaB

Dim Depart As String, Arrivee As String, Site As String, NUMKEY As String
Dim Json As Object, Elem As Object, Elem1 As Object
Dim ok As Boolean

NUMKEY = Range("API_KEY").Value

    With Sheets("Distancier_via_GoogleMaps")
        Depart = SupprCaracSpe(A)
        Arrivee = SupprCaracSpe(B)

        On Error Resume Next
        Site = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Depart & "&destinations=" & Arrivee & "&mode=driving&language=fr-FR&key=" & NUMKEY
        Set Json = oRecordSet(Site)

        For Each Elem In Json.rows
            For Each Elem1 In Elem.elements
                ok = Not (Elem1.status = "ZERO_RESULTS")
                AversB.dist = Elem1.distance.Value / 1000
                AversB.duree = Elem1.duration.Value / 24 / 60 / 60
            Next Elem1
        Next Elem

        ScriptControl.AddCode "Object.prototype.item=function( i ) { return this[i] } ; "
        AversB.ptA = Json.origin_addresses.item(0)
        AversB.ptB = Json.destination_addresses.item(0)

        If Not ok Then
            AversB.dist = 0
            AversB.duree = 0
        End If

        Set Json = Nothing

    End With

End Function

Et quand on me met pas la clé en variable, mais qu'on la met simplement en toutes "lettres" après le signe égal, ça marche impec !

Je ne comprends pas, franchement. Ça devrait fonctionner pourtant...

Je viens d'essayer d'autres solution :

• Paramétrer la variable "Site"' en amont, en ajoutant deux variables "Site = PreSite & API_Key"
• Mettre des [] au lieu du range,( en ayant renommé ma cellule contenant la clé en API_Key bien-sûr)
• Ajouter carrément une fonction, appelée pour avoir l'adresse complète.
• Remplacer l'appel de valeur de la cellule par un InputBox

Rien n'y fait ! Ca ne marche que dans les cas suivants :

• je ne mets pas de clé du tout (ca marche de temps en temps mais Google a fait en sorte que d'ici quelques jours ça ne marche plus du tout)
• j’écris la clé "en dur" dans le code, dans ma Function

Y'a t-il un moyen pour que le contenu de ma cellule [API_Key] vienne, à l'aide d'un autre macro, s'écrire en dur au bout de la ligne 71 de mon Module 1 (ou en Li 71, Col 151) ????

Je ne vois que cette solution...

Oui on peut faire une macro qui écrit une macro ... mais c'est complexe !

Es-tu sûr qu'il n'y a même pas un espace en trop ?

Je vais essayer avec ma clé ce soir et je te dirai.

Oui c'est sûr. Pas d'espace en trop, respect de la casse, des underscores, etc...

Alors voilà ...

Qu'il y ait ou non le clé, cela ne change rien !

J'ai pris un programme plus simple que le tien

1- j'ai ajouté un message donnant le site interrogé et montrant la présence de la clé elle est bien captée

2- ce "site" est aussi récupérable par le débogueur après avoir ajouté un debug.print dans la macro

3- l'interro de google se fait bien ... on voit du reste les villes comme Lyon décodée par google en Lyon, France

edit ... voir le post https://forum.excel-pratique.com/viewtopic.php?p=660296#p660296

Ensuite, j'ai changé de programme complètement, d'IP, bref tout ce que je pouvais ... et pour le moment avec ou sans clé en dur dans la macro ou dans la zone dédiée j'ai encore toujours des résultats ! jusque quand ? il faudrait que je fasse une interro en masse mais ce programme n'est pas vraiment fait pour cela ! ... je vais peut-être le bricoler pour une interro massive jusqu'à ce que google n'apprécie plus !

Mais à nouveau, pour ta demande initiale d'inclure une variable dans une macro (fonction ou sub), le problème est tel qu'il a été proposé plus haut.

je reviens sur mes explications avec ce nouveau code !!

ce code modifié permet de récupérer dans le presse-papier l'interrogation www

1- les valeurs sont bien récupérées !!! wahou voir ci-dessous

2- pour autant elles sont affichées à 0 ............. n'y aurait-il pas un problème dans la macro de lecture du json ?

{
   "destination_addresses" : [ "Lyon, France" ],
   "origin_addresses" : [ "Marseille, France" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "315 km",
                  "value" : 314823
               },
               "duration" : {
                  "text" : "3 heures 8 minutes",
                  "value" : 11283
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

{
   "destination_addresses" : [ "Rennes, France" ],
   "origin_addresses" : [ "Nantes, France" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "113 km",
                  "value" : 113313
               },
               "duration" : {
                  "text" : "1 heure 27 min",
                  "value" : 5199
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}