Compile Error: Argument not optional

[DoosanRuss]

Good Morning, Afternoon, Night

I'm back on VBA in Excel and I'm trying to get a change-event to fire off a Function set in Module1
Code for Function:

Function CableSizer(Volts, length, Current)
    ' If Volts is zero then exit.
    If Val(Volts) = 0 Then
        Exit Function
    End If
 
    ' If Length is blank or zero, assume length is as defined M6 m.
    If Val(length) = 0 Then
        length = Worksheets("POWER CABLE LIST").Range("M6")
    End If
 
    ' Line Current is zero then exit.
    If Val(Current) = 0 Then
        Exit Function
    End If
 
    'Look up tables 4E2a and 4E2B to establish minimum acceptable
    'cable size'
    '   I           Line current
    '   L           Cable Length
    '   V           Volts
    '   CableSize   minimum acceptable cable size
    '   Rating      rating from Table 4E2B for minimum acceptable cable size
    '   Limit       Voltage drop for minimum acceptable cable size
    '   ListRow     Current item in Cable List
    '   incr        Number of time we have stepped up table 4E2B
    Call Lookup(Val(Current), Val(length), Val(Volts), Size, Rating, VoltageDrop, ListRow, incr)
    CableSizer = Size
End Function
Sub Lookup(I, L, V, CableSize, Rating, VoltageDrop, ListRow, incr)
    Dim OK As Boolean
 
    With ActiveWorkbook.Worksheets("Electrical Data")
 
        LastRow = 20
        ' Set Rowfound to the smallest cable size available (ie at row 7)
        RowFound = 5
 
        ' Work from largest to smallest cable size in table 4E2A
        ' Until rating is acceptable.
 
        For Row = LastRow To 5 Step -1
            'Test to see if we have actually gone down too far
            ' NOTE - if this test fails for all cables then
            '        RowFound will remain equal to 7 (ie the smallest
            '        available cable)
            If I > .Cells(Row, 2) Then  'This cable is too small
                ' The smallest acceptable cable is the next size up.
                RowFound = Row + 1
                Exit For
            End If
        Next Row
 
        OK = False
        incr = 0
        'Starting at the smallest acceptable cable found from Table 4E2A,
        ' Check from Table 4E2B that voltage drop is less than 2.5 % of Voltage.
        ' If not acceptable then try next size up and so on.
        For Row = RowFound To LastRow
            Rating = .Cells(Row, 3)                 'Voltage drop per amp per meter (mV)
            VoltageDrop = I * L * Rating / 1000#    'Calculate voltage drop for this cable
            If VoltageDrop < V * 0.025 Then
                OK = True                           ' Voltage drop acceptable
                Exit For                            ' Stop searching
            End If
            incr = incr + 1                         ' Voltage drop too high update search counter
        Next Row                                    ' Continue searching
 
        If Not OK Then
                '   No cable is acceptable - probably due to data error
                '   Issue error message
                CableSize = 0
                Rating = 0
                VoltageDrop = 0
                OK = True
        End If
 
    CableSize = .Cells(Row, 1)
    End With
 
End Sub

I've placed the Change_Event code in the "This Workbook" and I'm trying to Call the Function: "CableSizer" See above
Code for Change Event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldVal
If Range("M6").Value <> OldVal Then
Call Module1.CableSizer
OldVal = Range("M6").Value
End If
End Sub

But i keep getting a Compile Error: Argument not optional on Call Module1.CableSizer

Any help as always will be gratefully recieved.

Note: Not really up for changing the function as it works, but any tips will be heard as advice is free.

BR

Russ

 

[nightcrawler23]

Your cablesizer function has arguments as input. You need to provide these arguments Volts, length, Current

also Try removeing Module1

 

[DoosanRuss]

cheers for quick response

Call now says: Call CableSizer

And Am still getting the Compile Error

 

[CharlesChuckieCharles]

CableSizer(Volts, length, Current) has parameters

Call Module1.CableSizer

Call is for sub routines

should be BooleanMyResult= CableSizer( the parameters.....

Static oldval is suspect especially where it is maybe put in the declaration section of module1

PUBLIC Oldvalue as variant

 

[DoosanRuss]

Cheers for the Help So I've changed the code to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Intersect(Target, Range("M6")) Is Nothing Then
  Exit Sub
 Else
BooleanMyResult = CableSizer(Volts, length, Current)
End If
End Sub

And at least I'm not getting the error message so thats all good, my only problem is, the Function CableSizer isn't firing if i change M6

 

[CharlesChuckieCharles]

Cheers for the Help So I've changed the code to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Intersect(Target, Range("M6")) Is Nothing Then
  Exit Sub
 Else
BooleanMyResult = CableSizer(Volts, length, Current)
End If
End Sub

And at least I'm not getting the error message so thats all good, my only problem is, the Function CableSizer isn't firing if i change M6

where is the Variable VOLTS updated I would expect to see a value there or a variable containing a value

what is happening to M6 and what does it mean? is that wheer a volt value changes if so CableSizer(Volts, length, Current) would be CableSizer(Range("M6").Value, length, Current) or something THE SAME GOES FOR length, Current)

 

[DoosanRuss]

the Volts are updated on the "POWER CABLE LIST" Sheet

https://docs.google.com/leaf?id=0B9...NjA4Mi00YWFlLWE0YzItNjdmZGRkNzlhOTMy&hl=en_US

highlighted by blue box

have i understood your question?

 

[DoosanRuss]

where is the Variable VOLTS updated I would expect to see a value there or a variable containing a value

what is happening to M6 and what does it mean? is that wheer a volt value changes if so CableSizer(Volts, length, Current) would be CableSizer(Range("M6").Value, length, Current) or something THE SAME GOES FOR length, Current)

Sorry, M6 is where we define a nominal cable length if no cable length is defined by the Engineerign team, like a back-up.

I'll give the latest suggestion a stab

Thanks for your help so far CCC

 

[DoosanRuss]

where is the Variable VOLTS updated I would expect to see a value there or a variable containing a value

what is happening to M6 and what does it mean? is that wheer a volt value changes if so CableSizer(Volts, length, Current) would be CableSizer(Range("M6").Value, length, Current) or something THE SAME GOES FOR length, Current)

Location of File:
https://docs.google.com/leaf?id=0B9...ZjJjMi00ZDM5LThkNGUtOGYyZTQ0YmU1ZmU3&hl=en_US