XL 2019 Peut-on lancer une macro (une Sub) à partir dans une cellule comme pour une fonction

[p'tit vieux]

Bonjour à tous
Comme l'indique le titre (enfin je crois) je désire savoir si je peux écrire l'appel à une macro directement dans une cellule comme on le fait avec une fonction.
Est ce possible sans passer par un bouton ou autre mais en saisissant, par exemple, le nom de la Sub()?

 

[JM27]

Bonjour
Il existe en VBA des macro événementielle qui se déclenche au changement de valeur dans une cellule
voir la sub dans le VBA de la feuille

 

[danielco]

Bonjour,

A ma connaissance, non. Cependant, tu peux mettre dans cette cellule, une fonction VBA qui peut exécuter une macro, avec des restrictions. Cette macro sera considérée comme partie de la fonction, et comme telle ne pourra pas, par exemple écrire dans une autre cellule.
Je ne vois toutefois pas l'intérêt ? Que cherches-tu à faire ?

Daniel

 

[soan]

Bonsoir p vieux, le fil,

pour apprendre les bases de VBA, je te suggère de regarder en haut de ton écran, dans la barre en bleu foncé du menu : TUTOS / VBA ; pour ton autre post, je t'ai répondu ICI. 🙂 (tu peux cliquer sur le lien bleu)​

soan

 

[p'tit vieux]

Bonsoir à tous 3
Merci pour vos réponses.
Dans l'ordre:
@JM27 Oui mais non 😉. Même si c'est une solution lors de saisies utilisateur ce n'est pas mon objectif ici. Merci.

@danielco Tu appuies juste là où ça fait mal 🤕. C'est bien là

Cette macro sera considérée comme partie de la fonction, et comme telle ne pourra pas, par exemple écrire dans une autre cellule.

A mes dépens, enfin ceux de mon Add-In, c'est ce que je viens de découvrir.

@soan J'allais vous répondre. J'ai testé votre exemple. En tant que macro ca marche bien. Pas de problème.
Mais là aussi ça ne répond pas à mon prob.

BON J'AVOUE !!
J'ai écris 2 questions qui se rejoignent.

Mon problème est résumé par @danielco.
J'ai écris une fonction add-in qui fait des "trucs et des machins" et qui en plus de retourner un résultat dans la cellule qui l'appelle DEVRAIT pouvoir écrire dans des cellules, en cas de réponses multiples,
A priori c'est impossible dans une fonction personnalisée?
Peut-être en l'écrivant avec Visual Studio?
Mais bon, ce n'est plus la même musique 🎸
Mes années de programmation sont devant moi … quand je me retourne et les technologies ont bien changées. On ne se moque pas ! 😉😁

J'espère finir par trouver.
Merci encore

 

[Dranreb]

Bonjour.
Dans une Function, Il n'est pas possible d'écrire dans des cellules.
Mais dans une Private Sub Workseet_Calculate, si, et dans une Function il est possible de ranger des éléments dans une variable globale déclarée Private Consigne As Collection.
Et la Workseet_Calculate, elle, peut faire un Do While Consigne.Count > 0
Partant de là tout redevient possible …

 

[p'tit vieux]

Bonsoir @Dranreb
HA ??!! En voilà une soluce? Même dans une fonction personnalisée d'un fichier EXTERIEUR (Add-In)?
Je suis (très) curieux car pas sûr que j'ai (tout) compris.
Pourriez-vous m'en dire plus ("Vous pouvez répéter la question?" 😊)
Je n'ai pas tout saisi. C'est trop concis pour moi
(C'est ca les p'tits vieux dur de la feuille et la vue basse, voire lent du ciboulot)

Merci

 

[job75]

Bonjour à tous,

J'ai écris une fonction add-in qui fait des "trucs et des machins" et qui en plus de retourner un résultat dans la cellule qui l'appelle DEVRAIT pouvoir écrire dans des cellules, en cas de réponses multiples,

Si ça vous intéresse notez qu'une fonction VBA peut renvoyer des résultats différents dans plusieurs cellules.

Voyez le fichier joint et cette fonction matricielle très simple :

Function Eclate(c As Range)
Eclate = Split(c) 'matrice/vecteur horizontal
End Function

Elle doit être validée par Ctrl+Maj+Entrée sur la plage sélectionnée.

A+

 

[Dranreb]

Ma solution est trop malcommode si la Function est écrite dans un projet VBA externe, car la mise à jour des cellules doit quand même être finalement entérinée par une Worksheet_Calculate ou une Workbook_SheetCalculate du projet du classeur où la Function est utilisée. Désolé.

 

[p'tit vieux]

Bonsoir @job75
Je viens de test ton exemple. Merci pour ton aide.
J'ai remarqué que la formule matricielle recopie la fonction dans toute les cellules. Ce n'est pas tout à fait ce que j'aimerais.
Là, je dois y aller. Je reviens vers vous demain aprés-midi.
A demain à tous

 

[p'tit vieux]

@Dranreb
C'est pas grave. Vous êtes … pardonné😁😉
Merci et si une idée vous viens "you're welcome"
A demain

 

[Dranreb]

Si le projet VBA du classeur de macro porte un nom différent de "VBAProject" il peut être coché dans les références du projet du classeur utilisateur. Ça éviterait au moins un lourd Application.Run pour invoquer la procédure qui applique les consignes laissées par la Function …
Juste un ProjetMacros.AppliquerConsignes dans une Private Sub Workbook_SheetCalculate du ThisWorkbook du classeur utilisateur.

 

[Dranreb]

Notez que du fait que vous dites :

…en cas de réponse multiple

, je comprends tout à fait la réponse de @job75 qui vous propose une fonction matricielle, capable, elle, de renvoyer plusieurs valeurs dans un tableau. C'est tout à fait normal que dans ce cas la formule apparaisse à l'identique sur toutes les cellules de la plage matricielle, mais elle n'est en fait évaluée qu'une fois.

 

[Dranreb]

Mais dans une autre discussion, vous parliez de modifier des cellules n'ayant rien à voir avec la cellule portant la formule, laquelle invoque une Function d'un classeur de macros.
Ça peut se réaliser comme ça dans un module standard de celui ci :

Option Explicit
Private Consignes As New Collection
Public Function Addition(ByVal Nb1 As Integer, ByVal Nb2 As Integer) As Double
   Addition = Nb1 + Nb2
   With Application.Caller.Worksheet
      EnDifféré(.Range("B2")) = Nb1
      EnDifféré(.Range("C2")) = Nb2
      End With
   End Function
Private Property Let EnDifféré(ByVal Rng As Range, ByVal V)
   Consignes.Add Array(Rng, V)
   End Property
Public Sub ExécuterConsignes()
   Dim TCsgn()
   Do While Consignes.Count >= 1
      TCsgn = Consignes(1)
      Consignes.Remove 1
      TCsgn(0).Value = TCsgn(1)
      Loop
   End Sub

Mais la sub ExécuterConsignes de ce même classeur de macros doit être invoquée depuis le projet du classeur utilisateur dans une de ses Worksheet_Calculate du module Worksheet représentant la feuille où la fonction est utilisée ou un Workbook_SheetCalculate du ThisWorkbook.

 

[p'tit vieux]

@Dranreb
Bonjour à vous.
Waouh ! Un couche tard !
Décidément ca change pas. Ca me rappelle mes longues nuits devant l'écran. Bref! 😵😎 … 🙂
L'idée est bonne et j'ai testé ton code.
Par contre dans un contexte Add-In utilisable par tous je vois mal utiliser cette solution.
De plus, si la F(Addition) et la Sub(ExécuterConsignes) sont dans un Add-In (ou un autre fichier) la collection Consignes et la variable AdressCell ont perdu leurs valeurs lorsque la Sub() est appelée depuis Workbook_SheetCalculate. Ce qui me semble normal (et je crois sans solution en VBA -je ne vais pas reprendre l'Assembleur quand même 😱😆-). Donc à priori là ca ne marche plus.
Qu'en pensez-vous?

Par contre, pour le fun, j'y ai apporté une petite modif. car ce qui me gène c'est qu'à chaque action sur la feuille on passe dans la Public Sub(ExécuterConsignes).
Donc j'ai juste ajouté le test suivant:

'  Dans LE MODULE de la feuille du classeur

Option Explicit
Private Consignes As New Collection
Public AdressCell As String ' Déclaration ajoutée en 'PUBLIC' afin que que le WorkBook ai la variable[/COLOR]

Public Function Addition(ByVal Nb1 As Integer, ByVal Nb2 As Integer) As Double
    AdressCell = ActiveCell.Address
   Addition = Nb1 + Nb2
   With Application.Caller.Worksheet
      EnDifféré(.Range("B2")) = Nb1
      EnDifféré(.Range("C2")) = Nb2
      End With
   End Function

Private Property Let EnDifféré(ByVal Rng As Range, ByVal V)
   Consignes.Add Array(Rng, V)
End Property

' 2 possibilités:
' Solution 1: Soit on teste dans la SUB() du module
Public Sub ExécuterConsignes()
Range(AdressCell).Select ' On remet le focus
Debug.Print AdressCell
If InStr(1, Range(AdressCell).Formula, "=Addition(") > 0 Then
   Dim TCsgn()
   Do While Consignes.Count >= 1
      TCsgn = Consignes(1)
      Consignes.Remove 1
      TCsgn(0).Value = TCsgn(1)
    Loop
End If
End Sub

' Solution 2: On teste dans ThsiWorkBook
' Dans ThisWorkBook:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
       Range(AdressCell).Select
      If InStr(1, Range(AdressCell).Formula, "=Addition(") > 0 Then
            ExécuterConsignes
      End If
End Sub

A choisir suivant l'utilisation.
Chez moi ca a fonctionné nickel.
C'était pour le fun.

Est ce que ce blocage d'écriture existe toujours si j'écris mon Add-In en VSTO.
Est ce plus compliqué? D'autres problèmes?
Merci à vous, et à tous, de vous intéresser à mon sujet.
cdlt