Import de Fonctions Python dans VBA




Dans cette présentation, nous allons voir comment incorporer une fonction écrite en python à la liste des fonctions Excel, et, plus encore, l’appeler depuis une procédure comme une fonction VBA.
En pratique, il suffit d’un click de mise à jour pour transformer une fonction écrite dans un fichier .py, appelée par exemple ma_fonction, en fonction VBA.
 

 
Cette section parcourt successivement les étapes suivantes

  1. Le module xlwings
  2. Comment exécuter directement une fonction écrite en Python dans Excel
  3. Création de fonctions personnalisées
  4. Du code Python dans un programme VBA

Le module xlwings

Si vous n’avez pas encore téléchargé la suite Anaconda, quelle que soit la distribution choisie, c’est le moment. Le module xlwings se trouve normalement dans le répertoire

C:\chemin_vers_dossier_Anaconda\Anaconda(2)\lib\site-packages\xlwings

En cas de doute, lancer python en ligne de commande à partir du répertoire C:\chemin_vers_dossier_Anaconda. Logiquement, cmd vous place directement dans ce dossier.
L’exécution de python fait apparaître les 3 chevrons >>> caractéristiques de l’interpréteur: vous pouvez alors entrer

import xlwings

puis

xlwings.__path__

(ne pas oublier qu’il y a deux tirets __ avant et après path)
Cette information va vous permettre de localiser le dossier xlwings dans lequel se trouve le fichier xlwings.bas que vous devez importer dans Excel.
Ex:

 
Pour résumer, supposons que votre répertoire de travail soit C:\Users\Utilisateur:
en ligne de commande,

C:\Users\Utilisateur>python

Puis, dans l’interpréteur (c.a.d. quand les chevrons apparaissent)

>>> import xlwings
>>> xlwings.__path__

Ouvrez un classeur Excel et allez dans l’éditeur VBA.
Puis, Fichier > Importer un fichier
 

 
xlwings apparaît dans le dossier Modules: celui-ci contient tous les programmes permettant d’interfacer votre code VBA avec les modules python contenant les fonctions que vous voulez utiliser.

Comment exécuter directement une fonction écrite en Python dans Excel

Vous pouvez maintenant insérer un nouveau module (appelé Module1 par défaut, dans l’éditeur VBA) et y écrire une procédure dans laquelle vous appellerez votre fonction python à l’aide de la fonction RunPython. Cette fonction prend un argument de type texte qui mentionne

  • le nom du fichier (extension .py) dans lequel se trouve la fonction
  • le nom de la fonction, préfixé par le module dans lequel elle est définie

 

RunPython ("import module_python;module_python.fonction_python")

 
Exemple: appel d’une fonction testlog() écrite en python dans un module projet_test.py. Dans ce 1er exemple, notre fichier projet_test.py est sauvegardé dans le même répertoire de travail que précédemment (C:\Users\Utilisateur) c’est à dire celui dans lequel se trouve le classeur Excel. L’instruction VBA sera
 

RunPython ("import projet_test;projet_test.testlog()")

On suppose que la fonction testlog() affiche des valeurs de logarithmes dans la 1ère feuille de notre fichier Excel (ça ne sert à rien, nous sommes d’accord). Pour lancer cette fonction à partir d’une procédure VBA, il faut placer au début du fichier python, la directive
import xlwings
 

 
Dans Module1, on exécute testlog() dans une procédure appelée par exemple test_Call()
 

 
En exécutant (F5) test_call, le résultat (valeur 1) s’affiche dans la cellule A1 (ou cells(1,1)) de la 1ère feuille du classeur. On relève que les collections en python sont indexées à partir de 0, alors que le rang du 1er élément d’une collection en VBA est 1.
 
Ici, la fonction projet_test.testlog() est reconnue de test_Call(), car, par défaut, le chemin d’accès au module python est le même que celui du classeur Excel. On peut le vérifier, à l’intérieur de la fonction Settings du module xlwings, avec le paramètre PYTHONPATH:

PYTHONPATH=ThisWorkbook.Path

Vous pouvez modifier l’emplacement du fichier .py en changeant ce paramètre: par exemple, si projet_test se trouve dans le dossier C:/Python34/, vous devez écrire

PYTHONPATH="C:/Python34/"

Fonctions personnalisées

Dans ce paragraphe, nous traitons des fonctions qui renvoient un résultat (RunPython se contentait de lancer une fonction python qui effectuait un traitement et affichait le résultat dans une feuille Excel). Ces fonctions peuvent être

  • intégrées à la liste des fonctions d’Excel (rubrique ‘personnalisées’)
  • appelables depuis une procédure VBA

NB: l’accent est mis, dans cette partie, sur le format des données renvoyées par python et sa compatibilité pour des échanges avec Excel/VBA.
 

Les fonctions personnalisées dans Excel

Ouvrez le module xlwings et cherchez la procédure Sub ImportPythonUDFs() (normalement tout en bas): cette procédure devra être exécutée après chaque mise à jour du fichier .py dans lequel vous écrivez vos fonctions Python (modification du code ou ajout d’une nouvelle fonction).
 

 
L’argument GetUdfModules, surligné dans l’image, exécute la procédure portant le même nom
 

 

