can't get caluclate event to fire vba

[cicada]

I'm new to this site so first of all hello to everyone, i'm bit stuck so hope someone can help.

I have the following code.


Private Sub Worksheet_Calculate()
With ThisWorkbook.Sheets("HILO_TURBO")
If Len(.Range("J7").Value)=6 AND (.Range("E40").value > 0) Then
.Parent.Sheets("sheet1").Range("D3: D6").Value = .Range("J19:J22").Value
End If
End With
'...
End Sub
HILO_TURBO has data entered into it from an external dde link that updates every 10 seconds or so and i have formulas that calculate the above mentioned data. The values in J19:J22 recalculate all the time but i would like to capture them in D3:D6 each time the above criteria is satisfied. this criteria is satisfied every so often and so the old values contained in D3:D6 i want to be replaced with the newly calculated values. I hope this clear. when i enter the code in visual basic it performs the above macro once the above conditions are satisfied, but when the conditions occur again it does not replace the old values with the new ones as desired. I have to press run (in visual basic) again while these conditions are active or present for the macro to run again. anyone have any ideas as to why this is happening. Change event will not work with a dde link and i cant see why calculate event will trigger it once and not again. thanks in advance for your help.

Cicada

[mdmackillop]

Welcome to VBAX
Whilst the update may not trigger the change, you can create an on-time procedure to run every 10 seconds. Would that suffice?

[austenr]

So you want VB to fire the macro from inside VB not Excel?

[austenr]

Was going to suggest that Malcomb but was unclear where the macro was triggered from. You beat me to it.

[ZVI]

Hi Cicada,

Updating of the cell by the aid of external DDE doesn’t trigger worksheet events.
But you can put dummy volatile function into empty cell formula, for example: =TODAY()
In this case after DDE updating the volatile function will update as well with triggering of Worksheet_Calculate.

Regards,
Vladimir

[cicada]

thanks for the replies everyone. Unfortunately the on time procedure does not suit my purposes. any other ideas? ZVI i'm curious, after some research i read that the change event will not fire with dde external links but calculate event will though your assertion does ring true because i havent been able to get the clalculate event to fire either. Though as i said it runs one time before stopping. i will have to look up what that dummy volatile function thing is that u mention and how that will help me. Could you help me there not sure what u meant by that. Thanks again.

Cicada

[mdmackillop]

I think he means enter =TODAY() anywhere on your sheet. Your event should trigger when this changes due to your update. You could also try =NOW() or maybe =A1*2 where A1 is one of your updated values.

If your still have problems and your link is to public data, can you post a sample workbook?

[ZVI]

Thanks Malcolm – I was exactly after using of volatile function in a cell as the dummy one to trigger sheet Calculate event.

Cicada, there are different methods to trigger DDE updating events.
Adding volatile function in a sheet is one of them. Volatile functions in Excel formulas like =TODAY() are recalculating at any cell values changing, even at DDE updating.
Let’s assume that there are some DDE updating cells in the sheet.
Just put anywhere in empty cell of that sheet such dummy formula: =TODAY()
After DDE updating volatile function runs and Calculate event of that sheet can be triggered.
You can hide cell with volatile function or use that one: =IF(TODAY(),"")

Instead of volatile function you can use the formula referenced to DDE updating cell as Malcolm has mentioned.
Let’s say, the DDE cell is A1 and the formula of B1 is: =A1
Then each time A1 updates, formula in B1 updates as well, Calculate event has happened and can be triggered in a such way:


' Assuming that A1 is DDE cell and B1 has formula: =A1
' Sheet module code
Private Sub Worksheet_Calculate()

' Show debugging message
MsgBox "Calculate has happened"

' Your triggering code is here
' ...

' Designates B1 to be recalculated when the next DDE updating occurs
' It's usefull to trigger the same value as previous one
Range("B1").Dirty

End Sub

An exotic but the fast way to catch DDE event is in placing of RefEdit control onto sheet with setting of its LinkedCell property to DDE cell and triggering of RefEdit1_Change event in the sheet VBA-module.

Pay also your attention on ThisWorkbook.SetLinkOnData method – have a look on example in VBA help.

Vladimir

[cicada]

just got back online, thanks everyone for ur responses, zvi ur suggestions sound great about to try the volatile function and if no luck will try setlinkondata method looked at that before but wasnt sure of the procedure for finding the name of the source to run setlinkondata will have a look at that example u suggested. thanks again.

Cicada

[Ted]

I have used this approach and its working. Thanks !

I want to make this event trigger smarter though. Lets say that there are 2 DDE updating cells but I want restrict calculation trigger for only the cell which is updated.

Currently with above logic if any of the cell is updated by DDE event, calculation will be triggered for all the cells.

Suggestion?Workarounds?

[ZVI]

Hi Ted,

The same methods as described in the post #8:
1. Isolate DDE cells in separate hidden sheet(s), name those cells and make reference to it in other sheet. Trigger Calculation events in the sheet where DDE cell is stored.

2. Use RefEdit control embedded into sheet, set its LinkedCell property to DDE cell. In this case
RefEdit1_Change code in the sheet triggers only at linked cell updating.

3. If DDE formula is used in DDE cell then use SetLinkOnData, it links directly DDE updating to the macro set as parameter of SetLinkOnData

Regards,
Vladimir

[mdmackillop]

Hi Vladimir
Can you summarise these methods and create an Article for the Articles section? I think it would be a useful addition.
Regards
MD

[ZVI]

Hi Vladimir
Can you summarise these methods and create an Article for the Articles section? I think it would be a useful addition.
Regards
MD
Hi Malcolm,
Thank you for proposition - I'll try to create the Article
Hope that someone will check it out :)
Regards,
Vladimir

[Ted]

While I work on the solution, please update the thread when article is published.