VBA Rubberducking

12:01 AM

RELOAD!

[IvenBach/AdventOfCode2020] 2 commits. 560 additions. 18 deletions.

[Minesweeper] Games Played: 116, Bombs Used: 70, Moves Performed: 16262, New Users: 13

12:58 AM

[rubberduck-vba/Rubberduck] akirasano starred us

13 hours later…

1:58 PM

@MathieuGuindon Sooooo..... the ODBC object doesn't expose an interface to use WithEvents. However, I was able to use the QueryTable associated with the ListObject where results are returned to Excel. I used the Worksheet_BeforeRightClick to cancel the Refresh of the QueryTable as follows:

'Class: "clsQT"

Option Explicit
Public WithEvents qt As QueryTable

Private Sub Class_Initialize()

    Set qt = ThisWorkbook.Worksheets("Data").ListObjects(1).QueryTable

End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    Cancel = True
End Sub

'Module: "Main":

Option Explicit
Private clsQT As clsQT

Public Sub init()
    Set clsQT = New clsQT
End Sub


'Worksheet: "SomeWorksheet"

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Main.init

What feels messy is that each time user right clicks I am getting a new class instance right? And I never tear down the class.

Any advice Ducks on better handling of what I have written? Basically, I need to ensure the right click on the querytable never leads to a refresh.

3 hours later…

Feeds

5:31 PM

We're worried that a regular whale will get into a 30-foot-deep ocean trench section and filter-feed on all the tiny whales.

2 hours later…

7:29 PM

@QHarr I don't know enough about what this class is or what you are doing with it. Can you simply reuse it like this?

    If clsQT Is Nothing Then Set clsQT = New clsQT

2 hours later…

9:19 PM

@HackSlash Hiya - Basically, if you use data > new query > and set up an odbc connection to pull data from SQL Server into a worksheet, you end up with a query table in the sheet. I wanted to prevent users from using the right click refresh in the table to refresh the table. Your idea makes sense.

And do I need to bother with teardown? I would place it upon Workbook_close but it would defo be out of scope at that point so seems a bit redundant.

Depends on how long you want it to live. If it should live during the whole time application is open then, yeah it should be torn down when you close application. Otherwise I would make a Dispose method and call it from a sensible place.

Does clsQT contain any external connections that need to be properly destroyed?

Links to unsafe objects?

Nope. What I have shown is the entirety of the class.

The connection is there permanently.

I just need to prevent one method of it being refreshed.

Oh, then it should die a graceful death

I can only intercept the event, as is odbcconnection, via the associated querytable which provides an interface

@HackSlash as in I just use the Class_Terminate() before Workbook_Close?

No, I think that will just happen automatically

9:30 PM

Aye. Ok. Thank you for your time.

You'll know if there is a problem when the host application stays in memory after you close it.

Lol...... that's just the sort of reason I came here asking...... nightmares!

9:52 PM

[rubberduck-vba/examples] pchemguy opened issue #7: SecureADODB: Existence of IDbConnection_CreateCommand appears counterintuitive

> What is the idea behind the IDbConnection_CreateCommand interface method?
IDbCommandFactory takes an IDbConnection interface and returns an IDbCommand interface with the logical place for initialization of the object behind it being the IDbCommandBase_CreateCommand interface. However, IDbCommandBase_CreateCommand in turn calls IDbConnection_CreateCommand interface, which merely duplicates the Guards present in IDbCommandBase_CreateCommand and then sets basic command properties passed from ID