Common Programming Pattern References

[Andre205th]

Does anyone know a resource for programming patterns, beginner level.

The project I'm working on loops through an Excel list and builds Word documents from the list. Some of these items need to be combined into one item and placed on the same Word doc. So I have to store the previous item and compare with the next item. Lucky for me that the items that need combining are one after the other on this list. So that would be another problem, what if they weren't one after the other? I sure I could come up with some long, nested if statement or something, but I'm sure there's a better way.

Is programming patterns the right term? Resources?

[SamT]

For each Item found


Dim TempVar As string

'Do your loop
'other code to find Item
If Not CBool(Instr(TempVar, Item)) Then TempVar = TempVar & ", " & Item
'end your loop

'Clean up TempVar leading comma space.
TempVar = Mid(TempVar, 3)

[Paul_Hossler]

Skeleton of approach

Didn't do anything actually using Word, but you can see where you'd include your code



Option Explicit


Sub List2Word()
Dim rList As Range, rList1 As Range
Dim i As Long, j As Long
Dim sPretendWordDoc As String
Dim aryList As Variant, v As Variant

'sort inputs
With ActiveSheet
Set rList = .Range("A1").CurrentRegion
Set rList1 = rList.Cells(2, 1).Resize(rList.Rows.Count - 1, rList.Columns.Count)



With .Sort
.SortFields.Clear
.SortFields.Add Key:=rList1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.SetRange rList
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

'make into array
aryList = Application.WorksheetFunction.Transpose(rList.Columns(1))

'combine same values
For i = UBound(aryList) To LBound(aryList) + 1 Step -1
If aryList(i) = aryList(i - 1) Then
aryList(i - 1) = aryList(i - 1) & Chr(1) & aryList(i) ' Chr(1) is just marker
aryList(i) = Empty
End If
Next i

'do wonderful stuff with MS Word
For i = LBound(aryList) + 1 To UBound(aryList)
If Not IsEmpty(aryList(i)) Then

sPretendWordDoc = "The Word doc will use ... " & vbCrLf & vbCrLf

v = Split(aryList(i), Chr(1))
For j = LBound(v) To UBound(v)
sPretendWordDoc = sPretendWordDoc & vbTab & v(j) & vbCrLf
Next j


MsgBox sPretendWordDoc
End If
Next i


End Sub

[Andre205th]

Thanks Sam and Paul for the examples.

Does it make any sense to create 2 classes and create objects for every item(they are unique) and create objects for each location(if not already created). If they used a few of the same properties then they would have a relationship, and the logic would be easier? I dont know. what do you think.


Loop....

dim i as string
i = element.offset(0,2)
dim i as new myItem
with i
.type
.location
.section
end with

dim x as string
x= element.offset(0,5)
dim x as new myLocations
with x
. location
. section
. etc....
end with

Would something like this work?

[Paul_Hossler]

Does it make any sense to create 2 classes and create objects for every item(they are unique) and create objects for each location(if not already created). If they used a few of the same properties then they would have a relationship, and the logic would be easier? I don't know. What do you think.


Well ... IMHO, unless I'm majorly misunderstanding what you want to to, it seems like a over complication.

It's not hard to use CreateObject to make an instance of Word, and then create - update - save documents as required

[SamT]

I'm now totally confused.
Please upload (Use the Advanced button) a sheet with a few "Items" on it.