Excel VBA Class Module

[kashif.special2005]

Hi,

I am new in learning about class module in Excel VBA, I somewhere read that manipulating data in memory is much faster that manipulating data in excel, I have huge data, and below are the samples, now my question is how can I store all the rows data in the class module related to unique project, like Project 1 is the key for all the rows related to that project and like wise for all project, Project 2 and Project 3 also, after all the data in the memory, when I want to fetch all the records related to particular project just give the project name can fetch all the records related to that project, like after all the data when I give Project 1, I want to fetch all records of Project 1, and can do some manipulation.

Can this is possible, and if yes, how can we do that, please teach me, I want to learn this type of methods in VBA.

Excel Workbook
	A	B	C	D	E
1	Projects	Name	Date	Area	Efficiency Status
2	Project 1	A	9/19/2017	NPO	Yes
3	Project 1	B	9/24/2017	Zamibia	Yes
4	Project 2	C	9/29/2017	NPO 2	Yes
5	Project 3	A	10/4/2017	NPO	Yes
6	Project 1	B	10/9/2017	Season 2	No
7	Project 2	C	10/14/2017	Season 2	No
8	Project 3	D	10/19/2017	WCEL	No
9	Project 2	E	10/24/2017	NPO	No
10	Project3	A	10/29/2017	Zamibia	Yes
Sheet1

Thanks in advance

Thanks
Kashif

 

[ParamRay]

Your class module code would be similar to below. You will need to create instances of the class from your code and populate them from the rows in your worksheet. You will also need to implement an additional method in the class to return records in the format you require, for example, to return an array if that's what you want.

Private m_intProjectNumber As Integer
Private m_strProjectName As String
Private m_dtmProjectDate As ProjectAreaType
Private m_udtProjectArea As String
Private m_udtProjectEfficiencyStatus As ProjectEfficiencyStatusType

Public Enum ProjectAreaType
  ProjectAreaUSA
  ProjectAreaGermany
  ProjectAreaJapan
  ProjectAreaFrance
  ProjectAreaUnitedKingdom
' Add/Amend areas as needed
End Enum

Public Enum ProjectEfficiencyStatusType
  ProjectEfficiencyStatusYes
  ProjectEfficiencyStatusNo
End Enum

Public Property Let ProjectNumber(ByVal Value As Integer)
  m_intProjectNumber = Value
End Property

Public Property Get ProjectNumber() As Integer
  ProjectNumber = m_intProjectNumber
End Property

Public Property Let ProjectName(ByVal Value As String)
  Value = UCase(Value)
  If Len(Value) = 1 And Asc(Value) >= Asc("A") And Asc(Value) <= Asc("Z") Then
    m_strProjectName = Value
  Else
    Err.Raise vbObjectError + 513, "Project.ProjectName", "Must be single a character between A and Z."
  End If
End Property

Public Property Get ProjectName() As String
  ProjectName = m_strProjectName
End Property

Public Property Let ProjectDate(ByVal Value As Date)
  m_dtmProjectDate = Value
End Property

Public Property Get ProjectDate() As Date
  ProjectDate = m_dtmProjectDate
End Property

Public Property Let ProjectArea(ByVal Value As ProjectAreaType)
  m_udtProjectArea = Value
End Property

Public Property Get ProjectArea() As ProjectAreaType
  ProjectArea = m_udtProjectArea
End Property

Public Property Let ProjectEfficiencyStatus(ByVal Value As ProjectEfficiencyStatusType)
  m_udtProjectEfficiencyStatus = Value
End Property

Public Property Get ProjectEfficiencyStatus() As ProjectEfficiencyStatusType
  ProjectEfficiencyStatus = m_udtProjectEfficiencyStatus
End Property

Furthermore, a class might not be the best way to do this with regards to efficiency. You could simply populate an array of variant type from your worksheet and return a subset of it from a user-defined function. It is up to you.

 

[kashif.special2005]

Hi ParamRay,

Thanks for your precious time for this code, however as I said I am new in class module, I know how to use it, means how to fill the data in class module and how can I retrieve rows for specific projects.

It would be great help, if you please post complete code for the same.

Thanks again for your help.

Thanks
Kashif