GetUdfModules renseigne les paramètres que la procédure Settings doit mettre à jour, en particulier la liste des modules (fichiers .py) contenant les fonctions Python, enregistrée dans la variable UDF_MODULES. Quand cette variable n’est pas renseignée, la fonction Settings va chercher le fichier qui porte le même nom que le classeur Excel, comme l’atteste le code suivant
 

 
Exemple
Soit une fonction en python, dénommée cube qui calcule le volume d’un parallélépipède: on suppose que cette fonction se trouve dans le fichier ProjetTest.py (sachant que le classeur Excel est ProjetTest.xlsm, nous sommes donc dans le cas où il n’est pas nécessaire de paramétrer UDF_MODULES dans Settings).
 

 
Pour être utilisée comme une fonction Excel, la définition de cube, comme chaque définition de fonction, doit être précédée de la directive
@xw.func
 
En lançant ImportPythonUDFs, il se peut que le message d’erreur suivant apparaisse
 

 
Au milieu d’un texte peu engageant, la mention « L’accès par programme au projet Visual Basic n’est pas fiable » indique que le modèle d’objet VBA peut compromettre l’intégrité de votre ordinateur. Il est possible de modifier le paramètre qui bloque, relancer ImportPythonUDFs, puis revenir à l’état antérieur. Pour ce faire,
Bouton Office > Options Excel > Centre de Gestion de la Confidentialité > Paramètres du Centre de Gestion de la Confidentialité… > cocher Accès approuvé au modèle d’objet du projet VBA
 
Une fois ImportPythonUDFs exécutée, un nouveau module xlwings_udfs apparaît dans le projet, contenant le code suivant
 

 
Votre fonction cube(x,y,z) est maintenant intégrée à la liste des fonctions personnalisées d’Excel. Par souci de sécurité, vous pouvez décocher la case ‘Accès approuvé au modèle d’objet du projet VBA’.
 
Si votre fichier .py ne porte pas le même nom que le classeur Excel (par exemple ProjetTestBis.py) et se trouve dans un répertoire distant (par exemple, C:/Python34/), vous devez modifier les paramètres PYTHONPATH et UDF_MODULES dans la fonction Settings

PYTHONPATH = « C:/Python34/ »
UDF_MODULES = « ProjetTestBis »

Pour exploiter les fonctions python dans un nouveau classeur, il suffit de copier les modules xlwings et xlwings_udfs dans celui-ci.

Les fonctions personnalisées dans Excel: cas des fonctions matricielles

Supposons qu’à la place d’une seule valeur aléatoire, nous souhaitions afficher sur notre feuille un tableau de 10 de ces valeurs tirées au hasard. Le programme python est immédiat
 

 
Le code ci-dessous permet d’obtenir une liste de valeurs, c’est à dire une matrice contenant une seule ligne. Conséquence: les données récupérées dans Excel devront être affichées sur la même ligne, ce qui est contraire aux habitudes de l’utilisateur, et surtout à la logique du tableur qui contient beaucoup plus de lignes que de colonnes.
Pour afficher ces valeurs sur une colonne, il faut que la liste renvoyée contienne 10 lignes avec 1 valeur sur chaque ligne, c’est à dire une liste dont les éléments sont des singletons. Le code qui permet d’obtenir cela n’est pas très différent du code précédant
 

 
On rappelle que, pour valider l’appel d’une fonction matricielle dans Excel, il faut commencer par sélectionner la plage de cellules qui va recevoir le résultat de la fonction appelée, puis choisir cette fonction (ici aleas()), renseigner la valeur des arguments si nécessaire (ici, il n’y en a pas) et valider par Ctrl + shift + entrée.
 

Du code Python dans un programme VBA

Nous avons vu dans la section consacrée aux collections, que Python offrait des facilités pour le traitement des séquences: définition de listes en compréhension, concaténation de séquences, « zippage », etc. Cet avantage offert par Python est encore plus manifeste avec le package numpy. Nous pouvons encore citer le cas des calculs dans le plan complexe, qui est immédiat en Python, laborieux en VBA.
Il peut être intéressant, dans un de ces cas, d’inclure une fonction développée en Python à l’intérieur d’une procédure VBA: il faut simplement que le type des valeurs passées à la fonction soit reconnu par Python et, inversement, que les valeurs renvoyées par la fonction soient reconnues par VBA. La règle est simple:

  • Python accepte les arguments de type Range et les transforme en objets list
  • les résultats retournés par la fonction Python sont automatiquement convertis en valeurs de type Variant

Dans l’exemple ci-dessous, on cherche à fusionner deux colonnes de données affichées dans la feuille « Feuil1 » d’un classeur Excel, trier le résultat et l’afficher dans la même feuille. Comme la fusion et le tri de listes est simplissime en Python, on souhaite utiliser une fonction Python personnalisée, appelée merge, à l’intérieur de la procédure VBA, appelée Merge2List.
Dans un module .py, par exemple projet_rem.py, on écrit

@xw.func
def merge(l1,l2):
l=l1+l2
l=[[l[i]] for i in range(len(l))] # pour afficher le résultat en colonne
l.sort()
return l

Puis, on relance ImportPythonUDFs() par un F5 dans l’éditeur VBA: le module xlwings_udfs s’enrichit d’une nouvelle fonction
 

Le programme VBA est prêt à être écrit, faisant appel à la fonction merge
 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *