Counting ticks and determine value

[bknight]

I asked this in the Access forum, but really it belongs in this forum.
Using the routine that Hans suggested I have built code to count ticks between numbers except for .4 and .9 which will always be excluded.

Code: Select all

Function CountTicks(varStart, varEnd)
    Dim I As Long, J As Long
    Dim intCurWhle As Integer, intPrevWhle As Integer
    Dim dblPrevFrc As Double, dblCurFrc As Double, dblBuyP As Double, dblSellP As Double
    Dim R As Long, P As Double, p1 As Double
    R = ActiveSheet.UsedRange.Row
    'varStart = 5
    'varEnd = 6.5
    If Cells(9, 4) = "Buy To Close" Or Cells(9, 4) = "Sell To Close" Then
            P = InStr(Cells(9, 3), "'")
            intCurWhle = Left(Cells(9, 3), P - 1)
            dblCurFrc = Mid(Cells(9, 3), P + 1)
    End If
    If Cells(10, 4) = "Buy To Open" Or Cells(10, 4) = "Sell To Open" Then
            P = InStr(Cells(10, 3), "'")
            intPrevWhle = Left(Cells(10, 3), P - 1)
            dblPrevFrc = Mid(Cells(10, 3), P + 1)
    End If
    If Cells(9, 12) = 46 Or Cells(9, 12) = 47 Then
        dblBuyP = intCurWhle + dblCurFrc / 32
    Else
        dblSellP = intCurWhle + dblCurFrc / 32
    End If
    If Cells(10, 12) = 46 Or Cells(10, 12) = 47 Then
        dblBuyP = intPrevWhle + dblPrevFrc / 32
    Else
        dblSellP = intPrevWhle + dblPrevFrc / 32
    End If
    varStart = dblPrevFrc
    varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc
    If varStart <= varEnd Then
        For J = 10 * varStart + 1 To 10 * varEnd
            If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                    CountTicks = CountTicks + 1
            End If
        Next J
    Else
        For J = 10 * varStart - 1 To 10 * varEnd Step -1
            If J Mod 10 <> 4 And J Mod 10 <> 9 Then
                    CountTicks = CountTicks + 1
            End If
        Next J
    End If
        If dblSellP > dblBuyP Then
            Cells(9, 15) = Abs(Cells(9, 2)) * CountTicks * 7.8125 - Abs(Cells(9, 8)) - Abs(Cells(10, 8))
        Else
            Cells(9, 15) = -Abs(Cells(9, 2)) * CountTicks * 7.8125 - Abs(Cells(9, 8)) - Abs(Cells(10, 8))
        End If
End Function

The code works for the few times I have used it today. My question is what additional coding must be added such that the code runs when a row of data is inserted into row 9 and contains either 47 or 49 in column 12(L). if those two conditions are not met then the code does not run when the data is inserted into row 9.

[snb]

No sample file ?

[bknight]

No sample file ?

Way to big, sorry.

