Using a collection class to implement mutliple find/replace strings in cells

[Bill Hertzing]

I need to iterate over a row of cells, and if the value matches
string1, replace it with string2. There's no inherent ordering.

I found that with a class called ReplaceColumnTitle which has two
properties oldstring and replacementstring, and a collection
CollReplaceColumnTitle, I can easily iterate over each
ReplaceColumnTitle in the CollReplaceColumnTitle, test the cell value
against each oldstring and replace it if it matchs.

I don't see any 'good' way to encapuslate all the
oldstring/replacementstring pairs (there are about 30, and growing)
for populating the collection CollReplaceColumnTitle. I know XML is
probably perfect for this, but the learning curve for adding XML
programming to Excel macros using VBA is too costly for my immediate
needs, I think.

one way I've considered is to create a collection class, and to add
the name/value pairs to the collection during Class_Initialize. That
lets me isolate the information into the class module. I've tried to
do this, but:

How do I tell VBA that the new class is a collection?

How do I refer to 'myself' within the Class_Initialize subroutine?

Example:
Private Sub Class_Initialize()

Dim acr As ReplaceColumnTitle
Set acr = New ReplaceColumnTitle
acr.OS = "Transport Service CPU"
acr.RS = TransportCPUPM
Me.Add acr
end sub
I've tried Me.Add, This.add, .Add - nope on any of them.

Thanks for any suggestions, especially big-picture ones that offer a
good way to encapsulate the name/value pairs!

 

[Bob Kilmer]

I am not sure why you could not use a plain Collection:

Sub Main()

Dim cell As Range
Dim col As Collection
Set col = New Collection
col.Add "newstring01", "oldstring01"
col.Add "newstring02", "oldstring02"
col.Add "newstring03", "oldstring03"
col.Add "newstring04", "oldstring04"
col.Add "newstring05", "oldstring05"
col.Add "newstring06", "oldstring06"
col.Add "newstring07", "oldstring07"
col.Add "newstring08", "oldstring08"
col.Add "newstring09", "oldstring09"
col.Add "newstring10", "oldstring10"
col.Add "newstring11", "oldstring11"
col.Add "newstring12", "oldstring12"

On Error Resume Next
For Each cell In Range("A1:O1").Cells
cell.Value = col(cell.Text)
Next cell

End Sub

 

[Bob Kilmer]

....but to answer your "How do I tell VBA that the new class is a
collection?" you have to make it act like one. First of all, your class
needs a place to store this collection of things - a private Collection
object.

Option Explicit

Private mcol As Collection
'-------------------------------------

Private Sub Class_Initialize()
Set mcol = New Collection
Dim acr As ReplaceColumnTitle
Set acr = New ReplaceColumnTitle
acr.OS = "Transport Service CPU"
acr.RS = TransportCPUPM
mcol.Add acr
End Sub

You will need to add accessor functions to get at your internal collection.

What follows is the code VB6 would generate for a collection class. You
could do away with the Add method and populate the class collection
explicitly in the Class_Initialize event handler, or make it private and
call it from the Class_Initialize event handler to populate the class. (I am
not recommending this collection class solution for your problem as I
understand it, but it will get you a collection class.)

Option Explicit
Private mCol As Collection

Public Function Add(OS As String, RS As String, Optional sKey As String) As
MyClass
'create a new object
Dim objNewMember As MyClass
Set objNewMember = New MyClass

objNewMember.OS = OS
objNewMember.RS = RS
If Len(sKey) = 0 Then
mCol.Add objNewMember
Else
mCol.Add objNewMember, sKey
End If

'return the object created
Set Add = objNewMember
Set objNewMember = Nothing

End Function

Public Property Get Item(vntIndexKey As Variant) As MyClass
Set Item = mCol(vntIndexKey)
End Property

Public Property Get Count() As Long
Count = mCol.Count
End Property

Public Sub Remove(vntIndexKey As Variant)
mCol.Remove vntIndexKey
End Sub

Public Property Get NewEnum() As IUnknown
'this property allows you to enumerate
'this collection with the For...Each syntax
Set NewEnum = mCol.[_NewEnum]
End Property

Private Sub Class_Initialize()
'creates the collection when this class is created
Set mCol = New Collection
End Sub

Private Sub Class_Terminate()
'destroys collection when this class is terminated
Set mCol = Nothing
End Sub

'-----------
Bob