EXCEL VBA－伊卡洛斯之翼

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