What Parameters do i need?

[DoosanRuss]

Good Afternoon All

I'm looking for a Worksheet Change_Event to fire the Function below if i change the Cell 'M6', I've tried several variations, but nothing is making the CableSizer Function fire...help
.xls located at google docs, link at bottom of page

Worksheet Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M6")) Is Nothing Then
BooleanMyResult = CableSizer(Val(Volts) = 0, Val(length) = 0, Val(Current) = 0)
End If
End Sub

Function: CableSizer

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 K3 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

Where 'M6' on 'POWER CABLE LIST' defines a backup cable length if Cable Length = 0
File Located here: https://docs.google.com/leaf?id=0B9...ZjJjMi00ZDM5LThkNGUtOGYyZTQ0YmU1ZmU3&hl=en_US

 

[Ruddles]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M6")) Is Nothing Then
BooleanMyResult = CableSizer(Val(Volts) = 0, Val(length) = 0, Val(Current) = 0)
End If
End Sub

If Not Intersect(Target, Range("M6")) Is Nothing, Shirley?

 

[DoosanRuss]

If Not Intersect(Target, Range("M6")) Is Nothing, Shirley?

Shirley? haha love it

But no, changed it to Not and it still doesn't want to fire my CableSizer Function

cheers

Russ

 

[Jonmo1]

It probably is firing, just not doing what you think it's doing...

try

BooleanMyResult = CableSizer(0,0,0)

 

[DoosanRuss]

Your probably Right jonmo1

this is what I've got in my spreadsheet in L10 (my function): =CableSizer(J10,K10,O10)

Where
J10 = Volts
K10 = Cable Length
O10 = Current

CableSizer works just fine if i have values (> 0) in column K and it even works if i have a Min Cable Length in M6 and column K = 0

But if i change the value in M6 the Function in column L where column K = 0 does nothing.

Might just sack it off and take the min value out or lookup to it with a list validation, probably not worth the hassle.

Sorry ladies and gents.

 

[Jonmo1]

ok, so you have the function in a cell (L10)
=CableSizer(J10,K10,O10)

I don't understand the relationship between M6 and that function?
Why would that function change depending on the value in M6?
It doesn't refer to M6 at all..

 

[DoosanRuss]

ok, so you have the function in a cell (L10)
=CableSizer(J10,K10,O10)

I don't understand the relationship between M6 and that function?
Why would that function change depending on the value in M6?
It doesn't refer to M6 at all..

If Column K equals (=) 0 then it looks at M6 to get the min cable length

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

' If Length is blank or zero, assume length is as defined K3 m.
If Val(length) = 0 Then
length = Worksheets("POWER CABLE LIST").Range("M6")
End If

 

[Jonmo1]

OK, I see.

Refer to M6 directly in the function on your sheet.

Change
Function CableSizer(Volts, length, Current)
to
Function CableSizer(Volts, length, Current, length2)

Then

If Val(length) = 0 Then length = length2


Now in your cell L10, put
=CableSizer(J10,K10,O10,'POWER CABLE LIST'!$M$6)

 

[DoosanRuss]

OK, I see.
Refer to M6 directly in the function on your sheet.
Change
Function CableSizer(Volts, length, Current)
to
Function CableSizer(Volts, length, Current, length2)
Then
If Val(length) = 0 Then length = length2
Now in your cell L10, put
=CableSizer(J10,K10,O10,'POWER CABLE LIST'!$M$6)

Thanks Jonmo, Code works a treat, the only niggle i had was forgetting to put length2 statement in a block, but the old F1 (help) button came in there!
last code snippet to finish out the thread

As Jonmo said changed the Function to:

Function CableSizer(Volts, length, Current, length2)

and this segment of the Code

' If Length is blank or zero, assume length is as defined in POWER CABLE LIST M6 m.
    If Val(length) = 0 Then
        length = length2
    End If

Case Closed, thankyou Jonmo, really appreciated.

 

[Jonmo1]

Glad to help, thanks for the feedback.