Fonction VBA customisée : possible de l'accélérer ?

Bonjour à tous!

Il y a presque 2 ans, Bouben faisait mon bonheur en créant une fonction personnalisée permettant de réaliser des requêtes depuis excel dans des fichiers access :

https://forum.excel-pratique.com/viewtopic.php?f=2&t=97193&start=10

Merci encore Bouben !!!

Le problème, c'est que ces fonctions ralentissent beaucoup mes fichiers excel.

Et Microsoft dit que "les fonctions VBA définies par l’utilisateur sont souvent plus lentes que les fonctions intégrées dans Excel (bien que les fonctions VBA soigneusement écrites puissent être rapides)". (https://docs.microsoft.com/fr-fr/office/vba/excel/concepts/excel-performance/excel-improving-calcuation-performance)

Je me demandais donc s'il y avait éventuellement moyen de rédiger autrement cette fonction pour l'accélérer :

Option Explicit

'***************************************************************************
'Recherche une valeur dans la base, à partir d'un ID
'
'3 paramètres:
'   psBase : chemin de la base de données
'   psId : ID
'   piNum : numéro de la valeur recherchée :
'           exemple : 1 => Valeur1, 2 => Valeur2, 3 => Valeur3
'***************************************************************************
Public Function Valeur(psBase As String, psId As String, piNum As Integer) As Variant

    Dim oCnx As Connection
    'Dim sBase As String
    Dim sChaine As String
    'Dim sId As String
    Dim sRequete As String
    Dim oRS As Recordset

    'vérifie que la base existe
    If Dir(psBase) = "" Then
        Valeur = "#ERR [Base inexistante]"
        Exit Function
    End If

    'vérifie qu'on a saisi un ID
    If psId = "" Then
        Valeur = "#ERR [ID non renseigné]"
        Exit Function
    End If

    'La chaîne de connexion dépend du type de base de données
    'Exemple pour ACCESS 2010
    'sChaine = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sBase & ";Persist Security Info=False"
    sChaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & psBase & " ;Persist Security Info=False;"

    'ouvre la connexion
    Set oCnx = New Connection

    oCnx.Open sChaine

    If oCnx.State <> 1 Then
        'MsgBox "Echec de connexion !", vbExclamation
        Valeur = "#ERR [Echec de connexion]"
        Exit Function
    End If

    'recherche sur l'ID
    sRequete = "SELECT Valeur1, Valeur2, Valeur3, Valeur4, Valeur5, Valeur6 FROM Table1 WHERE ID = '" & psId & "' "

    'exécute la requête
    Set oRS = oCnx.Execute(sRequete)

    'aucun enregistrement => message
    If oRS.EOF Then
        'MsgBox "Aucun enregistrement trouvé !", vbExclamation
        Valeur = "#ERR [ID non trouvé]"
        Exit Function
    End If

    Select Case piNum
        Case 1
            Valeur = oRS.Fields("Valeur1").Value
        Case 2
            Valeur = oRS.Fields("Valeur2").Value
        Case 3
            Valeur = oRS.Fields("Valeur3").Value
        Case 4
            Valeur = oRS.Fields("Valeur4").Value
        Case 5
            Valeur = oRS.Fields("Valeur5").Value
        Case 6
            Valeur = oRS.Fields("Valeur6").Value
        Case Else
            Valeur = "#ERR [Numéro incorrect]"
            Exit Function
    End Select

    'fermeture connexion
    oCnx.Close

    Set oCnx = Nothing

End Function

Je ne connais rien à la programmation VBA, donc j'espère que ma question fait du sens.

Merci de votre aide,

Nicopat

bonjour,

je ne vois pas comment améliorer significativement cette fonction, elle est déjà bien optimisée.

Tout dépend de la manière dont tu l'utilises. Le problème est peut-être là et comme tu te plains de lenteur, je suppose qu'elle ne convient pas à la façon dont tu l'utilises. Mais le code que tu as donné ne permet pas de se faire une idée précise.

Bonjour,

Edit : Salut h2so4

La fonction telle qu'elle est correctement rédigée et ne peut pas être optimisée ...

Par contre, la logique d'une fonction personnalisée ... me semble bien lourde ... et pas du tout rationnelle ...

Dans cette configuration, tu rèpètes tout l'ensemble de ton processus pour chaque cellule ...

Avec une macro standard ... tu travaillerais ' en batch ' ... une macro pour plusieurs cas ...

Merci à tous les 2 pour vos réponses.

Je vais cogiter pendant mes vacances aux perspectives qu'ouvre la réponse de James007, que je ne comprends pas trop pour l'instant.

Il s'agirait d'une macro qui réaliserait les requêtes vers des fichiers access ?