EXCEL VBA

 EXCEL 欄位數字換英文

Function GetColStr(nCol As Integer) As String

 

 GetColStr = ""

 If nCol > 26 Then

 GetColStr = Chr(65 + Fix(nCol / 26) - 1)

 End If

 

 GetColStr = GetColStr & Chr(65 + ((nCol - 1) Mod 26))

End Function

 

EXCEL 欄位數字轉英文

Function ConvertToLetter(iCol As Long) As String

 Dim a As Long

 Dim b As Long

 a = iCol

 ConvertToLetter = ""

 Do While iCol > 0

 a = Int((iCol - 1) / 26)

 b = (iCol - 1) Mod 26

 ConvertToLetter = Chr(b + 65) & ConvertToLetter

 iCol = a

 Loop

End Function

 

EXCEL 欄位英文換數字 (最多只能 3 個字母)

Function GetColNum(strCol As String) As Long

 Dim nCol As Long

 nCol = 0

 If VarType(strCol) = vbString And Len(strCol) > 0 Then

 strCol = UCase(strCol)

 nCol = Asc(Left(strCol, 1)) - Asc("A") + 1

 If Len(strCol) >= 2 Then

 nCol = nCol * 26 + Asc(Mid(strCol, 2, 1)) - Asc("A") + 1

 If Len(strCol) >= 3 Then

 nCol = nCol * 26 + Asc(Mid(strCol, 3, 1)) - Asc("A") + 1

 End If

 End If

 End If

 GetColNum = nCol

 

End Function