Class (Static) Methods in VBA

Question

I wonder, whether it is possible to create class-methods in VBA. By class-method I mean methods that can be called without having an object of the class. The 'static'-keyword does that trick in C++ and Java.

In the example below, I try to create a static factory method.

Example:

'Classmodule Person'
Option Explicit
Private m_name As String
Public Property Let name(name As String)
    m_name = name
End Property
Public Function sayHello() As String
    Debug.Print "Hi, I am " & m_name & "!"
End Function

'---How to make the following method static?---'
Public Function Create(name As String) As Person
    Dim p As New Person
    p.m_name = name
    Set Create = p
End Function

'Using Person'
Dim p As New Person
p.name = "Bob"
p.sayHello 'Works as expected'
Set p2 = Person.Create("Bob") 'Yields an error'

This doesn't cover the point of this question, which is class methods, but wouldn't most uses of a class method be covered by a method that returns a ref to another instance of the person class? e.g. In module you would have: Dim person As new Person: Set person = person.Create("Bob"). And the Create method in the class would be like: Public Function Create(name As String) As Person: Dim p As Person: Set p = New Person: p.name = name: Set create = p: End Function. I.e. once you have initialised a "handler" instance of the class, it can be used to return other instances of its own class. — Cor_Blimey, Apr 14 '12 at 21:48
@Cor_Blimey: The technique that you describe would surely work. However, the point of the question to get away without needing a "handler" instance. — Tom, May 27 '12 at 17:18

score 46 · Answer 1 · edited Mar 31 '12 at 11:05

1. Create a normal class containing the public method(s) you need to be 'static'

2. Include a public method [in this 'static' class] that initialises the [private] 'static fields' within the class (it can take parameters if you wish)

3. Create a module acts as a factory

Public Function CreateStaticClass(parameters for 'constructor') As StaticClass

    Dim static As StaticClass
    Set static = New StaticClass
    Call StaticClass.Constructor(pass in parameters)
    Set CreateStaticClass = static

End Function

4. you can now use the 'static' class by calling CreateStaticClass('parameters').MethodName('parameters') there is no need to initialise the instance as that is done by the factory method

