VBA codes

Note you have to have installed our Office add-in to use these codes. Add-in installs itself along with Macrobond main-app installation. See below prepared VBA codes to use in Office programs:

Excel

update all data from VBA code

 public sub UpdateMB()
 Dim mbAddIn As COMAddIn
 On Error Resume Next
 
 Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")
   
 If mbAddIn Is Nothing Then
   'Some error handling
 Else
   On Error GoTo 0
   mbAddIn.Object.RefreshMacroBondData
 End If
 End sub

upload all series from VBA code automatically when opening an Excel file

If you want it to be called automatically, we recommend calling it via a ThisWorkbook /Workbook_Open. 
In certain situations, instantaneous calling (without a few seconds of delay, introduced in Step 1) will cause the macro to fail. 

Step 1: Please add this under ThisWorkbook:
Private Sub Workbook_Open()
   alertTime = Now + TimeValue("00:00:05")
   Application.OnTime alertTime, "UploadSeries"
 End Sub

Step 2: Please add this under Module1

Private Sub UploadSeries()
   Dim mbAddIn As COMAddIn
   On Error Resume Next
 Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")

 If mbAddIn Is Nothing Then
   'Some error handling
 Else
   On Error GoTo 0
   mbAddIn.Object.UploadMacroBondInhouseData
 End If
 End sub

update a selected chart or object in Excel

Sub Macro1()
    Selection.Verb Verb:=xlOpen
End Sub

update all data from VBA code automatically when opening an Excel file

this can be executed in regular intervals with the Task Scheduler. Typical use case: client wants all excel files automatically opened at 8 AM each day and refreshed with new data.

Step 1: Please add this under ThisWorkbook:

Private Sub Workbook_Open()
   alertTime = Now + TimeValue("00:00:05")
   Application.OnTime alertTime, "MyRefresh"
End Sub

Step 2: Please add this under Module1:

Private Sub MyRefresh()
   Dim mbAddIn As COMAddIn
   On Error Resume Next
       Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")
   If mbAddIn Is Nothing Then
       'Some error handling
   Else
       On Error GoTo 0
       mbAddIn.Object.RefreshMacroBondData
   End If
   ThisWorkbook.Save
End Sub

upload in-house templates [all sheets - or specific range] from VBA code

public sub UploadMBInhouseData()
    Dim mbAddIn As COMAddIn
    On Error Resume Next
 
    Set mbAddIn = Application.COMAddIns("Mbnd.Excel.AddIn")
   
    If mbAddIn Is Nothing Then
      'Some error handling
    Else
      On Error GoTo 0
  
     ' You can either specify a static range like this:
     ' Set UpdateRange = Range("C1", "F1")
     ' Call mbAddIn.Object.UploadMacroBondInhouseData(UpdateRange)
  
     ' Or use the current cell selection to specify the range like this:
     ' Set UpdateRange = Application.Selection
     ' Call mbAddIn.Object.UploadMacroBondInhouseData(UpdateRange)
 
     ' This will update all in-house series.
     Call mbAddIn.Object.UploadMacroBondInhouseData()
    End If
 End sub

refresh current worksheet only

Sub a()
 For Each shp In ActiveWorkbook.ActiveSheet.Shapes
   If shp.Type = msoEmbeddedOLEObject Then
       If Not shp.oleformat Is Nothing Then
           Dim oleformat
           Set oleformat = shp.oleformat
           If oleformat.progID = "Mbnd.EmbeddedDataStore" Then

             On Error GoTo ErrorHandler:
               shp.Select
               Selection.Verb Verb:=xlOpen
             End If
             ErrorHandler:

           End If
       End If
 Next
End Sub

refresh charts in the current sheet

Sub RefreshMacrobondCharts()
    Dim sh As Shape
    Dim I As Integer
    For Each sh In ActiveSheet.Shapes
        sh.Select
        Selection.Verb Verb:=xlOpen
    Next
End Sub

Word

refresh charts

Sub RefreshMacrobondCharts()
    For I = 1 To ActiveDocument.InlineShapes.Count
        If Not ActiveDocument.InlineShapes.Item(I).OLEFormat Is Nothing Then
            ActiveDocument.InlineShapes.Item(I).OLEFormat.DoVerb VerbIndex:=1
        End If
    Next
End Sub

resize charts

Sub test()
Dim sRep
sRep = ""
For i = 1 To ActiveDocument.InlineShapes.Count
 Dim shape
 Set shape = ActiveDocument.InlineShapes(i)
 If shape.Type = msoEmbeddedOLEObject And Not shape.oleformat Is Nothing Then
  Dim oleformat
  Set oleformat = shape.oleformat
  If oleformat.ProgID = "Mbnd.mbnd" Then
  shape.Width = 150
  shape.Height = 150
 End If
End If
Next
End Sub

Powerpoint

refreshcharts

Sub RefreshPPT()
For SlideID = 1 To ActivePresentation.Slides.Count
Dim slide
Set slide = ActivePresentation.Slides(SlideID)
For shapeId = 1 To slide.Shapes.Count
Set shape = ActivePresentation.Slides(SlideID).Shapes(shapeId)
ActiveWindow.ViewType = ppViewSlide
If shape.Type = msoEmbeddedOLEObject Then
ActiveWindow.Selection.Unselect
ActiveWindow.View.GotoSlide slide.SlideIndex
shape.Select
  If Not shape.oleformat Is Nothing Then
   Dim oleformat
   Set oleformat = shape.oleformat
    If oleformat.ProgID = "Mbnd.mbnd" Then
     shape.oleformat.DoVerb (2)
    End If
   End If
  End If
Next
Next
End Sub

resize charts

For SlideID = 1 To ActivePresentation.Slides.Count
 Dim slide
 Set slide = ActivePresentation.Slides(SlideID)
 For shapeId = 1 To slide.Shapes.Count
  
  Set Shape = ActivePresentation.Slides(SlideID).Shapes(shapeId)
  If Shape.Type = msoEmbeddedOLEObject Then
  If Not Shape.oleformat Is Nothing Then
   Dim oleformat
   Set oleformat = Shape.oleformat
   If oleformat.ProgID = "Mbnd.mbnd" Then
       Shape.Width = 50
       Shape.Height = 50
       
   End If
   End If
  End If
 Next
Next
Tags: ExcelmacroVBA