Discussion :

[tcheulboulet]

Bonjour,

Je reprends des macros déjà faites par un ancien collègue qui nous permettent de générer des scripts SQL suivant les informations contenues dans plusieurs colonnes d'une feuille Excel.
Celui-ci avait codé certaines de ses fonctions avec le nom de la colonne "en dur" (exemple:ActiveCell.Offset(-1, 0).Range("A1").Select))
Le problème est que si je supprime la colonne A, c'est le contenu de la colonne B qui viendra dans la colonne A, et ceci créera des inconsistances au niveau des données générées dans mon script SQL.
D'où la réflexion suivante:
J'ai défini des plages (sous Insertion->Nom-> Définir) pour chaque colonne. Je voudrais faire appel à ces plages dans mes macros en lieu et place des noms de colonne. (exemple : plage RANGEMandant= $B$1, plage RANGEPF=$A$1 )
J'ai quelques fonctions qui me permettent de récupérer le nom de la colonne en fonction de la position du curseur.

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
 
Public sRangeName As String
 
Public Function SetColumnLetter(ByVal RangeName As String)
 
sRangeColumnNumber = ActiveCell.Column
sRangeName = GetColumnHeaderFromIndex(sRangeColumnNumber)
End Function
'  ===============================================================
'        RETOURNE L'INDEX NOMMé (HEADER RéEL) DE LA COLONNE  EXCEL
'                    ----------------------
'  GetColumnHeaderFromIndex  et GetLetterFromNumber indissociables
'  ===============================================================
Function GetColumnHeaderFromIndex(ByVal Index As Integer) As String
    Dim iInt%, iRest%
    If Index > 256 Then
        GetColumnHeaderFromIndex = vbNullString
    ElseIf Index < 27 Then
        GetColumnHeaderFromIndex = GetLetterFromNumber(Index)
    Else
        iInt = Index \ 26: iRest = Index Mod 26
        If iRest = 0 Then iInt = iInt - 1: iRest = 26
        GetColumnHeaderFromIndex = GetLetterFromNumber(iInt) & GetLetterFromNumber(iRest)
    End If
End Function
Function GetLetterFromNumber(ByVal Number As Integer) As String
    If Number < 1 Or Number > 26 Then GetLetterFromNumber = vbNullString Else GetLetterFromNumber = Chr$(Number + 64)
End Function

Le souci est que je veux appliquer ma fonction SetColumnLetter sur plusieurs plage dans une même Sub.

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
Sub test()
 
 
Dim sTest As String
Dim SqlDir
 
SetColumnLetter ("RANGEMandant")
 
 
Range(sRangeName & ActiveCell.Row).Select
 
sTest = ActiveCell.Value
SetColumnLetter ("RANGEPF")
 
 
 
Do While ActiveCell.Offset(-1, 0).Range(sRangeName & "1").Value = sTest
    ActiveCell.Offset(-1, 0).Range(sRangeName & "1").Select
Loop
 
End Sub

Dans le code ci-dessus, sRangeName garde toujours la valeur B, même après avoir appeler la fonction SetColumnLetter une deuxième fois avec un autre argument.

2 questions:

1. Comment faire en sorte que la variable

   Code :

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

   Public sRangeName

   soit réinitialisée à chaque appel de la fonction SetColumnLetter
   
2. Y'a t'il un moyen plus facile de récupérer ces noms de plage? Dans le "Onload" du classeur? Si oui, comment?

[enernaej]

Y a effectivement un os ... quelle est la cellule active quand on lance la procédure ?
Je ne sais pas.
Ce qui donne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub test()
  Dim sTest As String
  Dim SqlDir
 
  SetColumnLetter ("RANGEMandant")
  Public Function SetColumnLetter(ByVal RangeName As String)
 
    sRangeColumnNumber = ActiveCell.Column
    sRangeName = GetColumnHeaderFromIndex(sRangeColumnNumber)
   End Function
...
End Sub

donc on récupère la colonne de la cellule active et non de la plage RANGEMandant.

Remarque : le paramètre RangeName de la fonction SetColumnLetter n'est pas utilisé ... ça doit interpeller.

[tcheulboulet]

merci, effectivement mon argument n'était pas lu. J'ai modifié ma fonction SetColumnLetter comme ceci:

Code :

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

1
2
3
4
Public Function SetColumnLetter(ByVal RangeName As String)
sRangeColumnNumber = Range(RangeName).Column
sRangeName = GetColumnHeaderFromIndex(sRangeColumnNumber)
End Function

et ça marche. Merci

[Oliv-]

merci, effectivement mon argument n'était pas lu. J'ai modifié ma fonction SetColumnLetter comme ceci:

Code :

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

1
2
3
4
Public Function SetColumnLetter(ByVal RangeName As String)
sRangeColumnNumber = Range(RangeName).Column
sRangeName = GetColumnHeaderFromIndex(sRangeColumnNumber)
End Function

et ça marche. Merci

Ta fonction est faussebien qu'elle puisse fonctionner avec la déclaration PUBLIC de sRangeName

tu devrais avoir :

Code :

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

1
2
3
4
5
Public Function SetColumnLetter(ByVal RangeName As String)
 
sRangeColumnNumber = Range(RangeName).Column
SetColumnLetter = GetColumnHeaderFromIndex(sRangeColumnNumber)
End Function

Code :

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

1
2
3
4
5
Sub test()
 
sRangeName = SetColumnLetter("RANGEMandant")
MsgBox sRangeName
End Sub

Il y a plus simple avec la conversion LETTRE NUMERO DE colonne par 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
Function ColLetNum(LetOuNo As Variant)
 
    If IsNumeric(LetOuNo) Then
        'MsgBox Split(Columns(LetOuNo).Address(ColumnAbsolute:=False), ":")(1)
        ColLetNum = Split(Columns(LetOuNo).EntireColumn.Address, ":$")(1)
    Else
        On Error Resume Next
        ColLetNum = Val(Mid(Columns(LetOuNo).Address(ReferenceStyle:=xlR1C1), 2))
    End If
End Function
 
Sub testColLetNum()
    MsgBox ColLetNum("B")
    MsgBox ColLetNum(1)
    MsgBox ColLetNum("XFD")
 MsgBox ColLetNum(Range("RANGEMandant").Column)
End Sub