Formule VBA avec paramètres

Le 23/09/2019 à 12:38

Bonjour,

Je souhaiterai créer une macro qui insère dans la cellule active une formule comportant des paramètres.

La formule suivante est un peu complexe et comprend des décaler, sommeprod et indirect.

=SI(pEncours=0;0;SI(pEncours>SOMME(pFlux:DECALER(pFlux;;;;pParam));"impossible";SOMMEPROD((pEncours>SOUS.TOTAL(9;DECALER(pFlux;;1-LIGNE(INDIRECT("1:"&pParam));;LIGNE(INDIRECT("1:"&pParam)))))*1)))

J'ai donc converti la formule en anglais pour la rédaction de la macro :

=IF(pEncours=0,0,IF(pEncours>SUM(pFlux,OFFSET(pFlux,,,,-pParam)),"impossible",SUMPRODUCT((pEncours>SUBTOTAL(9,OFFSET(pFlux,,1-ROW(INDIRECT("1:"&pParam)),,ROW(INDIRECT("1:"&pParam)))))*1)))

L'idée est alors de définir les variables:

Dim pEncours As Range
Dim pFlux As Range
Dim pParam As String
Dim pResultat As Range
Set pResultat = ActiveCell

Ensuite le code donnerait :

Defining:
pParam = InputBox("Entrez le nombre de période", "Période", 1)
    If pParam = "" Then
            MsgBox ("Veuillez entrer une valeur valide")
    Else
        If IsNumeric(pParam) Then
        Else
            MsgBox ("Veuillez entre une valeur valide")
            GoTo Defining
        End If
    End If

On Error Resume Next
Set pEncours = Application.InputBox(Prompt:="Selectionnez la cellule de l'encours", _
Title:="En cours", Type:=8)
On Error GoTo 0

On Error Resume Next
Set pFlux = Application.InputBox(Prompt:="Selectionnez la cellule de flux", _
Title:="Flux", Type:=8)
On Error GoTo 0

pResultat.Formula = "=IF(pEncours=0,0,IF(pEncours>SUM(pFlux,OFFSET(pFlux,,,,-pParam)),"impossible",SUMPRODUCT((pEncours>SUBTOTAL(9,OFFSET(pFlux,,1-ROW(INDIRECT("1:"&pParam)),,ROW(INDIRECT("1:"&pParam)))))*1)))"

Sauf que la pour le coup, ça ne fonctionne pas du tout, il ne récupère pas mes variables comme je le souhaite et cela ne marche pas!

Pour simplifier voici un exemple ou cela fonctionne en Excel et ce que je voudrai obtenir.

15formule-avec-parametres.xlsx (9.07 Ko)

Toute aide est la bienvenue!

Merci pour votre temps,

Naxos

Le 23/09/2019 à 13:36

Bonjour,

essaie ceci

Defining:
    Set presultat = ActiveCell
    pParam = InputBox("Entrez le nombre de période", "Période", 1)
    If pParam = "" Then
        MsgBox ("Veuillez entrer une valeur valide")
    Else
        If IsNumeric(pParam) Then
        Else
            MsgBox ("Veuillez entre une valeur valide")
            GoTo Defining
        End If
    End If

    On Error Resume Next
    Set pEncours = Application.InputBox(Prompt:="Selectionnez la cellule de l'encours", _
                                        Title:="En cours", Type:=8)
    pEncoursa = pEncours.Address
    On Error GoTo 0

    On Error Resume Next
    Set pFlux = Application.InputBox(Prompt:="Selectionnez la cellule de flux", _
                                     Title:="Flux", Type:=8)

    pFluxa = pFlux.Address
    On Error GoTo 0

    presultat.Formula = "=IF(" & pEncoursa & "=0,0,IF(" & pEncoursa & ">SUM(" & pFluxa & ",OFFSET(" & pFluxa & ",,,," & -pParam & ")),""impossible"",SUMPRODUCT((" & pEncoursa & ">SUBTOTAL(9,OFFSET(" & pFluxa & ",,1-ROW(1:" & pParam & "),,ROW(1:" & pParam & "))))*1)))"
Le 23/09/2019 à 13:59

Bonjour H2SO4,

Merci beaucoup pour ta réponse et ton temps!

