7

I have several named columns on a sheet. I want to send the column number to a function that will return the column name.

For example, if column 1 is named "apple", I want to pass the column number 1 to a function that returns the column name "apple". My attempt:

Function getColName(colNumber As Integer) As String
'return column name when passed column number

    getColName = Cells(1, colNumber).Column.Name

End Function

How can I get this code to work?

Community
  • 1
  • 1
jmaz
  • 507
  • 3
  • 8
  • 19
  • 1
    Try `getColName = Cells(1, colNumber).Value` – Fadi Feb 11 '16 at 21:59
  • 1
    Columns in excel do not have names. You can name a range, or as @Fadi is saying find the value of the first cell in which usually is a label that describes the values in the column. – Scott Craner Feb 11 '16 at 22:03
  • @Scott Craner, you misunderstand me. I have named the entire column 1 (or column A if you prefer) "apple" (or [apple] if you prefer). I want to send the column number (1 in this case) to a function that returns the name of the column. – jmaz Feb 11 '16 at 22:13
  • [Here](https://stackoverflow.com/questions/3630496/how-do-you-get-a-range-to-return-its-name) is the answer to my question. Sorry for repeating a previous question – jmaz Feb 11 '16 at 22:20
  • 3
    `getColName = Cells(1, colNumber).EntireColumn.Name.Name` – Tim Williams Feb 11 '16 at 22:21
  • @Tim Williams, thank you, sir. – jmaz Feb 11 '16 at 22:22
  • Does this answer your question? [How do you get a Range to return its Name?](https://stackoverflow.com/questions/3630496/how-do-you-get-a-range-to-return-its-name) – Sandra Rossi Feb 17 '21 at 18:48

5 Answers5

6

Try this one :

set cell_address = Worksheet("Sheet1").Cells(1,1)
MsgBox cell_address.address(RowAbsolute:=False, ColumnAbsolute=False)

'returns A1 You can use it in Range("A1") as well and get more functionality. 

**Note**: RowAbsolute:=False, ColumnAbsolute=False are optional

for more information:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel

for non-vba: https://exceljet.net/formula/convert-column-number-to-letter

Jobin
  • 5,610
  • 5
  • 38
  • 53
Kavyajeet Bora
  • 612
  • 1
  • 7
  • 15
  • That doesn't answer the question. Your answer corresponds to that [question](https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter). The current question is answered and solved [there](https://stackoverflow.com/questions/3630496/how-do-you-get-a-range-to-return-its-name). – Sandra Rossi Feb 17 '21 at 18:53
1
Sub GetColumnNameFromRange()
    Dim Colname As String
    For Each cell In Selection.Cells
        If cell.EntireColumn.Hidden = False Then
            Colname = Mid(cell.Address, 2, Len(cell.Address) - (Len(cell.Row) + 2))
            MsgBox Colname
        End If
    Next cell
End Sub
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Pankaj Shakya
  • 11
  • 1
  • Hi Pankaj! Thank you for contributing an answer to Stackoverflow. Kindly take note that you can improve upon the readability of your answer by inserting the code in a code-block. Kindly see: https://meta.stackoverflow.com/questions/251361/how-do-i-format-my-code-blocks – Kevin Johnson Sep 04 '21 at 12:13
1
ColumnNumber = i 'where i need to be a integer number > 0 and <= 16,384
ColumnName = cells(1,i).Address(False, False)

The parameters inside Adress() are related if the rows and columns references are going to be absolute or relatives. In this case above they'll be relatives.

And it's over. Simple like that.

Pedro Albert
  • 21
  • 3
0
Dim ColAdr as String, ColNo as Long, ColName as String
Function ColName (ColNo as Long)
ColAdr = ThisWorkbook.Sheets("Sheet1").Cells(1, ColNo).Address(RowAbsolute:=True, ColumnAbsolute:=False)
ColName = Left(ColAdr, InStr(1, ColAdr, "$") - 1)
End function
Bülent Çakar
  • 1
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the [help center](https://stackoverflow.com/help/how-to-answer). – Lizesh Shakya Oct 06 '21 at 07:55
-2

If you need the column letter(s) from the index, here's a function I've written for that:

Function colNameOf(ByVal colindex As Long) As String
    
    Dim overAlphabetic, charId As Long
    charId = colindex Mod 26
    colindex = colindex - charId
    overAlphabetic = colindex / 26
    
    If charId = 0 Then
        charId = 26
        overAlphabetic = overAlphabetic - 1
    End If
    
    Dim overAlphStr As String
    overAlphStr = ""
    
    If overAlphabetic > 0 Then
        overAlphStr = colNameOf(overAlphabetic)
    End If
    
    Dim lastChar
    lastChar = ChrW(charId + 64)
    
    colNameOf = overAlphStr & lastChar
    
End Function

Note, that there is yet no error handling for values smaller than 1. In that case the function just returns any nonsense. DIY...

PyeDraX
  • 11