在Excel中使用VBA列数和列引用之间的转换

Option Explicit

Function ColNoToColRef(ColNo As Long) As String





If ColNo < 1 Or ColNo > Columns.Count Then



ColNoToColRef = "Invalid input value"



Else



'Finding the address of cell in the first row based on the specified column number

ColNoToColRef = Cells(1, ColNo).Address(True, False, xlA1)



'Extracting the column name from the address

ColNoToColRef = Left(ColNoToColRef, InStr(1, ColNoToColRef, "$") - 1)



End If



End Function

Function ColRefToColNo(ColRef As String)

Dim Rng As Range



On Error GoTo LastPart



'Assigning cell in the first row of the specified column reference as range

Set Rng = Range(ColRef & "1")



'Getting the column number of the specified range

ColRefToColNo = Rng.Column



Exit Function



LastPart:

ColRefToColNo = "Invalid input value"



End Function