Referencing code names in macro code

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Erik

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

Example formula - =RefByCodeName("CodeName1!F1").
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

Active Member
Joined
Nov 26, 2003
Messages
440
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you could somehow inverse your question: replace the codenames by the sheetnames :)

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
enhanced code
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
 

Forum statistics

Threads
1,137,298
Messages
5,680,678
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top