Link to home
Start Free TrialLog in
Avatar of mcs26
mcs26

asked on

VBA Class only let it be initialised once

Hi,

I have a class that I use in Excel. I only want this class to be initialised once is that possible? I know in C# you can use abstract - guessing there isn't something similar for VBA?

Thanks for any help,
M
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you show us the code that initializes the class and how that code is called?
Avatar of mcs26
mcs26

ASKER

Hi MatinLiss,

Thanks for the reply. I don't actually have any code yet just trying to plan how to best design it. I was just going to use myclass = new clsIMade.

Thanks,
M
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Generally, you would have an Initialization() routine for your class that only runs once and should be private.  How else would any initialization code run more than once?
VBA can create classes just the same as any other language that supports Object Oriented programming.

To create a class in VBA, you will select Insert / Class Module from the VBA menu of your project. Lets create a class called Hand.
Create a constructor and Destructor for the class, if the wizard did not already create it for you.
The Constructor must be named Class_Initialize and the Destructor must be named Class_Terminate. They are called automatically when a Class object is instantiated and destroyed, respectively.
eg
Private Sub Class_Initialize()
  ; place your initialisation code here
End Sub

Private Sub Class_Terminate()
  ; place any required cleanup code here
End Sub

You can give the class a method:
Public Sub Point( ByVal Direction as Integer )
   ; do pointy stuff here
End Function

In your main program you create an instance of this class thus:
Dim Finger as New Hand

; then to use it
Finger.Point( 10 );

The Constructor is only called once when the object is first instantiated, so your initialisation code is guaranteed to be executed only once FOR THAT OBJECT.
Avatar of mcs26

ASKER

Hi,

Thanks for the replies. I should I appreciate the intialise code will only run once. What I wanted to stop was having multiple objects of a class. So basically a static class would be an example in C#. I think MartinLiss has probably the simplest way of making sure that I do not have multiple objects.

Thanks,
Mark
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012