0

I have gotten this code below, it has worked in excel and I have had someone go through it to ensure it works but just not enough time for EIKON to calculate all the values (stocks) that are in A270:A371. This program is suppose to enter values from A270:A371 into Homepage J2 that will give different ratings on stocks that are in Investing A249:B260, then the code proceeds to copy Investing a249:B260 and start pasting values into Daily strategies E5 (G5,I5,K5,etc.). You can see the macro working and changing through the values that are in A270:A371 but it is not allowing EIKON to calculate for bringing in all prices for each stock. When you do this one stock at a time it will work but not update through the macro. I need a line of code to tell EIKON to update/calculate each time a new value gets pasted into Homepage J2. Let me know what else you may need, and I can answer your questions.


Sub insertVarious()

    'Application.CalculateFullRebuild

    Const hpgName As String = "Homepage"
    Const hpgCell As String = "J2"

    Const invName As String = "Investing"
    Const invAddr As String = "A249:B260"
    Const invAddr2 As String = "A270:A371"

    Const dstName As String = "Daily Strategies"
    Const dstFirst As String = "E5"

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim hpg As Range: Set hpg = wb.Worksheets(hpgName).Range(hpgCell)
    Dim inv As Range: Set inv = wb.Worksheets(invName).Range(invAddr)
    Dim inv2 As Range: Set inv2 = wb.Worksheets(invName).Range(invAddr2)
    Dim UB1 As Long: UB1 = inv.Rows.Count
    Dim UB2 As Long: UB2 = inv.Columns.Count
    Dim NoA As Long: NoA = inv2.Rows.Count

    Dim Daily As Variant: ReDim Daily(1 To UB1, 1 To NoA * UB2)
    Dim Curr As Variant, j As Long, k As Long, l As Long
    For j = 1 To NoA
        hpg.Value = inv2.Cells(j).Value
        'hpg.Parent.Calculate
        'inv.Parent.Calculate
        Curr = inv.Value
        GoSub writeDaily
    Next j

    wb.Worksheets(dstName).Range(dstFirst).Resize(UB1, NoA * UB2) = Daily

    MsgBox "Data transferred.", vbInformation, "Success"

    Exit Sub

writeDaily:
    For k = 1 To UB1
        For l = 1 To UB2
            Daily(k, (j - 1) * 2 + l) = Curr(k, l)
        Next l
    Next k
    Return

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
AND AND AND
  • 3
  • 4

1 Answers1

0

Dealing with async stuff in VBA is always a pain.

  1. Have you tried Application.Calculate
  2. Have you tried DoEvents
  3. Have you tried Application.OnTime

If the time is not predictable you could also try:

oldValue = someRange.value
While someRange.value = oldValue
  DoEvents
Wend

To wait until the data is present.

Sancarn
  • 2,575
  • 20
  • 45
  • All of this is more of a pain than anything unfortunately. I've tried Application.Calculate, and what I find is that VBA recognizes the code but EIKON doesn't allow the stock numbers to calculate fast enough (or at all during the code). Where would I put the "DoEvents" or "Application.OnTime" in my code? I will try these out in the meantime. Thank you again. – AND AND AND Jun 22 '20 at 01:25
  • @ANDANDAND I fear I've never used EIKON before, so don't know explicitely where you need the delay. – Sancarn Jun 24 '20 at 23:26