how to automatically record markets in a quick list

[cjones198]

So I have saved a new module and stuck only the code for getTicks function in there

and trying to type this in but does not work

If Abs(getTicks(Price,Sheets("Market").Cells(5, 15).Value) > 2 Then


Sheets("Selection").Cells(10, iCol).Value = Sheets("Market").Cells(5, 15).Value
Sheets("Selection").Cells(11, iCol).Value = Sheets("Market").Cells(5, 16).Value
Price = Sheets("Market").Cells(5, 15).Value
iCol = iCol + 1
End If

[Captain Sensible]

It's missing out a bracket

Abs(getTicks(Price,Sheets("Market").Cells(5, 15).Value) )>2

[cjones198]

do i need to declare getTicks?

my exact process is

create new module - insert getTicks function only in to it

write into code (event change worksheet "Market")

If Abs(getTicks(Price, Sheets("Market").Cells(5, 15).Value)) > 2 Then


Sheets("Selection").Cells(10, iCol).Value = Sheets("Market").Cells(5, 15).Value
Sheets("Selection").Cells(11, iCol).Value = Sheets("Market").Cells(5, 16).Value
Price = Sheets("Market").Cells(5, 15).Value
iCol = iCol + 1
End If

when i go to open my sheet in gruss BA it wont even load market page

[Captain Sensible]

getTicks is a function that was with all the other tick functions posted on page 2. It doesn't need to be declared or put into a separate module, it's best to keep all your functions in one module as they'll be easier to find if they need amending.

I've no idea why BA won't load the market sheet, sometimes it may be due to an error on the sheet that's 'frozen' the VBA. It's worth having a Sub routine to reset your sheet, something like below, that way you can simply run it as a macro to reset any events , variables etc

Code: Select all

Sub reset()

  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  End
End Sub

If there's an error on your sheet VBA will generally highlight that line to show you the error or if variables etc are not present. That's one of the reasons why it's generally better to split code into sub routines and call them separately and debug them rather than one long chunk of code.

[cjones198]

thanks captain will have a try of this later

[cjones198]

It could because I've got 2 modules onthe sheet
1 with all the code in it you sent
And another with just getTixks

[cjones198]

It says ByRef argument type mismatch error

and highlights "Price" within formula

If Abs(getTicks(Price, Sheets("Market").Cells(5, 15).Value)) > 2 Then

[Captain Sensible]

Where have you declared Price, and what have you declared it as?

It's basically saying you're trying to do a calculation on the wrong type of variable like multiplying a string with a number. It should be declared as Currency or you can force it to currency using CCur(Price) but best to declare it as currency to start with.

[cjones198]

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
Static Price As Variant
Static iCol As Variant
Static switchMarket As Boolean
Static getTicks As Variant
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

and now nothing happens at all lol

[Captain Sensible]

As I mentioned earlier getTicks is a function not a variable so shouldn't be declared, Price needs to be declared as currency i.e. it's a number 2.54 etc a variant can be anything.

If your getTicks code is within Worksheet_Change Price will be available to it, if not Price needs to be a Public variable so every bit of code can access it.

Static Price As Currency

When you say nothing happens I don't know what that means, BA doesn't send data to the sheet?

[cjones198]

yes BA doesnt send data to the sheet

[Captain Sensible]

Run the reset macro that was posted above

[cjones198]

where do i stick Price then? at present its on sheet Market as static

[cjones198]

nothing highlights when i run the reset macro

[Captain Sensible]

Static variables retain their value within that section of code, if you're not going to use sub routines and your getTicks routine is also within the same set of code it's fine withing the Worksheet_Change coding but should be declared as Currency i.e. Static Price As Currency.

If you will be using the Price variable elsewhere or in other routines you would be much better off declaring it as Public at the top of a module like so. It would also be a wise idea to have Option Explicit at the top of all your sheets as that will warn you of undeclared variables

Code: Select all

Option Explicit
Public Price As Currency

Sub reset()

  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  End
End Sub