Advertisement
codecaine

Convert Excel Column Number to Column Letter using Regex

May 11th, 2018
232
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Function ColumnNumToColumnLetter(ColumnNum As Long) As String
  2. 'returns an excel column letter from a column number
  3. 'if the column letter cannot be determine returns vbNullString
  4.    Dim regex As Object
  5.     Dim matches As Object
  6.     Dim addr As String
  7.     Set regex = CreateObject("VBScript.RegExp")
  8.     regex.Pattern = "[A-Z]+"
  9.     addr = Cells(1, ColumnNum).Address(False, False)
  10.     If regex.test(addr) Then
  11.         Set matches = regex.Execute(addr)
  12.         ColumnNumToColumnLetter = matches(0)
  13.     Else
  14.         ColumnNumToColumnLetter = vbNullString
  15.     End If
  16.     Set regex = Nothing
  17.     Set matches = Nothing
  18. End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement