• Document: Custom Capital IQ Macros for the VBA Environment
  • Size: 106.97 KB
  • Uploaded: 2019-05-17 14:36:09
  • Status: Successfully converted


Some snippets from your converted document:

Custom Capital IQ Macros for the VBA Environment  Macros to Refresh Excel Plug-in formulas o Macro to Refresh Selection o Macro to Refresh Worksheet o Macro to Refresh Workbook o Macro for Double Refresh o Macro for CIQ SCREEN Refresh  Macro to Unlink Excel Plug-in formulas Page 1 of 8 Macros to Refresh Excel Plug-in formulas These macros can be used to refresh Excel Plug-in formulas. They are as follows.  Refresh Selection This macro refreshes selected range of cells in a worksheet. Public Sub RefreshSelection() Dim Refreshbutton As CommandBarButton Set Refreshbutton = Application.CommandBars.FindControl(Tag:="menurefreshdatacell") Refreshbutton.Execute End Sub  Refresh Worksheet This macro is used to refresh a single worksheet in a workbook. Public Sub RefreshWorksheet() Dim Refreshbutton As CommandBarButton Set Refreshbutton = Application.CommandBars.FindControl(Tag:="menurefreshdatasheet") Refreshbutton.Execute End Sub  Refresh Workbook This macro is used to refresh entire workbook. Public Sub RefreshWorkbook() Dim Refreshbutton As CommandBarButton Set Refreshbutton = Application.CommandBars.FindControl(Tag:="menurefreshdatabook") Refreshbutton.Execute End Sub  Double Refresh This macro refreshes the entire workbook twice. Public Sub RefreshWorkbook() Dim Refreshbutton As CommandBarButton Set Refreshbutton = Application.CommandBars.FindControl(Tag:="menurefreshdatabook") Refreshbutton.Execute Refreshbutton.Execute End Sub Page 2 of 8  CIQ SCREEN Refresh Public Sub RefreshCIQScreen() Dim Refreshbutton As CommandBarButton Set Refreshbutton = Application.CommandBars.FindControl(Tag:="rcscreenrefresh") Refreshbutton.Execute End Sub This macro refreshes CIQ SCREEN formulas of an active sheet. It finds CIQSCREEN() formulas and refreshes them with an interval of 10 seconds (default) between one CIQ SCREEN refresh and another. Initially this macro refreshes the current worksheet. Option Explicit Dim AddressArray() As String Dim Counter As Integer Dim i As Integer, DummySec As Integer 'set time interval here in seconds between one CIQSCREEN refresh to another Const RefreshIntervalSec As Integer = 10 Sub CiqScreenRefreshMain() Application.StatusBar = "Capital IQ: Please wait..." Dim CheckAddress As String, LastRow As Long, ColumnNo As Long, RowNo As Long Dim CellsFound As Variant Set CellsFound = Cells.Find(What:="CIQSCREEN", LookIn:=xlFormulas, LookAt:=xlPart) If Not CellsFound Is Nothing Then CheckAddress = CellsFound.Address Counter = 0 Do ColumnNo = CellsFound.Column RowNo = CellsFound.Row LastRow = Cells(65536, ColumnNo).End(xlUp).Row If LastRow > RowNo Then Range(Cells(RowNo + 1, ColumnNo).Address & ":" & _ Cells(LastRow, ColumnNo).Address).ClearContents End If Counter = Counter + 1 ReDim Preserve AddressArray(1 To Counter) AddressArray(Counter) = Cells(RowNo, ColumnNo).Address Set CellsFound = Cells.FindNext(CellsFound) Loop While Not CellsFound Is Nothing And CellsFound.Address <> CheckAddress End If If Counter > 0 Then Page 3 of 8 'refreshing the current sheet for logging into excel plug-in Range("A1").Select Application.CommandBars.FindControl(Tag:="menurefreshdatasheet").Execute DummySec = 1 i=1 Call CIQScreenRefreshRoutine Else MsgBox "No CIQ SCREEN formulas found.", , "Capital IQ Excel Plug-in" Application.StatusBar = False End If End Sub Private Sub CIQScreenRefreshRoutine() If i > Counter Then Application.StatusBar = False End End If Range(AddressArray(i)).Select If Selection.HasFormula = True Then Application.CommandBars.FindControl(Tag:="rcscreenrefresh").Execute DummySec = RefreshIntervalSec Else DummySec = 1 End If i=i+1 Application.OnTime Now + TimeValue("00:00:" & DummySec), "CIQScreenRefreshRoutine"

Recently converted files (publicly available):