ByRef, affectation de variables.. [Résolu]

[d0n32]

Bonjour tout le monde,

Après lecture du lien ci-dessous (et je dois l'avouer, peu de compréhension de ce qui y est décrit ), je me décide à poster pour vous expliquer mon problème.

http://silkyroad.developpez.com/VBA/LesVariables/#LIV

J'ai créé une bête fonction dont l'objectif est de renvoyer le numéro de la dernière ligne non vide d'un tableau :

Code :

1
2
3
Function derniereLigneOccupee(cel As range)
    derniereLigneOccupee = cel.End(xlUp).Row
End Function

Dans une procédure, je trouve la valeur de cette dernière ligne

Code :

dlo = derniereLigneOccupee(range("B10"))

et je me sers du résultat dans une autre fonction

Code :

calculSommeDistanceModePose(dlo)

dont le code est le suivant :

Code :

1
2
3
4
5
6
7
8
9
10
 
Function calculSommeDistanceModePose(nomb As Integer)
    calculSommeDistanceModePose = 0
For i = 1 To nomb
 
    calculSommeDistanceModePose = calculSommeDistanceModePose + range("B4").Offset(i - 1 - 4, 0).Value * range("d4").Offset(i - 1 - 4, 0).Value
 
Next i
 
End Function

L'erreur renvoyée par Excel lors de l'exécution de la macro est le classique "type d'argument byref incompatible"..
Je ne trouve pas d'où provient l'erreur, d'autant plus que l'utilisation du résultat "dlo" ne pose aucun souci quand je l'utilise avec une autre fonction..

Merci d'avance de vos explications

[Daniel.C]

Bonjour,

Comment est définie la variable "dlo" ?

A tout hasard, modifie les lignes suivantes :

Code :

Function derniereLigneOccupee(cel As range) as Long

Code :

Function calculSommeDistanceModePose(nomb As Long) as Long

[d0n32]

Merci de ta réponse !

Avant calcul de dlo, j'ai défini dlo as long.
Avec les modifications que tu suggères, l'erreur se transforme en "400", ce qui me laisse penser à un mauvais appel de fonction. Tout semble bien orthographié cependant..

Edit : Si j'enlève le

Code :

dim dlo as long

, on retombe sur l'erreur "byref", localisée à l'appel de la fonction

Code :

calculSommeDistanceModePose(dlo)

[Daniel.C]

L'erreur se produit sur quelle ligne ?

[d0n32]

Cf poste édité ci-dessus, à l'appel de la fonction calculSommeDistanceModePose :

Code :

calculSommeDistanceModePose(dlo)

[Daniel.C]

Rectifie la ligne suivante :

Code :

    calculSommeDistanceModePose = calculSommeDistanceModePose + Range("B4").Offset(i - 1 - 4, 0).Value * Range("d4").Offset(i - 1 - 4, 0).Value

En effet :

Code :

Range("B4").Offset(i - 1 - 4, 0)

renvoie à la ligne 0 pour i=1

[d0n32]

Bien vu, merci de la peine que tu t'est donnée !
Après légère modification du code, la fonction ressemble à ça :

Code :

1
2
3
4
5
6
7
Function calculSommeDistanceModePose(nomb As Long) As Long
    calculSommeDistanceModePose = 0
For i = 4 To nomb - 1
 
    calculSommeDistanceModePose = calculSommeDistanceModePose + range("B" & i).Value * range("d" & i).Value
 
Next i

Et l'erreur n'est pas disparue. Sachant que dlo vaut au minimum 5, il ne devrait plus y avoir de problèmes dans l'indice de la boucle FOR..

Merci encore

[Daniel.C]

Telle qu'elle est conçue, la fonction "derniereLigneOccupee" renvoie le dernière ligne occupée au dessus de B4. Est-ce ce que tu veux ? Si tu veux la dernière ligne occupée au dessous de B4 (puisque dlo vaut au minimum 5), il faut mettre :

Code :

1
2
3
4
Function derniereLigneOccupee(cel As Range) As Long
    derniereLigneOccupee = Cells(Rows.Count, cel.Column).End(xlUp).Row
    'derniereLigneOccupee = cel.End(xlUp).Row
End Function

Le code suivant fonctionne sans erreur pour moi :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function derniereLigneOccupee(cel As Range) As Long
    derniereLigneOccupee = Cells(Rows.Count, cel.Column).End(xlUp).Row
    derniereLigneOccupee = cel.End(xlUp).Row
End Function
 
Function calculSommeDistanceModePose(nomb As Long) As Long
    calculSommeDistanceModePose = 0
For i = 4 To nomb - 1
 
    calculSommeDistanceModePose = calculSommeDistanceModePose + Range("B" & i).Value * Range("d" & i).Value
 
Next i
End Function
Sub test()
Dim dlo As Long
dlo = derniereLigneOccupee(Range("B10"))
MsgBox calculSommeDistanceModePose(dlo)
End Sub

[d0n32]

Merci beaucoup pour ton aide !

[d0n32]

(Re) bonjour,

Le même message d'erreur apparaît pour le bout de code suivant, à la dernière ligne, au niveau du "destinationArrivee". A croire que je n'ai pas bien compris le concept d'indexation !
J'ai du mal à m'expliquer pourquoi une erreur est possible au niveau de destinationArrivee, sachant que la variable est initialisée au début du code..

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
destinationArrivee = range("H11")
L = 0
j = 1
 
For i = 1 To 6
 
   L = range("C33").Offset(i - 1, 0).Value
 
    While range("G4").Offset(j - 1, 0).Value < L
 
        If j = 1 Then
 
            Call nouvelleCase(range("C4").Offset(j - 1, 0), range("G4").Offset(j - 1, 0).Value, destinationArrivee)
 
...

Le code de la fonction nouvelleCase :

Code :

1
2
3
4
5
6
Function nouvelleCase(cel As range, val, destin As range)
 
destin.Value = val
destin.Offset(0, 1).Value = cel.Value
 
End Function

J'ai un léger doute sur la rédaction de la fonction (pas de résultat retourné..), peut-être est-ce de là que vient l'erreur renvoyée par Excel.. ?

Merci d'avance de votre aide

[bbil]

Bonjour , quelques remarques :

Tout d'abord (c'est pas la solution à ton problème ...) :

