Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Run a macro on change of a value

Posted on 2011-10-27
24
Medium Priority
?
427 Views
Last Modified: 2012-05-12
Hi Guys,

I have got this file with various commodities and their LTP (Last Traded Price). The LTP is updated automatically with the help of an Add In provided by Bloomberg. I need to record the time of each change in LTP.

I have used the "Worksheet_Change(ByVal Target As Range)" option in VBA but this is not catching the change in the LTPs. The "Worksheet_Calculate()" is working, but I also need to know which LTP (among many) is changing. With the "Worksheet_Calculate()" option I can not identify it.

Any help is much appreciated.
ltp-change.xlsm
0
Comment
Question by:dynamicweb09
  • 11
  • 8
  • 5
24 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37043159
Worksheet_Change event won't work for this type of application.  You'll have to trap it with some other event, like calculate, or sheet selection change, or with a timer that looks every so many seconds.

Before I provide further assistance, think on this and decide what event you'd LIKe to catch the change in the LTPS

Read this thread as there's another E-Eer that does stuff like this and you might find these solutions useful:

Here's one that makes an alert when a cell value changes, so the traps are similar to what you're trying to do - in this case, the timer is started and every so much time, the values are tested and if changed, BING - you have what you need...

http:/Q_27417925.html

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043162
Here's how to do it with the timer:

 
Public runWhen As Double

Sub startTimer()
Dim refreshTime As Double
Dim waitMins As Long

    Call myMacro
    waitMins = 5
    refreshTime = 15 ' after testing at 15 seconds, then change to -> waitMins * 60 'time in seconds
    runWhen = Now() + refreshTime / 86400 '# seconds in 24 hours
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=True

End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=False
    
End Sub
Sub myMacro()
'check you cell values, here and do what you want to do if there was a change
end sub

Open in new window


Be sure on workbook_close, you run the stoptimer, just in case it hadn't been stopped.

Let me know if you need more assistance.

Cheers,

Dave
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37043571
HI Dave,

Thanks for your response.

As a matter of fact, I have already tried these options. But the nature of the job is such that I can't use a time based option. I must run the macro only when a change in LTP happens. Because if there is a change in the LTP during that time gap, I will miss it. And I can't have the time gap too close as it will choke the system.

I also can't use the calculate option as it will not give me the 'Target' and also the 'selection_change' is not going to work for me.

Please give me something else....

Thanks
Joydip
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 37043945
Hi, Joydip.

Please see attached. Couple of points...
(1) For testing purposes, I have put a formula in A1 to force a WorkSheet_Change event.
(2) Most of the WorkSheet_Change() code is to handle possible error values in the LTP cells. If the "Bloomberg" formulas in those cells can never return an error then the code can be simplified.
(3) Events are temporarily disabled while WorkSheet_Change() is running. If you have other active Event macros, please let me know.
Option Explicit

Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xValue As Variant
Dim xValue_Old As Variant
Dim xChange As Boolean
Dim xNow As String

Application.EnableEvents = False

    xNow = Format(Now(), "hh:mm:ss")
    
    For Each xCell In Sheets("Sheet1").Range("B3:F3")
        xValue = xCell.Value
        If IsError(xValue) Then xValue = "*Error*"
        xValue_Old = Sheets("Old_Sheet1").Range(xCell.Address)
        If IsError(xValue_Old) Then xValue_Old = "*Error*"
        If xValue <> xValue_Old Then
            xCell.Offset(-1, 0).Value = xNow
            xChange = True
        End If
    Next
    
    If xChange Then Call Refresh_Old_Condition_Values

Application.EnableEvents = True

End Sub

Open in new window

Option Explicit

Sub Auto_Open()

Application.EnableEvents = False

    Call Refresh_Old_Condition_Values

Application.EnableEvents = True

End Sub

Sub Refresh_Old_Condition_Values()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Range("B2:F3").Copy Destination:=Sheets("Old_Sheet1").Range("B2")
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
ltp-change-V2.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37043982
Joydip,

Apologies, my previous post refers to "WorkSheet_Change" when, of course, it should be "WorkSheet_Calculate". The code is correct.

Regards,
Brian.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37045039
If the bloomberg update does not affect a worksheet_change and timer doesn't work, you might be "out of luck".

Brian - I might stand corrected and blame it on short-term memory lapses, but I don't believe worksheet_calculate will be triggering on the change either, if worksheet_change doesn't.  IMHO - your best chance might be to put the =NOW() function in the worksheet and reference that cell from the Bloomberg cells in a non-interfering way.  MAYBE it will be updated when Bloomberg updates as a result, THEN calculate might work as the trigger.

Don't believe its going to work all on its own.

Dave
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37045162
dlmille,

I know my code worked cpatte7372, but of course Bloomberg may us a different method.

Joydip,

How does the update work - as a formula in the cell or some other way? Did the software come from Bloomberg or somewhere else?

