Incrémenter ParamArray dans function par Sub

**Ganeshiva** · 10/07/2015, 09h04

Bonjour Tout le monde

Je vous expose ma problématique :

je fais appel à une sub qui me copier une 40aine lignes en dessous celle-ci avec un décalage constant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
    Rows("77:116").Copy
    Rows("117:117").Insert Shift:=xlDown
    Application.CutCopyMode = False

Par rapport à ces mêmes lignes, j'ai une fonction personnalisé de type : Function Blabla(X As Range, ParamArray Lgt())
Le paramArray prend en compte la première ligne soit la ligne 77 (E77:AJ77), j'aurais voulu incrémenté mon ParamArray de tel sorte que Lgt() = E77:AJ77,E118:AJ118 soit un décalage de 41 lignes etc sans avoir à retoucher la formule manuellement... est ce possible ?

Sachant qui faudrait que j'inclus un compteur du nombre d'utilisation dans ma macro pour définir le nombre de paramArray a rajouter non ?

Merci pour votre aide dans tous les cas.

Invité · 10/07/2015, 10h00

bonjour,
ta question me pose d'autre question.
ParamArray Lgt() propose a ta méthode un passage de paramètre implicitement optionnel. Blabla range("A1"),"toto","titi",??,??...) comme le montre la déclaration Lgt() est un tableau

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
for i = 0 to Ubound( Lgt)
next

maintenant la question est de savoir si tu écrit tes valeur en dure dans l'appel de ta fonction ou si elle sont dynamique.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sub Test1()
 a=   Blabla( range("A1"),"toto","titi")
End Sub
 
sub Test2()
 a=   Blabla( range("A1"),"grgr")
End Sub
 
sub Test3()
 a=   Blabla( range("A1"),"Cheval","Autruche","Eléphant")
End Sub
 
'ou
sub Test4()
 a=   Blabla( range("A1"),array("Cheval","Autruche","Eléphant"))
End Sub

dans le premier cas le boucle utilisé précédemment reste vaable

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
for i = 0 to Ubound( Lgt)
next

dans le 2ime cas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
for i = 0 to Ubound( Lgt)
    for i2= 0 to Ubound( Lgt(i)) 'et là on tien comte du type d'objet (Range) Lgt(i)(i2,1) exemple ([A1])
    next
next

je ne te demandes as de comprendre le code qui suis c'est à titre d'exemple!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
tx=split("TOTO;TITI;Cheval;Autruche")
if Highlander(Init, tx,"test", MyRange(L, 1),MyRange(L, 3), MyRange(L, 4), MyRange(L, 5))= True Then Msgbox "Doublon"
 
Function Highlander(Init As Boolean, ParamArray Plage()) As Boolean
'..................................................
'La méthode Highlander, il ne peut en rester qu'un.
'Retourne True si doublon.
'..................................................
 
Static CollectDoublon As Collection
Dim T As String
Dim PlageIndex As Long
Dim myPlage As Range
Dim Col As Integer
Dim Tableau
If Init = False Then
Init = True
   Set CollectDoublon = Nothing
   Set CollectDoublon = New Collection
End If
 
 
T = "T"
For PlageIndex = 0 To UBound(Plage)
If TypeName(Plage(PlageIndex)) = "Range" Then
       Set myPlage = Plage(PlageIndex)
 
       For Col = 1 To myPlage.Columns.Count
        T = T & "_" & Trim("" & myPlage(1, Col))
       Next
    Else
        If TypeName(Plage(PlageIndex)) = "Variant()" Then
           Tableau = Plage(PlageIndex)
        Else
            If TypeName(Plage(PlageIndex)) Like "*()" Then
                Tableau = Plage(PlageIndex)
            Else
                Tableau = Split(Plage(PlageIndex) & ";", ";")
            End If
        End If
         For Col = 0 To UBound(Tableau)
           If Trim("" & Tableau(Col)) <> "" Then T = T & "_" & Trim("" & Tableau(Col))
         Next
    End If
Next
On Error Resume Next
CollectDoublon.Add T, T
If Err <> 0 Then Highlander = True
On Error GoTo 0
End Function

http://www.developpez.net/forums/d13...m/#post7484874

**Ganeshiva** · 10/07/2015, 10h56

