# VBA Help

This topic is 3709 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

Hey all, it's been a while since I posted here but seeing as this happens to be the most knowledgable place I know. I thought I'd give this a go. What I'm doing is creating an Excel Automated Macro to compare two fields of information for annomilies. Due to the way the data is put together (it's really in a horrid mess as it stands in the Excel sheet and there is nothing I can do to fix that... so I'm working with what I have). I have developed a method by which I created two seperate classes, one for a part number the other for the actual vendors and such assigned to the part numbers. Here are the classes as set up.
'Part Class
Private celPN As String
Private oldVendorName As Collection
Private oldNumVendors As Integer
Private newVendorName As Collection
Private newNumVendors As Integer
Public Property Get PN() As String
PN = celPN
End Property
Public Property Let PN(PartNum As String)
celPN = PartNum
End Property
Public Property Get OldVendorInfo(iteration As Integer) As Vendor
OldVendorInfo = oldVendorName(iteration)
End Property
Public Property Let OldVendorInfo(iteration As Integer, vend As Vendor)
End Property
Public Property Get OldVendorNum() As Integer
OldVendorNum = oldNumVendors
End Property
Public Property Let OldVendorNum(iteration As Integer)
oldNumVendors = iteration
End Property
Public Property Get NewVendorInfo(iteration As Integer) As Vendor
NewVenderInfo = newVendorName(iteration)
End Property
Public Property Let NewVendorInfo(iteration As Integer, vend As Vendor)
End Property
Public Property Get NewVendorNum() As Integer
NewVendorNum = newNumVendors
End Property
Public Property Let NewVendorNum(iteration As Integer)
newNumVendors = iteration
End Property
Public Sub setUpNewInstance()
Set oldVendorName = New Collection
Set newVendorName = New Collection
End Sub


'Vendor Class
Private vendorname As String
Private oldVendorPartNum() As String
Private oldNumParts As Integer
Private newVendorPartNum() As String
Private newNumParts As Integer

Public Property Get Name() As String
Name = vendorname
End Property
Public Property Let Name(vName As String)
vendorname = vName
End Property
Public Property Get OldParts(iteration As Integer) As String
OldParts = oldVendorPartNum(iteration)
End Property
Public Property Let OldParts(iteration As Integer, PN As String)
oldVendorPartNum(iteration) = PN
End Property
Public Property Get OldPartNum() As Integer
OldPartNum = oldNumParts
End Property
Public Property Let OldPartNum(partCounter As Integer)
oldNumParts = partCounter
If oldNumParts > 0 Then
ReDim oldVendorPartNum(1 To partCounter)
End If
End Property
Public Property Get NewParts(iteration As Integer) As String
NewParts = newVendorPartNum(iteration)
End Property
Public Property Let NewParts(iteration As Integer, PN As String)
newVendorPartNum(iteration) = PN
End Property
Public Property Get NewPartNum() As Integer
NewPartNum = newNumParts
End Property
Public Property Let NewPartNum(partCounter As Integer)
newNumParts = partCounter
If newNumParts > 0 Then
ReDim newVendorPartNum(1 To partCounter)
End If
End Property


Now, I'm doing a call from a module saying:
For Each tPart In parts
MsgBox tPart.PN
For partCounter = 1 To tPart.OldVendorNum
tPart.PrintVendorNames
Next partCounter
Next tPart


First off when trying to debug, for some reason OldVendorNum returns 0... I'm thinking that it has something to do with the classes themselves as if I run a function from the class to print off the names and such... it works just fine... Thanks in advance for any help. And do let me know if I've missed something completely obvious... It's been about 2 years since I last coded anything seriously...

##### Share on other sites
Sorry... make the post and already I screwed up... lol

The method I'm trying to run is:

    MsgBox tPart.OldVendorInfo(tPart.OldVendorNum)).Name

Essentially right now I'm trying to get VB to pass the vendor class BACK to my main sub. Any help would be appreciate... Thanks

1. 1
2. 2
JoeJ
20
3. 3
frob
17
4. 4
5. 5

• 10
• 10
• 11
• 13
• 9
• ### Forum Statistics

• Total Topics
632197
• Total Posts
3004728

×