Consulting

Results 1 to 6 of 6

Thread: Common Programming Pattern References

  1. #1

    Common Programming Pattern References

    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?

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    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?

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I'm now totally confused.
    Please upload (Use the Advanced button) a sheet with a few "Items" on it.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •