Discussion :

[deTouteBoT]

Hello la team,

Je me confronte à un petit problème depuis un moment. J'ai fais une fonction excel permettant de récupérer des données( données financières : donc date en 1er colonne et le reste) sur d'autres fichiers, via une filtre de date avec une date de début et une date de fin.

L'objectif final étant de mettre cette fonction dans une macro complémentaire afin d’être utilisé sur l'ensemble des classeurs

Le problème est que je n'arrive pas à la faire fonctionner sur excel( ça me sort un value), je suis obligé de passer par une procédure ( worksheet change ) mais cette méthode ne convient pas vraiment, il me faudrait une fonction !

C'est une imitation d'une fonction bloomberg type BDH pour ceux qui connaissent!

Auriez-vous une feinte ?

J'ai mise un fichier avec la macro et un fichier "ticker1" : c'est le fichier qui contient les données que la fonction doit interroger

Voici voulou, j'espère que j'étais assez claire

Merci à vous

Lucy

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
 
Public Function FichierExiste(MonFichier As String)
 
   If MonFichier <> "" And Len(Dir(MonFichier)) > 0 Then
      FichierExiste = True
   Else
      FichierExiste = False
   End If
End Function
 
'-------------------------------------------------------------------------------------------------------
Public Function getdata(ticker As String, Datedebut As String, Datefin As String) As Variant
 
Application.ScreenUpdating = False
 
Dim FilePath As String
Dim wbTicker As Workbook
Dim wsTicker As Worksheet
Dim Rng As Range
Dim datas()
 
FilePath = "C:\" & ticker & ".xlsx"
 
If FichierExiste(FilePath) = False Then
        MsgBox "Le fichier " & ticker & " n'existe pas dans la base, veuillez le créer!"
        Exit Function
 
Else:
    Set wbTicker = Workbooks.Open(FilePath)
    Set wsTicker = wbTicker.Sheets("Feuil1")
 
         With wsTicker
            .AutoFilterMode = False
            .Range("A2").AutoFilter Field:=1, Criteria1:=">=" & Format(Datedebut, "mm/dd/yyyy"), _
            Operator:=xlAnd, Criteria2:="<=" & Format(Datefin, "mm/dd/yyyy"), VisibleDropDown:=False
            On Error Resume Next
            Set Rng = .AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
    If Not Rng Is Nothing Then
        With wbTicker.Worksheets.Add
 
            Rng.Copy .Range("A1")
            datas = .UsedRange.Value
            Application.DisplayAlerts = False
            .Delete
            Application.DisplayAlerts = True
 
        End With
    getdata = datas
    Else
        MsgBox "Filtre sans résultat"
    End If
    wbTicker.Close SaveChanges:=False
 
End If
 
Application.ScreenUpdating = True
 
Set wbTicker = Nothing: Set wsTicker = Nothing:
 
End Function
 
'---------------------Partie Worksheet Change------------
 
 
 
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Excel.Range)
 
Dim datas As Variant
Dim ContenuInitial As String
Dim a() As String
Dim ticker As String, d1 As String, d2 As String
 
If Target.Count > 1 Then
Exit Sub
End If
ContenuInitial = Target.Value
 
If ContenuInitial Like "*,*,*" Then
    a() = Split(ContenuInitial, ",")
    ticker = a(0)
    d1 = a(1)
    d2 = a(2)
 
    datas = getdata(ticker, d1, d2)
    If datas <> "" Then
    Target.Resize(UBound(datas, 1), UBound(datas, 2)).Value = datas
    Else: Exit Sub
    End If
 
Else: Exit Sub
End If
 
End Sub

macro.xlsmticker1 (2).xlsx

[EricDgn]

Bonjour,

J'ai constaté que l'instruction Workbooks.Open ne peut être utilisée qu'au départ d'une routine (Sub).

Quand elle est appelée au départ d'une fonction, elle échoue et interrompt la procédure en cours ... sans donner de message d'erreur.

Donc, pour votre problème, il faut partager une routine (Sub), activable par exemple par un raccourci clavier (exemple en attaché) ou via un bouton ajouté dans le ruban, ou comme vous l'avez fait par une Sub ..._Change() au niveau d'une feuille ou du dossier.

Cordialement.

[Menhir]

En théorie, une fonction sert à calculer une valeur : on y entre des paramètres, on en ressort une valeur.
Elle n'est pas supposée pouvoir réaliser des actions, par exemple en utilisant des méthodes.

