Referencing code names in macro code

[sbendbuckeye]

Hello all,

Let's say I go into the Visual Basic editor and change the codename from Sheet1 to CodeName1. Can I then use the codename in cell formulas (eg =Sheet1!A1 - is there a corresponding way to do that with codename).

Thanks in advance for any ideas and/or suggestions!

 

[Norie]

The only way I can think of being able to use a sheet's codename on a worksheet would be to use a UDF, and I'm not 100% sure what the code for that would be.

 

[erik.van.geit]

Hi,

I tried to create some User Defined Function for this, but didn't succeed yet. (don't know if I will) What is the purpose of this ? In fact I cannot see any useful need for this. Perhaps there is another way to go.

kind regards,
Erik

 

[Norie]

Erik

I wrote this function, but like you I'm unsure of the need for it.

Example formula - =RefByCodeName("CodeName1!F1").

Function RefByCodeName(ref As String) As Variant
Dim ws As Worksheet
Dim strRange As String
Dim strCodeName As String
Dim pos As Long

    pos = InStr(ref, "!")
    
    If pos <> 0 Then
        strRange = Mid(ref, pos + 1)
        strCodeName = UCase(Left(ref, pos - 1))
        For Each ws In Worksheets
            If UCase(ws.CodeName) = strCodeName Then
                RefByCodeName = ws.Range(strRange)
                Exit Function
            End If
        Next ws
    End If
    
    RefByCodeName = "Error"
End Function

 

[erik.van.geit]

major drawback about your request
the formulas are not updated when changing the codename !!!

my first experiments
ActiveWorkbook.VBProject.VBComponents(shName).Properties("_CodeName") .....
leading to nothing

the loop Norie uses is perhaps the only way to know what sheet is refered to

still curious about the purpose

 

[sbendbuckeye]

Thanks for the feedback. We have some engineering calculation Excel files with extensive VBA macro code which is called by automation. This code references various named ranges as well as cell values.

My main question pertained to consistency between cell formulas and macro code, but after reading your comments and thinking about it some more it doesn't seem to be worth the hassle since the cell formulas and macro code are independent of one another.

 

[erik.van.geit]

you could somehow inverse your question: replace the codenames by the sheetnames

Sub sheetcodename_is_sheetname()
'Erik Van Geit
'060907
Dim sh As Worksheet

On Error Resume Next
    For Each sh In Worksheets
    ActiveWorkbook.VBProject.VBComponents(sh.CodeName).Properties( _
    "_CodeName").Value = Application.Substitute(sh.Name, " ", "_")
    Next sh

End Sub

 

[erik.van.geit]

enhanced code

Option Explicit

Sub sheetcodename_is_sheetname()
'Erik Van Geit
'060907
Dim sh As Worksheet
Dim msg As String

msg = "This code will replace all codenames of your sheets by the tabnames"
If MsgBox(msg, 36, "Replace codenames") = vbNo Then Exit Sub

msg = ""
On Error Resume Next

    For Each sh In Worksheets
    ActiveWorkbook.VBProject.VBComponents(sh.CodeName).Properties( _
    "_CodeName").Value = Application.Substitute(sh.Name, " ", "_")
        If Err Then
        Err.Clear
        msg = msg & vbLf & sh.Name
        End If
    Next sh
    
    If msg <> "" Then
    msg = "Some codenames could not be changed" & msg
    Workbooks.Add
    Range("A1") = msg
    MsgBox msg, 48, "ERROR REPORT"
    End If

End Sub