I have close to 50 excel files – I cannot change the data source – and I steadily get more.
My task is to make sense of all that data and, lo an behold, save that as another excel file (I asked if I should put it in a local database, but no, I should not).
Asking on SO lead me here, as I thought it might be a better fit here, but my problem, if it even is one, is quite specific.
In my current code I used a couple of classes to model the data; I read somewhere that data should be data and not a class but are dictionaries helpful in this case?
Private pPartnumber As String Private pDescription As String Private pLevel As Long Private pSupplier As Supplier Private pCustomers As CustomerCollection Private pSubAssemblies As AssemblyCollection Private pWarehouse As String Private pEligibility As String Private pApprovedData As String Private pApprovedDataBasedOn As String Private BuyOrMake As AssemblyBuyMake Public Enum AssemblyBuyMake Buy = 0 Make = 1 Both = 2 Other = 4 End Enum ' Lots of getters and setters ' removed for conciseness Private Sub SetEligibilityOfSubAssemblies(ByVal Value As String) Dim subAssembly As Assembly If Not pSubAssemblies Is Nothing Then For Each subAssembly In pSubAssemblies subAssembly.Eligibility = Value Next End If End Sub Private Sub SetApprovedDataOfSubAssemblies(ByVal Value As String) Dim subAssembly As Assembly If Not pSubAssemblies Is Nothing Then For Each subAssembly In pSubAssemblies subAssembly.ApprovedData = Value Next End If End Sub '@Description "Checks if Partnumbers are equal, ignores Description- and other deviations" Public Function Equals(ByVal Assembly As Assembly) As Boolean Equals = (pPartnumber = Assembly.Partnumber) End Function
As you can see my class has lots of fields; the two setter subs are private as any Subassembly automatically inherits the Topassembly’s ApprovedData and Eligibility as per requirement.
Private pAssemblyCollection As Collection Private Sub Class_Initialize() Set pAssemblyCollection = New Collection End Sub Public Sub Add(ByVal Value As Assembly, Optional ByVal Key As String) ' End Sub Public Sub Remove(Optional ByVal Value As Assembly, Optional ByVal Key As String) ' End Sub '@DefaultMember Public Function Item(Optional ByVal index As Long = -1, Optional ByVal Key As String) As Assembly ' End Function '@MemberAttribute VB_MemberFlags, "40" '@Enumerator Public Property Get NewEnum() As IUnknown Set NewEnum = pAssemblyCollection.(_NewEnum) End Property
CustomerCollection looks the same.
Private pName As String Private pID As String Private pCountry As String Private pHTSCode As String ' And its getters and setters '@Description "Returns True if Name, ID and HTSCode are the same" Public Function Equals(ByVal Customer As Customer) As Boolean Equals = (pName = Customer.Name And pID = Customer.ID And pHTSCode = Customer.HTSCode) End Function
HTSCode is currently part of the
Customer as the file I found it in contains Customer and sales data and barely enough assembly data to identify an assembly – think “customer has a partnumber for an assembly and my files have a partnumber which don’t match” and you are just supposed to know which ones refer to the same assembly.
Instead of using these classes should I use dictionaries of dictionaries?
And for comparing data of different sheets should I store either these classes or dictionaries with the data in new dictionaries or arrays?
I’m not sure if there’s a maximum on how many custom objects I can create (other than memory limits), but I fear I’ll end up with a lot of
Assembly instances in this case.
Dim arr() As Assembly
to collect all the data before parsing it to a sheet.