XL 2013 Problème de fonction VBA excel

JoelR

XLDnaute Nouveau
Bonjour,

Pour le besoin d'une maquette, j'ai créé une fonction Excel à l'aide du VBA Excel. Le code permet de compter le nombre d'occurrences de cellule vide d'une colonne d'une plage en lui donnant juste l'intitulé de la colonne à dénombrer pour se situer.

Le problème est que lorsque je fais appel à la fonction dans Vba, ça marche parfaitement sans souci mais quand je l'appelle à partir d'Excel dans une cellule, il m'indique parfois une erreur de référence circulaire. J'ai vérifié le code et j'ai mis dans deux feuilles différentes la plage de recherche et les résultats à retourner. Hélas, c'est toujours la même chose.

Voici le code:

Option Explicit
Public Function NbVide2(Tableau As Range, NomColonne As String)
Dim Trouve As Range, Table As Range, Cel As Range
Dim ColNum As Long, RowNum As Long, Count As Long, FirstRow As Long

'Recherche la position en terme de colonne de l'intitulé de colonne définie
Set Trouve = Tableau.Rows(1).Find(What:=NomColonne, LookAt:=xlWhole)
ColNum = Trouve.Column

'Détermination de la dernière ligne occupée par la plage (dans l'hypothèse où la première colonne détermine le nombre de lignes de la plage)
RowNum = Cells(Tableau.Rows.Count, 1).End(xlUp).Row

'Détermination de la première ligne occupée par la plage
FirstRow = Tableau.Rows(1).Row

'Définition de la plage de recherche
Set Table = Range(Cells(FirstRow, ColNum), Cells(RowNum, ColNum))


'Compte dans la plage sélectionnée l'occurrence des cellules vides
Count = 0
For Each Cel In Table
If Cel = "" Then
Count = Count + 1
End If
Next


NbVide2 = Count

Set Tableau = Nothing
NomColonne = ""
Count = ""
End Function

Pouvez-vous m'aider svp?

Merci d'avance.

Cordialement,
 

Robert

XLDnaute Barbatruc
Bonsoir Joël, bonsoir le forum,

Peut-être en simplifiant. Dans une cellule (en dehors du tableau et de la colonne de la cellule que tu vas sélectionner), tape "=" et clique sur n'importe quelle cellule de la colonne où tu veux que ça compte. Valide avec la touche [Entrée]...
Le code :

VB:
Public Function NbVide2(C As Range)
NbVide2 = Application.WorksheetFunction.CountBlank(Range(Cells(1, C.Column), Cells(Application.Rows.Count, C.Column).End(xlUp)))
End Function
 

JoelR

XLDnaute Nouveau
Bonjour Robert,

Merci de ton retour.

Oui en effet, c'est une solution car ça marche. D'autant plus, moins compliqué. :)

Sinon, en cas de besoin des autres personnes sur le forum, le problème de mon code est que Cells prend en compte la feuille active. Donc, la cellule où j'ai mis ma formule dans excel, cette dernière est comprise dans la sélection. Pour éviter cela il faut changer cette ligne de code:

Set Table = Range(Cells(FirstRow, ColNum), Cells(RowNum, ColNum))

en

Set Table = Range(Tableau.Cells(FirstRow, ColNum), Tableau.Cells(RowNum, ColNum))

C'était tout bête mais il fallait y penser.

Cordialement,
 

patricktoulon

XLDnaute Barbatruc
re
bonjour
@Robert c'est pas tout a fait l'intention de @JoelR
il y a une différence entre
range("x:y").columns(x).countblank
et columns(x).countblank

c'est pas la même chose
la première détermine un plafond haut et bas
la 2d ne se réfère qu'a la colonne
c'est pas du tout la même intention d'autant plus que l'on parle de nom de colonne et il est donc possible que ce soit un TS et combien meme que nom l'argument 1 (range) détermine la zone limite
j'ajouterais que si c'est un ts la limite est alors toute déterminer par le range du listobject lui même
mais si c'est pas le cas alors ta fonction ne rempli pas le job

je cite
VB:
Public Function NbVide2(Tableau As Range, NomColonne As String)
''.......
c'est pas toujours bon de prendre des raccourcis ;)
 

patricktoulon

XLDnaute Barbatruc
re
testez
VB:
Sub test()
    MsgBox "ne pas confondre  nonbre de cellules vides" & vbCrLf & "dans la colonne ""loulou"" de la plage A1:D17" & vbCrLf & _
           NbVide3([A1:d17], "loulou") & vbCrLf & _
           "nombre de cellule vide dans la colonne ""loulou"" end(xlup)de la feuille " & _
           vbCrLf & nbvide_PASBON([A1:d17], "loulou")
End Sub


Function NbVide3(rng, colonne)
    Dim col As Range
    If Not IsNumeric(colonne) Then
        Set col = rng.Rows(1).Find(colonne, LookIn:=xlValues, lookat:=xlWhole)
    Else: Set col = rng.Columns(colonne)
    End If
    NbVide3 = WorksheetFunction.CountBlank(col.Resize(rng.Rows.Count))
End Function



Function nbvide_PASBON(rng, colonne)
    Dim col As Range
    If Not IsNumeric(colonne) Then
        Set col = rng.Rows(1).Find(colonne, LookIn:=xlValues, lookat:=xlWhole)
    Else: Set col = rng.Columns(colonne)
    End If
    Set col = Range(col, Cells(Rows.Count, col.Column).End(xlUp))
    nbvide_PASBON = WorksheetFunction.CountBlank(col)
End Function

1645373923387.png
 

Robert

XLDnaute Barbatruc
Bonsoir le fil, bonsoir le forum,

