Create a Global Counter Variable and Global Limit Variable - VBA

[Johnny Thunder]

Hi guys,

Working on a niche project today that I don't seem to have found a straight answer for. I have a Bex Analyzer (SAP) query in an Excel file to run a query but I want to trigger a macro at the end of the query refresh/run. I found some code online to figure out when the query is finished (posted below). My issue is that the "Callback" code runs for every query you have in a workbook and most of my workbooks have at least 2 queries.

In my research for a solution is saw the below comment posted to a forum but no code or anything to show how to do this, anyone have any idea how? I would need my code below to be modified to do this step. I would be setting the Global "Limit" to 2 on like 98% of my reports. Thanks for the help


"create a global counter variable in VBA and a global 'limit' variable. Set the global limit variable with the number of bex objects in the workbook, then track when each Bex object is touched in VBA. Then execute the final step when the global counter and the global limit are equal. Then reset the global counter."

My thrown together code

Private Function getName(i_name As String) As Name
   Dim l_name As Name
     For Each l_name In ThisWorkbook.Names
        If l_name.Name = i_name Then
            Set getName = l_name
        End If
     Next l_name
End Function
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub CallBack(ParamArray varname())


    On Error Resume Next


Dim lColumn         As Integer
Dim lRange2         As Range
Dim lGridTitle      As String
Dim lName           As Name




lGridTitle = "DF_" & varname(2)


' Get all columns count and clear if necessary
Set lName = getName(lGridTitle)
If Not lName Is Nothing Then
   Set lRange2 = lName.RefersToRange
   Set lRange2 = lRange2.Offset(-1, 0).Rows(1)
   If lRange2.Cells(1, 1).Value = "Table" Then
      lRange2.Cells(1, 1).ClearContents
      lRange2.ClearFormats
   End If
   lName.Delete
End If


' Save new position as Name
ThisWorkbook.Names.Add lGridTitle, "=" & varname(1).Worksheet.Name & "!" & varname(1).Address


 
' Add Accessibility Features
  If Accessibility.paccessibility Then
      Accessibility.reset_menu
      Accessibility.show_menu
  End If
  
 MsgBox "Bex Ran..... Woohoo" 'this is just to prove out that the code ran and how many times
  
  
  End Sub