You may create a blank and add these values, though. I will add the data for row 9, repeating in row 10, except where noted
Cell(A,9) ZTUU23
Cell(B,9) 1 Cell(B,10) -1_______
Cell(C,9) 102'05.6 Cell(C,10) 102'2.6_______
Cell(D,9) "Buy To Close" Cell(D,10) "Sell to Open"_______
Cell(E,9) any date
Cell(F,,9) any date
CellG,9) any number
Cell(H,() -4.67
Cell(I,9) -.67
Cell(J,9) anything/nothing
Cell(K,9) anything/nothing
Cell(L,9) 47 Cell(L,10) 48_______
Cell(M,9) anything/nothing
Cell(N,9) anything/nothing

Then run the code.

[bknight]

I do have a Change class(?) modue for the spreadsheet so I thought I'd add a call to the CountTicks function, but when I added CountTicks(varStart, varEnd)at the end of the existing code I received an error: expected "=" what is the proper syntax to call the function?

[snb]

Code: Select all

Sub M_snb()
  Call Post a sample file
End Sub

[bknight]

Sorry I don't understand.

[HansV]

snb hints that he cannot help you without seeing a sample workbook.

[bknight]

He asked before and it is too large, but I can post the code.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TopLeft As Range
    Dim cel As Range
    Dim rng As Range
    Set TopLeft = Range("A2").End(xlDown)
    Set cel = Range("A2").End(xlDown).Offset(0, 3)
    Set rng = Range(TopLeft, TopLeft.Offset(0, 14))
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If cel.Value Like "*Open" Then
            rng.Interior.Color = 13551615
        ElseIf cel.Value Like "*Close" Then
            rng.Interior.ColorIndex = xlAutomatic
            rng.Font.Color = 0
        Else
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        'CountTicks(varStart, varEnd)
End Sub

The call is commented out so that the code runs. Error expected "="

[SpeakEasy]

>it is too large

Can you not just post an extract, rather than the whole thing?

[bknight]

No, minimized is 77K

[HansV]

77 KB is less than the maximum attachment size of 256 KB here.

[bknight]

It used to be 75K? But my memory is faulty I guess.

[bknight]

In compacting for submission the code I presented earlier isn't in and must be added prior to testing, sorry.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TopLeft As Range
    Dim cel As Range
    Dim rng As Range
    Set TopLeft = Range("A2").End(xlDown)
    Set cel = Range("A2").End(xlDown).Offset(0, 3)
    Set rng = Range(TopLeft, TopLeft.Offset(0, 14))
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If cel.Value Like "*Open" Then
            rng.Interior.Color = 13551615
        ElseIf cel.Value Like "*Close" Then
            rng.Interior.ColorIndex = xlAutomatic
            rng.Font.Color = 0
        Else
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        'CountTicks(varStart, varEnd)
End Sub

ETA oops, it is there, have at it.

[HansV]

It used to be 75K? But my memory is faulty I guess.

It has always been 256 KB since Eileen's Lounge was founded in 2010, and also in Woody's Lounge before that...

[HansV]

Since you don't do anything with the result of the function, you must either use

Call CountTicks(vsrStart, varEnd)

or

CountTicks varStart, varEnd

However, there is no need for varStart and varEnd as arguments of the function, since you assign values to them in the code of the function...

[bknight]

Since you don't do anything with the result of the function, you must either use

Call CountTicks(vsrStart, varEnd)

or

CountTicks varStart, varEnd

However, there is no need for varStart and varEnd as arguments of the function, since you assign values to them in the code of the function...

I really don't understand "Since you don't do anything with the result of the function"
The cells interior is set to a specific fill color. But I'll not argue.
CountTicks varStart, varEnd results in sub not defined, but call CountTicks varStart, varEnd, at least compiles and I have a an entry. Ah it worked, but I had to alter my code slightly.
Thanks

[HansV]

Your function CountTicks has 2 arguments varStart and varEnd.
But the procedure that calls CountTicks, i.e. the Worksheet_Change event procedure, does not specify any values for varStart and varEnd.
And in the code of CountTicks, you overwrite any value that you might have assigned to varStart and varEnd with different values, in the lines

Code: Select all

    varStart = dblPrevFrc
    varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc

So varStart and varEnd don't play any role as arguments of the function

[bknight]

The assigning is correct, since it is a variable varStart may not be necessarily need to be assigned but varEnd needs to be. Note the comment preceding. Neither variable is carried to any other function they are just used to calculate a value and then assign that value to cell(9,15). This is/was caused by skipping all .4 and .9. With out that skip it is simply a spreadsheet math solution.

[SpeakEasy]

>The assigning [within the function] is correct

Fine. Hans' point is that you therefore have no need whatsoever to pass them into the function as parameters, since you do not use those passed in parameters.

Furthermore, a function traditionally returns a value to the caller (again, as Hans has pointed out), but you are not then recovering that returned value, let alone then using it for anything. In other words, your function CountTicks is actually being used as Sub.

[bknight]

Hans said "Since you don't do anything with the result of the function, you must either use" and there is a bit of usage of what was meant by the result. The result was intended to assign a value into a cell and it does just that, so in my understanding of don't do anything with results is not equal. Now whether or not it should be a function or sub is defined by tradition, I have never understood the differentiation of the two. Whether you change function to sub the code does the same final result and I'm sorry to say that's my objective.
Now I'm not dismissing any suggestions presented here, but if I don't understand I don't understand. If a suggestion enables whatever the question pertains, I'm fine with it. I believe we have beat tis horse sufficiently.