Recently I have been splitting up a large Excel workbook into a small
Excel workbook and compiled DLL Add-in.

I tend to give code-names (e.g. shtControl) to my worksheets. e.g.
Sheets("Control Panel") so that the software will continue to work
satisfactorily should the user change the name. e.g. "Control Panel"
to "My Controls" for instance.

I have had considerable success with implementing my DLL. However, I
have noticed that it will not work if I refer to the sheets by their
code-names. I have to use the visible name.

Does anyone have any experience of this? Is there a work-around? There
are some visible sheets that I do not wish the user to rename. Does
any one have ideas about the preserving the sanctity of a sheet?

Many thanks.

On 18 Sep, 11:50, JAC wrote:
Recently I have been splitting up a large Excel workbook into a small
Excel workbook and compiled DLL Add-in.

I tend to give code-names (e.g. shtControl) to my worksheets. e.g.
Sheets("Control Panel") so that the software will continue to work
satisfactorily should the user change the name. e.g. "Control Panel"
to "My Controls" for instance.

I have had considerable success with implementing my DLL. However, I
have noticed that it will not work if I refer to the sheets by their
code-names. I have to use the visible name.

Does anyone have any experience of this? Is there a work-around? There
are some visible sheets that I do not wish the user to rename. Does
any one have ideas about the preserving the sanctity of a sheet?

Many thanks.

I have thought of writing a function that looks through the Sheets in
the workbook using the code-name to identify its index in the Sheets
collection, and then use the index to refer to the correct sheet.

This seems messy and indirect to me. If anyone can think of a better,
cleaner solution or a different approach entirely, then I should be
most grateful to hear of it.

What language for the DLL and can you show some of your non-working code ?

Tim

"JAC" wrote in message
...
On 18 Sep, 11:50, JAC wrote:
Recently I have been splitting up a large Excel workbook into a small
Excel workbook and compiled DLL Add-in.

I tend to give code-names (e.g. shtControl) to my worksheets. e.g.
Sheets("Control Panel") so that the software will continue to work
satisfactorily should the user change the name. e.g. "Control Panel"
to "My Controls" for instance.

I have had considerable success with implementing my DLL. However, I
have noticed that it will not work if I refer to the sheets by their
code-names. I have to use the visible name.

Does anyone have any experience of this? Is there a work-around? There
are some visible sheets that I do not wish the user to rename. Does
any one have ideas about the preserving the sanctity of a sheet?

Many thanks.

I have thought of writing a function that looks through the Sheets in
the workbook using the code-name to identify its index in the Sheets
collection, and then use the index to refer to the correct sheet.

This seems messy and indirect to me. If anyone can think of a better,
cleaner solution or a different approach entirely, then I should be
most grateful to hear of it.

On 19 Sep, 03:48, "Tim Williams" <timjwilliams at gmail dot com
wrote:
What language for the DLL and can you show some of your non-working code ?

Tim

"JAC" wrote in message

...

On 18 Sep, 11:50, JAC wrote:
Recently I have been splitting up a large Excel workbook into a small
Excel workbook and compiled DLL Add-in.

I tend to give code-names (e.g. shtControl) to my worksheets. e.g.
Sheets("Control Panel") so that the software will continue to work
satisfactorily should the user change the name. e.g. "Control Panel"
to "My Controls" for instance.

I have had considerable success with implementing my DLL. However, I
have noticed that it will not work if I refer to the sheets by their
code-names. I have to use the visible name.

Does anyone have any experience of this? Is there a work-around? There
are some visible sheets that I do not wish the user to rename. Does
any one have ideas about the preserving the sanctity of a sheet?

Many thanks.

I have thought of writing a function that looks through the Sheets in
the workbook using the code-name to identify its index in the Sheets
collection, and then use the index to refer to the correct sheet.

This seems messy and indirect to me. If anyone can think of a better,
cleaner solution or a different approach entirely, then I should be
most grateful to hear of it.

Tim,

The ActiveX DLL has been produced in VB6, taking working code from
Excel VBA with modification.

Here are extracts from one of the class modules. The statements that
are commented out used to work; now they cause an error in the DLL
which forces an error return to the calling Excel VBA.

Private Function IsPlan(ByVal ws As Worksheet) As Boolean

' Does the worksheet relate to a Plan?

With XLWorkbook
' IsPlan = Not (ws Is .shtAdverse Or ws Is .shtLender Or ws
Is .shtExample)
IsPlan = Not (ws.Name = "Adverse" Or ws.Name = "Lender" Or
ws.Name = "Example")
End With
End Function

Here, I would rather use the codenames (shtAdverse, shtLender and
shtExample), than the tab names "Adverse", "Lender" and "Example".

XLWorkbook (used successfully elsewhere in the class module) is
defined as:

Private m_XLWorkbook As Excel.Workbook

Public Property Get XLWorkbook() As Excel.Workbook
Set XLWorkbook = m_XLWorkbook
End Property

Also, there are similar problems with :

Private Sub StandardSheets()
Dim objSheet As clsRuleSheet

' Lender and Adverse are standard sheets

Set objSheet = New clsRuleSheet
If objSheet Is Nothing Then Exit Sub

XLWorkbook.Application.ScreenUpdating = False

With objSheet
'.RuleSheet = XLWorkbook.shtLender ' fails
.RuleSheet = XLWorkbook.Sheets("Lender") ' succeeds
.Header

' .RuleSheet = XLWorkbook.shtadverse ' fails
.RuleSheet = XLWorkbook.Sheets("Adverse") ' succeeds
.Header
End With

Set objSheet = Nothing
XLWorkbook.Application.ScreenUpdating = True
End Sub

I cannot use the codename in any form anywhere in the DLL and get it
to work. Furthermore, the VBE editor does not include the codename as
one of the methods/properties when I type the dot after the object
instance. This implies that the system doesn't recognise the codename.

I would appreciate any advice or workaround.