En pratique, VBA est un peu tolérant dans ce domaine (beaucoup plus que d'autres langages) mais il a ses limites et, pour la fiabilité du code, il est préférable de ne pas tenter de les enfreindre.
Mettre un Open dans une Function, c'est contre nature. Il faut le mettre dans un Sub.

Si tu veux que ce Sub renvoie une valeur, il y a plusieurs solution.
Tu peux par exemple utiliser une variable déclarée Public au niveau module.
Le plus propre, à mon avis, serait d'utiliser un paramètre du Sub indiqué ByRef dans la déclaration du Sub.

[Pierre Fauconnier]

Salut

[...]
Elle n'est pas supposée pouvoir réaliser des actions, par exemple en utilisant des méthodes.

Mwouais. Perso, je me méfie des généralités exprimées suite à un regard par "son petit bout de lorgnette"

Je suis très dubitatif sur l'affirmation ci-dessus. L'avantage d'une fonction, c'est qu'elle peut justement réaliser des actions ET renvoyer une valeur (précisant le résultat de l'action réalisée, par exemple). C'est comme cela que les api windows fonctionnent et je pense que ça devrait être mieux exploité dans nos codes.

Voici une fonction qui fait quelque chose ET qui renvoie une valeur (wouah, c'est magique, non? ) et pourtant c'est une fonction et pas une procédure... Comme quoi, les généralités... . La sub CreateSheet exploite le résultat renvoyé par la fonction pour informer l'utilisateur du résultat de son action. Dans une architecture "trois tiers", c'est une pratique courante pour réaliser des actions ET traiter le résultat de cette action.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Function CreateWorksheet(Name As String, Optional ReturnToActiveSheet As Boolean = False) As Long
  Dim sh As Object
  Dim Found As Boolean
  Dim Counter As Long: Counter = 1
  Dim shActive As Object
 
  If ReturnToActiveSheet Then Set shActive = ActiveSheet
  Do While Counter <= ActiveWorkbook.Sheets.Count And Not Found
    Found = StrComp(ActiveWorkbook.Sheets(Counter).Name, Name, vbTextCompare) = 0
    Counter = Counter + 1
  Loop
 
  If Not Found Then
    If Not ActiveWorkbook.ProtectStructure Then
      Set sh = ActiveWorkbook.Worksheets.Add()
      sh.Name = Name
      If ReturnToActiveSheet Then shActive.Activate
    Else
      CreateWorksheet = 2 ' classeur protégé
    End If
  Else
    CreateWorksheet = 1 ' Feuille existante
  End If
End Function
 
Sub CreateSheet()
  Select Case CreateWorksheet("Martine")
    Case 0
      MsgBox "La feuille a été créée"
    Case 1
      MsgBox "La feuille existe déjà"
    Case 2
      MsgBox "Le classeur est verrouillé"
  End Select
End Sub

[...]
Mettre un Open dans une Function, c'est contre nature. Il faut le mettre dans un Sub.[...]

Voilà une fonction qui ouvre un classeur et qui le renvoie en même temps. elle permet d'ouvrir un classeur et de le manipuler dans le code (la fonction renvoie un workbook) tout en restant dans le classeur actif.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Function getBook(Name As String, Optional Template As Boolean) As Workbook
  If Template Then
    Set getBook = Workbooks.Add(Name)
  Else
    Set getBook = Workbooks.Open(Name)
  End If
  ThisWorkbook.Activate
End Function

On peut aussi avoir une fonction qui reçoit un workbook en argument byRef et qui renvoie une valeur informant du fait qu'on a pu l'ouvrir ou qu'il n'existait pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Function getWorkbook(wb As Workbook, Filename As String, Optional AsActive As Boolean) As Long
  If Dir(Filename) <> vbNullString Then
    Set wb = Workbooks.Open(Filename)
    If Not AsActive Then ThisWorkbook.Activate
  Else
    getWorkbook = 1
  End If
End Function
 
Sub test()
  Dim wb As Workbook
 
  Select Case getWorkbook(wb, "c:\data\temp\monclasseur.xlsx")
    Case 0
      ' On travaille avec
    Case 1
      MsgBox "Le classeur n'existe pas"
  End Select
End Sub

Les raisons d'utiliser une fonction qui fait quelque chose sont donc nombreuses...

La seule différence que l'on peut faire entre une procédure et une fonction, c'est qu'une procédure ne renvoie jamais de valeur, alors qu'une fonction renvoie toujours une valeur. On ne pourra donc jamais avoir MaVariable = MaProc() alors qu'on pourra toujours avoir MaVariable = MaFonction()




@Eric (salut )

Bonjour,

J'ai constaté que l'instruction Workbooks.Open ne peut être utilisée qu'au départ d'une routine (Sub).

Quand elle est appelée au départ d'une fonction, elle échoue et interrompt la procédure en cours [...]

Comme je l'ai illustré plus haut, il n'y a aucun problème à utiliser Open dans une fonction. Par contre, là où je te rejoins, c'est que le code "en pas à pas" quitte le VBA la plupart du temps dans ce contexte. Il suffit souvent de placer le point d'arrêt plus loin et de laisser dérouler la routine jusqu'au point d'arrêt pour que cela ne pose pas de problème en mode "debug"...


Il faut savoir que dans la plupart des autres langages, on ne fait pas la différence entre une fonction et une procédure. Cette différence est une particularité du VBA. Dans les autres langages, on parle uniquement de fonction. Il y a des fonctions qui renvoient des valeurs et des fonctions "void" qui ne renvoient rien (les procédures VBA). Mais en fait, utiliser une fonction plutôt qu'une procédure permet de réaliser une action ET de renvoyer le résultat de celle-ci.

Le fait d'écrire une fonction plutôt qu'une procédure permet, en plus, de la masquer à l'interface de lancement des macros d'Excel, tant sur le ruban "développeur" que lors de l'affectation à un bouton...





Donc, se méfier des généralité et comprendre ce que l'on utilise. Ca permet de faire de très belles choses en programmation.

En résumé, fonction ou procédure, on s'en fout complètement. Une procédure réalisera les actions souhaitées (ou plantera), une fonction pourra réaliser exactement les mêmes actions (ou plantera) mais pourra renvoyer une valeur (texte, valeur numérique, objet, tableau, ...) te permettant de savoir comment l'action s'est réalisée ou pas, si elle a abouti ou pas, et les raisons de sa non-exécution. Tu pourrais avoir +/- les mêmes infos avec une procédure et une gestion d'erreur (on error..., mal aimé de certains, mais indispensable dans tout code digne de ce nom et qu'il suffit "d'apprivoiser" plutôt que de l'avoir en horreur...)

Au passage...

Le plus propre, à mon avis, serait d'utiliser un paramètre du Sub indiqué ByRef dans la déclaration du Sub.

@ByRef est la façon par défaut de passer un argument, ce qui rend donc l'indication ByRef inutile. Il faut noter cependant que:

• L'on peut déroger au ByRef Lors du passage d'un seul argument à une procédure en passant l'argument entre parenthèses à la procédure (<> fonction dans ce cas de figure);
• Que certains arguments, notamment les objets, sont toujours passé ByRef malgré un byVal explicite dans la signature de la procédure/fonction.

@DeTouteBot,

Je ne saurais trop te conseiller de découper ton code en fonctions ou procédures qui sont "petites" et qui n'ont qu'une responsabilité. Tu pourras dès lors tester plus facilement chaque portion de code (qui recevra ses propres paramètres) et tu les chaineras dans une procédure qui lie le tout... Les différentes techniques que j'ai illustrées ici t'aideront à avoir un code plus fiable, plus facilement testable et maintenable.


Moralité: Le monde est bien plus vaste que notre petite réalité étriquée vue du mauvais côté de notre petite lorgnette




* Une fonction perso utilisée dans une formule EXCEL ne peut "rien" réaliser dans Excel... Pour faire simple, on va dire qu'elle ne peut rien modifier dans l'interface Excel, mais elle peut réaliser plein de trucs à côté...

[deTouteBoT]

Bonjour à tous,

Merci pour tout vos retours plus que constructif!

Eric j'ai constaté la même chose que c'était l'ouverture du fichier qui faisait planter, oui effectivement je me suis tourné vers cette solution pour l'instant mais je cherche toujours un moyen

Mendir, je vais regardais ta solution avec By ref, concernant les limites des fonctions j'ai quand même bien vie d'essayer de les tâter

Pierre, merci aussi pour tout les exemples, je comptais morceler mon code quand il marcherait mais ce n'est peut être pas la meilleure stratégie

Concernant, les fonctions j'ai trouvé un code permettant de contourner cette limite et modifier d'autres cellules, je vous laisse tester!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function recopie(copieDE As Range, copieVERS As Range)
    ongletDE = copieDE.Parent.Name
    ongletVERS = copieVERS.Parent.Name
    Evaluate "remplace(" & copieDE.Address(False, False) & ",""" & ongletDE & """," & copieVERS.Address(False, False) & ",""" & ongletVERS & """)"
    recopie = Now
End Function
 
Private Sub remplace(copieDE As Range, ongletDE As String, copieVERS As Range, ongletVERS As String)
    For i = 1 To Sheets(ongletDE).Range(copieDE.Address).Rows.Count
        For j = 1 To Sheets(ongletDE).Range(copieDE.Address).Columns.Count
            Sheets(ongletVERS).Range(copieVERS.Address).Offset(i - 1, j - 1) = Sheets(ongletDE).Range(copieDE.Address).Cells(i, j)
        Next
    Next
End Sub

Bien à vous

Lucy

[Pierre Fauconnier]

Je pense que plus tôt tu découpes ton code et plus tôt tu adoptes une bonne architecture, mieux c'est.

Cela dit, je n'ai pas vraiment compris ce que tu cherchais à réaliser...

[EricDgn]

Bonjour,

Prenons ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Public Function Open_File(sPath As String)
    Workbooks.Open sPath
End Function
 
Sub Test1()
    Open_File ThisWorkbook.Path & "\macro.xlsm"
End Sub
 
Public Function Test2(s As String) As String
    Open_File ThisWorkbook.Path & "\macro.xlsm"
    Test2 = s
End Function

Si je lance Test1, cela fonctionne.
Si dans une feuille Excel 2010 je met dans une cellule la formule: =Test2(A1), cela n'ouvre pas le fichier.

Cordialement.

[Pierre Fauconnier]

Bonjour Éric

[...]
Si dans une feuille Excel 2010 je met dans une cellule la formule: =Test2(A1), cela n'ouvre pas le fichier.
Cordialement.

Mettre dans une cellule Excel une formule qui ouvre un fichier n'a aucun sens. Dès lors, à quoi sert cet exemple? Je n'ai personnellement jamais rencontré ce besoin dont les inconvénients sont évidents. A chaque recalcul, on ouvrirait le fichier. C'est tout simplement insensé.

[...]
* Une fonction perso utilisée dans une formule EXCEL ne peut "rien" réaliser dans Excel... Pour faire simple, on va dire qu'elle ne peut rien modifier dans l'interface Excel, mais elle peut réaliser plein de trucs à côté...

Comme je l'ai dit dans ma première réponse, une fonction perso utilisée dans une feuille de calcul ne peut pas perturber l'interface Excel. Ce n'est pas le fait d'ouvrir un fichier qui pose problème, c'est le fait d'interférer avec Excel qui pose problème dans le cas d'une fonction perso utilisée dans une cellule. Cette fonction ne peut donc pas ouvrir un fichier Excel, pas plus qu'elle ne peut supprimer ou ajouter des cellules, modifier des valeurs de cellules, etc, etc, etc. Par extension, une fonction utilisée dans une feuille de calcul ne peut pas utiliser des procédures qui modifient l'environnement Excel. C'est une limitation d'Excel que l'on peut comprendre sans problème avec un peu de logique. La fonction suivante, utilisée dans une cellule, ne videra pas le contenu de A1.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Function MaFonction()
  Application.Volatile
  Range("a1").ClearContents
End Function

Par contre, une fonction VBA, non utilisée dans l'interface Excel, peut sans soucis ouvrir un fichier ou modifier l'interface Excel.

[EricDgn]

Bonjour Pierre,

Oui, je suis tout à fait d'accord que c'est "logique" qu'Excel ne veuille pas ouvrir un fichier via une fonction perso, et je trouvais aussi assez tangent d'ouvrir un fichier via une fonction perso que l'on pourrait entrer plusieurs fois dans une même feuille, ... mais VBA fait tant de choses que bon, alors pourquoi pas cela aussi vu que cela ne change à priori rien dans le dossier en cours !

Merci pour ton explication détaillée, très éclairante.

Cordialement.

[deTouteBoT]

Bonjour à tous,
(désolé du retard!)

Et merci pour tout vos retours!

Pierre, je vais m'atteler à le découper merci du conseil et pour en revenir au code que je vous ai mis, il permet à une fonction perso lancé depuis excel de modifier les valeurs d'autres cellules ce qui est normalement impossible, ici de copier une range vers une autre ! Et voici un autre code permettant d'écrire un tableau

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Function ThreeEven1()
    Evaluate "eventFinal(" & Application.Caller.Offset(0, 1).Address(False, False) & ")"
    ThreeEven1 = "matrice >>"
End Function
 
Private Sub eventFinal(CellToChange As Range)
Dim numbers(2, 0) As Integer
    numbers(0, 0) = 25
    numbers(1, 0) = 2
    numbers(2, 0) = 4
    CellToChange.Resize(3, 1) = numbers
End Sub

Merci encore

Lucy