VBA Rubberducking

> This is something I've been after for a while too. I don't know if C# allows you to hook onto arbitrary COM events - e.g. does IDispatch expose VBA Class events in a way that lets C# enumerate and hook into them, so you can set up some EventLogger(source As IDispatch). I know VBA doesn't have any easy way of enumerating Events a class exposes, let alone adding handlers to them at runtime. If possible in C#, then a class that logs event args and timestamps / calling order would probably be s

Duga

2:37 PM

[rubberduck-vba/Rubberduck] bclothier added label typeinfo-processing to issue #5577: UnitTesting Events

[rubberduck-vba/Rubberduck] bclothier created comment on issue #5577: UnitTesting Events

> I see two possible solutions:

1) create a class module implementing the events with a `WithEvents` and use it as a SUT, executing `Verify` within the events of the class module, and having your test module call it.

2) Using type library API to create a connection point to the source interface representing the events. That requires a mock object, for which we need a mocking framework.

Duga

3:06 PM

[rubberduck-vba/Rubberduck] retailcoder closed issue #5577: UnitTesting Events

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #5577: UnitTesting Events

> I could have sworn I had written about "testing that an event was raised" in "how to unit test VBA code", ...but I can't find any mention of it (might be I removed it at one point).

The way it was setup felt a bit clunky (that's probably why I ended editing it out), but basically when your SUT is an event provider then you can have a `Private WithEvents SUT As Something`, and then some `WasSomeEventRaised As Boolean` state flag that your `SUT_SomeEvent` handler sets to `True` (and that your

[rubberduck-vba/Rubberduck] retailcoder reopened issue #5577: UnitTesting Events

[rubberduck-vba/Rubberduck] retailcoder edited comment on issue #5577: UnitTesting Events

> I could have sworn I had written about "testing that an event was raised" in "how to unit test VBA code", ...but I can't find any mention of it (might be I removed it at one point).

The way it was setup felt a bit clunky (that's probably why I ended editing it out), but basically when your SUT is an event provider then you can have a `Private WithEvents SUT As Something`, and then some `WasSomeEventRaised As Boolean` state flag that your `SUT_SomeEvent` handler sets to `True` (and that your

Mathieu Guindon

@Duga fat-fingered, sorry

Duga

[rubberduck-vba/Rubberduck] retailcoder edited comment on issue #5577: UnitTesting Events

> I could have sworn I had written about "testing that an event was raised" in "how to unit test VBA code", ...but I can't find any mention of it (might be I removed it at one point).

The way it was setup felt a bit clunky (that's probably why I ended editing it out), but basically when your SUT is an event provider then you can have a `Private WithEvents SUT As Something`, and then some `WasSomeEventRaised As Boolean` state flag that your `SUT_SomeEvent` handler sets to `True` (and that your

[rubberduck-vba/examples] retailcoder closed issue #6: Issues understanding the use of Interfaces in your Rubberduck website entry

this

3:37 PM

@Duga I had a lengthy but rambling reply typed but ...

FreeMan

One can comment on a closed issue...

this

Anyway, so I have this stupid problem - I'm making an Excel report via automation from Access. When I then present it to the user, Excel(?), Access(?) or Windows(?) refuse to act sanely and put the workbook in the front. I'm using SetForegroundWindow API and it works nicely in putting Excel in front.

However, it insist on showing the first spreadsheet that was opened, not the newly spreadsheet which is inexplicably behind those other windows on Excel. I tried Workbook.Activate but no dice. Anyone have the black magic incantation to smack some sense into Microsoft?

@FreeMan Indeed, one could, but I'd have to go and clean up my rambling gibberish into something decent.

FreeMan

@this meh...

ramble on my good man!

I would pay good money for a black magic incantation that smacks some sense into Microsoft! I'd even give up on #BlameMicrosoft!

this

IKR?

It's a little thing but when you're dying by 1,000 paper cuts....

FreeMan

(or 10,000)

Mathieu Guindon

3:55 PM

@this oh, I thought it was all sorted out already and you had replied on WP? Or it was another comment?

@this you have a reference to the worksheet you want to work with? Try sheet.Window.Activate

(or is it a Windows collection? I thinks it's a collection)

So sheet.Windows(1).Activate

this

I did reply via the WP, yes but that was a slightly differnt.

Mathieu Guindon

Feel free to comment on there regardless of issue status =)

this

Yeah, I ended up doing SetForegroundWindow sheet.Windows(1).hWnd instead of SetForegroundWindow app.hWnd. So it's me, not Microsoft, who's the idiot.

@MathieuGuindon Indeed but as Blaise Pascal once said, "I apologize for the length of the letter. I did not have time to shorten it."

Mathieu Guindon

lol!

@this merely a nudge ;-)

Seems every other Windows Update, a part of Excel dies

Like wth happened to MSForms.Control events?

this

are they even exposed? I thought they never were visible

I recall a old issue about us not knowing whether a MSForms.Textbox is also a MSForms.Control

Mathieu Guindon

4:06 PM

Yeah but now it's VBA that's no longer seeing it

this

checks still there in the OB

Mathieu Guindon

Yeah it's just weird

The assignment/cast from MSForms.TextBox to MSForms.Control fails at run-time

it 100% worked before

this

interesting

Mathieu Guindon

That's a wrench in my MVVM thingy

I want to be able to handle Exit for string formatting

this

works on my machine.... at least for now

Mathieu Guindon

4:10 PM

hm

this

does it happens to be a runtime-created control?

I recall a subtle difference between a control that's defined on a UF vs. one that got created at runtime.

Mathieu Guindon

Ah found it: twitter.com/rubberduckvba/status/1306640273037037569

Looks like OP deleted the SO question, I can't find it anymore

there's a link somewhere in the chat around Sept.17

I repro'd the RTE with both designer-created textboxes and in-memory late-bound test textbox instances

Argh I didn't post a link

Duga

7:41 PM

[rubberduck-vba/Rubberduck] Greedquest created comment on issue #5577: UnitTesting Events

> @retailcoder yes, and that's what I was suggesting as some _stringly generated template class_ - RD could do all the heavy lifting of generating source code for the Boolean toggling, event handling test classes

Or indeed instead of true/false, why not count events / store them as entries in a collection which captures the state of the parameters too, alongside a timestamp to order them. I wanted to test a buffered list that raised an event every `n` items for example, so `Assert.AreEqual 2

Duga

7:57 PM

[rubberduck-vba/Rubberduck] sancarn created comment on issue #5577: UnitTesting Events

> @retailcoder Yes I had a suspission this was the expected way currently.

> Using type library API to create a connection point to the source interface representing the events. That requires a mock object, for which we need a mocking framework.

@bclothier does the type library expose connection points? 😲 I had looked into it myself briefly but couldn't see where abouts connection points would be stored?

[rubberduck-vba/Rubberduck] sancarn edited comment on issue #5577: UnitTesting Events

> @retailcoder Yes I had a suspission this was the expected way currently.

> Using type library API to create a connection point to the source interface representing the events. That requires a mock object, for which we need a mocking framework.

@bclothier does the type library expose connection points? 😲 I had looked into it myself briefly but couldn't see where abouts connection points would be stored?

[rubberduck-vba/Rubberduck] sancarn edited comment on issue #5577: UnitTesting Events

> @retailcoder Yes I had a suspission this was the expected way currently.

> Using type library API to create a connection point to the source interface representing the events. That requires a mock object, for which we need a mocking framework.

@bclothier This is what I had hoped for! Does the type library expose connection points? 😲 I had looked into it myself briefly but couldn't see where abouts connection points would be stored?

[rubberduck-vba/Rubberduck] sancarn edited comment on issue #5577: UnitTesting Events

> @retailcoder Yes I had a suspission this was the current methodology.

> Using type library API to create a connection point to the source interface representing the events. That requires a mock object, for which we need a mocking framework.

@bclothier This is what I had hoped for! Does the type library expose connection points? 😲 I had looked into it myself briefly but couldn't see where abouts connection points would be stored?

Duga

8:13 PM

[rubberduck-vba/Rubberduck] bclothier created comment on issue #5577: UnitTesting Events

> No, we need the type library API in order to obtain the definition of source interface from VBA's class that sources events. The key is that we then use the IID (the GUID of the interface) to then create a connection point. See: IConnectionPointContainer::FindConnectionPoint and also [SafeEventedComWrapper](github.com/rubberduck-vba/Rubberduck/blob/next/…

Duga

8:30 PM

[rubberduck-vba/Rubberduck] RaydenX93 starred us

2

Quack Exchange

11:48 PM

-1

Q: Is there a way to speed up the following Excel VBA code that displays a 14 day weather forecast?

I am trying to see if anyone can help me in making this Excel VBA code a little faster in rendering the data from the weather site I am using. Maybe instead of getting all 14 days it includes in the site, could I get help in getting just 10 days? Any help is much appreciated. Thanks! Sub MiamiWe...

Transcript for