Optimiser fonctions VBA Excel

Le
Castor Lapon
Bonjour,

J'ai construit une base de données Access pour stocker des historiques de
cours boursiers

1) Je souhaite pouvoir accéder à ces données sous Excel, via des fonctions
VBA écrites dans un module XLA

Quel est le meilleur moyen pour optimiser le temps de calcul de ces
fonctions ?
Pour le moment j'utilise la méthode que je connais le mieux, ce qui donne
par exemple pour récupérer le dernier cours connu d'un titre à une date
donnée :

Public Function dbCours(iTicker As String, Optional iDate_Cours As Date = 0)
Dim strSQL As String
If iDate_Cours = 0 Then iDate_Cours = Now()
strSQL = "SELECT TOP 1 t_cours.cours FROM t_cours" _
& " WHERE t_cours.date_cours<=#" & Format(iDate_Cours,
"mm/dd/yyyy") & "#" _
& " AND t_cours.id_titre='" & iTicker & "'" _
& " ORDER BY t_cours.date_cours DESC;"
dbCours = dbQuery(strSQL)
End Function

Cette méthode fonctionne bien mais une feuille Excel pouvant contenir
plusieurs centaines d'appels à cette fonction met plusieurs minutes pour se
mettre à jour
J'ai lu qu'il serait plus efficace d'appeler directement une requête Access,
mais dans ce cas quelle est la syntaxe pour lui faire passer les paramètres
?
Y a-t-il une méthode plus rapide (sans être trop complexe !) ?


2) J'envisage ensuite de développer des fonctions de calculs (rendements,
volatilités etc.) : à votre avis quelle est la meilleure façon de faire ?
Est-ce judicieux de stocker ces champs calculés dans Access ? Ou bien
vaut-il mieux appeler les données de base (les cours) et faire les calculs
dans une macro Excel ?

D'avance merci pour vos conseils,

CL
  • Partager ce contenu :
Vos réponses
Trier par : date / pertinence
Gilles MOUGNOZ
Le #5966361
Bonjour,
J'ai construit une base de données Access pour stocker des historiques de
cours boursiers
1) Je souhaite pouvoir accéder à ces données sous Excel, via des fonctions
VBA écrites dans un module XLA
Quel est le meilleur moyen pour optimiser le temps de calcul de ces
fonctions ?
...
2) J'envisage ensuite de développer des fonctions de calculs (rendements,
volatilités etc.) : à votre avis quelle est la meilleure façon de faire ?
Est-ce judicieux de stocker ces champs calculés dans Access ? Ou bien
vaut-il mieux appeler les données de base (les cours) et faire les calculs
dans une macro Excel ?
D'avance merci pour vos conseils,
CL


Bonjour, CL

Un grand nombre de requête Access depuis Excel est en effet vite pénalisant.
Plusieurs solutions:
a) faire tes calculs dans Access et copier les valeurs dans Excel
(complètement statique et manuel).
b) rapatrier les données Access dans une feuille masquée dans Excel et faire
tes calculs en utilisant comme source de données cette feuille plutôt
qu'Access (statique mais le rapatriement des données peut être automatisé et
les calculs sont très rapides).
c) créer un formulaire Access qui ferait les mêmes calculs en prévoyant des
zones pour saisir tes paramètres (complètement dynamique mais mise en page
moins souple qu'Excel).

A toi de voir selon le temps et les connaissances dont tu disposes (pour les
connaissances, le newsgroup est toujours ouvert !).

Bonne continuation

Castor Lapon
Le #5946781
"Gilles MOUGNOZ" %
Bonjour,
J'ai construit une base de données Access pour stocker des historiques de
cours boursiers
1) Je souhaite pouvoir accéder à ces données sous Excel, via des
fonctions VBA écrites dans un module XLA
Quel est le meilleur moyen pour optimiser le temps de calcul de ces
fonctions ?



Merci à Gilles pour ces ces premières pistes.
Après réflexion je fais le choix de programmer mes calculs dans Excel
(option b) non pas dans une feuille cachée, mais dans des tableaux VB

Maintenant j'ai une question pour laquelle je n'ai pas trouvé la bonne
réponse dans les archives :
Pour rapatrier mes données (résultats de requêtes) dans Excel, j'écris le
code SQL dans ma macro, et je l'envoie à Access (cf ci-dessous)... c'est
assez lent et pas très pratique

J'ai lu qu'il était bien plus efficace de créer la requête dans Access, et
de l'appeler dans une macro function VBA dans Excel

Ma question : j'ai créé cette requête dans Access, mais quelle est la
syntaxe pour l'appeler depuis une macro Excel ?
(sachant qu'il y a en plus 2 paramètres à lui faire passer ?)

Merci,

CL

---
Public cnx As ADODB.Connection

Public Function dbCours(iTicker As String, iDate_Cours As Date)

Dim strSQL As String
strSQL = "SELECT TOP 1 t_cours.cours FROM t_cours" _
& " WHERE t_cours.date_cours<=#" & Format(iDate_Cours,
"mm/dd/yyyy") & "#" _
& " AND t_cours.id_titre='" & iTicker & "'" _
& " ORDER BY t_cours.date_cours DESC;"

Set cnx = New ADODB.Connection
cnx.Provider = "Microsoft.Jet.Oledb.4.0" 'Définition du pilote de
connexion
cnx.ConnectionString = "d:db.mdb" 'Définition de la chaîne de connexion
cnx.Open 'Ouverture de la base de données

Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

rst.CursorLocation = adUseClient 'permet de récupérer le nombre
d'enregistrements (?)
rst.Open strSQL, cnx 'Envoi de la requête

etc...


Gilles MOUGNOZ
Le #5946631
...
Ma question : j'ai créé cette requête dans Access, mais quelle est la
syntaxe pour l'appeler depuis une macro Excel ?
(sachant qu'il y a en plus 2 paramètres à lui faire passer ?)
Merci,
CL
Public cnx As ADODB.Connection
Public Function dbCours(iTicker As String, iDate_Cours As Date)
Dim strSQL As String
strSQL = "SELECT TOP 1 t_cours.cours FROM t_cours" _
& " WHERE t_cours.date_cours<=#" & Format(iDate_Cours,
"mm/dd/yyyy") & "#" _
& " AND t_cours.id_titre='" & iTicker & "'" _
& " ORDER BY t_cours.date_cours DESC;"
...
rst.Open strSQL, cnx 'Envoi de la requête
etc...


Bonjour, CL

Pour ouvrir une requête précise, il suffit de remplacer la chaîne SQL par le
nom de la requête. Ici:
strSQL = "TaRequête"
Par contre, pour les paramètres... Peut-être quelque choqe comme:
strSQL = "TaRequête;param1='xxx';param2='yyy'"
Tu n'as rien trouvé dans l'aide en ligne de ADO ?
Désolé de ne pouvoir t'aider plus...

Bonne continuation

agrimbert
Le #5946601

Bonjour, CL

Pour ouvrir une requête précise, il suffit de remplacer la chaîne S QL par le
nom de la requête. Ici:
strSQL = "TaRequête"


Oui, ça marche pour une requête simple (sans paramètres)

Par contre, pour les paramètres... Peut-être quelque choqe comme:
strSQL = "TaRequête;param1='xxx';param2='yyy'"



Non, et c'est bien là le problème ! J'ai cherché dans les archives
du groupe, j'ai trouvé plusieurs exemples dont aucun ne colle
parfaitement, et en tout cas aucun qui fonctionne chez moi...
Je cherche encore...

CL

Poster une réponse
Anonyme