CODEWITHSUNDEEP
excelvba
Goff
Goff

Reputation: 343

Can a VBA module be made to be separate instances

I have spent quite a bit of time work ing on a project under the assumstion that each sheet in an excell book would use an instance of the module I was writing. I have multiple sheets that are all the same template but the sheets are specific to the type of data being stored. In the module I have setters and getters however, I was expecting the getters on a given sheet to only access the varibles set by the setters on that same sheet (an instance of the module). As it turns out all of the sheets are using the same instance of the module and the setters are over-riding each other, so that only one sheet has acurate results calculated by the module. I don't want to get into TMI but the setters provide data that is used to format the raw data provided by the getters. Is there a way to make each sheet have an instance of the module with out having to code each and every sheet. What I mean by that is, when the operator has new set of relults to be stored and processed they just copy the sheet in excell and put the data on that new sheet. I don't want the operator to need to modify the code. I have tried to explain this but I am not sure I did a good job explaining it properly. Is there a way to make separate instances of the module?

Upvotes: 0

Views: 245

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

If you have multiple worksheets of the same "type" (same kind of data and layout etc), then a good approach (as suggested by others here) is to place the code which "manages" the sheet into a class module, and have a property in that class which can hold a reference to a specific worksheet (which could be the activesheet, or the sheet you're currently working with).

Eg: a simple class clsSheet with a single "Title" property which maps to a specific cell:

Option Explicit

Private m_sht As Worksheet

Public Sub Init(sht As Worksheet)
 Set m_sht = sht
End Sub

Public Property Let Title(t As String)
    m_sht.Range("A1").Value = t
End Property

Public Property Get Title() As String
    Title = m_sht.Range("A1").Value
End Property

Usage:

Dim shtObj as New clsSheet

shtObj.Init ActiveSheet
shtObj.Title = "my title"
msgbox shtObj.Title

Your existing code should likely map over fairly easily into the new class module: you just need to make sure to reference m_sht instead of (eg) ActiveSheet when working with Ranges etc.

Upvotes: 1

JeffO
JeffO

Reputation: 8043

You can do this with a class module instead of just a module. One difference is that you'll need to create an object on each worksheet as the same type as the class, but intantiate a separate instance of the object each time.

Depending on how you scope this class (using Dim) will determine where it is availabe.

This is a pretty good reference: http://www.cpearson.com/excel/classes.aspx

Upvotes: 4

Related Questions