Thanks,
Brian.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37045258
Brian -  knew those of us whod helped Carlton might be of help here

Keep the now reference in your back pocket as might be of use

Cheers
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37045420
Thanks, Dave. If the Sum() in A1 doesn't trigger a calculate then we may be dead. I know that Now() is volatile, but if Excel doesn't realise that the LTPS cells have changed (hard to believe) then it won't even be thinking about a Recalculate.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37045530
I was operating of the original OP post that worksheet_change wasn't impacted.  Perhaps sum() is just as effective as now() - if memory serves now was used in all examples I read to affect a change.  At any rate, we'll see soon, when the OP responds to your post.

Dave
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37045621
Dave, things usually get better with experience, but I'm not sure that works for memory. I suspect that my "experienced" memory is somewhat, eh, older than yours and so I'm about to re-learn something!
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37055285
Hi Guys,

Really appreciate your time and effort regarding this issue.

My apologies for not posting sooner.

I must provide you the following information :

a) The Worksheet_Calculate option does work in that sheet! Whenever the LTP is changed the sheet seems to get calculated.
b) This option of getting live data comes with Bloomberg software itself. It is not a Third party software.
c) To get the LTP or any other market related data, we must enter a formula.
d) The result of any formula with reference with the cells getting changed by Bloomberg formulas do change, but those can not be captured by the worksheet_change method. For example, any cell containing the formula of the sum of all the LTP do change when any of the LTPs change, but it does not trigger the worksheet_change.

Looking forward for you guys to give me way out on this...

Thanks and regards,
Joydip

The problem with using the 'calculate' option is that I can't pin point the exact contract for which the LTP has changed.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37055689
Joydip,

All that's OK.

"I can't pin point the exact contract"
 - The macro indirectly detects that some change has been made to one or more LTP's by the formula in A1.
 - Because it has stored the previous values of the LTP, it is then able to compare the old and new values to detect which  LTP has changed.

Give it a go!

Finally, how many LTP cells do you have in your live file?

Regards,
Brian.
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37055713
Hi Brian,

I have about 30 LTP cells in my live file.

Can you please resend me the file with the code in it so that I can find out in the Bloomberg system whether it is working or not?

Thanks,
Joydip
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37055738
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37055784
Hi Brian,

Never mind....I did not see that you had already updated the file :)

Let me check that in the bloomberg system and then I will get back you you.

Thanks a lot,
Joydip
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37056013
Hi Brian,

Sub Refresh_Old_Condition_Values()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Range("B2:F3").Copy Destination:=Sheets("Old_Sheet1").Range("B2")
    
Application.ScreenUpdating = True

End Sub 

Open in new window

Need to paste value in order to compare. As there are formulas in the LTP row which depend on reference of the contract names.

Thanks,
Joydip
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37056061
Joydip,

Please see attached. Only code change is...
Sub Refresh_Old_Condition_Values()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Range("B2:F3").Copy
    
    Sheets("Old_Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 37056066
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37056260
Hi Brian,

Thanks, it is working fine.

I would love to have a solution with worksheet_change somehow though...It would make my job alot easier.

Appreciate your help greatly.

Regards,
Joydip

0
 
LVL 26

Expert Comment

by:redmondb
ID: 37056426
Joydip,

Thanks kindly!

"It would make my job alot easier"
It would also be more efficient - my code runs more often than necessary and then has to do more work to identify the specific cell(s). Unfortunately, a formula result changing doesn't trigger the Worksheet_Change event.

I don't suppose there are any set-up parameters for the Bloomberg s/w that can be tweaked to update the spreadsheet differently?

I've never seen the Bloomberg s/w in action - how often do the updates arrive? Do they come individually or in blocks (e.g. every second the changes for the previous second all arrive together? Does the active cell change?

Regards,
Brian.
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37067692
Hi Brian,

I am not sure whether it is possible to tweak that software or not. I will try to give it a try though.

The updates appear as soon as there is any change in the market. It is real time. They don't come in blocks. The active cell never change. Only the value in which we have called the Bloomber formala, changes.

Do you think anyone here might have an idea about how we can solve this issue?

Regards,
Joydip
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37069522
Joydip,

Thanks for the answers. Unfortunately I don't see anything that can be used to generate a Change event - we're hard up against the fact that a formula's result changing simply doesn't fire a Change event.

You should try a new question with a title something like "How can I detect Bloomberg updates as Change events?". Explain that you already have a method for picking up the changes but that you're looking for an easier and/or more efficient solution. Include a link to this question. Also, add a few more zones - I googled  "site:experts-exchange bloomberg" and saw quite a few Access-related questions. (Even if Access isn't a runner for you, you may pick up some Bloomberg experts.)

Having been unable, so far, to help with your follow up, is there anything I can do to make the current solution easier for you?

Regards,
Brian.
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 37082139
Hi Brian,

You have helped me a lot!

I will let you know if there is anything else I need to know regarding this.

Thanks and regards,
Joydip
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question