Troubleshoot an Overflow Error

Function autoCreateTicker goes to the error handler and returns an Overflow error, but I don't understand why as my string variables are small.

[VBA]Option Explicit
Dim lastId As Long
Dim offset As Long

Function autoCreateTicker(symbol, secType, currencyCode, row) As Boolean
Dim server As String
Dim topic As String
Dim id As String
Dim reqType As String
Dim desc As String
Dim logSuccess As Boolean
Dim counter As Integer

' default the user entered fields
Worksheets("Tickers").Range("A" & CStr(row)).value = symbol
Worksheets("Tickers").Range("B" & CStr(row)).value = secType
Worksheets("Tickers").Range("G" & CStr(row)).value = "SMART"
Worksheets("Tickers").Range("I" & CStr(row)).value = currencyCode

' validate
If symbol = "" Or secType = "" Or currencyCode = "" Then
autoCreateTicker = False
Exit Function
End If

' build server
server = Worksheets("Tickers").Range("B5").value
If server = "" Then
autoCreateTicker = False
Exit Function
End If
server = "=" & server
On Error GoTo tik_ErrorHandler ' Returns an "Overflow" error here
' set topic
topic = "tik"

' create and set id
id = makeId()

If Not composeTickerRequest(reqType, desc, symbol, secType, "", "", "", "", "SMART", currencyCode) Then
autoCreateTicker = False
Exit Function
End If

' But spreadsheet seems to work up to this point...

Worksheets("Tickers").Range("K" & CStr(row)).value = server & "|" & topic & "!'" & id & "?" & reqType & "?" & desc & "'"
Worksheets("Tickers").Range("N" & CStr(row)).value = server & "|" & topic & "!" & id & "?bidSize"
Worksheets("Tickers").Range("O" & CStr(row)).value = server & "|" & topic & "!" & id & "?bid"
Worksheets("Tickers").Range("P" & CStr(row)).value = server & "|" & topic & "!" & id & "?ask"
Worksheets("Tickers").Range("Q" & CStr(row)).value = server & "|" & topic & "!" & id & "?askSize"
Worksheets("Tickers").Range("T" & CStr(row)).value = server & "|" & topic & "!" & id & "?last"
Worksheets("Tickers").Range("U" & CStr(row)).value = server & "|" & topic & "!" & id & "?lastSize"
Worksheets("Tickers").Range("X" & CStr(row)).value = server & "|" & topic & "!" & id & "?high"
Worksheets("Tickers").Range("Y" & CStr(row)).value = server & "|" & topic & "!" & id & "?low"
Worksheets("Tickers").Range("Z" & CStr(row)).value = server & "|" & topic & "!" & id & "?volume"
Worksheets("Tickers").Range("AA" & CStr(row)).value = server & "|" & topic & "!" & id & "?close"

logSuccess = logMessage("[autoCreateTicker]", "Ticker created for: " & symbol)

autoCreateTicker = True
Exit Function
tik_ErrorHandler:
autoCreateTicker = False
logSuccess = logMessage("[autoCreateTicker]", "An automated ticker request was attempted, but could not be created for: " & symbol & ". Error description: " & Err.Description)
End Function
[/VBA]

How can the code know the value of desc ?, let alone the value of reqtype (the argument is seqtype).

You problably need a new keyboard ;)

What does the function composeTickerRequest do?

There's a bunch of code here and several objects to explain. About ten years ago, a financial services company called Interactive Brokers (IB) offered their customers an Excel 1997-2003 workbook with VBA 6 macros to auto trade stocks at the front end of their proprietary online application called Trader Workstation (TWS).

I can't include links until I reach five posts but the Interactive Brokers Web site has a workbook file and a PDF document to explain what's going on:
interactivebrokers.com /download /TwsDdeBeginners.xls
interactivebrokers.com /download /ExcelApiBeginners.pdf

This code has become buggy as IB did not maintain it to keep up with Excel and Java/TWS updates so support was halted years ago. You can read some rants about IB and TWS over at the Elite Trader forum. Maybe I need a new keyboard, but I still have the same old fingers and want to keep this Excel API as an order manager. I think it's best to start from the original code and build it back up from scratch until it's where I want it. I appreciate all suggestions.

