Différence entre une fonction lancé sur VBA et une fonction sur Excel
Bonjour,
belle trouvaille en effet.
Peu orthodoxe pour une fonction mais très intéressant pour rendre des services dans des cas précis.
Dès que j'ai du temps, j'approfondirai sans aucun doute.
Merci
eric.
Hello,
Pas de soucis Eric!
Petite modification sur mon code ou il manquait une virgule mais ça ne fonctionne toujours pas... je continue!!
Function histo(ticker As String, date1 As String, date2 As String)
Evaluate "getdata1(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & ticker & "," & date1 & "," & date2 & ")"
histo = "matrice >>"
End FunctionLucy
A y réfléchir rapidement, pourquoi vouloir absolument que ce soit une fonction ?
Tu pars dans une usine à gaz alors que pour moi il y a plus simple.
Ton principe : passer les paramètres dans une fonction pour générer un tableau avec :=histo(ticker, date1, date2)
Tu pourrais mettre juste ces 3 paramètres ticker, date1, date2 dans une cellule (ou dans 3 tant qu'à faire) que tu supervises avec l'événement Change. Le résultat sera le même non ?
Ces paramètres peuvent aussi être des formules si c'est ce que tu faisais.
A chaque Change, ton sub contrôle la validité des paramètres, fait et restitue ton extraction.
Avec l'avantage en prime de lire en clair ces paramètres sans être obligée de sélectionner la formule pour les voir.
Enfin moi je ferai comme ça
eric
C'est simplement par rapport à l'utilisation qui résulte derrière, la fonction est bien plus adapté
de plus, l'objectif finale est de faire une macro complémentaire et avec le worksheet change je ne sais pas comment faire ( j'ai une vague idée )
Aussi j'aimerais bien ton avis la dessus, est ce que le fais d'avoir une evenement ws change ralentis l'ensemble du classeur en général vu que la "détection" est toujours active alors que la fonction est utilisé seulement quand elle est appelé ??
Pour les curieux j'avais fais ceci avec sheet 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)
Target.Resize(UBound(datas, 1), UBound(datas, 2)).Value = datas
Else: Exit Sub
End If
End SubMerciii du retour
lucy
Ces paramètres peuvent aussi être des formules si c'est ce que tu faisais.
A chaque Change, ton sub contrôle la validité des paramètres, fait et restitue ton extraction.
Attention, eric ne l'a pas précisé mais la sub événementielle ne se déclenche que sur les paramètres de base des formules, pas sur le résultat ... si celui-ci change il ne produira pas l'événement, il faudra tester le changement sur les données de base modifiées manuellement
Personnellement je n'ai jamais vu de ralentissement avec change. A contrario je suis perplexe sur une fonction personnalisée, volatile ou pas, qui me semble se déclencher quand elle veut ... parfois ou toujours même quand il n'y a pas besoin... ! enfin cela m'a toujours perturbé.
Mercii de m'avoir éclairée à ce sujet! je me pencherai dessus si je n'y arrive vraiment pas
Avez vous pu regarder ma fonction si ce n'est pas trop demandé?:)
Elle devrait fonctionner comme l'exemple ThreeEven mais je dois rater quelque chose mais je sais pas quoi...
Merci en tout cas
Lucy
Tu n'as pas pris le bon événement.
Un exemple sommaire ci-joint
eric
Edit :
il faudra tester le changement sur les données de base modifiées manuellement
Oui, il faudrait qu'elle précise. On n'a pas de vue globale
A contrario je suis perplexe sur une fonction personnalisée, volatile ou pas, qui me semble se déclencher quand elle veut ... parfois ou toujours même quand il n'y a pas besoin
Je confirme. Souvent elle se recalcule alors qu'on est dans un autre classeur qui n'a rien à voir. Si c'est une fonction gourmande (on peut le supposer s'il s'agit d'une extraction) excel va devenir inutilisable et source de crises de nerfs...
enfaite je parlais de la fonction impossible à faire techniquement! ( je suis reloue je sais) :)
J'ai basculé sur le workbook plutot que sur le worksheet car j'avais envie que cela fonctionne sur l'ensemble du classeur et pas seulement, je me pencherai dessus avec plus de précisions mercii en tout cas
en voila, meme deux ; ) merci!!!
je regarde
réponse demain matin (heure française)
ta sub fonctionne, j'ai changé les dates de string en long, mais l'intégration pose en effet problème ... à suivre
J'en arrive à la conclusion que l'ouverture du fichier est impossible.
- J'ai d'abord testé la sub et la façon de transmettre les données. Ce qui m'amène à un premier changement en mettant
tickerentre guillemets et en passant les dates en long (peut-être pas indispensable pour les dates) - J'ai donc reproduit la même chose sur la fonction. Si je mets
Goto finavant l'ouverture du fichier, je n'ai pas d'erreur, si je l'enlève j'ai une erreur (les premières instructions sont quand même bien passées).
Sub test()
getdata Range("C20"), "ticker1", 44005, 44013
End Sub
Function histo(ticker As String, date1 As Long, date2 As Long)
Evaluate "getdata(" & Application.Caller.Offset(0, 1).Address(False, False) & ",""" & ticker & """," & date1 & "," & date2 & ")"
histo = Now
End Function
Private Sub getdata(CellToChange As Range, ticker As String, Datedebut As Long, Datefin As Long)
CellToChange.Offset(-1, 0) = ticker
CellToChange.Offset(-1, 1) = Datefin
CellToChange.Offset(-1, 2) = Datedebut
CellToChange.Offset(-1, 3) = CellToChange.Address(False, False)
'Application.ScreenUpdating = False
Dim FilePath As String
Dim wbTicker As Workbook
Dim wsTicker As Worksheet
Dim datas() As Variant
FilePath = ThisWorkbook.Path & "\" & ticker & ".xlsx"
Debug.Print FilePath
'GoTo fin
'If FichierExiste(FilePath) = False Then
' MsgBox "Le fichier " & ticker & " n'existe pas dans la base, veuillez le créer!"
' Exit Sub
'Else:
Set wbTicker = Workbooks.Open(FilePath)
Set wsTicker = wbTicker.Sheets(1)
Debug.Print "fichier ouvert"
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
CellToChange.Resize(UBound(datas, 1), UBound(datas, 2)) = datas
Else
MsgBox "Filtre sans résultat"
End If
wbTicker.Close SaveChanges:=False
'End If
'Application.ScreenUpdating = True
Set wbTicker = Nothing: Set wsTicker = Nothing:
fin:
End SubJ'ai voulu tester tout ceci sans ouvrir un autre fichier, juste pour tenter de comprendre ... j'ai donc ramené les valeurs dans un autre onglet !
- si je fais la macro test, c'est ok
- si je fais la fonction, elle ne donne plus #valeur mais ne donne aucun résultat quant au filtre appliqué sur l'onglet 2 (alors même que test avec les mêmes paramètres donne un résultat)
conclusion : il n'y a pas que l'ouverture du fichier qui coince, mais la fonction marche en partie puisqu'elle donne les premiers résultats liés aux premières instructions !
et si je ramène les données sur la même feuille cela ne fonctionne pas
Hello Steelson,
Merci des retours, je vois mieux la logique pour tester le fonctionnement d'une macro
Donc je comprends bien, la fonction ne fonctionne pas à partir du moment ou on va chercher les données que cela soit dans un autre classeur ou dans une autre feuille
Est ce que ça serait pas le filtre automatique qui pose problème ?
J'avais pensé du coup à faire un filtre "manuelle", avec un find qui permettrai de trouver les adresses (numéro de ligne ) des dates et copier l'intervalle entre les deux
Quant à l'ouverture du fichier, est possible de récupérer des données sans passer par un open ? J'ai vu quelque chose de similaire ici (j'ai survolée pour l'instant)
https://forum.excel-pratique.com/viewtopic.php?t=67318
Je vais essayer de mettre en forme tout cela pour voir ce que ça donne et je ferai suivre
Merci !!!!
D'ailleurs, j'ai eu un petit retour sur les formules bloomberg, elles sont codés en C++ ou C# du coup, j'imagine qu'ils permettent un champs d'action plus large que vba
Lucy
Hello Steelson,
Merci des retours, je vois mieux la logique pour tester le fonctionnement d'une macro
Donc je comprends bien, la fonction ne fonctionne pas à partir du moment ou on va chercher les données que cela soit dans un autre classeur ou dans une autre feuille
Est ce que ça serait pas le filtre automatique qui pose problème ?
J'avais pensé du coup à faire un filtre "manuelle", avec un find qui permettrai de trouver les adresses (numéro de ligne ) des dates et copier l'intervalle entre les deux
Quant à l'ouverture du fichier, est possible de récupérer des données sans passer par un open ? J'ai vu quelque chose de similaire ici (j'ai survolée pour l'instant)
https://forum.excel-pratique.com/viewtopic.php?t=67318
Je vais essayer de mettre en forme tout cela pour voir ce que ça donne et je ferai suivre
Merci !!!!
D'ailleurs, j'ai eu un petit retour sur les formules bloomberg, elles sont codés en C++ ou C# du coup, j'imagine qu'ils permettent un champs d'action plus large que vba
Lucy
Lucy,
- je vais en effet continuer à explorer sans le filtre, en faisant juste une copie, puis une copie à partir d'un autre onglet etc. pour en connaître les limites
- oui on peut importer sans ouvrir un fichier, par 2 méthodes
- écrire la fonction = qui va chercher dans un autre classeur du type formulalocal = "=________ici le fichier l'onglet et la cellule__________" et peut-être même avec formulaarray, j'en joue assez souvent dès lors que l'on sait où cela se trouve,
- avec ADODB, je maîtrise moins
Quelques essais
- essai 1 simple c'est l'exemple qui est fourni : copie de la valeur
Function recopie(copieDE As Range, copieVERS As Range) Evaluate "remplace(" & copieDE.Address(False, False) & "," & copieVERS.Address(False, False) & ")" recopie = Now End Function Private Sub remplace(copieDE As Range, copieVERS As Range) copieVERS.Value = Range(copieDE.Address) End Sub - essai 2 cela devient cocasse ... copie d'une valeur d'une autre feuille : déjà pour transmettre le nom de la feuille faut pas hésiter sur les guillemets ! et puis si je me mets en feuille2 et que je change la valeur source, alors la cellule de la feuille de destination (!!!!) prend aussi la valeur source (mais sur la feuille 1 rien ne change) => il faut donc aussi préciser la feuille destinataire
Function recopie(copieDE As Range, copieVERS As Range) ongletDE = copieDE.Parent.Name Evaluate "remplace(" & copieDE.Address(False, False) & ",""" & ongletDE & """," & copieVERS.Address(False, False) & ")" recopie = Now End Function Private Sub remplace(copieDE As Range, ongletDE As String, copieVERS As Range) copieVERS.Value = Sheets(ongletDE).Range(copieDE.Address) End Sub - nouvel essai en précisant aussi la feuille de destination, c'est ok
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) Sheets(ongletVERS).Range(copieVERS.Address) = Sheets(ongletDE).Range(copieDE.Address) End Sub - maintenant essayons une formule ... alors c'est une fois 0, une fois ok, une fois 0, une fois ok
Function recopie(copieDE As Range, copieVERS As Range) ongletDE = copieDE.Parent.Name Evaluate "remplace(" & copieDE.Address(False, False) & ",""" & ongletDE & """," & copieVERS.Address(False, False) & ")" recopie = Now End Function Private Sub remplace(copieDE As Range, ongletDE As String, copieVERS As Range) 'copieVERS.Value = Sheets(ongletDE).Range(copieDE.Address) copieVERS.FormulaR1C1 = "=deuxieme_onglet!R[-3]C" End Sub - donc pas de formule, copie d'une plage d'un autre onglet (il faudrait essayer de filtrer ce que je n'ai pas fait) : j'y suis allé cellule après cellule
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
Helloo,
Merci je vais essayer cette solution si l'ouverture par le biais d'une fonction s'avère impossible
Dans la continuité des tests,
Copier coller d'un classeur à un autre (ouvert), un essai ne fonctionne pas vraiment, je dois rater quelque chose...
Function recopie1(copieDE As Range, copieVERS As Range)
Dim ongletDE As String, ongletVERS As String
Dim classeurDE As String, classeurVERS As String
ongletDE = copieDE.Parent.Name
ongletVERS = copieVERS.Parent.Name
classeurDE = copieDE.Parent.Parent.Name
classeurVERS = copieVERS.Parent.Parent.Name
Evaluate "remplace(" & copieDE.Address(False, False) & ",""" & ongletDE & """,""" & classeurDE & """," _
& copieVERS.Address(False, False) & ",""" & ongletVERS & """,""" & classeurVERS & """)"
recopie1 = "now"
End FunctionPrivate Sub remplace1(copieDE As Range, ongletDE As String, classeurDE As String, copieVERS As Range, ongletVERS As String, classeurVERS As String)
For i = 1 To Workbooks(classeurDE).Sheets(ongletDE).Range(copieDE.Address).Rows.Count
For j = 1 To Workbooks(classeurDE).Sheets(ongletDE).Range(copieDE.Address).Columns.Count
Workbooks(classeurVERS).Sheets(ongletVERS).Range(copieVERS.Address).Offset(i - 1, j - 1) = Workbooks(classeurDE).Sheets(ongletDE).Range(copieDE.Address).Cells(i, j)
Next
Next
End SubJe vais tenter l'ouverture de fichier ou la récupération directe de donnée et pour finir le filtre
Petite question! Comment décide t'on de la quantité de guillemets qu'il faut mettre ?
J'imagine que c'est pour le faire connaitre un string mais je ne saisis pas la chose
Bon week end
Lucy