Headache Producing Question Here. Please help anyway!

Tom Schreiner · Apr 3rd, 2002, 12:36 AM

Hi
Ivan helped me out on this about a week ago by telling me that a DDE link may not fire off the change event.
Solution:
Link to a formula. Use the calculation event instead. Works fine!

I will try and encapsulate this as best as I am able.

Am writing a trade tracking spreadsheet which tracks changes in two values:
Price and volume

However, I do not have access to the DDE link for the fellow I am creating the spreadsheet for.
Instead I created a standalone VB program which throws random prices and volumes into the spreadsheet via Ole.
Really, my main question is this:
Does Excel respond to Ole the same way it responds to a DDE link?

My problem is repeating values

Take these two seperate transactions:

Time Price Volume
01:13 5420.5 40
01:15 5420.5 40

The program will need to accumulate the volume for the same price.

The spreadsheet would save this as:

5420.5 80

The problem is this:
The calculation event is not fired if the same value replaces that which is in the cell. However, the change event does fire. Even when replaced with the same value.
If a DDE link will not fire off the change event, how do I capture the same volume?

Any help would be greatly appreciated!
I do hate failing!
Thanks!
Tom

Dave Hawley · Apr 3rd, 2002, 12:43 AM

Hi Tom

If I have followed you correctly (not sure I have) could you simply create a link to this cell on the same sheet and then use the Calculate Event. For example if the cell that is having the data dropped in is A1, in cell Z1 put =A1 this will cause the Calculate Event to fire.

Tom Schreiner · Apr 3rd, 2002, 01:11 AM

Hi Dave!
Thanks for the reply.
You did follow me correctly, to a point.

The calculation event does indeed fire if the value fed via DDE is different from the value which is currently in the cell.

The calculation event does not seem to fire if there is no calculation. I'm overwhelmed with my brilliance> HA HA

Problem = this:

If a DDE link does not fire off a change event and the value of volume is the same as the previous transaction. I am stuck???

Time Price Volume
01:13 5420.5 40
01:15 5420.5 40

These are two different transactions.
These are actually two separate volumes.
They just happen to contain the same value.
If the same value = no calculation fired...
...and DDE does not fire the change event, how do I trap the second value?

?????????HEADACHE????????????

Cerebral Circular Reference!!!!!

And I'm doing this for free.
I deserve a headache!
Thanks for any help!
Tom

Dave Hawley · Apr 3rd, 2002, 01:18 AM

Hi Tom

RE: If a DDE link does not fire off a change event and the value of volume is the same as the previous transaction. I am stuck???

Why can't you use the method I suggested, as this WILL fire the Calculation Event, If the value is the same or not.

Tom Schreiner · Apr 3rd, 2002, 01:21 AM

Hi Dave
That is the method I am using =A1 ect...
I placed a break in the calculation event and no code was run when the value was replaced with the same value?
Thanks
Tom

Dave Hawley · Apr 3rd, 2002, 01:36 AM

Sorry Tom, I should have tested that first. The way around this could be to reference it via a Volatile function, eg:
=OFFSET(A1,1,1)

Tom Schreiner · Apr 3rd, 2002, 01:54 AM

Thanks Dave.
I'll give it a try.
Volatile Function?
Will educate myself on this one.
Thanks!
Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 12:36 AM	#1
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Ivan helped me out on this about a week ago by telling me that a DDE link may not fire off the change event. Solution: Link to a formula. Use the calculation event instead. Works fine! I will try and encapsulate this as best as I am able. Am writing a trade tracking spreadsheet which tracks changes in two values: Price and volume However, I do not have access to the DDE link for the fellow I am creating the spreadsheet for. Instead I created a standalone VB program which throws random prices and volumes into the spreadsheet via Ole. Really, my main question is this: Does Excel respond to Ole the same way it responds to a DDE link? My problem is repeating values Take these two seperate transactions: Time Price Volume 01:13 5420.5 40 01:15 5420.5 40 The program will need to accumulate the volume for the same price. The spreadsheet would save this as: 5420.5 80 The problem is this: The calculation event is not fired if the same value replaces that which is in the cell. However, the change event does fire. Even when replaced with the same value. If a DDE link will not fire off the change event, how do I capture the same volume? Any help would be greatly appreciated! I do hate failing! Thanks! Tom

Apr 3rd, 2002, 12:43 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Tom If I have followed you correctly (not sure I have) could you simply create a link to this cell on the same sheet and then use the Calculate Event. For example if the cell that is having the data dropped in is A1, in cell Z1 put =A1 this will cause the Calculate Event to fire.

Apr 3rd, 2002, 01:11 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Dave! Thanks for the reply. You did follow me correctly, to a point. The calculation event does indeed fire if the value fed via DDE is different from the value which is currently in the cell. The calculation event does not seem to fire if there is no calculation. I'm overwhelmed with my brilliance> HA HA Problem = this: If a DDE link does not fire off a change event and the value of volume is the same as the previous transaction. I am stuck??? Time Price Volume 01:13 5420.5 40 01:15 5420.5 40 These are two different transactions. These are actually two separate volumes. They just happen to contain the same value. If the same value = no calculation fired... ...and DDE does not fire the change event, how do I trap the second value? ?????????HEADACHE???????????? Cerebral Circular Reference!!!!! And I'm doing this for free. I deserve a headache! Thanks for any help! Tom

Apr 3rd, 2002, 01:18 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Tom RE: If a DDE link does not fire off a change event and the value of volume is the same as the previous transaction. I am stuck??? Why can't you use the method I suggested, as this WILL fire the Calculation Event, If the value is the same or not.

Apr 3rd, 2002, 01:21 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Dave That is the method I am using =A1 ect... I placed a break in the calculation event and no code was run when the value was replaced with the same value? Thanks Tom

Apr 3rd, 2002, 01:36 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Sorry Tom, I should have tested that first. The way around this could be to reference it via a Volatile function, eg: =OFFSET(A1,1,1)

Apr 3rd, 2002, 01:54 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Thanks Dave. I'll give it a try. Volatile Function? Will educate myself on this one. Thanks! Tom