• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

update other prog. while running VB-Excel macro

In an Excel spread sheet I have a cell which pulls a value (changing number) from another program with the formula:


Now I want to copy the actual value into another cell every 5 minutes (everytime a new cell, because the value is changeing) so that I can see the result after some 3 hours. I wrote a macro in
Visual Basic, but with that macro I do have the same value in each cell, because the other programm is not delivering the new value. Or is Excel not looking for the new one?

Question: How can I copy a changing value from one cell into anther automatically?
  • 3
  • 2
1 Solution
use the calculate event of the worksheet and the copy method of the range object.  keep track of the next location in a global variable.  
question4Author Commented:
I think I used the calculate method already. Here is my
VB programm:

Const spalte = 2

Sub test()

    For zeile = 5 To 10
    ActiveCell.FormulaR1C1 = "=View|tagname!'x1'"
    Range(Cells(zeile, spalte), Cells(zeile, spalte)).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Next zeile
End Sub

So how would I use the calculate event according to your answer?
Please clearify. Thank you.
question4Author Commented:
see above
' use this routine for the control
Dim PauseTime, Start, Finish, TotalTime

sub Run
      PauseTime = 5 * 60      ' Set duration.
      Start = Timer      ' Set start time.
      Do While Timer < Start + PauseTime
            DoEvents      ' Yield to other processes.
end sub

' use one of these events to get the value when it changes with ' the previoes code to pull the value when it changes

' these can be found in the sheet objects by double clicking on ' the sheet from the VBA project window

Private Sub Worksheet_Calculate()

' something like this
Range("B3").copy Range("B4")

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub

' if your other program is not delivering you the correct answer ' though then it will not work
question4Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now