[Toutes versions] Fonction Lettre de la colonne [Résolu]

[jfontaine]

Bonjour,

Cette fonction permet de retourner la lettre correspondant au numéro de colonne passé en paramètre

Code :

1
2
3
Public Function Lettre_Colonne(colonne As Long) As String
Lettre_Colonne= Split(Sheets(1).Cells(1, colonne ).Address, "$")(1)
End Function

[corona]

Bonjour Jérôme,
Sympa comme fonction

[jfontaine]

Merci

[patricktoulon]

bonsoir jfontaine

et pourquoi pas rassemblé toute ls possibilitéen une

un exemple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Public Function propertycell(cellule As Range, index_property As Variant) As String
    Select Case index_property
    Case 1
        propertycell = Split(cellule.Address, "$")(1)
    Case 2
        propertycell = Split(cellule.Address, "$")(2)
    Case 3
        propertycell = cellule.Font.Name
    Case 4
        propertycell = cellule.Font.Size
    Case 5
        propertycell = cellule.Interior.Color
    Case 6
        propertycell = cellule.Font.Color
        ' et on peut en ajouter autant que de propriété que la cellule peut avoir
 
    End Select
 
End Function

'un exemple d'appel de la fonction

Code :

1
2
3
Sub essaie()
    MsgBox propertycell(ActiveCell, 5)
End Sub

il n'y a qu'a changer le chiffre

au plaisir

[Qwazerty]

Salut
Dans quel cas as tu besoin du nom de la colonne?
++
Qwaz

[Ormonth]

Bonjour,

penser aussi à utiliser la
FAQ et son moteur de recherche


Comment obtenir la valeur en lettre de la colonne ?

et ne pas oublier que Split a fait son apparition avec les versions Office 2000...

cordialement,

Didier

[patricktoulon]

bonjour ormonth et jfontaine

de toute façon même en dessous de la version 2000 le "range(x: x).address"
existait déjà
donc sans la fonction split

un boucle sur le len de l'adresse jusque au 2 "$" nous donne les ou la lettre de la colonne

ensuite

Code :

 lettre = left (l'adresse,le nombre obtenue dans la boucle

ensuite

Code :

 lettre =replace ( lettre ,"$","")

un peu barbare mais ca fonctionne
sinon il y a encore autre chose

avec l'instruction "instr" et" mid " on obtient aussi un bon résultat
exemple :

Code :

1
2
3
4
5
6
7
 
Sub donne_la_lettre_de_la_collone()
adresse = "$AQ$41" ' ca peut etre "active cell ou même une variable représentant l'adresse de la plage
limite = InStr(2, adresse, "$") 'renvoie le placement du $ dans la chaîne de caractere que représente l'adresse de la cellule
MsgBox Mid(adresse, 2, limite - 2) 'avec la fonction "mid" on démarre a partir du 2 eme caractère _
car forcément le premier est un "$" et on termine au 2eme "$" les lettres se trouve forcément entre les deux
End Sub

au plaisir

[bbil]

Envoyé par Qwazerty

Salut
Dans quel cas as tu besoin du nom de la colonne?
++
Qwaz

+1

[patricktoulon]

bonsoir a tous

allez pour le fun
exemple avec la fonction "instr et mid"

la fonction te retourne la colonne en lettre si il y a une seule cellule sélectionnée

ou les deux si c'est une plage

un exemple d'appel de la fonction dans le worksheet par le selection change

Code :

1
2
3
4
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    texte = colone_en_lettre(Selection.Address)
    MsgBox texte
'End Sub

code a mettre dans un module standard

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Dim debutcol2 As Variant, col1 As Variant, col2 As Variant, limite As Variant, limite2 As Variant
Dim adresse As String
Public Function colone_en_lettre(adresse As String) As String
    simultiple = InStr(2, adresse, ":") + 1
    If simultiple = 1 Then
        limite = InStr(2, adresse, "$")    'renvoie le placement du $ dans la chaine de caractere que représente l'adresse de la cellule
        colone_en_lettre = Mid(adresse, 2, limite - 2)
        ElseIf simultiple <> 1 Then
        limite = InStr(2, adresse, "$")
        col1 = Mid(adresse, 2, limite - 2)
 
       debutcol2 = simultiple + 1 'on demarera la chaine a la 1ere lettre apres le $ qui suit le ":"
        limite2 = InStr(simultiple + 1, adresse, "$")
        limite2 = limite2 - debutcol2
 
        col2 = Mid(adresse, debutcol2, limite2)
        colone_en_lettre = "ca commence dans la colone " & col1 & " et ca termine par la colonne " & col2
    End If
End Function

mais bon heureusement que l'on a "split" quand même

au plaisir

[jfontaine]

pour répondre à l'interrogation de Qwazerty et Bbil

Dans quel cas as tu besoin du nom de la colonne?

Cette fonction trouve sont utilité lors de la construction dynamique d'une formule de cellule

Ici on met en A1 la somme d'une cellule sur 2 entre B et T

Code :

1
2
3
4
5
6
7
8
9
10
 
Dim strFormule As String
Dim i As Long
 
strFormule = "="
For i = 2 To 20 Step 2
    strFormule = strFormule & "+" & Lettre_Colonne(i) & "1"
Next i
 
Range("A1").FormulaLocal = strFormule

[mercatog]

Bonsoir
Je joins Bbil et Qwazerty.
Pour l'exemple de jfontaine, personnellement j'utiliserai l'un des 2 codes suivants:

Code :

1
2
3
4
5
6
7
8
9
Sub XXX()
Dim strFormule As String
Dim i As Integer
 
For i = 2 To 20 Step 2
    strFormule = strFormule & "+RC[" & i - 1 & "]"
Next i
Range("A1").FormulaR1C1 = "=" & Mid(strFormule, 2)
End Sub

Code :

1
2
3
4
5
6
7
8
9
Sub YYY()
Dim strFormule As String
Dim i As Integer
 
For i = 2 To 20 Step 2
    strFormule = strFormule & "+" & Cells(1, i).Address(0, 0)
Next i
Range("A1").Formula = "=" & Mid(strFormule, 2)
End Sub

[AlainTech]

+1

[jfontaine]

En effet les solutions de mercatog sont plus adaptées à ce contexte

Finalement cette fonction n'a peut être pas d'utilité si ce n'est la satisfaction de son auteur (et c'est déjà ça)

