Needing A Little Help With VB

[p7m8hk]

Any chance that one of you fine gentlemen might be able to help me with a small excel/vb issue. I have vb code that time stamps and records a cell value anytime it changes (see below). What I need is for this it to happen every 60 seconds regardless if the value changes or not. Thus, if the value is the same for ten minutes, I would have ten entries each a min apart recording the same value.

Any help would be greatly appreciated.

Clete

Private Sub Worksheet_Calculate()
Sheets("Log").Range("A65536").End(xlUp).Offset(1, 0) = Now
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 1) = Sheets("Log").Range("B2").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Sheets("Log").Range("A65536").End(xlUp).Offset(1, 0) = Now
Sheets("Log").Range("A65536").End(xlUp).Offset(0, 1) = Target.Value
End If
End Sub

 

[jawolthuis]

See if this helps

Imports System.Timers

Public Class MyClass
    Shared MyTimer As Timer

    Shared Sub Start()
    MyTimer = New Timer(1000)
    AddHandler MyTimer.Elapsed, New ElapsedEventHandler(AddressOf TimeElapsed)
    MyTimer.Enabled = True
    End Sub

    Shared Sub TimeElapsed(ByVal sender As Object, ByVal e As ElapsedEventArgs)
    '' Do Work here
    End Sub
End Class

 

[p7m8hk]

jawolthuis, thank you for the help. I'm sorry to admit I' don't have any knowledge of this type of work. I tried to just add what you posted but I had no luck. I'm sure there are basic things I should know and it would work... Any chance you could put the two part together the way I should know how to?....

I truly appreciate the help
Clete

 

[jawolthuis]

I posted an example of adding a timer to a vb.net project. I do not know how to add it to what you have without seeing the rest of your project.
Is the code you posted inside a class? or is it a .vbs file you are calling from elsewhere? What makes the code you have run to begin with?

 

[JaxGTO]

I use the timer method for a form that updates a large font data display. Mainly so I can see it from accr4oss the room for testing analog data.

dtmDisplayDataFormStartTime = Now + TimeValue("00:00:02")

Application.OnTime dtmDisplayDataFormStartTime, "subDataDisplayRefresh"

 

[p7m8hk]

It's just a stand alone excell work sheet that I have a linked via opc to a changing PLC tag/value. that's it. B2 is the cell that gets recorded (see below). The info I posted is by going to the tab and doing "veiw code". What I posted is the only thing there

Variable Name Value
35.66667
Time & Date Value
8/23/2016 18:16:09 #N/A
8/23/2016 18:16:09 36.33333
8/23/2016 18:16:52 37
8/23/2016 18:17:52 36.33333
8/23/2016 18:18:52 35.66667

 

[jawolthuis]

I'm sorry, but I don't think I will be much help with that.
The one option that may work, would be to have excel monitor another tag that changes every second, this will allow you to control logging from the PLC.

 

[Archie]

Would this be of any help:

https://msdn.microsoft.com/en-us/library/office/aa211461(v=office.11).aspx

 

[nhatsen]

Or try this:

http://stackoverflow.com/questions/...ode-every-set-number-of-seconds-i-e-120-secon

 

[kwade]

Or try this:

http://stackoverflow.com/questions/...ode-every-set-number-of-seconds-i-e-120-secon

The first answer in this link can work(XL2007 confirmed). Just put the 'EventMacro' sub in a module.