Hello, I am working on the Order Entry Tool in excel and I need to add in a locate broker field t...

mjc

Hello, I am working on the Order Entry Tool in excel and I need to add in a locate broker field that is not already in the template, any ideas on how to do that?

zoya faberov

Hello @mjc,

Please check that you have, on your worksheet, defined column "N" and in the column, in every populated row, there are valid values that are being read.

One option is to, temporarily, remove all the rows, except for one and test your update to the Sun SendOrder with that single row. Once the updated Sub SendOrder works to your satisfaction, add the rows back.

zoya faberov

Hello @mjc,

When you "view code" for Order Entry example, observe how Order object is populated in Sub SendOrder().

1. To your sheet add (as the last column) a new column for LocateBroker values, for example, column "N"

2. Absorb the value from the new column into your created Order as part of SendOrder sub:

Side = Worksheets("REDIEquityEntry").Cells(I, "E").Value
Quantity = Worksheets("REDIEquityEntry").Cells(I, "F").Value
...
LocateBroker = Worksheets("REDIEquityEntry").Cells(I, "N").Value  
...
hOrder.LocateBroker = LocateBroker

Does this work as you expect?

mjc

Hello,

I did the above and i am getting a sub script out of range. the code for everything is below. I only added what you said before.

Sub SendOrder()
Dim I As Integer
Dim endRow As Integer
endRow = GetLastRow("REDIEquityEntry", "G")
For I = 3 To endRow
' Check if order was alread submitted
Dim Exchange, Side, Quantity, Symbol, Price, PriceType, TIF, Account, Status, LocateBroker
Side = Worksheets("REDIEquityEntry").Cells(I, "E").Value
Quantity = Worksheets("REDIEquityEntry").Cells(I, "F").Value
Symbol = UCase(Worksheets("REDIEquityEntry").Cells(I, "G").Value)
Exchange = Worksheets("REDIEquityEntry").Cells(I, "H").Value
PriceType = Worksheets("REDIEquityEntry").Cells(I, "I").Value
Price = Worksheets("REDIEquityEntry").Cells(I, "J").Value
TIF = Worksheets("REDIEquityEntry").Cells(I, "K").Value
Account = Worksheets("REDIEquityEntry").Cells(I, "L").Value
Status = Worksheets("REDIEquityEntry").Cells(I, "M").Value
LocateBroker = Worksheets("REDIEqityEntry").Cells(I, "N").Value
' Check if doneaway trade can be entered
Dim IsOrderGood As Boolean: IsOrderGood = True
Dim MSG As String: MSG = ""
Dim lvl As String: lvl = ""
If Symbol = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing Symbol."
Exit For
End If
If Exchange = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing Destination. "
End If
If Side = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = "missing Side. "
End If
If Quantity = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing Quantity. "
End If
If PriceType = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing Price. "
ElseIf PriceType = "Limit" Then
If Price = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing Price. "
End If
End If
If TIF = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing TIF. "
End If
If Account = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "missing Account. "
End If
If Not Status = "" Then
IsOrderGood = False: lvl = "ERROR": MSG = MSG & "This trade was already submitted. "
End If
Debug.Print IsOrderGood & "--" & I
If Not IsOrderGood = False Then
Dim errMsg As Variant
Dim rtnVal As Variant
Dim ordInf As String
Dim RetVal As Variant
Dim RetVal1 As Variant
Dim vName As Variant
Dim vValue As Variant
Dim vType As Variant
Dim cnt As Variant
Dim J As Integer
Dim hOrder As New Order
hOrder.Side = Side
hOrder.Quantity = Quantity
hOrder.Symbol = Symbol
hOrder.Exchange = Exchange
hOrder.PriceType = PriceType
hOrder.Price = Price
hOrder.TIF = TIF
hOrder.Account = Account
hOrder.Warning = False
hOrder.Ticket = "Bypass"
hOrder.LocateBroker = LocateBroker
RetVal = hOrder.GetMBFieldCount(cnt)
'MsgBox cnt
If cnt = 0 Then
Worksheets("REDIEquityEntry").Cells(I, "N").Value = "No Additional Parameters required"
Else
For J = 0 To cnt - 1
RetVal = hOrder.GetMBFieldX(J, vName, vValue, vType)
RetVal1 = hOrder.SetNewVariable("(MB) " + vName, CStr(Worksheets("REDIEquityEntry").Cells(I, 14 + J).Value))
Next J