IB Excel DDE VBA question

m4a1, please read what libertine (and others before him) says, because he has understood exactly what I am talking about. We're not talking about a mistake that "I want to hide", in fact, it's not even a "mistake", as you can tell by libertine's explanation. It doesn't even ever re-appear during the whole day, and it's only caused by the starting of DDE. Maybe you are one step further and are referring to something else, or maybe you use different platforms and you don't know precisely what we are dealing with.

Now, to libertine. I would like to get rid of this (small) problem by using the "Iserror" function in vba rather than on the excel sheet, because it seems to be that this function in the same cells as the DDE links will get calculated each time these cells get updated (even though this doesn't seem to be tiring for excel), but I don't know how to write the code needed in vba.

Also, it would make even more sense to slow down, as you suggested, the execution of macros until those DDE data are in. The problem is once again I don't know how to.

On my workbook for some reason, to make it work correctly, I have to call two times the DDE links referring to TWS. The mistakes are recorded (and probably happen) only on the first time I call the links with "request market data". I don't know how I could write some code to stop everything else while calling those links.

Or maybe I do know. On mistake go to handler...then handler tells the program to ignore the mistake if the cell returns "not available" or other problems. At this point it might be easier to just let it write all mistakes and not worry about them. Or if I could find out how to stop execution of code...

...wait I could simply write "if those cells with the DDE links are N/A then exit sub", ok, I will try it when I get home and see if it works.
 
I agree with those who are saying that what you are doing now is no different from the On Error Resume Next solution. Also by not posting ANY code you're kind of making us guess about what you are up to. You don't have to post the holy grail part; just say "Execute magic code here" but show what the code that refers to the DDE cell is doing. . . .

Now, referring to the problem of calling IB twice, what you might be able to do is add a do-loop in which you continue to request market data until the error condition ceases. Then you can continue with the magic formula.
 
Got it! No errors at all of any kind (not even "overflow") if you insert this line in the errorhandler, referring to the cells being fed live data from TWS, via the Excel DDE method. This code makes sure the variable type is a double, or else exits sub:

If VarType(Cells(2, 3)) <> 5 Or VarType(Cells(2, 4)) <> 5 Or VarType(Cells(3, 3)) <> 5 Or VarType(Cells(3, 4)) <> 5 Or VarType(Cells(2, 6)) <> 5 Or VarType(Cells(5, 6)) <> 5 Or VarType(Cells(5, 7)) <> 5 Then Exit Sub


Thanks a lot to libertine and to everyone else. It works perfectly. I really don't understand those people saying this doesn't solve the problem, but "hides" it. Yes, it does, of course, but it solves the problem because there is no real problem other than the error message - it's an issue related to DDE links, and there's nothing else that can be solved. I cannot change the nature of Excel DDE. No actual mistakes were being made to begin with.

I am completely satisfied with this solution, and I thank everyone, even the critical ones, because they all helped.
 
hey travis. as far as vba is concerned there are errors. the error is that you are feeding a string "#NA" into a double variable. your solution is telling vba this: "don't report the errors to me." that's why you don't see the error messages anymore. but they are still there.

in this case since the #NAs only shows up once, your solution is fine if you're satisfied.

there are several ways to fix the problem properly. the best way depends on how your code is laid out. one way is to find the first point of contact and filter out strings.
 
Quote from travis:

Regretfully, I don't know exactly where in the sub procedure these 36 "type mismatch" errors are coming from. And this is the most important procedure in my system, so I am not too happy about posting it here, or sending it out. However, thanks to everyone for their willingness to help me.
More...

If you are checking the order status in your code using an IF function, the error can come from this cell. If so, declare a string variable and assign to this variable the "cell status", and then do the IF statement.

Regards,

Hihi.
 
Ok, yes, you are right, this seems to solve my problem even better. So I should use something at the start of my procedure (where it first makes contact) saying:

If VarType(Cells(2, 3)) = 8 ... Then Exit Sub

It seems it should work, and it precedes the error (it doesn't just stop the error from being reported), as you were stating from the start (my apologies - you were indeed one step further), except now the program has to work more, because it doesn't just work on this stuff when it gets the error, but it has to process one more line of code every time it goes through the procedure. Before it just worried about it if it got an error.

No matter where you place your line that filters out string variables, the program will have to process it each time. Instead, if you only use a line that gets processed once the error has happened (a line saying if it's this kind of error, don't worry about it), then the program will have to read one less line as long as everything runs smoothly. Sorry if I repeat over and over the same things, but I am explaining them to myself. Thanks for all your explanations and patience.

Thanks to hihi for the idea, but I don't think that's the case with my code.
 
You must log in or register to reply here.
Back
Top