Merci rdurupt pour ta réponse.

Je vais essayer de répondre à ton autre question du coup

Effectivement ma fonction est rentré manuellement dans un premier temps, c'est à dire :

Etage correspond à une range
Et ParramArray correspond aussi à une range Logement qui suivant mon tableau aurait besoin d'être incrémenter.
Du coup, je repensais à cela, peut être pourrais-je le virer des arguments de ma formule pour être inscrit en dur

soit
Lgt = range("E77:AJ77")
Lgt(i) = lgt(i) + 41

Voici ma fonction initial :

Qui en fonction d'un certain nombre de critère, additionne les quantités lorsqu'il y a concordance de valeur dans les ranges de logements et Etage

Logement peut faire appel à plusieurs tableaux que je recopie grace à une simple sub copier-coller en dessous du tableau initiale mis les uns à la suite des autres mais m'oblige à reprendre manuellement ma fonction en fonction du nombre de tableau que j’insère.

mon souhait c'est que l'en fonction de cette sub, il incrémente mon Lgt() automatiquement sans écraser la range "Etage"

Si tu veux, je peux t'envoyer mon tableau par mail si tu veux bien me la passer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Function Nombre_APS_Cumule_EC(Etage As Range, ParamArray Lgt() As Variant)
 Dim i&
 Dim Logement As Range
 Dim Et As Range
 Dim Total As Range
 Dim debitsup As Range
 Dim EC As Single
 Dim x As Range
 
Set Total = Cells(Application.Caller.Row, 2)
Set x = Cells(Application.Caller.Row, 4)
Set debitsup = Application.Caller.Offset(-1, 0)
 
 
If IsEmpty(x) = True Or WorksheetFunction.CountA(Etage) = 0 And Total <> "TOTAL" Then
Nombre_APS_Cumule_EC = 0
 
ElseIf IsNumeric(debitsup) = False Or Total.Offset(-1, 0) = "TOTAL" Then
For i = 0 To UBound(Lgt)
         For Each Logement In Lgt(i).Cells
        For Each Et In Etage
              If Logement = Et Then
              EC = EC + Logement.Offset(32, 0).Value
              End If
            Next Et
        Next Logement
   Next i
 Nombre_APS_Cumule_EC = EC
 
 ElseIf Total = "TOTAL" Then
 Nombre_APS_Cumule_EC = debitsup
 
 Else
 For i = 0 To UBound(Lgt)
         For Each Logement In Lgt(i).Cells
        For Each Et In Etage
              If Logement = Et Then
              EC = EC + Logement.Offset(32, 0).Value
              End If
            Next Et
        Next Logement
   Next i
Nombre_APS_Cumule_EC = EC + debitsup
   End If
 
End Function

Invité · 10/07/2015, 13h09

Pour ton problème je ne suis pas sur d'être le mieux placé pour t'aider, je n'y comprends rien!

mais vraiment rien, je ne suis même pas capable de formuler une question!

Une question pas formulée c'est pas de réponse du tout!

**Ganeshiva** · 10/07/2015, 14h59

Ah mince si un expert ne comprends pas ça va pas le faire ...

**Qwazerty** · 10/07/2015, 21h27

Salut

