Bet Angel and Excel

[CPerry]

Cheers SamT! If this works it will be exactly what I need! So the way I see it is I have a few codes now: 1) Timer function, 2) twenty () function.

Now if the timer is operating at exactly 20m/s and I assume VBA works through it’s code in a linear fashion, will it miss readings or generally bug if I just put the timer code first then at the bottom call the twenty () sub in a single module, like this:
(P.S Will this loop continuously, not just do one second and then stop?)

Insert code

Private Declare Function getFrequency Lib "kernel32" Alias _ 
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long 
Private Declare Function getTickCount Lib "kernel32" Alias _ 
"QueryPerformanceCounter" (cyTickCount As Currency) As Long 
 
Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0" 
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002 
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long 
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long 
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long 
 
Function MicroTimer() As Double 
     '
     ' returns seconds
     '
    Dim cyTicks1 As Currency 
    Static cyFrequency As Currency 
     '
    MicroTimer = 0 
    If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
    getTickCount cyTicks1 ' get ticks
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds
    
Call twenty
 
End Function 
 
 'Calling macro
Sub Test() 
    Dim i As Long 
    Dim Tim As Double 
    Dim Result1 As Double, Result2 As Double 
    Dim Factor As Long 
    Factor = 10000 '<== adjust to show clear result
    Tim = MicroTimer 
    For i = 1 To 100000 
        DoEvents 
    Next 
    Result1 = MicroTimer - Tim 
    Tim = MicroTimer 
    For i = 1 To 1000 ‘or perhaps 50?
        DoEvents 
    Next 
    Result2 = MicroTimer - Tim 
    Call twenty ()
 End Sub

Sub twenty()    Dim outarr(1 To 1, 1 To 4) As Variant 
    inarr = Range("H45:H51") 
    cnt = Cells(42, 1) 
    If cnt = 10 Then 
        cnt = 1 
    End If 
    indi = 1 
    For i = 1 To 7 Step 2 
        outarr(1, indi) = inarr(i, 1) 
        indi = indi + 1 
    Next i 
    Application.EnableEvents = False 
    Range(Cells(cnt + 1, 38), Cells(cnt + 1, 41)) = outarr 
    cnt = cnt + 1 
    Cells(42, 1) = cnt 
    Application.EnableEvents = True 
    End Sub

How would you suggest to best get around this? (Bearing in mind, the rest of the blue section will be used for indicators which will be constantly completing functions as the data pours in).

Then, to get around it not simultaneously ticking every sheet in my workbook (as I only need it to focus on one at a time), could I start with the code:

Private Declare Function ()
If (1,1) = “1”, Call Sub Timer

AND THEN… if all of that works, that will simply give me 10 iterations in the yellow section. I then need to calculate the Open (first in this list), High (biggest number), Low (smallest number) and Close (last number) and then have an extension to my vba code for this to then tick down under the blue section at 200m/s.

I hope that all made sense and somebody can get their heads around what I’m trying to do. I have quite a lot going on there but I think the code is slowly starting to piece together now. Thank you all very much for your help so far!

Cheers,
CPerry.