none
How do I create a shared class, and a shared method. RRS feed

  • Question

  • Can anyone tell me how to create a shared class, but more importantly, how to create a shared method in a class I create in VBA?

    In VB.NET and C#.NET I think it is:
    Public Shared MyMethod()
    and
    public static void MyMethod()

    Is this possible in VBA? I have some functions I would like to call without having to instantiate the class over and over again.
    Thanks!
    Monday, September 14, 2015 3:51 PM

Answers

  • I'm sorry, I did misunderstand you.  The answer to your question is no.  As far as I know, if you want to create a public function in VBA that can be used throughout the system, you must do that in a standard/basic module.  You can then use that same function in your class module and/or wherever else you choose.

    To call upon a procedure in your class module, you must instantiate it first.



    Monday, September 14, 2015 5:06 PM

All replies

  • To create a class module, on the Ribbon, select Create>Class Module.

    To add a public procedure, simply define your procedure as Public, else Private.

    Public Function MyProcedure() As Boolean

    End Function

    or

    Public Sub MySubProcedure()

    End Sub


    Monday, September 14, 2015 4:33 PM
  • I really don't think you understand my question. A "Shared" and "Static" Method/Class is different then a "Public" and "Private" Method/Class. Shared is the syntax used in Visual Basic. Static used in C Sharp. This attribute gives a class the ability to be called upon without having to create an instance of the class itself. A Shared/Static method is one that is shared amongst the every single instance of that particular class throughout the entire application.

    Monday, September 14, 2015 4:44 PM
  • I'm sorry, I did misunderstand you.  The answer to your question is no.  As far as I know, if you want to create a public function in VBA that can be used throughout the system, you must do that in a standard/basic module.  You can then use that same function in your class module and/or wherever else you choose.

    To call upon a procedure in your class module, you must instantiate it first.



    Monday, September 14, 2015 5:06 PM
  • I believe that in VBA you can use the Static keyword when declaring variables, subroutines or functions. Static variables retain their values as long as the code is running. Variables within Static procedures retain their values between invocations.  (Whether there is just one or more physical instances of the code in memory is hard to know since the VBA runtime is a bit of a black box, but perhaps someone with more knowledge can say.)

    Monday, September 14, 2015 5:10 PM
  • I believe that in VBA you can use the Static keyword when declaring variables, subroutines or functions. Static variables retain their values as long as the code is running. Variables within Static procedures retain their values between invocations.  (Whether there is just one or more physical instances of the code in memory is hard to know since the VBA runtime is a bit of a black box, but perhaps someone with more knowledge can say.)


    Using Static in VBA is different than what the OP is talking about.  When a function or sub is declared using Static, the storage space for all local variables is allocated only once and the variables will retain their values as long as the program is running.
    Monday, September 14, 2015 5:21 PM
  • I think what you want it to be able to create a class where you don't need to instantiate a new object. Like a VB.NET Shared class.

    You can't do that in VBA. You need to instantiate a new class object. Remember that VBA is basically an offshoot of VB6.

    What you can do is create a standard module and use it's properties and methods without needing to instantiate an object.

    Monday, September 14, 2015 5:48 PM
  • I think what you want it to be able to create a class where you don't need to instantiate a new object. Like a VB.NET Shared class.

    You can't do that in VBA. You need to instantiate a new class object. Remember that VBA is basically an offshoot of VB6.

    What you can do is create a standard module and use it's properties and methods without needing to instantiate an object.


    Thanks for confirming exactly what I already told the OP...
    Monday, September 14, 2015 5:57 PM
  • Yes, I know. I think I keyed off of his use of the term 'Static' and was trying to clarify that Static does something different in VBA...


    • Edited by Gary Voth Monday, September 14, 2015 6:10 PM
    Monday, September 14, 2015 6:04 PM
  • Great, well thats dissapointing.  Thanks for all the help. What does OP stand for??
    Tuesday, September 15, 2015 3:59 PM
  • Great, well thats dissapointing.  Thanks for all the help. What does OP stand for??

    You're very welcome. 

    I believe OP stands for original poster, but don't quote me. :)

    Tuesday, September 15, 2015 4:34 PM