Hello
J'ai un code qui fonctionne en procédure, mais pas en Fonction.
L'idée est la suivante :  
 

 
Problème : la fonction n'affiche que #VALEUR! , sauf si j'ouvre en parallèle le fameux fichier.
Je voudrais au contraire que ce soit transparent et invisible pour l'utilisateur.
 
Est ce une limitation des fonctions VBA ?
 
 

 
           Bonjour,
 
           peut-on voir la procédure ? …
 

en simplifiant le code, ça donne ceci :
Si j'ai fichier.xls ouvert, ça marche, mais sinon j'obitiens #VALEUR!  
 
 
 
 
Function Portefeuille(NumColonne As Integer)
 
    Dim Repertoire As String
    Dim Fichier As String
    Dim CalculTotal As Currency
 
    Repertoire = "C:\Repertoire\"
    Fichier = "fichier.xls"
     
    'Ouverture du fichier
    Application.DisplayAlerts = False
    Workbooks.Open Filename:=Repertoire & Fichier
     
    CalculTotal = Application.Sum(Rows(NumColonne))
 
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    '______________
     
    Portefeuille = CalculTotal
 
End Function

 
           Au fait t'es sûr de ta ligne n° … Ah zut, t'as pas utilisé l'icône pour le code ‼
 
           Bref, j'ai déjà un doute sur la justesse du calcul …
 
           En fait tu aurais pu déjà trouver la solution en le faisant manuellement car cela fait partie des fonctions de base d'une feuille de calcul !
 
           Deux classeurs ouverts, la formule commence à être saisie =SOMME( puis tu changes de classeur et
           tu sélectionnes la colonne ou seulement une partie de la colonne, tu reviens dans le classeur et tu clôtures la formule
           par la parenthèse fermante et tu valides …
           T'as plus qu'à reluquer la formule comme une p'tite pépée se trémoussant sur la piste de danse d'un night club !
 
           Matière à réflexion, non ?!   Toujours commencer par les bases, soit en manuel soit via l'Enregistreur de macros puis après on peaufine,
           sauf si vraiment on est un cador, mais là c'est une autre histoire …
 
           

Ta méthode marche en Procédure, de même que la mienne.
Mais ici on a une fonction : dans une cellule tu tapes =MACHIN(argument)  et ça te ramène une valeur.
Et j'ai l'impression que l'ouverture de feuille fonctionne moyennement en "Function"

 
           Heu, quand les messages se suivent, pas la peine de citer le message précédent ! …    
 
           Ah là c'est une fonction personnalisée (appelée depuis une cellule) et non pas une simple fonction VBA !
           Effectivement il y a une limitation comme ne pas pouvoir toucher aux cellules, juste renvoyer une valeur à l'appelante …
 
           J'ai essayé par la méthode de l'instance cachée récupérant bien la somme mais tiltant Excel lors du retour !
 
 
           Je ne vois plus que la connexion ADO (ActiveX Data Objects) mais disposant aussi de contraintes :
 
           ◙  une référence doit être activée mais ce n'est pas la même selon la version des fichiers à accéder si < à 2007 ou >= à 2007 …
 
           ◙  Limitations lors de la transposition interne d'un tableau mais possibilité de contourner via une procédure VBA
              (voir Comment faire pour transférer des données d'un jeu d'enregistrements ADO vers Excel avec l'Automation).
 
           ◙  Pas la possibilité d'utiliser un objet colonne, il faut indiquer précisément l'adresse des cellules à récupérer
               mais s'arrête dès la première cellule vide rencontrée …
               Dans ma fonction à suivre, si seulement la première cellule est indiquée, elle comble automatiquement l'adresse de fin de colonne.
 
           ◙  Apparemment il ne faudrait pas de $ dans l'adresse de la plage des cellules
               car il sert à séparer le nom de la feuille de calcul et la plage de cellules; ma fonction le gère aussi.
 
           ◙  La fonction personnalisée doit être définie comme  Volatile  afin qu'elle se mette à jour en cas de changement dans la source,
               mais elle est recalculée chaque fois qu'un calcul est effectué dans une cellule quelconque de la feuille de calcul,
               donc plus de cellules utilisent cette fonction personnalisée plus le temps de calcul s'allonge …
 
               #VALEUR!  peut aussi apparaître dans une cellule utilisant cette fonction
               mais tout rentre dans l'ordre au prochain calcul dans la feuille …  Rien qu'en supprimant une cellule vide !    
 
 
           Autre article à consulter :   Méthodes de transferts de données vers Excel à partir de Visual Basic …
 
 
           Dans l'environnement VBA, il faut activer une référence via le menu Outils.
 
           Pour accéder aux .xlsx et autres fichiers à partir d'Excel 2007, je n'ai pas la référence exacte, ma version sur un portable
           itinérant n'étant pas encore rentrée et, qui plus est, je ne serais pas disponible pendant une bonne semaine.
 
           Au cas où :   Microsoft Download Center, 2007 Office System Driver : Data Connectivity Components
 
           J'ai coché la référence Microsoft ActiveX Data Objects 6.1 Library pour accéder à un fichier .xls …
 

           Exemple de formule dans une cellule :   =Actions(G11)
 
 
           Si tu ne t'en sors pas, je te laisse te documenter de ton côté …
 
           Et si tu trouves une autre méthode, merci ne nous la faire partager !
 
 
           _______________________________________________________________________________________
           Comme la vitesse de la lumière est supérieure à celle du son, certains ont l'air brillant avant d'avoir l'air con !

Merci pour cette réponse très complète !  
Je vais tester ça, mais c'est vrai que ça devient lourd à gérer, surtout que la fonction doit être chargée sur d'autres PC, donc d'autres config (et éventuellement d'autres versions d'Excel).
 

 
           En revenant aux bases, simplement tu nommes une cellule par exemple Colonne7 puis tu la caches
           et elle se met à jour à l'ouverture du classeur :
 

           Ensuite dans les formules tu utilises ce nom :   =Colonne7
 
           Si t'en as plusieurs de ce genre, tu peux alors leur dédier une feuille du classeur et la cacher,
           ce qui n'empêche pas d'utiliser leurs noms …
 

 
           Je viens de tester sans souci sur Excel 2007 l'accès à un fichier .xls (donc antérieur à 2007) avec ma procédure ADO telle quelle !
 
           Donc si tu n'as pas besoin de dépasser la limite des 65536 lignes par 256 colonnes,
           tu peux alors enregistrer le fichier source en .xls vu la compatibilité ascendante des versions ultérieures …
 
           Maintenant, ma préférence reste sur des cellules dédiées, tout dépend en fait du contexte …

 
           Bonjour,
 
           suite à un problème récent de la même veine, je complète ce sujet en clarifiant la connexion ADO (ActiveX Data Objects)
           étant quelque peu fautif et pourrait aussi servir de sujet référence pour des questions similaires …
 
           Deux méthodes possibles de déclaration de variable objet : la liaison anticipée et la liaison tardive.
 
           La liaison anticipée (Early Binding), avant toute intervention sur un objet au moyen d'une variable objet,
           associe le type exact de l'objet en question à la variable objet s'y référant via l'instruction Dim
           encore faut-il ajouter une librairie aux Références du projet (via le menu Outils) …
 
           Avantages :  exécution un peu plus rapide car pas d'ambiguïté vis à vis d'une variable objet générique,
           le pré-compilateur sachant d'avance à quoi s'attendre, tout est prêt au moment de l'exécution;
           conservation de l'IntelliSense au cours de la rédaction du code (Complément automatique des instructions des Options Éditeur),
           dès le point tapé contre le nom d'un objet, la liste de ses méthodes & propriétés associées s'affichant automatiquement …
                       
           Mais si le nom exact de la librairie à activer est inconnu, impossible donc de déclarer la variable objet en liaison anticipée !
           Qui plus est selon la version d'Excel ou du système d'exploitation, les librairies peuvent changer, nuisant ainsi à la portabilité du projet.
           Dans ces cas, la liaison tardive (Late Binding) s'impose de facto …
 
           Mea Culpa F22Raptor car dans mon code du 26/7 j'ai réussi à effectuer un pont entre ces deux méthodes
           avec une liaison tardive tout en ayant l'obligation d'activer la référence d'une librairie !    
           Certainement par trop pressé, cela arrive quand on veut boucler la semaine en menant plusieurs fronts à la fois …
 
           Conservant la problématique de ce sujet, les codes à suivre sont en liaison anticipée car la référence à activer
           reste la même en version 2003 comme en 2007 d'Excel.             La liaison tardive va aussi être abordée …
 
       ◙  Premier cas :  connexion ADO pour accéder à un fichier fermé  .xls  (format antérieur à la version 2007)
                                indifféremment en version 2003 ou 2007 d'Excel (version remaniée du 26/7) :

           En ligne n°13, Feuil1 représente le nom de la feuille de calcul du classeur fermé, le $ le séparant de la plage de cellules …
 
       ◙  Voici comment passer en liaison tardive quelle que soit la version d'Excel, en cas de souci par exemple pour activer la référence :
 
           →  Insérer avant la ligne n°10 cette ligne :  Set Cnx = CreateObject("ADODB.Connection" )
 
           →  Supprimer la ligne n°4  Dim  puis les lignes n°1 & 2 …
 
 
       ◙  Second cas :  connexion ADO pour accéder à un classeur fermé  .xlsx  à partir d'Excel 2007 :

           Seule la commande de connexion change par rapport au cas précédent (lignes n°10-11) …
 
           Je ne sais pourquoi fin juillet j'ai essuyé un échec lors de mon test en liaison anticipée sur Excel 2007,
           je croyais ne pas utiliser la bonne référence mais, comme déjà évoqué, je n'avais guère le temps …
 
           Bonus :  cette connexion fonctionne aussi avec les classeurs  .xls  antérieurs à Excel 2007,
                        la préférer donc sous Excel 2007, une seule connexion quel que soit le type de classeur !
 
 
       ◙  Astuce :  par défaut, Excel 2003 ne peut ouvrir les classeurs  .xlsx  de la version 2007.
 
                        C'est devenu possible grâce au Pack de compatibilité Microsoft Office 2007 !
 
                        Et pour le second cas ci-dessus de la connexion ADO, c'est aussi possible grâce
                        au Pilote d'Office System 2007 des composants de connectivité des données !
                        (lien déjà donné fin juillet mais cette fois-ci en version française)
 
                        Excel 2003, ainsi mis à jour, un message prévient de la conversion en cours lors de l'ouverture d'un classeur  .xlsx   …
                        Il n'y a pas de message lors de la connexion ADO avec un tel classeur,
                        elle est juste quelque peu ralentie le temps de la conversion mais cela fonctionne !
 
                        Toutefois certainement dans les limites du nombre de lignes et de colonnes d'une feuille d'un classeur d'Excel 2003 …