2

I try to replicate the second answer from this question. Here is my code for ThisWorkBook:

Code

Option Explicit

Private Type TView
    UserInterFace As UIFrm
    Model as UIModel
End Type

Private this As TView

Friend Property Get UserInterFace() As UIFrm
    If this.UserInterFace Is Nothing Then
        Set this.UserInterFace = New UIFrm
    End If

    Set UserInterFace = this.UserInterFace
End Property

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not this.UserInterFace Is Nothing Then
        Unload this.UserInterFace
        Set this.UserInterFace = Nothing
    End If
End Sub

This should - according to the answer in the link above - make me able to do this in another module of the same project:

ThisWorkbook.UserInterface.Show

Context

I want to make a modeless Userform where the instance "lives" as long as the Workbook. I have heard that this could cause issues but nothing specific. I want to do this in order to preserve my UIModel.

UIModel is the Model Class UIFrm the Modeless UI Userform

Problem

I am not able to access the costum property using ThisWorkBook.UserInterFace and if I declare the Property Get as Public I will get a compilation error about: Private Properties not beeing able to be accessed publically.

Is this even possible to have a Property Get inside the ThisWorkbook-Object ?

braX
  • 11,506
  • 5
  • 20
  • 33
Lucas Raphael Pianegonda
  • 1,151
  • 12
  • 29
  • I tried with a simplified case (`Private m_Test As Double` `Friend Property Get Test() As Double: Test = m_Test: End Property`) in ThisWorkbook and got no problem accessing `ThisWorkbook.Test`. – Vincent G Oct 18 '18 at 13:07
  • @VincentG I am using excel 2010. Maybe that's the issue? – Lucas Raphael Pianegonda Oct 18 '18 at 13:26
  • @GSerg That was the issue! I am using the german version where the object is translated into german. The Translated object is the one with `Test` in it. You can put this as an answer. – Lucas Raphael Pianegonda Oct 18 '18 at 13:35
  • 1
    @GSerg I used to be wondering why `ThisWorkbook` appears twice in Intellisence, not anymore, thanks to you! – Vincent G Oct 18 '18 at 13:38

1 Answers1

2

ThisWorkbook may not be what you think it is.

There is Application.ThisWorkbook, which is a property of the Application object. It returns the workbook in which the code that calls this property resides.

There is also "just" ThisWorkbook, a code name that represents the Workbook class instance in which your code resides. The problem is that this ThisWorkbook, unlike the Application.ThisWorkbook, is subject to localization. It is not a part of an object model, it is an arbitrary name.

For the English version of Excel, the localized name happens to be ThisWorkbook too.
For non-English Excels, it's going to be something different.

Thus, due to scope precedence, ThisWorkbook resolves to "the code name" in the English Excel and to Application.ThisWorkbook in non-English Excels.

They are the same object, but the Friend properties are only accessible "truly internally", so when accessing the workbook via the Application.ThisWorkbook route, you cannot see them.

The fix is to rename the localized ThisWorkbook object back to ThisWorkbook in the project tree. Like sheet code names, this change is local to the file.

Using the localized name instead of ThisWorkbook in your code will also work.

GSerg
  • 76,472
  • 17
  • 159
  • 346