Option explicit workbook help

tezza

tezza

Active Member
Joined
Sep 10, 2006
Messages
375
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
  2. Web
Hi all

I've got macros that I want to keep in a blank workbook that I can send to others for them to use on other workbooks. Personal doesn't do what I need.

Option explicit seems to be the way to go but I don't know what Dims' to add to make it work.

I haven't added option explicit at the top yet but I know I have to, to make it work.

Can someone please look at the code and add what's missing please?

Don't judge my coding, as it's mostly done by recording macros and taking what I need plus help from here and the internet as I'm no programmer.

Thank you.

VBA Code: 
Sub Run_RC()

Application.Run ("Helper")

Worksheets("Helper").Select

     ActiveSheet.Range("A1", "AR" & Range("c" & Rows.Count).End(xlUp).Row).AutoFilter Field:=20, Criteria1:= _
     "=CCC-RC  (Adults)"
     
Application.Run ("Copy_Data")

Application.Run ("Module1.Tidy_up")

Application.Run ("MakeGroups")

Application.Run ("condformat")

Range("A1").Select

End Sub

Sub Run_CS()

Application.Run ("Helper")

Worksheets("Helper").Select

    ActiveSheet.Range("$A$1:$AM$4612").AutoFilter Field:=20, Criteria1:=Array( _
    "CCC-CS (RC)", "CCC-CS (SS)", "CCC-CS/PC", "CCC-ECM DB"), Operator:=xlFilterValues

     
Application.Run ("Copy_Data")

Application.Run ("Module1.Tidy_up")

Application.Run ("MakeGroups")

Application.Run ("condformat")

Range("A1").Select

End Sub
Sub Manual_TS_Rate_Sheet()

Application.Run ("Helper")

Worksheets("Helper").Select
     
Application.Run ("Copy_Data")

Application.Run ("Module1.Tidy_up")

Application.Run ("MakeGroups")

Application.Run ("condformat")

Range("A1").Select

End Sub

Private Sub checkSheet()

Dim ws As Worksheet

    Application.ScreenUpdating = False
    
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If ws.Name <> "TS" Then ws.Delete
    Next ws
    Application.DisplayAlerts = True

        Worksheets.Add.Name = "Helper"
        Worksheets.Add.Name = "Call_Logs"
      
    Application.ScreenUpdating = True

End Sub

Private Sub Tidy_up()
'
' Macro2 Macro
'

'
    Application.ScreenUpdating = False
    
    Worksheets("Call_Logs").Select
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .RowHeight = 30
    End With
    Columns("A:P").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter

    End With

    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Range("A1").Select
  
    Rows(1).Resize(, 13).Interior.Color = RGB(191, 191, 191)
    
    Columns("F:K").Select
    Selection.NumberFormat = "[hh]:mm"
    Columns("O").Select
    Selection.NumberFormat = "[hh]:mm"
    Columns("B").Select
    Selection.NumberFormat = "dd/mm/yyyy"
    Range("O1").NumberFormat = "General"
    
    Range("D1").Value = "Clients"
    Range("C1").Value = "Staff"
    Range("k1").Value = "Duration"
    Range("I1").Value = "Real Start"
    Range("j1").Value = "Real End"
    Range("m1").Value = "Notes"
    
    Application.ScreenUpdating = True
    
    Worksheets("Call_Logs").Columns("A:P").AutoFit
    
    Range("a1").Select

End Sub
Private Sub MakeGroups()

  Dim rB As Range
  Worksheets("Call_Logs").Select
  
    Application.ScreenUpdating = False
    
  With Range("A1").CurrentRegion
    .Sort key1:=.Columns(4), order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Key3:=.Columns(6), Order3:=xlAscending, Header:=xlYes
    .Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4)
  End With
   For Each rB In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks)

    rB.Resize(3).EntireRow.Insert
    rB.Rows(-1).Resize(, 13).Interior.Color = RGB(191, 191, 191)
  Next rB
  ActiveSheet.UsedRange.RemoveSubtotal

    Application.ScreenUpdating = True

End Sub

