vba - implementing bloomberg time delays with a large amount of BDH cell references

Question

I have already looked at a few examples of how to use Application.OnTime, to check for progress within the cell before updating and wrote up an implementation but I'm not sure why it wont work. I dont want to paste the whole thing here, because it may be more confusing than just looking at the subs within the workbook.

I was wondering if someone with experience with this type of thing would be willing to look at my code. I can pm the file I'm working on.

Here is the method that loads data into the shell sheet. Ideally the data will all load before the pattern_recogADR sub is run... otherwise there is an error.

Sub build_singleEquity()

'x As Long

Dim x As Long
x = 6


'Dim x As Long
'x = 4

Application.ScreenUpdating = False
Call DefineTixCollection 'creates table of inputs
'check
'Debug.Print TixCollection(4).ORD

'set up data points - from "Input" sheet
'Dim x As Long
'Dim path As String
'path = Sheets("Input").Range("V1").value

'For x = 1 To TixCollection.Count

    Sheets("SingleEquityHistoryHedge").Activate

    'clear inputs
    Range("B2:B8").Clear

    Dim Inputs() As Variant
    Dim name As String

    name = "SingleEquityHistoryHedge"

    'insert new inputs

        Inputs = Array(TixCollection(x).ADR, TixCollection(x).ORD, TixCollection(x).ratio, _
        TixCollection(x).crrncy, TixCollection(x).hedge_index, TixCollection(x).hedge_ord, _
        TixCollection(x).hedge_ratio)
        Call PrintArray(2, 2, Inputs, name, "yes") ' prints inputs

    Dim last_row As Long
    last_row = Range("A" & Rows.count).End(xlUp).Row

    Range("AN11") = "USD" & TixCollection(x).crrncy
    Range("AA11") = "USD" & TixCollection(x).crrncy

 '   Dim sht_name As String

'Application.Run "RefreshAllStaticData"

 BloombergUI.ThisWorkbook.RefreshAll

 '   sht_name = TixCollection(x).ADR

  '  Call Sheet_SaveAs(path, sht_name, "SingleEquityHistoryHedge") 'save collection of sheets

'Next x

'Call TriggerCalc



'check this out
Call pattern_recogADR(x + 4, 5, 13)


End Sub

Here is the pattern_recogADR sub.... as you can see I have tried a ton of different thing which are commented out.

Sub pattern_recogADR(pos As Long, pat_days As Long, sht_start As Long)

'

'Application.Wait Now + "00:00:20"

'Dim pat As pattern

'Dim tix As clsTix

Dim newTime As Date
newTime = Now + TimeValue("00:00:30")

Do While Not Now >= newTime
'add back in as parameters
'Dim pos As Long
Dim x As Long
'Dim pat_days As Long
'Dim sht_start As Long
'************************
'pos = 5
'pat_days = 5
'sht_start = 13

Sheets("SingleEquityHistoryHedge").Activate
'Sleep 20000 'sleeps


Dim st As Long
Dim st_num As Long
Dim st_end As Long
Dim count As Long
Dim patrn As Long


count = sht_start

Dim i As Long
Dim j As Long
Dim patPLUSret() As Variant
Dim k As Long
Dim z As Long


k = 2
z = 3

For j = 8 To 12


'**************************************
    count = sht_start
    st_num = sht_start
    st_end = 13


        If IsNumeric(Cells(count, j).value) Then
                'sets default pattern to beginning cell value

               ' Debug.Print st_num


                If Cells(st_num, j).value < 0 Then
                    For i = count + 1 To count + 1 + pat_days
                        If IsNumeric(Cells(i, j).value) Then
                            If Cells(i, j).value < 0 Then
                            st_end = i
                            'Debug.Print st_end
                            End If
                        Else
                            Exit For


                        End If
                    Next i

                        patrn = st_end - st_num

                       ' Debug.Print count
                       ' Debug.Print patrn

                        ReDim Preserve patPLUSret(k * 2 + 1)
                        patPLUSret(0) = Range("B2").value 'ADR
                        patPLUSret(1) = Range("B3").value 'ORD
                        patPLUSret(k) = patrn
                        patPLUSret(z) = Application.WorksheetFunction.Average(Range(Cells(st_num, j), Cells(st_end, j)))

                    '    Debug.Print patPLUSret(j)
                    '    Debug.Print patPLUSret(j + 1)

                        st_num = sht_start 'resets starting point to initial
                        st_end = sht_start



                       ' For x = 4 To 6
                       '     If Range("L" & x).value = "x" Then
                       '       ReDim Preserve mac_array(x - 4)
                       '       mac_array(x - 4) = Range("N" & x).value
                       '     End If
                       ' Next x


                        ' check this out
                         'tix.arbPnl = patrn
                        'save to separate class for patterns
                         'TixCollection.Add tix, tix.ADR
                        '******************************


                ElseIf Cells(st_num, j).value > 0 Then
                    For i = count + 1 To count + 1 + pat_days
                        If IsNumeric(Cells(i, j).value) Then
                            If Cells(i, j).value > 0 Then
                                st_end = i
                            End If
                        Else
                            st_end = st_num
                            Exit For
                        End If
                    Next i

                        patrn = st_end - st_num

                        ReDim Preserve patPLUSret(k * 2 + 1)
                        patPLUSret(0) = Range("B2").value 'ADR
                        patPLUSret(1) = Range("B3").value 'ORD
                        patPLUSret(k) = patrn
                        patPLUSret(z) = Application.WorksheetFunction.Average(Range(Cells(st_num, j), Cells(st_end, j)))

                       ' Debug.Print patPLUSret(j)
                       ' Debug.Print patPLUSret(j + 1)


                        st_num = sht_start 'resets starting point to initial
                        st_end = sht_start

                      '  Debug.Print patrn

                        'pat.arbPnl = patrn
                        'save to separate class for patterns
                End If


                k = k + 2
                z = z + 2
        Else
            count = count + 1
            st_num = count
        End If

   '
  '  k = k + 1

        'new_array = patPLUSret


Next j

  '  Debug.Print patPLUSret

    Sheets("PatternADR_ORD").Activate
    Range(Cells(pos, 1), Cells(pos, 10)) = patPLUSret


Loop


End Sub

Answer 1

If you wait or loop to simulate a wait in your second sub, it won't give the control back to the spreadsheet and your formulae won't update.

Instead of

Call pattern_recogADR(x + 4, 5, 13)

why don't you call:

Application.onTime "'pattern_recogADR ""x + 4"", ""5"", ""13""'"