Advice Sought

[JAC]

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.

 

[JAC]

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 Williams]

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

Tim

 

[JAC]

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

Tim

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.

 

[Tim Williams]

I'm surprised this works without "Set". What is "RuleSheet" defined as in
your class?

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

You wouldn't expect to see your sheet codenames in Intellisense in VB6,
since they're not part of the Excel object model, but "custom" to the
particular workbook you're working with (and your VB6 code only knows about
standard members).

You could try creating a utliity function to access sheets by their
codename:

'*********************************************
Sub Tester()
Dim oWB As Excel.Workbook
Set oWB = Workbooks("Book1")
MsgBox SheetFromCodeName(oWB, "Sheet3").Name
End Sub

Function SheetFromCodeName(oWB As Excel.Workbook, sCodeName As String)

Dim s As Excel.Worksheet
Dim oSheet As Excel.Worksheet

Set oSheet = Nothing
For Each s In oWB.Worksheets
If s.CodeName = sCodeName Then
Set oSheet = s
Exit For
End If
Next s

Set SheetFromCodeName = oSheet

End Function
'********************************************



Tim