5. (Optional) If you want to enforce singleton instances you can create a module that acts as a singleton container - include a private instance variable and a public accessor property. optionally you can use a 'let' setter to allow the singleton to be 'replaced' with a new [static] class (using different constructor parameters - see #2,3). Use 'Let' for the setter, so you can assign the singleton without using 'set' ala OO languages

Private curStaticClass as StaticClass

Public Property Get CurrentStaticClass() As StaticClass 

    If curStaticClass Is Nothing Then Set curStaticClass = CreateStaticClass

    Set CurrentStaticClass = curStaticClass  

End Property

Public Property Let CurrentStaticClass(value As StaticClass)

    If Not (curStaticClass Is Nothing) Then Set curStaticClass = Nothing

    Set curStaticClass = value 

End Property

6. To assign the singleton:

CurrentStaticClass = CreateStaticClass(parameters)

7. To use the singleton:

[value = ] CurrentStaticClass.MethodName(parameters)

i was looking for how to write a singleton in excel vba. Google brought me to your answer. Thank you. — Kim Stacks, Mar 31 '12 at 10:17
So If I just want singleton (initialization is not important), then I use 5) ? — przemo_li, Mar 20 '14 at 12:20
With your solution (if I understood well), I propose [here](https://stackoverflow.com/a/64088588/9150270) a basic example how to define a "static" counter of instances i.e. +1 at each `New`. — Sandra Rossi, Sep 27 '20 at 13:40

i_saw_drones · Answer 2 · 2017-07-01T16:27:27.360

You could try setting the VB_PredeclaredId attribute of the class you wish to be static to True. This creates a default instance of the class in much the same way that forms work in VBA (notice that you can refer to them directly without creating an instance. I know that this is not best practice but it is possible).

This means that you would have more of a singleton-style class, but it could serve your requirements...

You can't set this directly from the VBA IDE itself, however, you can perform the following steps:

1. Export the class you wish to make static to a folder.

2. Open the .cls file you exported in your favourite text editor and change the entry for VB_PredeclaredId so that it reads VB_PredeclaredId = True.

3. Save the file and re-import into VBA.

You should then be able to call your public methods on the class without having to instantiate the class. Bear in mind that the Initialize method is only called the first time you execute a class method/access a class property, and Terminate method is never called. Therefore you may wish to write your own constructor and also ensure you explicitly call the destructor if you need one.

Reference: UtterAccess.com Singleton Example

Reference: http://msdn.microsoft.com/en-us/library/ee199159.aspx

Excellent solution and thank you for the precise VBA spec reference. This deserves to be top answer and I think is only behind because it comes 4 years after (currently) top answer. I recommend. — S Meaden, Jun 30 '17 at 19:34
This is the correct way to achieve 'static' class behaviour in VBA — MacroMarc, Jul 01 '17 at 17:40

score 29 · Accepted Answer · edited Dec 07 '11 at 01:50

29

That ("Public Shared") would only work in VB.Net.

There is no way to define Class Methods in VBA (or VB). I'd suggest to create a public function in a module.

edited Dec 07 '11 at 01:50

John Saunders

160,644
26
247
397

answered Dec 28 '08 at 12:36

4

[There is a way but it's cheating](http://stackoverflow.com/a/25015767/2140173) ;) – Jul 30 '14 at 07:07
it's possible - see answer from *i_saw_drones*: https://stackoverflow.com/a/24574675/1915920 – Andreas Covidiot Mar 14 '19 at 10:43

score 9 · Answer 4 · answered Aug 27 '09 at 05:15

Bit late in the day but what the heck

There are no class or static methods in VB6/VBA. But you can explicity state the name of a module. You can't have a module and a class of the same name but you could call it something similar.

So I could have a class called Employee and a module called EmployeeUtil and then I can write:

  Dim emp As Employee
  Dim code As String
  Set emp = EmployeeUtil.Create( "Smith", "John", 21-Feb-1988)
  code = "123XY"
  If EmployeeUtil.IsCodeValid( code) Then
    emp.Code = code
  Else
    emp.Code = EmployeeUtil.DefaultCode
  EndIf

Yes, the values are hard coded and the code handling should probably be under the property setter but that is not the point I'm trying to make. EmployeeUtil is essentially being a place holder for non-instance members.

You'll note that the Create method this way gives us a pseudo like constructor for the Employee class. All that function does is create an instance of Employee, assign the parameters via the property setters and then returns the instance. If your constructing instances of objects in a lot of places, then this can save a lot of code.

score 4 · Answer 5 · answered Dec 28 '08 at 16:29

4

AFAIK, the closest you can get (and it's not that close) is to use an "anonymous" instance, so something like this:

With New NotReallyStaticClass
    .PerformNotReallyStatic Method, OnSome, Values
End With

answered Dec 28 '08 at 16:29

Mike Woodhouse

51,832
12
88
127

score 2 · Answer 6 · answered Sep 08 '11 at 03:59

While this is not strictly an answer to the question itself, I'd like to point out that Mike Woodhouse's solution should be avoided. Every time creating a new instance of an object is a performance hit and it really doesn't solve the original problem - it doesn't create a static object and it doesn't provide static methods either.

Since VBA has no concept of class functions, the closest one can get is using functions in modules.

As for factory methods, I suggest creating a module with the word Factory appended to the name of the class the module is creating. Something like:

'Module PersonFactory
Option Explicit

Public Function Create(ByVal sName As String) As Person

    'Code here

End Function

This is far from the static method concept of other languages but at least it provides a pattern that can be used in a project.

fuat aygan · Answer 7 · 2011-12-07T19:19:35.730

Instancing property of a similar class is available for use in static classes. Instancing property for it 'GlobalMultUse' must specify.

Static class example:

' Error Class in ClassInstancing ActiveDLL project
Option Explicit

Private m_errorID As Integer
Private m_Description As String

Public Property Get ErrorID() As Integer
ErrorID = m_errorID
End Property

Public Property Let ErrorID(ByVal vNewValue As Integer)
m_errorID = vNewValue
End Property

Public Property Get Description() As string
    Description = m_Description
End Property

Public Property Let Description(ByVal vNewValue As string)
    m_Description = vNewValue
End Property

Public Function Error() As Error
    Dim errorInstance As New ClassInstancing.Error

    With errorInstance
        .ErrorID = Me.ErrorID
        .Description = Me.Description
    End With

    Set Error = errorInstance
End Function

Public Sub RaiseError(ByVal pErrorID As Integer, ByVal errorSource As String, ByVal errorDesc As String)
Err.Raise pErrorID, errorSource, errorDesc
End Sub

Public Sub ShowError()
   MsgBox "Error ID: " & CStr(Me.ErrorID) & vbCrLf & _
    "Desc: " & Me.Description
End Sub

GlobalMultiUse Instancing property to specify the class as a set of...

Sample usage this global (static!) class in other standart EXE project:

Private Sub Command1_Click()

    ClassInstancing.Description = "Sample-1 error using !"
    ClassInstancing.ErrorID = 9990

    'Dim multiuseClass As ClassInstancing.Error
    'Set multiuseClass = ClassInstancing.Error

    MsgBox ClassInstancing.Error.ErrorID & vbCrLf & ClassInstancing.Error.Description, vbInformation, "Sample Usage 1"

    ClassInstancing.Description = "Sample-2 error using !"
    ClassInstancing.ErrorID = 1110

    ClassInstancing.ShowError
End Sub

Finally, notes in MSDN ((MSDN Library Visual Studio 6.0, 'Instancing Property')):

GlobalMultiUse. Similar to MultiUse, with one addition: properties and methods of the class can be invoked as if they were simply global functions. It’s not necessary to explicitly create an instance of the class first, because one will automatically be created.

This is only available in VB6 and not in VBA. – i_saw_drones Jul 03 '14 at 10:48 — i_saw_drones, Jul 03 '14 at 10:48

score -3 · Answer 8 · answered Feb 19 '09 at 12:12

you have to declare p2 before you can use the Set as follows:

dim p2 as Person

Once you do this, you have to replace the Set statement using a standard assignment: p2 = Person.Create("Bob")

In the Function: remove the "Set" key word...this could also be the source of an error.

I'm flying blind, but logically it seems like this should work. I am new to using Class modules in VBA but they aren't too different from using VB.Net properties.

Class (Static) Methods in VBA

8 Answers8

Linked