CODEWITHSUNDEEP
vbaexcelarguments
BOB
BOB

Reputation: 131

Compile error: Argument not optional when calling sub

So, while I have read some of the many questions on here about similar errors, I haven't found one which helped me understand my problem, so far. If their is a similar question with an appropriate answer, please link it and I shall remove this one.

Now, I am using a small sub Refresh_Activesheet() to look at the name of the WorkSheets and according to that name run the appropriate sub. Most of the subs are in the same module as Refresh_Activesheet(). However, two are in their separate module, for the moment. Now the problem is that when I run Refresh_Activesheet vba returns the argument not optional error and I can't fathom why.

Refresh_Activesheet

Sub Refresh_Activesheet()

    If IsNumeric(Left(ActiveSheet.Name, 1)) Then
        Emissions_Calculation
    ElseIf ActiveSheet.Name = "Voyages List" Then
        Refresh_Table
    ElseIf ActiveSheet.Name = "Émission Total" Then
        Call Refresh_Emissions.Emission_Total
    ElseIf ActiveSheet.Name = "Consommation Total" Then
    Exit Sub
End If

End Sub

Refresh_Emissions which is the module that contains Emission_Total

Option Explicit
Option Base 1

Public j, i, k, n, m, p, l, info, mation, Total As Variant
Public AverageEmission, TotalEmission As String

Sub Emission_Total(ByVal ws As Worksheet)

Application.ScreenUpdating = False
On Error Resume Next

Dim SoxRevenue, SoxGross, MilesTotal, MilesLaden, CargoMT As Double