Humm, pourquoi utiliser un paramArray ?
Pourquoi ne pas utiliser tout simplement un range puisque les valeurs saisies dans la paramarray font (si j'ai bien compris) référence à une plage de cellule?

Plutôt que de demander à résoudre ton problème de code, ne voudrais tu pas remonter d'un cran et nous expliquer ce que tu souhaites faire avec ce code, il y a peut-être une façon plus simple d'y arriver.

++
Qwaz

PS: Pense à utiliser le bouton code "#" pour mettre ton code dans les balises idoines.

**Ganeshiva** · 15/07/2015, 08h18

Pour te répondre Qwazerty

Le tableau Décompte des appareils sanitaires par local / Logement me permet par numéro, nom de local de compter le nombre d’appareils sanitaires par local.

Le tableau dimensionnement des alimentation par colonne montante me permet en reprenant juste le numéro / nom du local de récupérer les informations de quantités, de débit total par logement et de les additionner et de les cumuler.

J’ai fait une macro copiercoller qui me copie les lignes 77:115 en ligne 117.
Si tu regardes bien la fonction : Nombre_APS_Cumule_EF, elle est constituée de deux choses, un sélection range Etage qui correspond à E124:N124 qui représente les locaux à comparer, et d’un paramarray Lgt qui correspond à la ligne du E77 : AJ77 qui est aussi une range pour justement retrouver les noms des locaux et additionner en offset les quantités d’appareils sanitaires/ Local.

Le problème se situe lorsque j’ai besoin d’augmenter le tableau décompte des appareils sanitaires lors de gros projets, j’aurais voulu que Nombre_APS_Cumule_EF s’adapte automatiquement et prenne en compte le rajout de la range rajouter en E117:AJ117 (le nombre de tableau décompte n’est pas limité)

Ce que j’imaginais, c’est faire un compteur qui incrémente en fonction du nombre de fois que la sub copiercoller est utilisé (Je ne sais pas si c’est possible) et de faire que Lgt prennent en compte les ranges rajouter dans la formule sans pour autant faire que cela soit un paramarray

Qu’en penses-tu ?

**Qwazerty** · 15/07/2015, 19h30

Salut

Uff, heu tu aurais un fichier exemple ? :p

++
Qwaz

Invité · 16/07/2015, 10h16

Bonjour,
je pense que tu te torture le cerveau! en effet, pour rendre ta plage dynamique il ne faut pas intervenir sur la fonction mais sur la formule.
=Nombre_APS_Cumule_EF(DECALER( D124;0;1):DECALER(P124;0;-2);DECALER( D$77;0;1):DECALER(AK$77;0;-1))!
cette formule encadre les plages que tu veux traiter, si tu insert une ligne ou un colonne entre les bornes de chaque terme tu augmente la plage de traitement!

**Ganeshiva** · 16/07/2015, 10h48

bonjour rdurupt

Je viens d'essayer de faire ce que tu me disais, mais je ne comprends pas trop comment le décaler peut marcher
Initialement ma formule est :

Nombre_APS_Cumule_EF(Etage, ParramArray Lgt())

Nombre_APS_Cumule_EF(E163:N163;$E$77:$AJ$77)
si comme le précisait précédemment j'insère un tableau de décompte en plus, ma formule devient

Nombre_APS_Cumule_EF(E163:N163;$E$77:$AJ$77;$E$117:$AJ$117)
je réinsère un encore un tableau, ma formule devient :

Nombre_APS_Cumule_EF(E163:N163;$E$77:$AJ$77;$E$117:$AJ$117;$E$157:$AJ$157)
etc ...

Ou E163:N163 corresponde à Étage et $E$77:$AJ$77;$E$117:$AJ$117;$E$157:$AJ$157 à ParramArray Lgt()

en VBA, il faudrait peut être que j'utilise "Union" que je passe ParramArray Lgt() en Lgt as Range

Invité · 16/07/2015, 10h58

non DECALER(AK$77;0;-1) est égale à AJ$77 DECALER(Range,Linge,Colonne) revient à prendre l'adresse du range.offset(0,-1)

si tu insert un colonne en AK77 la formule devient DECALER(AL$77;0;-1) ce qui revient à AK77
cet formule :=Nombre_APS_Cumule_EF(DECALER( D124;0;1):DECALER(P124;0;-2);DECALER( D$77;0;1) :DECALER(AK$77;0;-1)) !
est égale à: =Nombre_APS_Cumule_EF( E124:N124; E$77:AJ$77) sauf quelle est dynamique!

**Ganeshiva** · 16/07/2015, 11h33

Ok , je viens de comprendre le principe mais (car il y a un mais) je décale en vertical soit DECALER($E$77;40;0)

ECALER($AJ$77;40;0) mais cela ne marche qu'une seule fois car après il me faudrait DECALER($E$77;80;0)

ECALER($AJ$77;80;0)

$E$77 : $AJ$77
$E$117 : $AJ$117
$E$157 : $AJ$157
$E$197 : $AJ$197
etc .. en cumulé car il pourrait y avoir toutes ces plages à prendre en compte

Invité · 16/07/2015, 11h48

il ne faut pas prendre la cellule de début et la cellule de fin car celle ci peuvent changer, il faut que les cellule que tu décales encadre la plage qui t'intéresses!
DECALER($E$77;80;0) implique que tu garde toujours 80 linge alors DECALER(E158;-1;0) fait la même chose mais est dynamique

si ce que je veux traiter est B2 les cellule qui encadres sont [A1];[A2];[A3];[B1];[B3];[C1];[C2];[C3]

DECALER(A1;1;1) :DECALER(C3;-1;-1) donne bien [B2]
DECALER(A2;0;1) :DECALER(C2;0;-1) donne bien [B2]
DECALER(B1;1;0) :DECALER(B3;-1;0) donne bien [B2]
DECALER(B1;1;0) :DECALER(C3;-1;-1) donne bien [B2]
DECALER(A1;1;1) :DECALER(C2;0;-1) donne bien [B2]
DECALER(A3;-1;1) :DECALER(C1;1;-1) donne bien [B2]

**Ganeshiva** · 16/07/2015, 14h08

Oui, je comprends ce que tu veux dire, mais je vois pas trop comment cela s'adapte à mon message de 10h58 en prenant en compte le décalage et le cumul de 40 lignes à chaque fois

soit plage initial : "($E$77 : $AJ$77)"
plage initial + 1 tableau en plus : "($E$77 : $AJ$77;$E$117 : $AJ$117)"
plage initial + 2 tableau en plus : "($E$77 : $AJ$77;$E$117 : $AJ$117;$E$157 : $AJ$157)"
etc ...

Invité · 16/07/2015, 14h51

soit plage initial : "($E$77 : $AJ$77)"
plage initial + 1 tableau en plus : "($E$77 : $AJ$77;$E$117 : $AJ$117)"
plage initial + 2 tableau en plus : "($E$77 : $AJ$77;$E$117 : $AJ$117;$E$157 : $AJ$157)"

soit plage initial : "(Decaler(D$77 ;0;1: Decaler(AK$77;0;-1))"
plage initial + 1 tableau en plus : "(Decaler(D$77 ;0;1): Decaler(AK77;0;-1);Decaler(D$117;0;1) : Decaler(AK$117;0;-1))"
plage initial + 2 tableau en plus : "(Decaler(D$77;0;1) : Decaler(AK$77;0;-1);Decaler(D$117;0;1) : Decaler(AK$117;0;-1);Decaler(D$157;0;1) : Decaler(AK$157;0;-1))"

Note que le $D fixe la position sur la colonne D alors que D est un position relative et change la colonne dans la formule au moment de l'insertion de colonnes.

**Ganeshiva** · 16/07/2015, 16h07

Je vois encore une fois ce que tu veux dire rdurupt mais c'est pas trop ce que recherche. Mais je prends bonne note ce me servira par la suite

Ta solution m'oblige à rentrer dans ma formule les différentes plages alors que je veux les automatisés, c'est à dire plus j'insère de tableau plus ma formule s'adapte en fonction du nombre de tableau sans intervention de l'utilisateur.

En gros je veux pouvoir inscrire des données type Range dans un ParamArray de type Variant.

Mais je n'arrive pas à le faire.
Il faudrait que j'arrive a transformer un range en type variant.

Invité · 16/07/2015, 16h21

C'est exactement ce que je te propose ta formule s'adapte toute seule avec la solution que je te propose!

Si la baignade est surveillé jusqu'à 50 mètrs du bord, peut importe la ligueurs de la plage!

**Ganeshiva** · 16/07/2015, 16h26

Bah je comprends pas comment faire du coup, je dois être un peu neuneu

Invité · 16/07/2015, 16h41

Tu as une plage qui vas de [B150] à [Y150] ta formule prend les valeurs qui encadre cette plage [A150] à [Z150]
Si tu decal ces valeurs de 0 ligne et d'une colonne vers la droite pour la première et vers la gauche pour la deuxième, ta formule occupe la même plage, mais si tu enserres une colonne entre a et z ta formule s'adapte à la nouvelle plage!
Pareil si tu supprime une colonne!

Decaler([A150];0;1)Décaler vers droite
Decaler( [Z150];0;-1)' une colonne vers la gauche

Incrémenter ParamArray dans function par Sub

Macros et VBA Excel

Discussions similaires

Partager

Partager