wimgielis.com

Excel's kolommen

• 28/10/2011

	Voorbeeldbestanden bij dit artikel:
	Kolomnamen en -nummers

Inleiding

Hier een pagina over de kolommen van Excel. Het merendeel van deze pagina is door de gemiddelde Excel gebruiker wel geweten, maar er staan toch nog heel wat interessante weetjes en VBA-code. Voor ieder wat wils eigenlijk!

Hoeveel kolommen telt een Excel werkblad eigenlijk? Wel, het zijn er 256, dit is 2^8 (computers werken binair, dus 0 of 1, en dit zijn 2 mogelijkheden). Velen onder ons hebben reeds Excel 2007 (Excel 12) of Excel 2010 (Excel 13). Die nieuwste versies van Excel hebben veel meer kolommen: 16384 (2^14). Microsoft heeft hiermee eindelijk erkend dat 256 kolommen toch net iets te weinig is. Het liet bijvoorbeeld niet toe om 366 dagen naast mekaar in 1 rij te zetten.

In de namen van de kolommen zit een duidelijke structuur. Eerst A, B, C, ..., Z. Vervolgens een A met nog eens A, B, C, ..., Z eraan vastgeplakt. Dan een B met A, B, C, ..., Z eraan vastgeplakt, dan hetzelfde voor de C, enz. Het stopt dus met kolom IV (of kolom XFD vanaf 2007). Als je mij niet gelooft, ga dan maar eens in een leeg blad staan en druk Ctrl + →. De functie =KOLOM() in een cel zetten geeft het kolomnummer.

Excel opties

Excel laat toe om te kiezen wat je op je scherm krijgt. Via Extra > Opties > weergave kan je een aantal opties beheren m.b.t. de kolommen. Onder andere of Rij- en kolomkoppen getoond moeten worden. Bij Extra > Opties > Algemeen kan je i.p.v. kolomnamen (A, B, C, …, IV) ook kolomnummers tonen. Dat is de optie verwijzingstype R1K1. Een laatste instelling is het tonen van rijen en kolommen Van rechts naar links. Dat vind je op het tabblad Internationaal. Best wel raar als je dat eens uitprobeert!

Ook via de overige menu’s in Excel kan je acties uitvoeren op kolommen. Bekijk maar eens Opmaak > Kolom. Je kan ze verbergen of zichtbaar maken (selecteer ze eerst), of versmallen of verbreden. Invoegen > Kolommen laat je toe om kolommen in te voegen. Via Bewerken > Verwijderen kan je ze weer verwijderen. Allemaal redelijk logisch, niet? Al zullen de meesten (mezelf incluis) wel het contextmenu gebruiken dat je krijgt bij het klikken met de rechtermuisknop op een kolomtitel.

Stukjes VBA-code

Er is code die ik schreef om de kolomnamen (letters) om te zetten in het corresponderende getal. Dus A wordt een 1, B een 2, Z een 26, AA een 27, AB een 28, enz. Zie bovenaan in de downloads sectie.

Function ColNameToNumber(ByVal sColName As String) As Long
' Wim Gielis

' 06/18/2007

' VBA-code to convert a column name to a number

'    (not limited to 256 columns)

' Also on https://www.wimgielis.com

    Dim sLength As Integer
    Dim i As Integer
    Dim Numb As Long
    Dim sTempNumb As Integer
    Const iCnt As Byte = 26

    sColName = UCase(sColName)
    sLength = Len(sColName)

    For i = 2 To sLength
        Numb = Numb + iCnt ^ (i - 1)
    Next

    For i = 1 To sLength
        sTempNumb = Asc(UCase(Mid(sColName, i, 1))) - 64 - 1
        Numb = Numb + sTempNumb * iCnt ^ (sLength - i)
    Next

    ColNameToNumber = Numb + 1
End Function

Indien je minder dan een getal wil omzetten dat ten hoogste 256 is (of als je met Excel 2007 werkt), dan lukt dat met een veel simpeler trucje:

Function ColNameToNumberSimpel(ByVal sColName As String) As Long
    ColNameToNumberSimpel = Cells(1, sColName).Column
End Function

Het kan zelfs met de hand: klik op de letter A van kolom A, en sleep tot en met de kolom waarvan je het nummer wil kennen. Bekijk dan in de gele tooltip hoeveel kolommen je geselecteerd hebt. Dat is het nummer dat je zoekt!

Wat nu volgt, is de code voor het omgekeerde: een kolomnummer omzetten in de corresponderende kolomnaam. Dus 1 wordt een A, 2 een B, 26 een Z, 27 een AA, 28 een AB, enz. De code staat ook in bovengenoemd bestand.

Function ColNumberToName(ByVal lNumb As Long) As String
' Wim Gielis

' 06/18/2007

' VBA-code to convert a column number to a name

'    (not limited to 256 columns)

' Also on https://www.wimgielis.com

    Dim iMultiples As Byte
    Dim lRest As Long
    Dim str As String
    Dim i As Integer
    Dim iPower As Integer
    Dim getal As Long
    Const iCnt As Byte = 26

    Do
        iPower = iPower + 1
        getal = getal + iCnt ^ iPower
    Loop While getal < lNumb

    lRest = lNumb
    i = 1
    Do Until i = iPower + 1
        iMultiples = Int((lRest - (iCnt ^ (iPower - i - 1))) / (iCnt ^ (iPower - i)))

        lRest = lRest - iMultiples * (iCnt ^ (iPower - i))

        str = str & Chr(64 + iMultiples - (i = iPower))
        i = i + 1
    Loop

    ColNumberToName = str
End Function

Ook deze code kan gemakkelijker als je met weinig kolommen werkt of met Excel 2007 werkt:

Function ColNumberToNameSimpel(ByVal lNumb As Long) As String
    ColNumberToNameSimpel = Substitute(Cells(1, lNumb).Address(0, 0), "1", "")
End Function

Function ColNumberToNameSimpel_2(ByVal lNumb As Long) As String
    ColNumberToNameSimpel_2 = Split(Cells(1, lNumb).Address, "$")(1)
End Function

Elders op het web

Ik las op de schitterende Excel weblog van Dick Kusleika een pagina waar Dick aan zijn lezers vraagt om zulke code te schrijven. Zelfs notoire Excel MVP’s hadden er wat moeite mee! Ik heb Dick ondertussen mijn oplossing eens toegeschoven. Als jullie een betere oplossing hebben, mail me gerust.

• « Vorig Excel artikel
• Volgend Excel artikel »