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
"JAC" <(E-Mail Removed)> wrote in message
news:bc912759-dc65-4f55-89f5-(E-Mail Removed)...
> 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" <johnaco...@gmail.com> wrote in message
>>
>> news:0851b673-625c-460e-8af5-(E-Mail Removed)...
>>
>> > On 18 Sep, 11:50, JAC <johnaco...@gmail.com> 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.
>
|
Similar Threads
