next
11-11-2011, 11:12 AM
I'm trying to accomplish a task with classes, but I'm a bit confused and don't know how to go further. Here's what I have so far, this is the data I'm working with:
Invoice Bulkcode
528020 9134
528018 9134
528019 9134
528021 9134
525950 9135
525947 9135
525948 9135
525949 9135
528022 9136
528023 9137
528024 9137
528026 9137
528025 9137
528027 9138
528029 9138
528028 9138
528030 9138
528031 9139
528032 9139
528033 9139
528034 9139
528037 9140
528035 9140
528036 9140
528038 9140
This is my code that I was able to write so far:
Sub test()
Dim Cell As Range
Dim invoice As cInvoice
Dim Invoices As Collection
Set Invoices = New Collection
For Each Cell In Range("B:B")
If IsEmpty(Cell) Then Exit For
If IsNumeric(Cell) Then
Set invoice = New cInvoice
invoice.BulkNumber = Cell.value
invoice.InvoiceNumber = Cell.Offset(0, -1)
Invoices.Add invoice
End If
Next Cell
For Each invoice In Invoices
Debug.Print invoice.BulkNumber
Next invoice
End Sub
Just in case, here's the class:
Option Explicit
Private pBulkNumber As Double, pInvoiceNumber As Double, pSubTotal As Double, pTax As Double, pTotal As Double
Private pAccountCode As String, pStoreNumber As Double
Private pServiceDate As Date, pServiceDescription As String, pServicePO As String
'Bulk invoice number
Public Property Get BulkNumber() As Variant
BulkNumber = pBulkNumber
End Property
Public Property Let BulkNumber(value As Variant)
pBulkNumber = value
End Property
'Single Invoice number
Public Property Get InvoiceNumber() As Variant
InvoiceNumber = pInvoiceNumber
End Property
Public Property Let InvoiceNumber(value As Variant)
pInvoiceNumber = value
End Property
'SubTotal amount
Public Property Get SubTotal() As Variant
SubTotal = pSubTotal
End Property
Public Property Let SubTotal(value As Variant)
pSubTotal = value
End Property
'Tax Amount
Public Property Get Tax() As Variant
Tax = pTax
End Property
Public Property Let Tax(value As Variant)
pTax = value
End Property
'Gross billing amount
Public Property Get Total() As Variant
Total = pTotal
End Property
Public Property Let Total(value As Variant)
pTotal = value
End Property
'Account Code
Public Property Get AccountCode() As Variant
AccountCode = pAccountCode
End Property
Public Property Let AccountCode(value As Variant)
pAccountCode = value
End Property
'Store Number
Public Property Get StoreNumber() As Variant
StoreNumber = pStoreNumber
End Property
Public Property Let StoreNumber(value As Variant)
pStoreNumber = value
End Property
'Service Date
Public Property Get ServiceDate() As Variant
ServiceDate = pServiceDate
End Property
Public Property Let ServiceDate(value As Variant)
pServiceDate = value
End Property
'Service Description
Public Property Get ServiceDescription() As Variant
ServiceDescription = pServiceDescription
End Property
Public Property Let ServiceDescription(value As Variant)
pServiceDescription = value
End Property
'
Public Property Get ServicePO() As Variant
ServicePO = pServicePO
End Property
Public Property Let ServicePO(value As Variant)
pServicePO = value
End Property
The task here is to accomplish the following: I need to create number of classes, one for each BulkCode; each BulkCode should have associated invoice numbers tied with them. So I want to store and be able to refer to the above data the following way:
Access each bulk code -> first is 9134 -> contains invoices 528020, 528018, 528019 and 528021, each invoice will have billing amounts and dates tied to them.
I'm not sure how to go about it. I can accomplish this with multi dimensional arrays, but it would be such a mess then. Can anyone help?
Invoice Bulkcode
528020 9134
528018 9134
528019 9134
528021 9134
525950 9135
525947 9135
525948 9135
525949 9135
528022 9136
528023 9137
528024 9137
528026 9137
528025 9137
528027 9138
528029 9138
528028 9138
528030 9138
528031 9139
528032 9139
528033 9139
528034 9139
528037 9140
528035 9140
528036 9140
528038 9140
This is my code that I was able to write so far:
Sub test()
Dim Cell As Range
Dim invoice As cInvoice
Dim Invoices As Collection
Set Invoices = New Collection
For Each Cell In Range("B:B")
If IsEmpty(Cell) Then Exit For
If IsNumeric(Cell) Then
Set invoice = New cInvoice
invoice.BulkNumber = Cell.value
invoice.InvoiceNumber = Cell.Offset(0, -1)
Invoices.Add invoice
End If
Next Cell
For Each invoice In Invoices
Debug.Print invoice.BulkNumber
Next invoice
End Sub
Just in case, here's the class:
Option Explicit
Private pBulkNumber As Double, pInvoiceNumber As Double, pSubTotal As Double, pTax As Double, pTotal As Double
Private pAccountCode As String, pStoreNumber As Double
Private pServiceDate As Date, pServiceDescription As String, pServicePO As String
'Bulk invoice number
Public Property Get BulkNumber() As Variant
BulkNumber = pBulkNumber
End Property
Public Property Let BulkNumber(value As Variant)
pBulkNumber = value
End Property
'Single Invoice number
Public Property Get InvoiceNumber() As Variant
InvoiceNumber = pInvoiceNumber
End Property
Public Property Let InvoiceNumber(value As Variant)
pInvoiceNumber = value
End Property
'SubTotal amount
Public Property Get SubTotal() As Variant
SubTotal = pSubTotal
End Property
Public Property Let SubTotal(value As Variant)
pSubTotal = value
End Property
'Tax Amount
Public Property Get Tax() As Variant
Tax = pTax
End Property
Public Property Let Tax(value As Variant)
pTax = value
End Property
'Gross billing amount
Public Property Get Total() As Variant
Total = pTotal
End Property
Public Property Let Total(value As Variant)
pTotal = value
End Property
'Account Code
Public Property Get AccountCode() As Variant
AccountCode = pAccountCode
End Property
Public Property Let AccountCode(value As Variant)
pAccountCode = value
End Property
'Store Number
Public Property Get StoreNumber() As Variant
StoreNumber = pStoreNumber
End Property
Public Property Let StoreNumber(value As Variant)
pStoreNumber = value
End Property
'Service Date
Public Property Get ServiceDate() As Variant
ServiceDate = pServiceDate
End Property
Public Property Let ServiceDate(value As Variant)
pServiceDate = value
End Property
'Service Description
Public Property Get ServiceDescription() As Variant
ServiceDescription = pServiceDescription
End Property
Public Property Let ServiceDescription(value As Variant)
pServiceDescription = value
End Property
'
Public Property Get ServicePO() As Variant
ServicePO = pServicePO
End Property
Public Property Let ServicePO(value As Variant)
pServicePO = value
End Property
The task here is to accomplish the following: I need to create number of classes, one for each BulkCode; each BulkCode should have associated invoice numbers tied with them. So I want to store and be able to refer to the above data the following way:
Access each bulk code -> first is 9134 -> contains invoices 528020, 528018, 528019 and 528021, each invoice will have billing amounts and dates tied to them.
I'm not sure how to go about it. I can accomplish this with multi dimensional arrays, but it would be such a mess then. Can anyone help?