我正在使用VBA开发Excel(2010)应用程序,并遇到一个问题,一旦查询完成执行,就不会调用AfterRefresh事件函数.
我无法找到许多有关如何在类模块中触发此事件功能的合适资源或文档.我决定使用类模块设计路线,而不是在收到对先前关于QueryTables的问题的回复(在此处找到Excel VBA AfterRefresh)后将事件处理程序放在工作表中.
这是我的类模块的代码,称为CQtEvents
Option Explicit
Private WithEvents mQryTble As Excel.QueryTable
Private msOldSql As String
' Properties
Public Property Set QryTble(ByVal QryTable As QueryTable): Set mQryTble = QryTable:
End Property
Public Property Get QryTble() As QueryTable: Set QryTble = mQryTble:
End Property
Public Property Let OldSql(ByVal sOldSql As String): msOldSql = sOldSql:
End Property
Public Property Get OldSql() As String: OldSql = msOldSql:
End Property
Private Sub Class_Initialize()
MsgBox "CQtEvents init"
End Sub
' Resets the query sql to the original unmodified sql statement
' This method is invoked when the Refresh thread finishes executing
Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
' Problem is here
' This function is never called :( Even if the query successfully runs
Me.QryTble.CommandText = Me.OldSql
End Sub
下面是创建此类实例的代码的快照,查找相关的QueryTable,然后调用Refresh
Option Explicit
Sub RefreshDataQuery()
'Dependencies: Microsoft Scripting Runtime (Tools->References) for Dictionary (HashTable) object
'From MGLOBALS
cacheSheetName = "Cache"
Set cacheSheet = Worksheets(cacheSheetName)
Dim querySheet As Worksheet
Dim interface As Worksheet
Dim classQtEvents As CQtEvents
Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")
Set classQtEvents = New CQtEvents
Dim qt As QueryTable
Dim qtDict As New Scripting.Dictionary
Set qtDict = UtilFunctions.CollectAllQueryTablesToDict
Set qt = qtDict.Item("Query from fred2")
''' Building SQL Query String '''
Dim sqlQueryString As String
sqlQueryString = qt.CommandText
Set classQtEvents.QryTble = qt
classQtEvents.OldSql = sqlQueryString ' Cache the original query string
QueryBuilder.BuildSQLQueryStringFromInterface interface, sqlQueryString
' Test message
MsgBox sqlQueryString
qt.CommandText = sqlQueryString
If Not qt Is Nothing Then
qt.Refresh
Else
' ... Error handling code here...
End If
''' CLEAN UP '''
' Free the dictionary
Set qtDict = Nothing
End Sub
我首先想到的可能是问题是通过值传递QueryTable.我不是最有经验的VBA开发人员,但我推断这会创建一个副本并在不相关的表上调用该事件.但是,情况并非如此,并且通过Reference传递也没有解决问题.
此外,当数据正确显示并刷新时,确认查询成功运行.
编辑
我将BeforeRefresh事件函数添加到CQtEvents类Module并确认一旦调用Refresh就调用此函数
Private Sub mQryTble_BeforeRefresh(Cancel As Boolean)
MsgBox "Start of BeforeRefresh"
End Sub
我如何改变这段代码从QTableModule的RefreshDataQuery()Sub例程获取我的QueryTable,以便在成功运行查询时调用AfterRefresh函数?