First compile error appears in Module "ArrayQueries"

[vba]
Function extractRequest(server, topic, reqStr)
' Next line throws Compile error: Wrong number of arguments or invalid property assignment and "extractid" highlighted.
extractRequest = idToRequest(extractid(server, topic, reqStr))
End Function
[/vba]

Second compile error shown below also appears in Module "ArrayQueries" after trying the above change.

[vba]
Function extractid(reqStr)
' Tried the next line to correct the above error but produced a new error shown below in Object "Sheet 12 (Historical Data)".
' Function extractid(server, topic, reqStr)
Dim idStart As Integer, idEnd As Integer
Dim cleanedReq As String
cleanedReq = Replace(reqStr, util.TICK_CHAR, "")
idStart = InStr(cleanedReq, "!id") + 1
idEnd = InStr(cleanedReq, "?")
extractid = Mid(cleanedReq, idStart, idEnd - idStart)
End Function
[/vba]

Third compile error shown below appears in Object "Sheet 12 (Historical Data)" after trying the above change.

[vba]
Sub Worksheet_Calculate()
On Error Resume Next
Dim cellStatus As String, rowMod As Integer
For rowMod = Range(monitorStart).value To Range(monitorEnd).value
cellStatus = Cells(rowMod, controlCol).value
If cellStatus = ArrayQueries.RECEIVED Then
Dim server As String, id As String, request As String, theName As String, TheArray() As Variant
server = util.getServerVal(serverCell)
If server = "" Then Exit Sub
' The next line throws a Compile error: Argument not optional and "extractid" is highlighted
id = ArrayQueries.extractid(Cells(rowMod, controlCol).Formula)
request = ArrayQueries.idToRequest(id)
TheArray = ArrayQueries.doRequest(server, topic, request)
theName = ArrayQueries.composeName(Cells(rowMod, pageCol).value, id, topic)
Call populatePage(theName, theName, TheArray, baseX, baseY, True)
End If
Next rowMod
End Sub
[/vba]

Rather obvious:

you use 3 arguments

Function extractRequest(server, topic, reqStr)
extractRequest = idToRequest(extractid(server, topic, reqStr))
End Function

But the function can only receive 1
Function extractid(reqStr)

Thanks, that solved it. Now I have the "Variable not defined" compile error message in Module "ArrayQueries" shown below. These four instances are the only place where variable ctrY and ctrX appear in the entire project. I suspect I'll have the same variable undefined problem with ctrX.

[vba]
Option Explicit

Public Const FINISHED = "FINISHED"
Public Const SUBSCRIBED = "SUBSCRIBED"
Public Const RECEIVED = "RECEIVED"
Public Const QRESULT = "?result"
Public Const DO_NOT_SPECIFY_WIDTH = -1

Sub testPopulatePage()
Const arHeight = 5
Const arWidth = 8
Dim TheArray2(arHeight, arWidth)
For ctrY = 0 To arHeight - 1 ' Compile error: Variable not defined.
For ctrX = 0 To arWidth - 1 ' probably the same variable not defined error.
TheArray2(ctrY, ctrX) = ctrY * 1000 + ctrX
Next ctrX
Next ctrY
Call populatePage("Stock2", "PopulateMe", TheArray2, 2, 2)
Const arLength = 12
Dim TheArray3(arLength)
For ctr3 = 0 To arLength - 1
TheArray3(ctr3) = ctr3 * ctr3 * ctr3
Next
Call populatePage("Stock2", "PopulateMe2", TheArray3, 2, 20)
End Sub
[/vba]

Remove Option Explicit.

Ignore that very bad 'advice', declare the variables instead.

Option Explicit stays in, variables declared and code compiled OK. Thanks.

