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]
