Couldn't find this on the forum so forgive me if this is a duplicated post but here's my code I use to convert numbers to column letters.
Example:
ColumnNumberToLetters(0) = "A"
ColumnNumberToLetters(25) = "Z"
ColumnNumberToLetters(1023) = "AMJ" (Max columns in a spreadsheet)
Let me know if you find any bugs or have any questions.
Thanks,
BN
Code: Select all
Function ColumnNumberToLetters(ColumnNumber as integer) as string
Dim FirstLetter as string, SecondLetter as string
if ColumnNumber >= 0 then
if ColumnNumber < 26 then
ColumnNumberToLetters = Chr(65 + ColumnNumber)
elseif ColumnNumber <= 1023 then
If ColumnNumber >= 702 Then
ColumnNumber = ColumnNumber - 702
ColumnNumberToLetters = "A"
end if
FirstLetter = Chr(Int((ColumnNumber) /26)+65)
SecondLetter = Chr(((ColumnNumber) mod 26) + 65)
ColumnNumberToLetters = ColumnNumberToLetters & FirstLetter & SecondLetter
else
ColumnNumberToLetters="There's no column letter past 'AMJ' aka column number larger than '1023' "
end if
else
ColumnNumberToLetters="Please use a number greater than or equal to [0] "
end if
End Function
Code: Select all
Function ColumnNumberToLetters(ColumnNumber as integer) as string
Dim fa
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
ColumnNumberToLetters= fa.callFunction("ADDRESS", Array(1, ColumnNumber ,4))
ColumnNumberToLetters= Mid(ColumnNumberToLetters,1,Len(ColumnNumberToLetters)-1)
End Function