Convert Excel Address to Column & Row number in VBA

Today I was working on some Excel code for a CRM system.

My end goal was to automate the process of creating a drop down list using Data Validation.

To  do this I needed to convert an Excel Address to a column number and row number. This was harder than expected.

There is a lot of examples on the Internet of how to convert an Excel column letter to a column number, but this assumes that the Excel Address has already been parsed into a column letter and row number.

The solution I came up with is compact and elegant, but a bit obtuse.

It relies on two string functions gfHead and gfTail to parse the Excel Address.

gfHead and gfTail are string functions. gfHead returns the first element (or Head) of a delimited string. gfTail returns the Tail of the delimited string. E.g.

gfHead("This is a test", " ") returns "This"

gfTail("1,2,3,4) returns "2,3,4"

gf stands for Global Function and is a programming convention I use.

Here is the code:

Public Function gfGetColumnFromAddress(rng As Range) As Integer
   gfGetColumnFromAddress = Range(gfHead(gfTail(rng.Address, "$"), "$") & "1").Column
End Function



Public Function gfGetRowFromAddress(rng As Range) As Integer
   gfGetRowFromAddress = CInt(gfTail(gfTail(rng.Address, "$"), "$"))
End Function



Function gfHead(ByVal sString As String, Optional sDelim) As String

   Dim iPosition As Integer
   
   ' check for delimiter, if missing
   ' set delimiter to comma
   If IsMissing(sDelim) Then
      sDelim = ","
   End If
   
   ' make sure that string has trailing delimiter
   If Right(sString, Len(sDelim)) <> sDelim Then
      sString = sString & sDelim
   End If
   
   iPosition = InStr(sString, sDelim) - 1
   
   If iPosition < 1 Then
      gfHead = ""
   Else
      gfHead = Left$(sString, iPosition)
   End If
   
End Function



Function gfTail(ByVal sString As String, Optional sDelim) As String

   Dim iPosition As Integer
   
   ' check for delimiter, if missing
   ' set delimiter to comma
   If IsMissing(sDelim) Then
      sDelim = ","
   End If
      
   iPosition = InStr(sString, sDelim)
   
   If iPosition < 1 Then
      gfTail = ""
   Else
      gfTail = Mid$(sString, iPosition + Len(sDelim))
   End If
   
End Function