VBA range variables declaration

When using the VBA Formula method, I like to get all the info at first glance. Is really annoying for me to find .cells(lR, #) where the # does not give any hint about the column meaning. I better prefer the .cells(lR, lC_worksheetName_infoColumn) notation. For this, you need the lC_worksheetName_infoColumn to be declared before use, and having so much of this variables is a burden I would prefer to not do manually. Here it comes handy a little macro that can generate the declaration block, via “Const” or via “Dim”. There is also some code for indention purposes.

Private Function fVariableNamer()
    Dim bIndent As Boolean: bIndent = False
    Dim bConst As Boolean: bConst = False
    
    Dim oXlCell As Excel.Range
    Dim strWshName As String
    Dim strVar As String
    Dim strText As String
    Dim strOut As String
    Dim strChr As String
    Dim strPrev As String
    Dim iChr As Integer
    Dim lgChr As Long
    
    strWshName = Selection.Parent.Name
    For Each oXlCell In Selection.Cells
        strText = oXlCell.Value2
        strOut = vbNullString
        
        ' Avoid spaces
        strText = VBA.Trim$(strText)
        Do While VBA.InStr(1, strText, "  ")
            strText = VBA.Replace(strText, "  ", " ")
        Loop
        
        ' For ending character
        lgChr = VBA.Len(strText)
        strChr = VBA.Mid$(strText, lgChr, 1)    'iChr = VBA.Asc()
        strPrev = VBA.Mid$(strText, lgChr - 1, 1)
        If strPrev = "." Or strPrev = "-" Then
            strOut = VBA.UCase$(strChr)
        Else
            strOut = strChr
        End If
        
        ' For other characters
        For lgChr = VBA.Len(strText) - 1 To 2 Step -1 ' from back to front
            strChr = VBA.Mid$(strText, lgChr, 1) 'iChr = VBA.Asc()
            strPrev = VBA.Mid$(strText, lgChr - 1, 1)
            If strChr = "." Or strChr = "-" Or strChr = " " Then
            Else
                If strPrev = "." Then
                    strOut = VBA.UCase$(strChr) & strOut
                    lgChr = lgChr - 1
                ElseIf strPrev = "-" Then
                    strOut = VBA.UCase$(strChr) & strOut
                    lgChr = lgChr - 1
                ElseIf strPrev = " " Then
                    strOut = VBA.UCase$(strChr) & strOut
                    lgChr = lgChr - 1
                Else
                    strOut = strChr & strOut
                End If
            End If
        Next lgChr
        
        ' For starting character
        strChr = VBA.Mid$(strText, 1, 1)  'iChr = VBA.Asc()
        If strChr = "." Or strChr = "-" Then
        Else
            strOut = VBA.UCase$(strChr) & strOut
        End If
        
        strVar = "lC_" & strWshName & "_" & strOut
        If bIndent Then
            strIndent = VBA.Space(25 - VBA.Len(strVar))
        End If
        
        If bConst Then
            Debug.Print "Private Const lC_" & strWshName & "_" & strOut & strIndent & " As Long = " & oXlCell.Column
        Else
            Debug.Print "Dim " & strVar & " As Long:" & strIndent & strVar & " = " & oXlCell.Column
        End If
    Next oXlCell
End Function

Enjoy it!