1

I am wondering if, in VBA, it's possible to define and create a class object that is a specific instance of another, existing class object. For example, I would like to create a class called SpecialWorkbook which has properties specific to a unique workbook (class) in my directory. These properties would include Path, Name, FullName (Path & Name), Password, etc.

I know it would be possible to access all of the properties of the Application.Workbook class -- 

With Application.Workbooks(SpecialWorkbook.Name)

-- but that route may seem unintuitive to a user since the SpecialWorkbook class does not have the properties and methods of a standard Workbook class.

Would it be possible to do something like:

'In Class Module "SpecialWorkbook"

Private Sub Class_Initialize()

    Dim SpecialWorkbook as Workbook

    Set SpecialWorkbook = Application.Workbook("SpecialFileName.xlsx")

End Sub

And then use it like:

'In Standard Module

SpecialWorkbook.sheets.count

The above "for instance" obviously doesn't work but that's the general idea I would like to implement if possible.

Thanks in advance!

C.George
  • 49
  • 4
  • @c-george: you might want to take a look at [How to use Implements in VBA](https://stackoverflow.com/questions/19373081/how-to-use-the-implements-in-excel-vba/26604768) – Our Man in Bananas Aug 08 '18 at 15:48
  • @c-george: and here is the other one: [How to use comparison methods between class object modules in VBA in a similar manner as VB.NET?](https://stackoverflow.com/questions/19881863/how-to-use-comparison-methods-between-class-object-modules-in-vba-in-a-similar-m) – Our Man in Bananas Aug 08 '18 at 15:51
  • @c-george: and this might also be worthwhile reading: [VBA Class Modules – The Ultimate Guide](https://excelmacromastery.com/vba-class-modules/) – Our Man in Bananas Aug 08 '18 at 16:00
  • You are conflating *class* and *object*. An object is an *instance* of a class. A class is a *blueprint* for an object; its only purpose is to define the *shape*, i.e. the *interface* of the object. An object can only ever exists at run-time. – Mathieu Guindon Aug 08 '18 at 16:46

1 Answers1

0

What you need is static class.

For it to work, you need to make just one change. Say, you have this class called SpecialWorkbook:

Option Explicit

Private m_workbook_path As String

Private Sub Class_Initialize()
    m_workbook_path = "C:\Temp\static_class\mybook.xlsx"
End Sub

Public Property Get Path() As String
    Path = m_workbook_path
End Property

  1. You need to remove this class with exporting (Remove SpecialWorkbook -> Yes -> Select location).

  2. Open SpecialWorkbook.cls in any text editor and change Attribute VB_PredeclaredId = False to Attribute VB_PredeclaredId = True.

  3. Save changes and import this class module back.

Now you can use it this way:

Sub F()
    MsgBox SpecialWorkbook.Path
End Sub

Add other properties and methods you need.

JohnyL
  • 6,216
  • 2
  • 18
  • 37