Now here is my broken Function autoCreateTicker from Object ExampleUtil, it used to work but I think my new overflow problem is a division by zero error. I make a connection with the server and bring in symbol (stock name), secType (always STK as compared with OPTions or FUTures or FutureOPtions or INDexes or CASH), "SMART" (online application signal to autoselect the least expensive broker, and currencyCode (always USDollars) so I should pass validation.

I should build the server because my user name is still at cell B5 in Object Worksheets("Tickers").Range("B5"). I've commented out the error handler so I think the code fails at the If Not statement.

I thought I was successfully passing Function composeTickerRequest because reqType is always req, Tickersymbol (stock name IBM or whatever) comes from the server ok, secType is always STK, "SMART" is hard coded, and currencyCode is always USD.

May I have a hint how to debug?

[vba]
Option Explicit
Dim lastId As Long
Dim offset As Long
Dim desc As String
Dim order As String

Function autoCreateTicker(symbol, secType, currencyCode, row) As Boolean
Dim server As String
Dim topic As String
Dim id As String
Dim reqType As String
Dim desc As String
Dim logSuccess As Boolean
Dim counter As Integer

' default the user entered fields
Worksheets("Tickers").Range("A" & CStr(row)).value = symbol
Worksheets("Tickers").Range("B" & CStr(row)).value = secType
Worksheets("Tickers").Range("G" & CStr(row)).value = "SMART"
Worksheets("Tickers").Range("I" & CStr(row)).value = currencyCode

' validate
If symbol = "" Or secType = "" Or currencyCode = "" Then
autoCreateTicker = False
Exit Function
End If

' build server
server = Worksheets("Tickers").Range("B5").value
If server = "" Then
autoCreateTicker = False
Exit Function
End If

server = "=" & server

' On Error GoTo tik_ErrorHandler

' set topic
topic = "tik"

' create and set id
id = makeId()

' I think this code blows up on the next line. What to check?

If Not composeTickerRequest(reqType, desc, symbol, secType, "", "", "", "", "SMART", currencyCode) Then
autoCreateTicker = False
Exit Function
End If

Worksheets("Tickers").Range("K" & CStr(row)).value = server & "|" & topic & "!'" & id & "?" & reqType & "?" & desc & "'"
Worksheets("Tickers").Range("N" & CStr(row)).value = server & "|" & topic & "!" & id & "?bidSize"
Worksheets("Tickers").Range("O" & CStr(row)).value = server & "|" & topic & "!" & id & "?bid"
Worksheets("Tickers").Range("P" & CStr(row)).value = server & "|" & topic & "!" & id & "?ask"
Worksheets("Tickers").Range("Q" & CStr(row)).value = server & "|" & topic & "!" & id & "?askSize"
Worksheets("Tickers").Range("T" & CStr(row)).value = server & "|" & topic & "!" & id & "?last"
Worksheets("Tickers").Range("U" & CStr(row)).value = server & "|" & topic & "!" & id & "?lastSize"
Worksheets("Tickers").Range("X" & CStr(row)).value = server & "|" & topic & "!" & id & "?high"
Worksheets("Tickers").Range("Y" & CStr(row)).value = server & "|" & topic & "!" & id & "?low"
Worksheets("Tickers").Range("Z" & CStr(row)).value = server & "|" & topic & "!" & id & "?volume"
Worksheets("Tickers").Range("AA" & CStr(row)).value = server & "|" & topic & "!" & id & "?close"

logSuccess = logMessage("[autoCreateTicker]", "Ticker created for: " & symbol)

autoCreateTicker = True

Exit Function

tik_ErrorHandler:
autoCreateTicker = False
logSuccess = logMessage("[autoCreateTicker]", "An automated ticker request was attempted, but could not be created for: " & symbol & ". Error description: " & Err.Description)

End Function

Function composeTickerRequest(ByRef reqType As String, ByRef desc As String, ByVal symbol As String, _
ByVal secType As String, ByVal expiry As String, ByVal strike As String, ByVal right As String, _
ByVal multiplier As String, ByVal exchange As String, ByVal currencyCode As String) As Boolean

desc = util.cleanUnderscore(symbol) & "_" & secType & "_"

If ((secType = "OPT" Or secType = "FUT" Or secType = "FOP") And expiry = "") Then
reqType = "req2"

Else
reqType = "req"

If secType = "OPT" Or secType = "FUT" Or secType = "FOP" Then
desc = desc & expiry & "_"
End If

If secType = "OPT" Or secType = "FOP" Then
desc = desc & strike & "_" & right & "_"
If multiplier <> "" Then
desc = desc & multiplier & "_"
End If
End If

desc = desc & util.cleanUnderscore(exchange) & "_" & currencyCode

End If

composeTickerRequest = True

End Function
[/vba]

When debugging, comment out On Error lines of VBA code.

In the VBE, in a routine use F8 to step through code one line at a time.

Use Debug.Print to print variable contents to the Immediate window.

Am I getting this from a cell with a non-numeric value? Do I need some validation before executing the line "cellStatus = Cells(rowMod, controlCol).value" ?

[vba]
Option Explicit
Dim genId As Integer

Const reqOffset = 4
Const controlCol = 1
Const pageCol = controlCol + 1
Const activateCol = controlCol + 2
Const baseX = 2
Const baseY = 2
Const topic = "scan"
Const monitorStart = "firstScanRow"
Const monitorEnd = "lastScanRow"
Const serverCell = "scanServer"
Const errorRange = "scanErrorPosition"
Const scanCtrl = "scanCtrl"

Sub Worksheet_Calculate()
Dim rowMod As Integer, cellStatus As String
' On Error Resume Next
For rowMod = Range(monitorStart).value To Range(monitorEnd).value
cellStatus = Cells(rowMod, controlCol).value ' Run-time error '13': Type mismatch error on this line
If cellStatus = ArrayQueries.RECEIVED Then
Dim server As String, id As String, request As String, theName As String, TheArray() As Variant
server = util.getServerVal(serverCell)
If server = "" Then Exit Sub
id = ArrayQueries.extractid(Cells(rowMod, controlCol).Formula)
request = ArrayQueries.idToRequest(id)
TheArray = ArrayQueries.doRequest(server, topic, request)
theName = ArrayQueries.composeName(Cells(rowMod, pageCol).value, id, topic)
Call ArrayQueries.populatePage(theName, theName, TheArray, baseX, baseY, Cells(rowMod, activateCol).value)
End If
Next rowMod
End Sub
[/vba]

Do not use merged cells.
Leave all the declarations for later (& comment out ' option explicit).

[vba]
' option explicit
' Dim genId As Integer
' Dim rowMod As Integer, cellStatus As String
[/vba]

Thank you for the help. Still working in Object Sheet13(Market Scanner) now I get a Compile error: ByRef argument type mismatch on the fifth line of the Sub module. Adding a watch doesn't work until I compile. Am I mismatching variables when querying the server?

[vba]
' Option Explicit
' Dim genId As Integer

Const reqOffset = 4
Const controlCol = 1
Const pageCol = controlCol + 1
Const activateCol = controlCol + 2
Const baseX = 2
Const baseY = 2
Const topic = "scan"
Const monitorStart = "firstScanRow"
Const monitorEnd = "lastScanRow"
Const serverCell = "scanServer"
Const errorRange = "scanErrorPosition"
Const scanCtrl = "scanCtrl"

Sub requestScannerData()
Dim server As String, req As String, reqType As String, id As String
server = util.getServerStr(serverCell)
If server = "" Then Exit Sub
id = util.getIDpost(genId) ' On this line I get a Compile error: ByRef argument type mismatch and genId is highlighted.
reqType = "req"

' specify query
Dim numberOfRows As String, instrument As String, locationCode As String, scanCode As String, _
abovePrice As String, belowPrice As String, aboveVolume As String, _
averageOptionVolumeAbove As String, marketCapAbove As String, marketCapBelow As String, _
moodyRatingAbove As String, moodyRatingBelow As String, _
spRatingAbove As String, spRatingBelow As String, maturityDateAbove As String, _
maturityDateBelow As String, couponRateAbove As String, couponRateBelow As String, _
excludeConvertible As String, scannerSettingPairs As String, stockTypeFilter As String

Dim theRow As Integer
theRow = ActiveCell.row
scanCode = UCase(Cells(theRow, reqOffset + 0).value)
instrument = UCase(Cells(theRow, reqOffset + 1).value)
locationCode = UCase(Cells(theRow, reqOffset + 2).value)
stockTypeFilter = UCase(Cells(theRow, reqOffset + 3).value)
numberOfRows = UCase(Cells(theRow, reqOffset + 4).value)
abovePrice = UCase(Cells(theRow, reqOffset + 5).value)
belowPrice = UCase(Cells(theRow, reqOffset + 6).value)
aboveVolume = UCase(Cells(theRow, reqOffset + 7).value)
averageOptionVolumeAbove = UCase(Cells(theRow, reqOffset + 8).value)
marketCapAbove = UCase(Cells(theRow, reqOffset + 9).value)
marketCapBelow = UCase(Cells(theRow, reqOffset + 10).value)
moodyRatingAbove = UCase(Cells(theRow, reqOffset + 11).value)
moodyRatingBelow = UCase(Cells(theRow, reqOffset + 12).value)
spRatingAbove = UCase(Cells(theRow, reqOffset + 13).value)
spRatingBelow = UCase(Cells(theRow, reqOffset + 14).value)
maturityDateAbove = UCase(Cells(theRow, reqOffset + 15).value)
maturityDateBelow = UCase(Cells(theRow, reqOffset + 16).value)
couponRateAbove = UCase(Cells(theRow, reqOffset + 17).value)
couponRateBelow = UCase(Cells(theRow, reqOffset + 18).value)
excludeConvertible = UCase(Cells(theRow, reqOffset + 19).value)
scannerSettingPairs = UCase(Cells(theRow, reqOffset + 20).value)

If instrument = "" Or locationCode = "" Or scanCode = "" Then
MsgBox "You must enter all of scanCode, locationCode, and instrument"
Exit Sub
End If

req = util.cleanUnderscore(scanCode) & util.UNDERSCORE & instrument & util.UNDERSCORE & _
locationCode & util.UNDERSCORE & util.orEmpty(stockTypeFilter) & util.UNDERSCORE & _
util.orEmpty(numberOfRows) & util.UNDERSCORE & util.orEmpty(abovePrice) & util.UNDERSCORE & _
util.orEmpty(belowPrice) & util.UNDERSCORE & util.orEmpty(aboveVolume) & util.UNDERSCORE & _
util.orEmpty(averageOptionVolumeAbove) & util.UNDERSCORE & util.orEmpty(marketCapAbove) & util.UNDERSCORE & _
util.orEmpty(marketCapBelow) & util.UNDERSCORE & util.orEmpty(moodyRatingAbove) & util.UNDERSCORE & _
util.orEmpty(moodyRatingBelow) & util.UNDERSCORE & util.orEmpty(spRatingAbove) & util.UNDERSCORE & _
util.orEmpty(spRatingBelow) & util.UNDERSCORE & util.orEmpty(maturityDateAbove) & util.UNDERSCORE & _
util.orEmpty(maturityDateBelow) & util.UNDERSCORE & util.orEmpty(couponRateAbove) & util.UNDERSCORE & _
util.orEmpty(couponRateBelow) & util.UNDERSCORE & util.orEmpty(excludeConvertible) & util.UNDERSCORE & _
util.orEmpty(scannerSettingPairs)

' Place req in spreadsheet
Cells(theRow, controlCol).Formula = util.composeControlLink(server, topic, id, reqType, req)
ActiveCell.offset(1, 0).Activate ' move cursor down
End Sub
[/vba]

As far as I can see 'genId' has no value.

Don't remove Option Explicit, and uncomment the declaration of genId as Integer since I suspect that's the type your routine is expecting, not a Variant

Got it, Option Explicit and genId declaration are back in and the Project compiles. Thanks.

Moving on to Module ExampleUtil I get a Run-time error '6': Overflow. Would I lose the Option Explicit here or change the data type for id from Long to Double?

[vba]
Option Explicit

Dim lastId As Long
Dim offset As Long
Dim desc As String
Dim order As String

Function makeId() As String
Dim id As Long
id = (Date - 39000) * 1000000 + (Time * 1000000) ' this line is highlighted and throws the error when I step in.
If id > lastId Then
offset = 0
lastId = id
Else
offset = offset + 1
End If
makeId = "id" & (id + offset)
End Function
[/vba]

id and lastId both need to be Double

I got the idea to make the result smaller instead of making the data type larger. Changing from 39000 to 41200 moved the baseline to a more recent date in Excel and cured the overflow error. Everything's running ok now, we'll see how it goes tomorrow when the market opens. If it holds together, I can mark this post as solved.