[patricktoulon]

bonjour a tous

juste pour le fun

voila encore un truc encore plus bête pour trouver la ou les lettres de la colonne de la cellule

ici on va utiliser un array
bon je n'ai pas tout mis mais la totalité des éléments de l'array prend 11 lignes

dans le module du sheet

Code :

1
2
3
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox lettre_colonne(ActiveCell.Column)
End Sub

la fonction dans un module standard

Code :

1
2
3
4
5
Function lettre_colonne(col As Long) As String
Dim colone
colone = Array("", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
lettre_colonne = colone(col)
End Function

au plaisir

[Pierre Fauconnier]

Et tu vas créer un array de 16384 entrées pour XL2007 et 2010?

[patricktoulon]

bonjour perre fauconnier

oui je suis d'accors avec toi mais cependant le sujet ici etait de trouver les lettre de colones

ensuite il a été signalé que avant la version 2000 le split n'existait pas

alors bien que fou
j'ai posté quand meme cette version car si seules une partie des colones est utile dans le code ca peut etre une alternative

surtout que si je fait les test de comparaison en temps! avec ma premiere version avec le "instr et le mid" et celui avec l'array et bien l'array l'emporte haut la main( 2 fois moins de temps)

rogolo non?
et pour conclure je n'aurais jamais pu commencer avec une version anterieure a 2007 j'aurais certainement attraper une arthrite au doigts (trop de code a taper)
au plaisir

[Pierre Fauconnier]

Code :

LettreColonne = Left(Cells(1, Colonne).Address(1, 0), InStr(Cells(1, Colonne).Address(1, 0), "$") - 1)

Avec colonne comprenant une valeur entre 1 et 16384 (pour Excel2007 et Excel 2010) ou entre 1 et 256 pour les versions antérieures, ce code fonctionne sans split, du moment que la librairie Excel soit active. Si l'on veut que le code fonctionne quelle que soit la version, on l'intègre dans une fonction qui teste la version.

Code :

1
2
3
4
5
6
7
8
9
10
Function LettreColonne(Colonne As Long) As String
  ' Renvoie la lettre du numéro de colonne passé en paramètre
  ' Si Colonne en dehors des limites acceptables, renvoie une chaine vide
 
  Dim NombreColonnes As Long
 
  If Application.Version < 12 Then NombreColonnes = 257 Else NombreColonnes = 16385
  If Colonne > 0 And Colonne < NombreColonnes Then _
    LettreColonne = Left(Cells(1, Colonne).Address(1, 0), InStr(Cells(1, Colonne).Address(1, 0), "$") - 1)
End Function

Dès lors, je ne vois pas l'utilité de se taper des lignes de constantes pour construire un array.

Quant à la rapidité de l'array par rapport à ce qui est présenté, je n'ai même pas envie de perdre du temps à en débattre tellement c'est ridicule. Je serais d'ailleurs bien curieux de voir comment mesurer la différence entre les deux.

j'ai posté quand meme cette version car si seules une partie des colones est utile dans le code ca peut etre une alternative

=> Ce qui signifierait qu'il faille adapter le code en fonction des colonnes concernées... Et tu me parles plus haut de "gain de temps" avec ton array... Allons allons, il faut rester raisonnable!

[Pierre Fauconnier]

Quant à l'utilité de la fonction, que Jérôme ne soit pas déçu, il y a au moins deux raisons à souhaiter utiliser la fonction.

1. vu que nous sommes habitués à utiliser les références A1 plutôt que L1C1, une personne créant du VBA pour Excel peut s'y retrouver plus facilement en VBA avec le même type de références plutôt que de devoir jongler avec un style de références moins bien maîtrisé ;
2. si l'on doit recréer des formules utilisant INDIRECT(), il sera sans doute plus utile d'utiliser les références A1 pour une question de lisibilité de la formule dans Excel, car une formule du style =INDIRECT("L(1)C(-3)";FAUX) créée en VBA risque de perturber l'utilisateur s'il est en références A1.

[bbil]

Bonsoir,

je suis toujours pas convaincu de l'utilité de cette fonction .. mais bon pour le plaisir je vous propose une approche différente :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
Function LettreColonne(iNumColonne As Integer)
 Dim st As String
 Dim iReste As Integer
 Dim X As Integer
 X = iNumColonne
 If X = 0 Then Stop
 While X > 0
   iReste = (X - 1) Mod 26
   st = Chr(iReste + 65) & st
   X = (X - 1) \ 26
 Wend
 LettreColonne = st
End Function

[Pierre Fauconnier]

Salut bbil,

J'aime bien aussi ta fonction car elle permet de retrouver la "lettrecolonne" même sans la librairie Excel activée. Cela pourrait avoir un intérêt pour la construction d'un fichier Excel ex nihilo au format 2007, en VB ou en .net, par exemple.