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
  • 55
  • 5
  • @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

2 Answers2

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,894
  • 3
  • 22
  • 41
  • This is good info, and I see how it helps, but it's not the solution I was looking for. Thanks all the same! – C.George Feb 01 '23 at 04:51
0

It's not possible and it's not really needed. I only want to refer to one specific object, not multiple instances thereof. Therefore, I just need a wrapper:

Sub test()

    Debug.Print SpecialWorkbook.Name
    Debug.Print SpecialWorkbook.Sheets.Count

End Sub

Function SpecialWorkbook() As Workbook

    Set SpecialWorkbook = Workbooks.Open("C:\Temp\mybook.xlsx")
    
End Function

This assumes that "C:\Temp\mybook.xlsx" exists and is my SpecialWorkbook.

C.George
  • 55
  • 5