Automatically changing cells in EXCEL

I have an MS EXCEL workbook sheet named 'Simulator' (which is always open) and it will receive DDE values from another application running on the same PC

I want to check every second if certain cells containing DDE values in my worksheet 'Simulator' change their value.

e.g. If the value in Cell A1 changes at any time then I want to compare the value in A1 to a known value to determine what to do ....

If A1 = 0 then do nothing

If A1 = 1 then increment B1 by 1 every second until B1 = C1

If A1 = -1 then decrement B1 by 1 every second until B1 = D1

I want to further extend this such that every second I check a range of cells from A1 to A100 and if any of them change then I want to put a value in the relative B cell in the same row

ie

If A43 = 0 then do nothing

If A43 = 1 then increment B43 by 1 every second until B43 = C43

If A43 = -1 then decrement B43 by 1 every second until B43 = D43
aphukAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Insert this in a module

Option Explicit
Public dTime As Date

Sub RunOnTime()
    Dim ws As Worksheet, rw As Long
    Set ws = ThisWorkbook.Worksheets("Simulator")
    dTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime dTime, "RunOnTime"
        
    For rw = 1 To 100
        If ws.Range("A" & rw) = 1 Then
            If ws.Range("B" & rw) < ws.Range("C" & rw) Then
                ws.Range("B" & rw) = ws.Range("B" & rw) + 1
            End If
        ElseIf ws.Range("A" & rw) = -1 Then
            If ws.Range("B" & rw) > ws.Range("D" & rw) Then
                ws.Range("B" & rw) = ws.Range("B" & rw) - 1
            End If
        End If
    Next rw
End Sub

Sub CancelOnTime()
    Application.OnTime dTime, "RunOnTime", , False
End Sub

Open in new window


And this in the ThisWorkbook module, to make the function start on workbook open.

Private Sub Workbook_Open()
    RunOnTime
End Sub

Open in new window


And/or insert a start and stop button on the sheet, see attached.
Macro-run-on-time.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aphukAuthor Commented:
Thank You for the xlsm sheet and the very elegant solution Ejgil. Much appreciated.
As a beginner to EXCEL macros I just need to get my head around the relationship between the macro and EXCEL. What I have immediately realised is that I have to save my workbook as an 'xlsm' rather than an 'xlsx'
I had one further question and hopefully won't disturb you any further ... Now i have found my way to the the code residing in Project - VBA Project
XL VBA Project WindowI am wondering what the 'Ark1 (Simulator) is as it only contains the line "Option Explicit" ?
0
Ejgil HedegaardCommented:
All my sheets are named Ark1, Ark2 etc. similar to Sheets1, Sheets2 etc. at start before renamed.
That is language dependant.
Option Explicit tells VBA that all variables must be declared with Dim, Public, Const.
I have set my VBA to automatically require variable declaration, select Menu Tools and Options.
Declaring variables prevent misspelling, and the autofill option can be used, type the first few characters, press Ctrl+Space and VBA inserts the rest, or show a list of possible options to select from if more than one.
If not declared, any misspelling makes a new empty variable, but with Option Explicit the code can not compile.
Declaring the sheet (Dim ws as Worksheet) tells VBA that ws is a worksheet, so typing ws. displays a list of what can be used with a worksheet, so I don't have to remember.
0
aphukAuthor Commented:
Ejgil has provided a thorough and informative answer along with further supporting information to allow me to completely understand.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.