Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

How do I reference class modules without adding them to a workbook in Excel/VBA?

Is it possible to reference class modules in without adding them to a workbook in Excel/VBA?

I know that I can reference class modules in a workbook or addin, but when doing this, they are "Public Not Createable", which means that I need to add a function in the workbook/addin that I reference, that creates an instance of the object, e.g.:

Public Sub NewInstanceOfSomeObject() as SomeObject
   Set NewInstanceOfSomeObject = New SomeObject
End Sub

Open in new window


Can I in some way reference class modules, so that they can be used as if they where part of the workbook?

COM/VSTO etc. is unfortunately not an option due to extremely bureaucratic and strict security policy, which make it take waaay too much time to order an dll registration for each new user that uses the sheet. Unless there is a not too complex way to use it without registration?
0
andreas_rafn
Asked:
andreas_rafn
  • 2
  • 2
  • 2
  • +2
1 Solution
 
NorieCommented:
Why not have the function for creating a new instance in the workbook/add-in with the class module?
0
 
Martin LissRetired ProgrammerCommented:
If Class1 is a class in a workbook then you can simply do this.

Dim C As New Class1

Set C = New Class1
0
 
andreas_rafnAuthor Commented:
imnorie: seems clumsy that I have to create an instancing  function for each class in the module. I was hoping that there would be a way to access and use the class modules, similar to that of COM dlls, where classes behave as if they were defined in the workbook.

MartinLiss: this is not possible in the context i described as the classes can only be either private (hidden from the workbook that references) or public not creatable (visible, but not possible to create a new instance).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
I'm probably then misunderstanding the question or the situation, but try this.
Book1.xlsm
0
 
NorieCommented:
andreas

Not sure about clumsy, it's one line of code and you would only need to add it to the workbook/addin with the class.
0
 
aikimarkCommented:
A class module is a package of VB source code that constitutes a class object.  You can add modules to a VB project programmatically, but trying to access a class without source code seems impossible by its very description.  

I think imnorie has the right idea by packaging the objects within an add-in.
0
 
Rory ArchibaldCommented:
You need to change the instancing which you can only do in code:


activeworkbook.VBProject.VBComponents("SomeClass").Properties("Instancing") = 5

Open in new window


then once you have a reference to that project set you can both declare and instance objects as the class in question.
0
 
aikimarkCommented:
@rorya

Does that work if there isn't a class module in the project?
0
 
Rory ArchibaldCommented:
You lost me - you can't change the instancing of a class that doesn't exist, no. ;)
0
 
andreas_rafnAuthor Commented:
Exactly what I was looking for, works like a charm. Added the code to the open event of the referenced workbook to make sure it always work, and again it works like a charm.

Its a mystery while this way of instancing (public) was not added to the "instancing" property selector in the "Properties" of a class module.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now