Il ne faut jamais utiliser Range(...) sans rien devant il faut toujours le préfixer avec la feuille concernée... par exemple :

Code :

1
2
 
destinationArrivee = ThisWorkbook.Sheets("Feuil1").range("H11")

où

Code :

1
2
3
4
5
6
7
8
9
10
 
Dim f As WorkSheet 
set f = ThisWorkbook.Sheets("Feuil1")
destinationArrivee = f.range("H11")
(...)
 L = f.range("C33").Offset(i - 1, 0).Value
(...)
 calculSommeDistanceModePose = calculSommeDistanceModePose + f.Range("B" & i).Value * f.Range("d" & i).Value
 
(...)

ce principe est à appliquer à tout ton code..


Autre règle : il faut déclarer toutes les variables (pour ne pas en oublier rajoute Toujours la ligne Option Explict en haut de toutes tes pages de codes.)


Pour ton problème pour obtenir une référence à ta plage il faut que ta variable soit de type range et tu l'affecte avec l'instruction Set :

Code :

1
2
3
4
5
 
Dim DestinationArrivee as range
(...)
set destinationArrivee = ThisWorkbook.Sheets("Feuil1").range("H11")
(..)

Envoyé par d0n32

J'ai un léger doute sur la rédaction de la fonction (pas de résultat retourné..), peut-être est-ce de là que vient l'erreur renvoyée par Excel.. ?

Merci d'avance de votre aide

Ta fonction ne renvoyant rien transforme la en procédure en remplaçant Function par Sub ... tu peu conserver ton appel avec le Call sans problèmes..

[Daniel.C]

Bonjour,

Tu as défini le troisième argument de ta fonction en tant que Range, or :

Code :

destinationArrivee = Range("H11")

renvoie la valeur de H11 dans destinationArrivee.

D'autre part, tu appelles la fonction comme une macro et tu ne récupères nulle part le résultat. Un exemple d'utilisation est le suivant :

Code :

1
2
3
4
'Réf. 111103.xlsm d0n32
Dim destinationArrivee As Range
Set destinationArrivee = Range("H11")
Résultat = nouvelleCase(Range("C4").Offset(j - 1, 0), Range("G4").Offset(j - 1, 0).Value, destinationArrivee)

Le résultat de la fonction étant stocké dans la variable "Résultat".

[d0n32]

Génial merci à vous 2, mon code fonctionne déjà mieux, et remplit désormais les règles principales décrites par bbil



Merci encore