ThisWorkbook.Sheets("Émission total").Range("A7 : M200").Clear
j = 7
k = 1
For Each ws In ActiveWorkbook.Sheets
    If IsNumeric(Left(ws.Name, 1)) Then
        ws.Unprotect ("12345")

        ThisWorkbook.Sheets("Émission Total").Range("A" & j).Value = ws.Range("C5").Value
        ThisWorkbook.Sheets("Émission Total").Range("B" & j).Value = ws.Range("T2").Value
        ThisWorkbook.Sheets("Émission Total").Range("C" & j).Value = ws.Range("T3").Value
        ThisWorkbook.Sheets("Émission Total").Range("D" & j).Value = ws.Range("G2").Value
        ThisWorkbook.Sheets("Émission Total").Range("E" & j).Value = ws.Range("J2").Value

        info = ThisWorkbook.Sheets("Émission total").Range("F7 : M200").Address
        mation = ws.Range("D6:D8").Address

        SoxRevenue = WorksheetFunction.Sum(ws.Range("V28:V30").Value)
        SoxGross = ws.Range("V43").Value
        MilesLaden = ws.Range("M4").Value
        MilesTotal = ws.Range("M6").Value
        CargoMT = ws.Range("O5").Value

        With ThisWorkbook.Sheets("Émission total").Range(info)
            If (ws.Range(mation).Cells(1, 1).Value) = "" Then
             .Cells(k, 1).Value = ws.Range("W49").Value
             .Cells(k, 2).Value = ws.Range("W50").Value
             .Cells(k, 3).Value = ws.Range("W54").Value
             .Cells(k, 4).Value = ws.Range("W56").Value
             .Cells(k, 5).Value = ws.Range("V43").Value
             .Cells(k, 6).Value = SoxRevenue
             .Cells(k, 7).Value = SoxGross * 1.852 * 1000 * 1000 / (CargoMT * MilesTotal)
             .Cells(k, 8).Value = SoxRevenue * 1.852 * 1000 * 1000 / (CargoMT * MilesLaden)

             .Cells(k, 1).NumberFormat = "?0.00"
             .Cells(k, 2).NumberFormat = "?0.00"
             .Cells(k, 3).NumberFormat = "?0.00"
             .Cells(k, 4).NumberFormat = "?0.00"
             .Cells(k, 5).NumberFormat = "?0.0000"
             .Cells(k, 6).NumberFormat = "?0.0000"
             .Cells(k, 7).NumberFormat = "?0.0000"
             .Cells(k, 8).NumberFormat = "?0.0000"

            ElseIf (ws.Range(mation).Cells(1, 1).Value) = 0 Then
             .Cells(k, 1).Value = ws.Range("W49").Value
             .Cells(k, 2).Value = ws.Range("W50").Value
             .Cells(k, 3).Value = 0
             .Cells(k, 4).Value = 0
             .Cells(k, 5).Value = ws.Range("V43").Value
             .Cells(k, 6).Value = ws.Range("V43").Value
             .Cells(k, 7).Value = 0
             .Cells(k, 8).Value = 0

             .Cells(k, 1).NumberFormat = "?0.00"
             .Cells(k, 2).NumberFormat = "?0.00"
             .Cells(k, 3).NumberFormat = "?0.00"
             .Cells(k, 4).NumberFormat = "?0.00"
             .Cells(k, 5).NumberFormat = "?0.0000"
             .Cells(k, 6).NumberFormat = "?0.0000"
             .Cells(k, 7).NumberFormat = "?0.0000"
             .Cells(k, 8).NumberFormat = "?0.0000"

             ElseIf (ws.Range(mation).Cells(2, 1).Value) = 0 And (ws.Range(mation).Cells(3, 1).Value) = 0 Then
             .Cells(k, 1).Value = ws.Range("W49").Value
             .Cells(k, 2).Value = ws.Range("W50").Value
             .Cells(k, 3).Value = (ws.Range("W49").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value) + (ws.Range(mation).Cells(3, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 4).Value = 0
             .Cells(k, 5).Value = ws.Range("V43").Value
             .Cells(k, 6).Value = ws.Range("V43").Value
             .Cells(k, 7).Value = (ws.Range("V43").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value) + (ws.Range(mation).Cells(3, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 8).Value = 0

             .Cells(k, 1).NumberFormat = "?0.00"
             .Cells(k, 2).NumberFormat = "?0.00"
             .Cells(k, 3).NumberFormat = "?0.00"
             .Cells(k, 4).NumberFormat = "?0.00"
             .Cells(k, 5).NumberFormat = "?0.0000"
             .Cells(k, 6).NumberFormat = "?0.0000"
             .Cells(k, 7).NumberFormat = "?0.0000"
             .Cells(k, 8).NumberFormat = "?0.0000"

             ElseIf (ws.Range(mation).Cells(2, 1).Value) = "" And (ws.Range(mation).Cells(3, 1).Value) = "" Then
             .Cells(k, 1).Value = ws.Range("W49").Value
             .Cells(k, 2).Value = ws.Range("W50").Value
             .Cells(k, 3).Value = (ws.Range("W49").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value) + (ws.Range(mation).Cells(3, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 4).Value = 0
             .Cells(k, 5).Value = ws.Range("V43").Value
             .Cells(k, 6).Value = ws.Range("V43").Value
             .Cells(k, 7).Value = (ws.Range("V43").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value) + (ws.Range(mation).Cells(3, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 8).Value = 0

             .Cells(k, 1).NumberFormat = "?0.00"
             .Cells(k, 2).NumberFormat = "?0.00"
             .Cells(k, 3).NumberFormat = "?0.00"
             .Cells(k, 4).NumberFormat = "?0.00"
             .Cells(k, 5).NumberFormat = "?0.0000"
             .Cells(k, 6).NumberFormat = "?0.0000"
             .Cells(k, 7).NumberFormat = "?0.0000"
             .Cells(k, 8).NumberFormat = "?0.0000"

            Else
             .Cells(k, 1).Value = ws.Range("W49").Value
             .Cells(k, 2).Value = ws.Range("W50").Value
             .Cells(k, 3).Value = (ws.Range("W49").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value) + (ws.Range(mation).Cells(3, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 4).Value = (ws.Range("W50").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 5).Value = ws.Range("V43").Value
             .Cells(k, 6).Value = ws.Range("V43").Value
             .Cells(k, 7).Value = (ws.Range("V43").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value) + (ws.Range(mation).Cells(3, 1).Value))) * 1000 * 1000 / 1.853
             .Cells(k, 8).Value = (ws.Range("V43").Value) / ((ws.Range(mation).Cells(1, 1).Value) * ((ws.Range(mation).Cells(2, 1).Value))) * 1000 * 1000 / 1.853

             .Cells(k, 1).NumberFormat = "?0.00"
             .Cells(k, 2).NumberFormat = "?0.00"
             .Cells(k, 3).NumberFormat = "?0.00"
             .Cells(k, 4).NumberFormat = "?0.00"
             .Cells(k, 5).NumberFormat = "?0.0000"
             .Cells(k, 6).NumberFormat = "?0.0000"
             .Cells(k, 7).NumberFormat = "?0.0000"
             .Cells(k, 8).NumberFormat = "?0.0000"

            End If

         End With

        k = k + 1
        j = j + 1

        ws.Protect ("12345")

    ElseIf ws.Name = "Émission total" Then
        ws.Unprotect ("12345")

    End If

Next ws
    k = k + 7
    p = k - 1

    ThisWorkbook.Sheets("Émission total").Range("D" & k).Value = "AverageEmission"
    ThisWorkbook.Sheets("Émission total").Range("F" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("F7 : F" & p))
    ThisWorkbook.Sheets("Émission total").Range("G" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("G7 : G" & p))
    ThisWorkbook.Sheets("Émission total").Range("H" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("H7 : H" & p))
    ThisWorkbook.Sheets("Émission total").Range("I" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("I7 : I" & p))
    ThisWorkbook.Sheets("Émission total").Range("J" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("J7 : J" & p))
    ThisWorkbook.Sheets("Émission total").Range("K" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("K7 : K" & p))
    ThisWorkbook.Sheets("Émission total").Range("L" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("L7 : L" & p))
    ThisWorkbook.Sheets("Émission total").Range("M" & k).Value = Application.Average(ThisWorkbook.Sheets("Émission total").Range("M7 : M" & p))

    ThisWorkbook.Sheets("Émission total").Range("F" & k).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("G" & k).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("H" & k).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("I" & k).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("J" & k).NumberFormat = "?0.0000"
    ThisWorkbook.Sheets("Émission total").Range("K" & k).NumberFormat = "?0.0000"
    ThisWorkbook.Sheets("Émission total").Range("L" & k).NumberFormat = "?0.0000"
    ThisWorkbook.Sheets("Émission total").Range("M" & k).NumberFormat = "?0.0000"

    l = k + 2

    ThisWorkbook.Sheets("Émission total").Range("D" & l).Value = "TotalEmission"
    ThisWorkbook.Sheets("Émission total").Range("F" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("F7 : F" & p))
    ThisWorkbook.Sheets("Émission total").Range("G" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("G7 : G" & p))
    ThisWorkbook.Sheets("Émission total").Range("H" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("H7 : H" & p))
    ThisWorkbook.Sheets("Émission total").Range("I" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("I7 : I" & p))
    ThisWorkbook.Sheets("Émission total").Range("J" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("J7 : J" & p))
    ThisWorkbook.Sheets("Émission total").Range("K" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("K7 : K" & p))
    ThisWorkbook.Sheets("Émission total").Range("L" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("L7 : L" & p))
    ThisWorkbook.Sheets("Émission total").Range("M" & l).Value = Application.Sum(ThisWorkbook.Sheets("Émission total").Range("M7 : M" & p))

    ThisWorkbook.Sheets("Émission total").Range("F" & l).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("G" & l).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("H" & l).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("I" & l).NumberFormat = "?0.00"
    ThisWorkbook.Sheets("Émission total").Range("J" & l).NumberFormat = "?0.0000"
    ThisWorkbook.Sheets("Émission total").Range("K" & l).NumberFormat = "?0.0000"
    ThisWorkbook.Sheets("Émission total").Range("L" & l).NumberFormat = "?0.0000"
    ThisWorkbook.Sheets("Émission total").Range("M" & l).NumberFormat = "?0.0000"

ThisWorkbook.Sheets("Émission Total").Protect ("12345")
ThisWorkbook.Protect ("12345")
Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 10278

Answers (1)

tigeravatar
tigeravatar

Reputation: 26660

The problem is this line:

Call Refresh_Emissions.Emission_Total

In that line, you don't provide an argument. But the Emission_Total subroutine requires that a worksheet object be provided as an argument, as shown by its declaration:

Sub Emission_Total(ByVal ws As Worksheet)

It looks like you are calling different subroutines based on the worksheet name, so my guess is you simply want to send the worksheet that matched the call, in which case:

Sub Refresh_Activesheet()

    If IsNumeric(Left(ActiveSheet.Name, 1)) Then
        Emissions_Calculation
    ElseIf ActiveSheet.Name = "Voyages List" Then
        Refresh_Table
    ElseIf ActiveSheet.Name = "Émission Total" Then
        Call Refresh_Emissions.Emission_Total(ActiveSheet)  'Note I added the worksheet as an argument here
    ElseIf ActiveSheet.Name = "Consommation Total" Then
    Exit Sub
End If

End Sub

EDIT: Looking at your posted code some more, it looks like you never actually use the passed worksheet variable in the Emission_Total subroutine. In which case you could just remove the ws variable requirement from the declaration:

Sub Emission_Total()

And then leave your Refresh_Activesheet code as you originally posted.

Upvotes: 1

Related Questions