C'est presque bon pour moi, est-il possible de définir pEncours et pFlux en cellule à adresse relative et pas en absolue pour pouvoir tirer la formule ?

Exemple si pEncours est la cellule A1 ; la formule affichera A1 et non pas &A&1

Ce serait génial!

Merci encore pour ton temps,

Naxos

Le 23/09/2019 à 15:13

bonjour

pour des adresses relatives

Defining:
    Set presultat = ActiveCell
    pParam = InputBox("Entrez le nombre de période", "Période", 1)
    If pParam = "" Then
        MsgBox ("Veuillez entrer une valeur valide")
    Else
        If IsNumeric(pParam) Then
        Else
            MsgBox ("Veuillez entre une valeur valide")
            GoTo Defining
        End If
    End If

    On Error Resume Next
    Set pEncours = Application.InputBox(Prompt:="Selectionnez la cellule de l'encours", _
                                        Title:="En cours", Type:=8)
    pEncoursa = pEncours.Address(False, False)
    On Error GoTo 0

    On Error Resume Next
    Set pFlux = Application.InputBox(Prompt:="Selectionnez la cellule de flux", _
                                     Title:="Flux", Type:=8)

    pFluxa = pFlux.Address(False, False)
    On Error GoTo 0

    presultat.Formula = "=IF(" & pEncoursa & "=0,0,IF(" & pEncoursa & ">SUM(" & pFluxa & ",OFFSET(" & pFluxa & ",,,," & -pParam & ")),""impossible"",SUMPRODUCT((" & pEncoursa & ">SUBTOTAL(9,OFFSET(" & pFluxa & ",,1-ROW(1:" & pParam & "),,ROW(1:" & pParam & "))))*1)))"
Le 23/09/2019 à 20:20

Merci H2SO4,

C'est super !!!!

Pour un autre projet qui utilise indirect, j'ai néanmoins un problème qui survient également dans cet exemple

 presultat.Formula = "=IF(" & pEncoursa & "=0,0,IF(" & pEncoursa & ">SUM(" & pFluxa & ",OFFSET(" & pFluxa & ",,,," & -pParam & ")),""impossible"",SUMPRODUCT((" & pEncoursa & ">SUBTOTAL(9,OFFSET(" & pFluxa & ",,1-ROW([size=150]INDIRECT(""1:" & pParam & ")")[/size],,ROW([b]INDIRECT(""1:" & pParam & ")"[/b]))))*1)))"

Néanmoins lorsque je lance l'enregistreur de macro c'est pourtant (il me semble) la syntaxe qu'il utilise!

Si tu as une idée je suis preneur, le cas échéant, on fera sans!

Merci encore,

Naxos

Le 23/09/2019 à 23:48

Bonsoir,

cela devrait être ceci :

presultat.Formula = "=IF(" & pEncoursa & "=0,0,IF(" & pEncoursa & ">SUM(" & pFluxa & ",OFFSET(" & pFluxa & ",,,," & -pParam & ")),""impossible"",SUMPRODUCT((" & pEncoursa & ">SUBTOTAL(9,OFFSET(" & pFluxa & ",,1-ROW(INDIRECT(""1:" & pParam & """)),,ROW(INDIRECT(""1:" & pParam & """)))))*1)))"

cependant dans ce cas, l'utilisation de indirect n'est pas nécessaire, puisque pParam deviendra une constante dans la formule finale.

si pParam vaut 5, ROW(INDIRECT(""1:" & pParam & """)) deviendra ROW(INDIRECT("1:5")), ce qui est la même chose que ROW(1:5), mais avec des complications inutiles.

Le 24/09/2019 à 08:53

Bonjour H2S04,

Merci pou ton retour c'est top. Un problème de parenthèses donc.

h2so4 a écrit :

Cependant dans ce cas, l'utilisation de indirect n'est pas nécessaire, puisque pParam deviendra une constante dans la formule finales.

Tu as tout à fait raison sur ce point! C'est finalement sur la base de cet exemple que tu m'as aidé mais en vérité c'est pour un autre projet lié à une formule avec variable.

Merci encore pour ton temps, tes lumières! Le post est résolu!

Excellente journée,

Naxos

Rechercher des sujets similaires à "formule vba parametres"