@patrick,
Blablablabla... À aucun moment on a eu un fichier exemple pour tester. Personne n'a parlé de tableau structuré. Je ne sais pas comment tu peux connaître l'intention de Joël !?... Disons que tu as une autre proposition différente c'est tout... Laisse le demandeur juger.

c'est pas toujours bon de prendre des raccourcis ;)
 

patricktoulon

XLDnaute Barbatruc
Bonjour @Robert
ce n’était pas une critique négative
simplement une observation
je connais pas l'intention de @JoelR

mais la déclaration de sa fonction en donne un petit aperçu quand même non ?

il demande ceci:
les vides d'une colonnes dans un range déterminé
VB:
Public Function NbVide2(Tableau As Range, NomColonne As String)

tu lui donne cela
les vides d'un range
Code:
Public Function NbVide2(C As Range)

c'est pas la même chose du tout

et tu détermine la fin de ta colonne avec un end(xlup) alors que comme je le démontre le résultat peut etre très différent
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour à toutes et tous :)

Une fonction personnalisée qui doit fonctionner que le tableau soit une plage ordinaire ou bien un tableau structuré.
Voir utilisation en colonne C et N.

VB:
Public Function NbVide2(Tableau As Range, NomColonne As String)
Dim Plage As Range, CellEnTete As Range, t, nbr As Long, x, i As Long

   'définit la plage: que tableau soit une plage ordinaire  ou bien un tableau structuré-
   On Error Resume Next
   Set x = Tableau.Cells(1, 1).ListObject
   If x Is Nothing Then Set Plage = Tableau Else Set Plage = x.Range
 
   'Recherche la position en terme de colonne de l'intitulé de colonne définie
   Set CellEnTete = Plage.Rows(1).Find(What:=NomColonne, LookAt:=xlWhole)
   On Error GoTo 0
   If CellEnTete Is Nothing Then NbVide2 = CVErr(xlErrRef): Exit Function     'si en-tete non trouvée
 
   'tableau des données de la colonne à considérer
   t = Plage.Columns(CellEnTete.Column - Tableau.Column + 1)    'transfert des valeurs de la colonne dans un tableau de type type Array
 
   'on passe les cellules vides du bas (on s'arrête à 2 => l'en-tête est excluse)
   For i = UBound(t) To 2 Step -1
      If t(i, 1) <> "" Then Exit For
   Next i
   'on compte les cellules non vides
   For i = i To 2 Step -1: nbr = nbr + IIf(t(i, 1) = "", 1, 0): Next
 
   NbVide2 = nbr
End Function
 

Pièces jointes

  • JoelR- compter vide- v1.xlsm
    21.5 KB · Affichages: 3

patricktoulon

XLDnaute Barbatruc
re
bonjour @mapomme
sérieux une boucle pour compter 😂
je la remet au cas ou
VB:
Function NbVide3(rng, colonne)
    Dim col As Range
    If Not IsNumeric(colonne) Then
        Set col = rng.Rows(1).Find(colonne, LookIn:=xlValues, lookat:=xlWhole)
    Else: Set col = rng.Columns(colonne)
    End If
    NbVide3 = WorksheetFunction.CountBlank(col.Resize(rng.Rows.Count))
End Function
l'argument colonne peut être un nom ou un numérique
 

patricktoulon

XLDnaute Barbatruc
re
ben oui c'est ce que j'expliquais à robert
les cellules vide de la colonne (x) d'une plage
c'est pas pareil que
la colonne(x) de la feuille qui se trouve dans la plage
et il est pas de question ici de parler de end(xlup) puisque la plage est déterminée

'Recherche la position en terme de colonne de l'intitulé de colonne définie

VB:
Set CellEnTete = Plage.Rows(1).Find(What:=NomColonne, LookAt:=xlWhole)
   On Error GoTo 0
   If CellEnTete Is Nothing Then NbVide2 = CVErr(xlErrRef): Exit Function     'si en-tete non trouvée
 
   'tableau des données de la colonne à considérer
   't = Plage.Columns(CellEnTete.Column - Tableau.Column + 1)    'transfert des valeurs de la colonne dans un
    'on peut faire comme ça aussi
   t=CellEnTete.resize(plage.rows.count).value
   'ou sans l'entete
   t=CellEnTete.offset(1).resize(plage.rows.count-1).value
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re @patricktoulon :)

Ce que je voulais dire c'est que pour la colonne en question, je ne compte les vides que depuis le début de la colonne jusqu'au dernier élément non vide de la colonne. Si la colonne n'est pas entièrement remplie, je laisse tomber le comptage des cellules vides en fin de colonnes.

C'est le commentaire et la ligne de code de l'auteur qui m'ont incité à procéder de la sorte:
'Détermination de la dernière ligne occupée par la plage (dans l'hypothèse où la première colonne détermine le nombre de lignes de la plage)
RowNum = Cells(Tableau.Rows.Count, 1).End(xlUp).Row

Comme quoi, quand aucun exemple n'est fourni, on laisse libre cours à l'imagination des répondeurs.

C'est pour ça aussi, que j'ai codé pour que ça marche avec une plage ordinaire ou un tableau structuré.
 

patricktoulon

XLDnaute Barbatruc
re
et oui sans exemple on est pas sur du contexte
c'est un problème récurent sur le forum

perso si j'argumente une fonction avec un range et en 2d argument la colonne
pour moi la colonne c'est celle du range sinon je met pas d'argument range
je cherche "truc bidule" avec find previous et je prend l'entirecolumn.end(xlup)mais la on est dans une colonne de la feuille

perso je me suis fié a çà
VB:
Public Function NbVide2(Tableau As Range, NomColonne As String)
bref le demandeur nous diras si il daigne revenir pour apporter des précisions
;)