IB Excel DDE VBA question

Well, I am pretty sure all mistakes happen when I open up the excel workbook, and I answer the opening popup question by pressing "Update" (DDE links). These links are the IB links in cells in the form of "=userid|tik!id2?last" for example. These are links to numbers, such as last price, bid, ask, high, low, volume.

Then, in my vba code, I define those values I got from the DDE links as "dim last_price as double", "dim bid as double" and let's say "last_price = cells(1,1)", "bid = cells(1,2)".

Now, whether I define these values as "double" or "integer" the errors I get are just in the same amount.

I wonder what exactly you are saying about getting DDE links as "strings". It sounds like it could be the problem, yet aren't the DDE values just what they look like? In other words, if I get a price from the DDE link, isn't that automatically an integer or a double? Are you saying that "=userid|tik!id2?last" for example could be perceived as a string by vba, even though it produces a number in my sheet cell?
 
:-) - I would overwrite the dde links with values , and then point them to the required dde links when the workbook is fully open ... as initailly the dde links will return n/a#.... I have this with quite a few of my applications that trigger on 'Calculation' events.
 
First, what you should be doing when you have a problem like this is to use the debugger to find out what the variable looks like. Without knowing what they are everything else is just guess work.

Several possible solutions: With DDE before the data connection starts the values may be #N/A, so if your code is called early enough these errors will be read by the function. You can test using the IsNA boolean function and exit the function when any of the variable tested returns True.

Similarly you can test each variable with the IsNumeric function. If any test false exit the function.

You can also declare your variable as Variants but I'm not sure if an error is a variant -- I think not. Code runs slower with variants but you probable won't notice the difference.
 
Hi, all your advice was great. I used all of it to come up with an answer. But some of you were right on target.

Also one guy, Alex, at the IB discussion board was right:
http://www.interactivebrokers.com/cgi-bin/discus/board-auth.pl?lm=1161176853&file=/2/38381.html

The answer to my problem is simply this:

=IF(ISERROR(Tickers!N45),0,Tickers!N45)


Nothing more was needed to get rid of my 36 type mismatch errors. Just a few more lines I had to add to the code on my sheet. Nothing needed at the vba level.

I wonder how tiring on my system and on my excel sheet are these lines, where, in order to get rid of the initial startup error (which caused no harm to the functioning of my system), I substituted for a simple

=Tickers!N45

a certainly more tiring

=IF(ISERROR(Tickers!N45),0,Tickers!N45)

Needless to say I had to repeat this for all the data I am using, such as bid price, ask price, last price, volume...

However, if the system can stand it, then I guess it means it's not too tiring to have to calculate a few extra functions (even though it does so continuously, because price updates all the time).
 
One potential problem: if you are using the data in some VBA function, sometimes you are using 0, other times the real data. You might need a way to know that your sub or function is using incomplete data.
 
Well, no, I don't see why you are saying this.

First of all, I had to change the "0" to "1", so I don't get occasional division by zero problems.

Concerning your objection, I only get the "1" value (previous "0") at the start, in the first half a second after pressing "update" and starting the DDE. For the rest of the day I just get the DDE values from TWS.

One problem I did get today, with these new functions, is that I get a few Errors 6 - Overflow - but just at the start, and they don't hurt the functioning of the system. I know I should and will address this problem as well, but for now I have solved enough problems, so thanks a lot to everyone.
 
the iserror formula you posted is doing the same thing as "on error resume next" in vba.

Quote from travis:

Hi, all your advice was great. I used all of it to come up with an answer. But some of you were right on target.

Also one guy, Alex, at the IB discussion board was right:
http://www.interactivebrokers.com/cgi-bin/discus/board-auth.pl?lm=1161176853&file=/2/38381.html

The answer to my problem is simply this:

=IF(ISERROR(Tickers!N45),0,Tickers!N45)


Nothing more was needed to get rid of my 36 type mismatch errors. Just a few more lines I had to add to the code on my sheet. Nothing needed at the vba level.

I wonder how tiring on my system and on my excel sheet are these lines, where, in order to get rid of the initial startup error (which caused no harm to the functioning of my system), I substituted for a simple

=Tickers!N45

a certainly more tiring

=IF(ISERROR(Tickers!N45),0,Tickers!N45)

Needless to say I had to repeat this for all the data I am using, such as bid price, ask price, last price, volume...

However, if the system can stand it, then I guess it means it's not too tiring to have to calculate a few extra functions (even though it does so continuously, because price updates all the time).
More...
 
Well, good point, but not exactly in my opinion (and obviously), even though it has similarities. I don't fully understand all the implications of the differences. Among them, the fact that if I place "iserror" on a cell it will only affect that cell, which in turn allowed me to understand where the error was coming from. Another difference is that my "on error goto errorhandler" writes a report of all errors, and the function doesn't, but yes, I agree that it has a similar use in some cases.
 
i presumed that you were unhappy with "on error resume next " because you wanted to fix the error instead of just cosmetically hiding it. the iserror + if statement formula you posted is also just cosmetically hiding it.

this should be fine unless there are so many errors that it's slowing the spreadsheet down.
 
The "#N/A" values that will show in Excel prior to the data connection with TWS being established will only show up for a couple of seconds....why not hold off on running any code until the "#N/A"'s have all been replaced by data?

You can use IsError within the spreadsheet as you have mentioned...you can also use it within your VBA code, which is what I do.
 
You must log in or register to reply here.
Back
Top