Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    This code has constantly evolved over the last several months and I am now at a point where it has become difficult to understand, so I would appreciate any guidance that can be offered in cleaning it up.

    Public ws As String - The code uses an array of worksheets that needs to be accessed in other modules, so I know that I need to keep this. However, I'm not sure why this starts out as a string and is then converted into an array of worksheets.

    Dim Out as worksheet, Dim Sht as Variant - Much the same thing (I think), but I am switching back and forth in different sections of the code. spagetti......

    My thoughts are to change Public ws from string to worksheet (I don't think I need the string??), and then scrap Out and Sht in favour of ws as worksheet. In summary, I just want this to be as clean, efficient and understandable as possible.

    All pointers with the above (and any other's) greatfully received.

    [codebox]
    Public ws As String

    Option Private Module

    Sub Batches()

    Dim wb As Workbook
    Dim inp As Worksheet, out As Worksheet
    Dim m As Long, r As Long, t As Long
    Dim Sht As Variant
    Dim Msg As String, pw As String

    Set wb = ThisWorkbook
    Set inp = Worksheets("Input Sheet")

    pw = "xyz"

    Application.Calculation = xlCalculationAutomatic

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''
    'Certegy Connection Checker

    Call CheckConnections

    If intSessCount = 0 Then 'We have no open session, so create one
    Msg = vbCrLf & "Please note:-"
    Msg = Msg & vbCrLf & vbCrLf & "You do not have a Certegy Session open."
    Msg = Msg & vbCrLf & vbCrLf & "A Certegy Session will now be opened. Please log in."
    Msg = Msg & vbCrLf & vbCrLf
    MsgBox Msg, vbExclamation, "Create Certegy Session"
    Call CreateSession
    Exit Sub
    End If

    If intSessCount > 0 And booProcessed = False Then 'We have an open session, but need to log in
    Msg = vbCrLf & "Please note:-"
    Msg = Msg & vbCrLf & vbCrLf & "You are not logged in to Certegy."
    Msg = Msg & vbCrLf & vbCrLf & "Your Certegy Session will now be shown. Please log in."
    Msg = Msg & vbCrLf & vbCrLf
    MsgBox Msg, vbExclamation, "Show Certegy Session"
    Call ShowSession
    Exit Sub
    End If

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''

    If inp.Range("B17") = 0 Then
    MsgBox "There is no data to process. Please add some data and try again!", vbExclamation, "No data"
    Exit Sub
    End If


    wb.Unprotect Password:=pw
    inp.Unprotect Password:=pw

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each Sht In Array("10", "20", "25", "30", "40")
    Set out = Worksheets(Sht)
    With out
    .Unprotect Password:=pw
    .Rows("21:1019").ClearContents
    .Range("D5").ClearContents
    End With
    Next

    Application.Calculation = xlCalculationManual

    m = inp.Cells(inp.Rows.Count, 5).End(xlUp).Row 'Set input sheet last row
    For r = 21 To m 'Set input sheet range of row 21 to last row
    Set out = Worksheets(CStr(inp.Cells(r, 4))) 'Set relevant Corp sheet
    t = out.Cells(out.Rows.Count, 4).End(xlUp).Row + 1 'Set next available row number on relevant Corp sheet
    If t = 20 Then t = 21 'Ignore row 20
    out.Cells(t, 2) = t - 20 'Set item number on corp sheet
    out.Cells(t, 4) = inp.Cells(r, 5) 'Add account number to corp sheet
    out.Cells(t, 6) = Round(inp.Cells(r, 6), 2) 'Add value to corp sheet
    out.Cells(t, 8) = inp.Cells(r, 7) 'Add transaction date to corp sheet
    out.Cells(t, 10) = inp.Cells(r, 8) 'Add description to corp sheet
    Next r

    Application.Calculation = xlCalculationAutomatic

    For Each Sht In Array("10", "20", "25", "30", "40")
    Set out = Worksheets(Sht)
    out.Protect Password:=pw, DrawingObjects:=True, Contents:=True, Scenarios:=True
    If out.Range("D9") > 0 Then
    ws = Sht
    Worksheets(ws).Unprotect Password:=pw
    Call ProcessBatches
    With Worksheets(ws)
    .Visible = xlSheetVisible
    .PageSetup.PrintArea = "$A$1:$K$" & Worksheets(ws).Range("D9") + 20
    .PrintOut Copies:=1, Collate:=True
    .Visible = xlSheetHidden
    End With
    Worksheets(ws).Protect Password:=pw, DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
    Next

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''
    'Print Counter Sheets

    With Sheets("PAYMENTS")
    .Visible = xlSheetVisible
    .PrintOut Copies:=1, Collate:=True
    .Visible = xlSheetHidden
    End With

    With Sheets("RECEIPTS")
    .Visible = xlSheetVisible
    .PrintOut Copies:=1, Collate:=True
    .Visible = xlSheetHidden
    End With

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''

    inp.Select
    inp.Range("E21").Select

    inp.Shapes("UpBatches").Visible = False

    inp.Protect Password:=pw, DrawingObjects:=True, Contents:=True, Scenarios:=True
    wb.Protect Password:=pw, Structure:=True, Windows:=False

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Msg = "Batch Upload(s) to Certegy Complete."
    Msg = Msg & vbCrLf & vbCrLf & inp.Range("H13") & "-" & inp.Range("H14") & "-" & inp.Range("H15") & " - " & inp.Range("H17")
    For Each Sht In Array("10", "20", "25", "30", "40")
    Set out = Worksheets(Sht)
    If out.Range("D9") > 0 Then
    Msg = Msg & vbCrLf & vbCrLf & "Corp " & (Sht) & " - " & out.Range("D9") & " - " & Format(out.Range("D7"), _
    "#,##0.00") & " - Batch " & out.Range("D5") & " - " & out.Range("E1")
    End If
    Next
    Msg = Msg & vbCrLf & vbCrLf & "Total All Corps - " & inp.Range("B17") & " - " & Format(inp.Range("C17"), "#,##0.00")
    Msg = Msg & vbCrLf & vbCrLf
    MsgBox Msg, vbInformation, "AUTO CERTEGY MULTI BATCH UPLOADER"

    End Sub

    [/codebox]

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Apologies for the wide code box, I've not done a good job of repairing that. I will remember to check the width in future!

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't need the variable ws at all.

    Keep the variable Sht (as a variant), scrap the line ws = Sht and use Worksheets(Sht) instead of Worksheets(ws).

    You don't really need the variable Out either; you can use Worksheets(...) instead (the ... varies throughout the code)

    I'd keep the variable inp - it is set only once at the beginning, and never changes after that, so it's efficient to use it.

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I think I follow.....

    So I am loosing ws and out, and adjusting Sht as variant from Dim to Public.

    How would I deal with the section starting at:

    Set out = Worksheets(CStr(inp.Cells(r, 4))) ?

    Would that change to

    Set Sht = Worksheets(CStr(inp.Cells(r, 4)))

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > adjusting Sht as variant from Dim to Public

    No! There is no reason whatsoever to do that. Leave the declaration as it is now.

    > Set Sht = Worksheets(CStr(inp.Cells(r, 4)))

    No! Change

    Code:
    Set out = Worksheets(CStr(inp.Cells(r, 4)))
    t = out.Cells(out.Rows.Count, 4).End(xlUp).Row + 1
    If t = 20 Then t = 21
    out.Cells(t, 2) = t - 20
    out.Cells(t, 4) = inp.Cells(r, 5)
    out.Cells(t, 6) = Round(inp.Cells(r, 6), 2)
    out.Cells(t, 8) = inp.Cells(r, 7)
    out.Cells(t, 10) = inp.Cells(r, 8)
    to

    Code:
    With Worksheets(CStr(inp.Cells(r, 4)))
      t = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
      If t = 20 Then t = 21
      .Cells(t, 2) = t - 20
      .Cells(t, 4) = inp.Cells(r, 5)
      .Cells(t, 6) = Round(inp.Cells(r, 6), 2)
      .Cells(t, 8) = inp.Cells(r, 7)
      .Cells(t, 10) = inp.Cells(r, 8)
    End With

  6. #6

Posting Permissions

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