Private Sub Helper()

    Application.Run ("checkSheet")
    
    Application.ScreenUpdating = False
    
    Sheets("TS").Cells.Copy Destination:=Sheets("Helper").Range("A1")

    Worksheets("Helper").Select
        
    Range("D1").EntireColumn.Insert
    Range("D1", "D" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=Helper!c1&"", ""&Helper!B1"
    Range("D1").Value = "Full_Name"
    Range("AO1", "AO" & Range("C" & Rows.Count).End(xlUp).Row).Value = "=left(Helper!L1,1)&left(Helper!M1,1)"
    Range("AO1").Value = "Staff Initials"
    
    Range("AE:AG").EntireColumn.Insert
    
    Range("AE2", "AE" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=IFERROR(IF(OR(MINUTE(RC[-2])=0,RC29=""""),RC[-6]+RANDBETWEEN(-8,8)/1440,RC[-2]),"""")"
    Range("AF2", "AF" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=IF(RC[-1]=""***"",RC[-6]+RANDBETWEEN(-7,7)/1440,IF(RC[-2]="""",(RC[-1]+RC[-5])+RANDBETWEEN(-7,7)/1440,RC[-2]))"
    Range("AG2", "AG" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=IFERROR(MOD(RC[-1]-RC[-2],1),"""")"
    Range("AS2", "AS" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=IF(RC[-16]="""",""No Start Time"","""")"
    
    Columns("AE:AG").Select
    Selection.NumberFormat = "[hh]:mm"
    Range("A1").Select
    
    Range("AE1").Value = "Real Start Helper"
    Range("AF1").Value = "Real End Helper"
    Range("AG1").Value = "Duration Helper"
    Range("AS1").Value = "Notes"

    Application.ScreenUpdating = True
       
End Sub

Private Sub condformat()
'
' Macro6 Macro
'

'
    Application.ScreenUpdating = False
    
    Worksheets("Call_Logs").Select
    
    For Each TmpSht In ThisWorkbook.Sheets
    TmpSht.Cells.FormatConditions.Delete
    Next
    
    Columns("I:I").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$M1=""No Start Time"""
    With Selection.FormatConditions(1).Interior
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
   
    Columns("J:J").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I2>J2"
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Columns("K:K").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(COUNTA(A1:M1)>1,$H1-$K1>TIME(0,15,0))"
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Range("J2:K13,O2:O13").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K2<>$O2"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
Range("N2", "N" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=IF(AND(COUNT(RC[-13]:RC[-1])=1,RC[-6]<>""""),""P"","""")"
Range("O2", "O" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=RC[-5]-RC[-6]"
Range("P2", "P" & Range("c" & Rows.Count).End(xlUp).Row).Value = "=IF(OR(RC[-1]=RC[-5],MROUND(RC[-1],""00:01"")=MROUND(RC[-5],""00:01"")),"""",""Check"")"
Range("O1").Value = "=COUNTIF(R[1]C[-1]:R[9999]C[-1],""P"")"
Range("P1").Value = "=COUNTA(UNIQUE(R[1]C[-12]:R[9999]C[-12]))-1"
Range("O1").NumberFormat = "General"

    Columns("N:N").Select
    With Selection.Font
        .Name = "Wingdings 2"
        .Size = 11
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Completed", vbInformation

End Sub

Private Sub Copy_Data()

Application.ScreenUpdating = False

    Worksheets("Helper").Select
    
'Day & Date
    Range("W1:X1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("A1").PasteSpecial Paste:=xlPasteValues
    
'Chargeable Rate Sheet
    Range("T1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("E1").PasteSpecial Paste:=xlPasteValues
    
'Start, End, Duration
    Range("Y1:AA1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("F1").PasteSpecial Paste:=xlPasteValues

'Real Start, End Duration
    Range("AE1:AG1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("I1").PasteSpecial Paste:=xlPasteValues
    
'Client ID
    Range("AH1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("L1").PasteSpecial Paste:=xlPasteValues
    
'Full Client Name
    Range("D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("D1").PasteSpecial Paste:=xlPasteValues
    
'Staff Initials
    Range("AR1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("C1").PasteSpecial Paste:=xlPasteValues
    
'No Real Start Time
    Range("AS1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Call_Logs").Range("M1").PasteSpecial Paste:=xlPasteValues
                    
Application.ScreenUpdating = True
                    
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Looking at what you are trying to accomplish and asking about the implementation "Option Explicit", I don't think you understand exactly what Option Explicit does for you. There is a ton of info out there to look it up so I won't explain it here. However, to answer your question- If you put "Option Explicit" at the top of your module, it will take care of itself...
 
Upvote 0
igold said:
Looking at what you are trying to accomplish and asking about the implementation "Option Explicit", I don't think you understand exactly what Option Explicit does for you. There is a ton of info out there to look it up so I won't explain it here. However, to answer your question- If you put "Option Explicit" at the top of your module, it will take care of itself...
Click to expand...
You're absolutely right, I've no idea what it does.

I use another sheet someone else created to run in any workbook, which is where I got the idea from.

I tried researching more about it but either I'm still not understanding or I'm looking in the wrong place.

Anyway, I added Option Explicit on a new workbook and run the code on another sheet where it works fine, but thankfully, it showed me where an error was and what type. I don't know if I chose the correct option but it seems to work....... so far.

VBA Code: 
Option Explicit
Dim TmpSht As Variant
 
Upvote 0
That's exactly what it does, it prevents stupid (not yours) program errors to prevent you from chasing your tail when you are trying to debug. One of the main benefits is that it makes you declare all your variables...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
364
rlv01
R
PIsabel
  • Question
Transpose columns to rows but without blanks
Replies
1
Views
178
bobaol
B
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
982
Joe4
Joe4
S
  • Question
How to streamline?
Replies
2
Views
173
sachavez
S
E
  • Solved
VBA fails when worksheet is protected?
Replies
1
Views
155
EssKayKay
E

Forum statistics

Threads
1,215,069
Messages
6,122,958
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top